Xml Query Returns Incorrect Results In 11.1.0.X And 11.2.0.1.0 With Count(Distinct... (Doc ID 1112466.1)

Last updated on MAY 28, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms

In 11.1.0.x and 11.2.0.1.0, given this data:

test.xml:

<Parent>
  <Name>123</Name>
  <Child>
    <Name>xyz</Name>
  </Child>
  <Child>
    <Name>abc</Name>
  </Child>
  <Child>
    <Name>xyz</Name>
  </Child>
</Parent>

and test2.xml:

<Parent>
  <Name>456</Name>
  <Child>
    <Name>xyz</Name>
  </Child>
  <Child>
    <Name>mno</Name>
  </Child>
  <Child>
    <Name>abc</Name>
  </Child>
</Parent>

and this setup:

drop directory test_dir;
create directory test_dir as '<the path to where the xml data files are>';
grant read on directory test_dir to public;
drop user test cascade;
grant connect, resource, dba to test identified by test;
connect test/test
DECLARE
  doc varchar2(2000) :=
'<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb"
elementFormDefault="qualified" xdb:storeVarrayAsTable="true">
<xs:element name="Parent" xdb:defaultTable="PARENT">
<xs:complexType xdb:SQLType="Parent_T">
<xs:sequence>
<xs:element ref="Name"/>
<xs:element ref="Child" minOccurs="0" maxOccurs="unbounded"
xdb:SQLCollType="Child_VARRAY"
xdb:SQLName="Child_V"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Name" type="xs:string" xdb:defaultTable=""/>
<xs:element name="Child" xdb:defaultTable="">
<xs:complexType xdb:SQLType="Child_T">
<xs:sequence>
<xs:element ref="Name"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>';
BEGIN
  dbms_xmlschema.registerSchema('http://www.samplexml.com/Test.xsd',doc,true,true,false,true);
END;
/
insert into parent values
  (XMLType
     (bfilename
        ('TEST_DIR', 'test.xml'),
        nls_charset_id('AL32UTF8')
     )
  );
insert into parent values
  (XMLType
     (bfilename
        ('TEST_DIR', 'test2.xml'),
        nls_charset_id('AL32UTF8')
     )
  );
commit;

both of these queries:

select     y.name child,
           count
             (distinct extractValue
                (object_value,
                 'Parent/Name'
                )
             ) parent_count
  from     parent,
           XMLTABLE
             ('/Parent/Child/Name' passing object_value COLUMNS name varchar2(2000) path '.') y
  group by y.name;

and:

select     extractValue
             (value(y),
              'Child/Name'
             ) child,
           count
             (distinct extractValue
                (value(x),
                 'Parent/Name'
                )
             ) parent_count
  from     parent x,
           table
             (xmlsequence
                (extract
                   (value(x),
                    '/Parent/Child'
                   )
                )
             ) y
  group by extractValue
             (value(y),
              'Child/Name'
             );

return incorrect results in SQL*Plus. Both return:

CHILD      PARENT_COUNT
---------- ------------
abc                   1
mno                   1
xyz                   1

when they should return:

CHILD      PARENT_COUNT
---------- ------------
abc                   2
mno                   1
xyz                   2

This problem does not occur in 10.2.0.4.0.

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