Database Software

I'm experimenting with XQUF on 11.2.0.3 and found the following issue with Object-Relational storage (and binary XML too) :

Test case :

set echo on
set long 10000
select * from v$version;

begin
 dbms_xmlschema.deleteSchema('workbook.xsd', dbms_xmlschema.DELETE_CASCADE);
end;
/

begin
 dbms_xmlschema.registerSchema(
   schemaURL => 'workbook.xsd'
 , schemaDoc => '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
  <xs:complexType name="worksheetType" xdb:SQLType="T_WORKSHEET">
    <xs:attribute name="sheetName" type="xs:string"/>
    <xs:attribute name="sheetId" type="xs:integer"/>
  </xs:complexType>
  <xs:complexType name="workbookType" xdb:SQLType="T_WORKBOOK" xdb:maintainDOM="false">
    <xs:sequence>
      <xs:element name="worksheet" type="worksheetType" minOccurs="1" maxOccurs="unbounded" xdb:SQLCollType="T_WORKSHEET_COLL"/>
    </xs:sequence>
  </xs:complexType>
  <xs:element name="workbook" type="workbookType" xdb:defaultTable="WORKBOOK_XML"/>
</xs:schema>'
 , local => true
 , genTypes => true
 , genTables => true
 , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
 );
end;
/

begin
 dbms_xmlstorage_manage.renameCollectionTable(
   tab_name => 'WORKBOOK_XML'
 , xpath => '"XMLDATA"."worksheet"'
 , collection_table_name => 'WORKSHEET_XML'
 );
end;
/

