XMLExist With Upper-case function with where clause with 'OR' operator Returns Wrong results

(Doc ID 1562613.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

When the XMLExist is used on a where clause with 'OR' operator and upper-case function, the result set returns additional wrong rows

Changes

 [celclnx15]/testcases/rsaif> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 5 13:22:42 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@S112W6 > create user xmltest identified by xmltest default tablespace
users temporary tablespace temp;

User created.

SYS@S112W6 > grant dba to xmltest;
Grant succeeded.
SYS@S112W6 > connect xmltest/xmltest
Connected.
XMLTEST@S112W6 > declare
   doc varchar2(1000) := '
  ';
  begin
 
dbms_xmlschema.registerSchema('http://www.anycompanyname.com/upcaseBug.xsd', doc);
  end;
  /

PL/SQL procedure successfully completed.

XMLTEST@S112W6 > CREATE TABLE upcaseBugTable OF XMLTYPE
   XMLSCHEMA "http://www.anycompanyname.com/upcaseBug.xsd" ELEMENT
"upcaseBug";

Table created.

XMLTEST@S112W6 > insert into upcaseBugTable values (
xmltype('
       ABCD
      '));
1 row created.

XMLTEST@S112W6 > insert into upcaseBugTable values (
xmltype('
       EFGH
      ')); 
1 row created.

XMLTEST@S112W6 > select XMLQuery('/upcaseBug/Shelf/text()' PASSING
OBJECT_VALUE RETURNING CONTENT) Shelf,
XMLQuery('/upcaseBug/OldShelf/text()' PASSING OBJECT_VALUE
RETURNING CONTENT) OldShelf
from upcaseBugTable
where XMLExists('/upcaseBug/OldShelf[contains(upper-case(.),"ABCD")]' PASSING
OBJECT_VALUE)
OR
XMLExists('/upcaseBug/Shelf[contains(upper-case(.),"ABCD")]'
PASSING OBJECT_VALUE);

SHELF
------------------------------------------------------------------------------
--
OLDSHELF
------------------------------------------------------------------------------
--
ABCD


EFGH

XMLTEST@S112W6 > select XMLQuery('/upcaseBug/Shelf/text()' PASSING
OBJECT_VALUE RETURNING CONTENT) Shelf,
XMLQuery('/upcaseBug/OldShelf/text()' PASSING OBJECT_VALUE RETURNING
CONTENT) OldShelf
from upcaseBugTable
where XMLExists('/upcaseBug/Shelf[contains(upper-case(.),"ABCD")]' PASSING
OBJECT_VALUE)

    OR
  XMLExists('/upcaseBug/OldShelf[contains(upper-case(.),"ABCD")]' PASSING
OBJECT_VALUE);

SHELF
------------------------------------------------------------------------------
OLDSHELF
------------------------------------------------------------------------------
--
ABCD
--

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