Wrong Results When Using SYN(keyword) WITHIN <section> and SYN(keyword)

(Doc ID 1594398.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Text - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

When using 'SYN(keyword) WITHIN <section>' and 'SYN(keyword)' the query returns wrong results

select count(*)
from searchable_cvs_tmp
where
contains (composite_cv,
'(($(SYN({manager}) ,
SYN({aix})) WITHIN ROLES)
AND $(SYN({aix})))
AND (((((CTGJ,CTAV,CTNE,CTSR,CTOS,CTSX,CTEE,CTIB,CTAP,CTLJ,
CTUC,CTLE,CTCL,CTMK,CTHR,CTOI,CTCS,CTCO,CTDB,CTER,CTRJ,
CTPS,CTBJ,CTJX,CTYJ,CTOR) WITHIN COUNTRY)
AND (YYY WITHIN SEARCHABLE) AND (7D WITHIN POSTED)
AND ((YYY) WITHIN PERMIT))*10)*10)*10')>0;

  COUNT(*)
----------
         0


select count(*)
from searchable_cvs_tmp
where
contains (composite_cv,
'(($(SYN({manager})) WITHIN ROLES)
AND $(SYN({aix})))
AND (((((CTGJ,CTAV,CTNE,CTSR,CTOS,CTSX,CTEE,CTIB,CTAP,CTLJ,
CTUC,CTLE,CTCL,CTMK,CTHR,CTOI,CTCS,CTCO,CTDB,CTER,CTRJ,
CTPS,CTBJ,CTJX,CTYJ,CTOR) WITHIN COUNTRY)
AND (YYY WITHIN SEARCHABLE) AND (7D WITHIN POSTED)
AND ((YYY) WITHIN PERMIT))*10)*10)*10')>0;

  COUNT(*)
----------
        65

The first query should return at least the 65 rows returned by the second query


Using the SYN({aix}) WITHIN ROLES and SYN({aix}) clauses causes the deduplicator to be invoked.  
This leads to a failure of the query.  
Workaround:
Try this, simply upper case the first occurance of "aix". By doing this, the two SYN clauses will not match.  This will cause your query to run successfully.


select count(*)
from searchable_cvs_tmp
where
contains (composite_cv,
'(($(SYN({manager}) ,
SYN({Aix})) WITHIN ROLES)  <<<--- Notice we changed from aix to Aix
AND $(SYN({aix})))
AND (((((CTGJ,CTAV,CTNE,CTSR,CTOS,CTSX,CTEE,CTIB,CTAP,CTLJ,
CTUC,CTLE,CTCL,CTMK,CTHR,CTOI,CTCS,CTCO,CTDB,CTER,CTRJ,
CTPS,CTBJ,CTJX,CTYJ,CTOR) WITHIN COUNTRY)
AND (YYY WITHIN SEARCHABLE) AND (7D WITHIN POSTED)
AND ((YYY) WITHIN PERMIT))*10)*10)*10')>0;

  COUNT(*)
----------
        65

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms