My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.