Strange number comparison behaviour in Oracle 11.2.0.3.0 with existsNode

Question

I'm trying to select all rows of a table where one column contains a specific xml-value. It is working fine when I run the query against my local database or the one of our internal testservers. However, on the customers testserver, the query does not return any rows.

SELECT * FROM product WHERE existsnode(...) = 1

I found out that if I quote the comparison value, it works on all databases:

SELECT * FROM product WHERE existsnode(...) = '1'

I'd like to understand why this happens and what a good solution for this issue would be. The database version is 11.2.0.3.0 on all systems.

EDIT: I did some further research and found out that the returned datatype is actually a number, as stated in the Oracle documentation.

SELECT dump(existsNode(...)) FROM product;

Returns "Typ=2, Len=1: 128" on all databases.

However, I have some double quotes inside of the existsnode query string, and these double quotes seem to get lost on the databases with which I am having the strange comparison problem:

SELECT existsnode(xmltype(attributes), '/attrs/attr[@name="SomeFlag"]') FROM product;

On the databases where the query works, I get the following result as the column name:

EXISTSNODE(XMLTYPE(ATTRIBUTES), '/ATTRS/ATTR[@NAME="SOMEFLAG"]')

On the other databases I get

EXISTSNODE(XMLTYPE(ATTRIBUTES), '/ATTRS/ATTR[@NAME=SOMEFLAG]')

Answer 1

Check out document number, Doc ID 14087914.8 (Bug Number 14087914) on support.oracle.com.

Per the document, try to following to see if the results no produce what you are expecting.

alter session set "_fix_control"='9569678:off

Your other option, involves upgrading to 11.2.0.4.