insert into workbook_xml values(
 xmltype('<workbook>
 <worksheet sheetName="Sheet1" sheetId="1"/>
 <worksheet sheetName="Sheet2" sheetId="2"/>
 <worksheet sheetName="Sheet3" sheetId="3"/>
</workbook>')
);

commit;

col sheetName format a30
col sheetId format 99

select x.* 
from workbook_xml t
   , xmltable( '/workbook/worksheet' passing t.object_value
               columns sheetName varchar2(4000) path '@sheetName'
                     , sheetId   number         path '@sheetId' ) x
;

update workbook_xml t
set t.object_value = xmlquery(
'copy $d := . 
 modify ( 
  for $i in $d/workbook/worksheet
  return replace value of node $i/@sheetName with concat("NEW_", $i/@sheetName)
 ) 
return $d'
passing t.object_value
returning content
);

select xmlquery(
'copy $d := . 
 modify ( 
  for $i in $d/workbook/worksheet
  return replace value of node $i/@sheetName with concat("NEW_", $i/@sheetName) 
 ) 
return $d'
 passing t.object_value
 returning content
) as modified_doc
from workbook_xml t
;

select /*+ no_xml_query_rewrite */ xmlquery(
'copy $d := . 
 modify ( 
  for $i in $d/workbook/worksheet
  return replace value of node $i/@sheetName with concat("NEW_", $i/@sheetName) 
 ) 
return $d'
 passing t.object_value
 returning content
) as modified_doc
from workbook_xml t
;

Output :

SQL> @xquftest.sql
SQL> set echo on
SQL> set long 10000
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

5 rows selected.

SQL>
SQL> begin
  2   dbms_xmlschema.deleteSchema('workbook.xsd', dbms_xmlschema.DELETE_CASCADE);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2   dbms_xmlschema.registerSchema(
  3     schemaURL => 'workbook.xsd'
  4   , schemaDoc => '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
  5    <xs:complexType name="worksheetType" xdb:SQLType="T_WORKSHEET">
  6      <xs:attribute name="sheetName" type="xs:string"/>
  7      <xs:attribute name="sheetId" type="xs:integer"/>
  8    </xs:complexType>
  9    <xs:complexType name="workbookType" xdb:SQLType="T_WORKBOOK" xdb:maintainDOM="false">
 10      <xs:sequence>
 11        <xs:element name="worksheet" type="worksheetType" minOccurs="1" maxOccurs="unbounded" xdb:SQLCollType="T_WORKSHEET_CO
LL"/>
 12      </xs:sequence>
 13    </xs:complexType>
 14    <xs:element name="workbook" type="workbookType" xdb:defaultTable="WORKBOOK_XML"/>
 15  </xs:schema>'
 16   , local => true
 17   , genTypes => true
 18   , genTables => true
 19   , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
 20   );
 21  end;
 22  /

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2   dbms_xmlstorage_manage.renameCollectionTable(
  3     tab_name => 'WORKBOOK_XML'
  4   , xpath => '"XMLDATA"."worksheet"'
  5   , collection_table_name => 'WORKSHEET_XML'
  6   );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> insert into workbook_xml values(
  2   xmltype('<workbook>
  3   <worksheet sheetName="Sheet1" sheetId="1"/>
  4   <worksheet sheetName="Sheet2" sheetId="2"/>
  5   <worksheet sheetName="Sheet3" sheetId="3"/>
  6  </workbook>')
  7  );

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> col sheetName format a30
SQL> col sheetId format 99
SQL>
SQL> select x.*
  2  from workbook_xml t
  3     , xmltable( '/workbook/worksheet' passing t.object_value
  4                 columns sheetName varchar2(4000) path '@sheetName'
  5                       , sheetId   number         path '@sheetId' ) x
  6  ;

SHEETNAME                      SHEETID
------------------------------ -------
Sheet1                               1
Sheet2                               2
Sheet3                               3

3 rows selected.

SQL>
SQL> update workbook_xml t
  2  set t.object_value = xmlquery(
  3  'copy $d := .
  4   modify (
  5    for $i in $d/workbook/worksheet
  6    return replace value of node $i/@sheetName with concat("NEW_", $i/@sheetName)
  7   )
  8  return $d'
  9  passing t.object_value
 10  returning content
 11  );
update workbook_xml t
 *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row


SQL>
SQL> select xmlquery(
  2  'copy $d := .
  3   modify (
  4    for $i in $d/workbook/worksheet
  5    return replace value of node $i/@sheetName with concat("NEW_", $i/@sheetName)
  6   )
  7  return $d'
  8   passing t.object_value
  9   returning content
 10  ) as modified_doc
 11  from workbook_xml t
 12  ;
ERROR:
ORA-01427: single-row subquery returns more than one row



no rows selected

SQL>
SQL> select /*+ no_xml_query_rewrite */ xmlquery(
  2  'copy $d := .
  3   modify (
  4    for $i in $d/workbook/worksheet
  5    return replace value of node $i/@sheetName with concat("NEW_", $i/@sheetName)
  6   )
  7  return $d'
  8   passing t.object_value
  9   returning content
 10  ) as modified_doc
 11  from workbook_xml t
 12  ;

MODIFIED_DOC
--------------------------------------------------------------------------------
<workbook><worksheet sheetName="NEW_Sheet1" sheetId="1"/><worksheet sheetName="N
EW_Sheet2" sheetId="2"/><worksheet sheetName="NEW_Sheet3" sheetId="3"/></workboo
k>


1 row selected.

So as you can see, the error occurs when we try to update more than one node at a time (unless we force functional evaluation).
In my understanding, I would have expected the optimizer to rewrite the query like this :

SQL> set autotrace on explain
SQL> set lines 120
SQL> update table(
  2    select t.xmldata."worksheet"
  3    from workbook_xml t
  4  )
  5  set "sheetName" = 'NEW_' || "sheetName"
  6  ;

3 rows updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 3126950886

-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |               |     1 |  2012 |     5   (0)| 00:00:01 |
|   1 |  UPDATE             | WORKSHEET_XML |       |       |            |          |
|*  2 |   INDEX RANGE SCAN  | SYS_C0011990  |     1 |  2012 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| WORKBOOK_XML  |     1 |    10 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("WORKSHEET_XML"."NESTED_TABLE_ID"= SYS_OP_ENFORCE_NOT_NULL$
              (SELECT "T"."SYS_NC0000800009$" FROM "WORKBOOK_XML" "T"))

Note
-----
   - dynamic sampling used for this statement (level=2)

Is there something to do to have the optimizer rewrite the query to use the underlying storage structure, or are we just looking at an unsupported feature?