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;

Hello Ko. I have attempted to look into this behaviour for you. I
pasted your script verbatim into 10g release 10.1.0.2.0 personal on
Windows XP. The behaviour you observe in 9i is not reproducible in 10g.
Full details are included in the trace below. I suggest you look at
metalink for the bug fix.
Kind regards
Mike

TESSELLA Michael.OShea@tessella.com
__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
http://www.tessella.com Registered in England No. 1466429



SQL>
SQL> drop table bug;

Table dropped.

SQL>
SQL> create table bug ( doc_id NUMBER, data_char xmltype );

Table created.

SQL> insert into bug values( 1, XMLType(
2 '<document>
3 <ti>Test 1</ti>
4 <py>2002</py>
5 </document>' ) );

1 row created.

SQL>
SQL> insert into bug values( 2, XMLType(
2 '<document>
3 <ti>Test 2</ti>
4 <py>2002</py>
5 </document>' ) );

1 row created.

SQL>
SQL> insert into bug values( 3, XMLType(
2 '<document>
3 <ti>Test 3</ti>
4 <py>2003</py>
5 </document>' ) );

1 row created.

SQL>
SQL> insert into bug values( 4, XMLType(
2 '<document>
3 <ti>Test 4</ti>
4 <py>2002</py>
5 </document>' ) );

1 row created.

SQL>
SQL> insert into bug values( 5, XMLType(
2 '<document>
3 <ti>Test 5</ti>
4 <py>2003</py>
5 </document>' ) );

1 row created.

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

Index created.

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

COUNT(DOC_ID)

-------------

3


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

COUNT(DOC_ID)

-------------

3


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

Index created.

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

COUNT(DOC_ID)

-------------

3


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

COUNT(DOC_ID)

-------------

3


SQL> SELECT *
2 FROM V$VERSION;

BANNER

----------------------------------------------------------------

Personal Oracle Database 10g Release 10.1.0.2.0 - Production

PL/SQL Release 10.1.0.2.0 - Production

CORE 10.1.0.2.0 Production

TNS for 32-bit Windows: Version 10.1.0.2.0 - Production

NLSRTL Version 10.1.0.2.0 - Production


SQL> SPOOL OFF



Ko van der Sloot wrote:
> 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;

Michael O'Shea wrote:
> Hello Ko. I have attempted to look into this behaviour for you. I
> pasted your script verbatim into 10g release 10.1.0.2.0 personal on
> Windows XP. The behaviour you observe in 9i is not reproducible in 10g.

Thanx. Good to hear.
This gives me more ammunition to force an upgrade to 10g
I'm just a user, fighting the DBA

> Full details are included in the trace below. I suggest you look at
> metalink for the bug fix.

I will contact my DBA about this.
But as you might know, big wheels keep on turning slowly...

> Kind regards
> Mike

Tanks again
Ko