Problem with XMLType indexing. Please confirm

From: Ko van der Sloot <Ko.vanderSloot_at_uvt.nl>
Date: Mon, 01 Aug 2005 14:13:38 +0200
Message-ID: <dcl3li$r6e$1@troll.uvt.nl>

Hello,
i posted this a few months ago on comp.databases.oracle.misc, but got no reaction at all. So here is a retry on comp.databases.oracle.server

Below you'l find a self contained script, which, after adding a ctxsys.CTXXPATH index, produces very strange results. i.c: (A OR B) gives 4 hits where (B OR A) gives 5 hits.

It's not clear to me if this is due to some wrong ideas about XMLType or a misbeahaving of Oracle.
I would be very pleased if some of your good people try the script on various Orcacle versions and report their findings.

Our Orcale version:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

We get the following counts out of this script: 3, 3, 3, 5
The last number is unbelievable WRONG!

thanks for your help
Ko vd Sloot
Tilburg University

here is the script: (maybe needs some reformating)

---

drop table bug;

create table bug ( doc_id NUMBER, data_char xmltype ); insert into bug values( 1, XMLType(
'<document>

<ti>Test 1</ti>
<py>2002</py>

 </document>' ) );

insert into bug values( 2, XMLType(
'<document>

<ti>Test 2</ti>
<py>2002</py>

 </document>' ) );

insert into bug values( 3, XMLType(
'<document>

<ti>Test 3</ti>
<py>2003</py>

 </document>' ) );

insert into bug values( 4, XMLType(
'<document>

<ti>Test 4</ti>
<py>2002</py>

 </document>' ) );

insert into bug values( 5, XMLType(
'<document>

<ti>Test 5</ti>
<py>2003</py>

 </document>' ) );

create index my_idx on bug(data_char) indextype is ctxsys.context;

select count( doc_id ) from bug where
existsNode(data_char,'//py[.>"2002"]') > 0 OR contains(data_char,'2 inpath(//ti)' ) > 0;

select count( doc_id ) from bug where contains(data_char,'2 inpath(//ti)' ) > 0 OR existsNode(data_char,'//py[.>"2002"]') > 0;

create index my_x_idx on bug(data_char) indextype is ctxsys.CTXXPATH;

select count( doc_id ) from bug where
existsNode(data_char,'//py[.>"2002"]') > 0 OR contains(data_char,'2 inpath(//ti)' ) > 0;

select count( doc_id ) from bug where contains(data_char,'2 inpath(//ti)' ) > 0 OR existsNode(data_char,'//py[.>"2002"]') > 0; Received on Mon Aug 01 2005 - 07:13:38 CDT