Counting Hits with a Structured Predicate containing LIKE in Oracle returns wrong results

Question

I'm trying to use Oracle Text to perform a query where i'm searching for any OS name that starts with "AIX" and also contains the substring 'XYZ'. Somehow this formulation of the query results in 0 results, even though if I break it up into separate parts there are clearly results:

SELECT 
  COUNT(*) AS cnt
FROM 
  package_master 
WHERE 
  CONTAINS(doc,'%XYZ%',1)>0 AND UPPER(os) LIKE 'AIX%'

This returns 0 results.

But curiously if I modify it to:

SELECT 
  COUNT(*) AS cnt
FROM 
  package_master 
WHERE 
  CONTAINS(doc,'%XYZ%',1)>0 AND UPPER(os)='AIX 6.1.0.0'

it returns results, but of course only those that pertain to AIX 6.1.0.0...

I'm using Oracle 11g2.

Is it possible there is a bug in the ORACLE TEXT package?

I guess I can break into two INTERSECT queries and do a COUNT(*) of the results, but that complicates matters and seems to run for a long while... I would like to use the simple 'AND' form.... If possible...

This works but runs for a long while and is unnecessarily complex:

SELECT count(*) FROM (
SELECT 
  host, package_name
FROM 
  package_master 
WHERE 
  CONTAINS(doc,'%XYZ%',1)>0
INTERSECT 
SELECT 
  host, package_name
FROM 
  package_master 
WHERE 
  UPPER(os) LIKE 'AIX%'
)

Also note if I try to do an EXPLAIN on the original query, it's as though the "LIKE" portion of the query is not even executed at all...! This is rather bizarre:

Plan hash value: 1075233541


    ----------------------------------------------------------------------------------------
    | Id  | Operation        | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |                     |     1 |   238 |    55   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE  |                     |     1 |   238 |            |          |
    |*  2 |   DOMAIN INDEX   | PACKAGE_MASTER_IDX7 |   100 | 23800 |    55   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------

    -


    Predicate Information (identified by operation id):
    ---------------------------------------------------

       2 - access("CTXSYS"."CONTAINS"("DOC",'%XYZ%',1)>0)
           filter(UPPER("OS") LIKE 'AIX%')

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit. NLS_COMP value is BINARY, NLS_SORT value is null. The table is only loaded once so it is not an issue with synching the index.

It should be noted that if I replace COUNT(*) with the list of columns, the query runs correctly and returns the correct # of rows... — user3892260, Oct 26 '14 at 18:44
In other words: SELECT host, package_name FROM package_master WHERE CONTAINS(doc,'%XYZ%',1)>0 AND UPPER(os) LIKE 'AIX%' return the expected 770 rows.... — user3892260, Oct 26 '14 at 18:45
have you seen this? http://stackoverflow.com/questions/7358137/oracle-full-text-search-with-condition — Multisync, Oct 26 '14 at 19:00
Also, please add your settings for `NLS_COMP` and `NLS_SORT` — Frank Schmitt, Oct 26 '14 at 21:24
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production — user3892260, Oct 26 '14 at 22:11

score 1 · Accepted Answer · answered Nov 10 '14 at 18:46

In a bizarre twist, I am no longer seeing this issue! I cannot reproduce the 0 result behavior and now if I perform an EXPLAIN plan, I see that the optimizer is working properly. Not much has changed. Maybe Oracle just needed a restart... I guess I will close out this question, even though there was no satisfactory reason/explanation as to how the issue resolved itself.

score 0 · Answer 2 · answered Nov 13 '14 at 20:31

Actually the issue came back...

My counts are once again are showing incorrect values...

The oracle optimizer once again decided to ignore one of the conditions of the WHERE clause. I ran the EXPLAIN PLAN and confirmed that it was ignoring one half of the where clause, which looks like a bug to me.

I had decided to rewrite all the queries such that the CONTAINS() portion is in one area and the rest of the filtering is done in a separate place.

It appears to be holding up.

The new query format that I picked that seems to be working is:

WITH x AS (
  SELECT 
   * 
  FROM 
   package_master_naught 
  WHERE 
   CONTAINS(p_n_c,'%XYZ%',1)>0
) 
SELECT 
  COUNT(*) AS cnt
FROM 
  x 
WHERE 
  UPPER(os) LIKE 'AIX%';

Counting Hits with a Structured Predicate containing LIKE in Oracle returns wrong results

2 Answers2