My Oracle Support Banner

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

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
  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.