13 Replies Latest reply: Apr 5, 2013 6:52 AM by Marco Gralike RSS

    Correlated subquery in SELECT clause against XMLTable produces wrong result

    Pollocks01
      select * from v$version
      
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE    11.2.0.3.0    Production
      TNS for Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      I already previously posted a related issue under XMLTable expression causing core dumps in some cases - there, I was getting core dumps. In the example below (similar - because it was generated dynamically using my meta-data driven framework), I now just get the wrong results:
      with xref as (select max(xref.data_object_id) as data_object_id, 
                                               xref.data_trans_id                     
                                          from tdata_trans_data_object_xref xref,
                                               tlstg_batch tlb
                                         where TLB.LSTG_BATCH_ID = :LSTG_BATCH_ID
                                           and tlb.data_trans_id = xref.data_trans_id
                                      group by xref.data_trans_id)
      select xml.CONTACT_EXT_REF_NO,
             xml.TRANS_REF_ID,
             xml.TITLE,
             (select nvl2(min(crm_ext_ref_no), 'U', 'I')
              from    tparty target 
              where   target.crm_ext_ref_no = xml.contact_ext_ref_no) as STG_ACTION_TYP,
             xml.RETAILER_ROLE_NO,
             xml.REL_TRANS_REF_ID,
             xml.LSTG_UPDATE_USER,
             xml.LSTG_UPDATE_DATE,
             :LSTG_CREATE_USER,
             xml.LSTG_CREATE_DATE,
             :LSTG_BATCH_ID,
             xml.LAST_NAME,
             xml.FIRST_NAME,
             xml.ERR_MSG 
      from tdata_object_version vdoc, 
           xref, 
           xmltable('for $i in /TransactionDocument/DistinctContacts/DistinctContact
                      return element row {
                                          $i/*[(not(name()="STG_ACTION_TYP") and @Common="Y") or @Lookup="Y"],
                                          element CONTACT_EXT_REF_NO {data($i/RetailerContactID)},
                                          element TITLE {data($i/SALUTATION)},
                                          element FIRST_NAME {data($i/FirstName)},
                                          element LAST_NAME {data($i/LastName)},
                                          element STG_ACTION_TYP {string("OracleSelectOverride")}
                                         }'
      passing vdoc.xml_content
      columns
      CONTACT_EXT_REF_NO VARCHAR2(255)    path './CONTACT_EXT_REF_NO'
      ,TRANS_REF_ID NUMBER(22,0)          path './TRANS_REF_ID'
      ,TITLE VARCHAR2(10)                 path './TITLE'
      ,STG_ACTION_TYP VARCHAR2(1)         path 'string("OracleSelectOverride")'
      ,RETAILER_ROLE_NO VARCHAR2(255)     path './RETAILER_ROLE_NO'
      ,REL_TRANS_REF_ID NUMBER(22,0)      path './REL_TRANS_REF_ID'
      ,LSTG_UPDATE_USER VARCHAR2(30)      path './LSTG_UPDATE_USER'
      ,LSTG_UPDATE_DATE DATE              path './LSTG_UPDATE_DATE'
      ,LSTG_CREATE_DATE DATE              path './LSTG_CREATE_DATE'
      ,LAST_NAME VARCHAR2(30)             path './LAST_NAME'
      ,FIRST_NAME VARCHAR2(30)            path './FIRST_NAME'
      ,ERR_MSG VARCHAR2(4000)             path './ERR_MSG'
      ) xml 
      where xref.data_object_id = VDOC.DATA_OBJECT_ID;
      Here, STG_ACTION_TYP comes back as 'U' when it should come back as 'I'. I can guarantee you that there is no matching record in "TPARTY". The actual XML parsing is just fine: it's just the correlated sub-query which is not executing right.

      This used to function as desired before I started 'passing' the XML from the 'tdata_object_version' table - I used to simply bind it in and the query was simply:
      select xml.CONTACT_EXT_REF_NO,
             xml.TRANS_REF_ID,
             xml.TITLE,
             (select nvl2(min(crm_ext_ref_no), 'U', 'I')
              from    tparty target 
              where   target.crm_ext_ref_no = xml.contact_ext_ref_no) as STG_ACTION_TYP,
             xml.RETAILER_ROLE_NO,
             xml.REL_TRANS_REF_ID,
             xml.LSTG_UPDATE_USER,
             xml.LSTG_UPDATE_DATE,
             :LSTG_CREATE_USER,
             xml.LSTG_CREATE_DATE,
             :LSTG_BATCH_ID,
             xml.LAST_NAME,
             xml.FIRST_NAME,
             xml.ERR_MSG 
      from xmltable('for $i in /TransactionDocument/DistinctContacts/DistinctContact
                      return element row {
                                          $i/*[(not(name()="STG_ACTION_TYP") and @Common="Y") or @Lookup="Y"],
                                          element CONTACT_EXT_REF_NO {data($i/RetailerContactID)},
                                          element TITLE {data($i/SALUTATION)},
                                          element FIRST_NAME {data($i/FirstName)},
                                          element LAST_NAME {data($i/LastName)},
                                          element STG_ACTION_TYP {string("OracleSelectOverride")}
                                         }'
      passing :v_xml
      columns
      CONTACT_EXT_REF_NO VARCHAR2(255)    path './CONTACT_EXT_REF_NO'
      ,TRANS_REF_ID NUMBER(22,0)          path './TRANS_REF_ID'
      ,TITLE VARCHAR2(10)                 path './TITLE'
      ,STG_ACTION_TYP VARCHAR2(1)         path 'string("OracleSelectOverride")'
      ,RETAILER_ROLE_NO VARCHAR2(255)     path './RETAILER_ROLE_NO'
      ,REL_TRANS_REF_ID NUMBER(22,0)      path './REL_TRANS_REF_ID'
      ,LSTG_UPDATE_USER VARCHAR2(30)      path './LSTG_UPDATE_USER'
      ,LSTG_UPDATE_DATE DATE              path './LSTG_UPDATE_DATE'
      ,LSTG_CREATE_DATE DATE              path './LSTG_CREATE_DATE'
      ,LAST_NAME VARCHAR2(30)             path './LAST_NAME'
      ,FIRST_NAME VARCHAR2(30)            path './FIRST_NAME'
      ,ERR_MSG VARCHAR2(4000)             path './ERR_MSG'
      ) xml;
      I had to change things to pull the XML from a table (with binary xmltype storage and xmlindex) for performance reasons.

      If I throw in a
       /*+ NO_QUERY_TRANSFORMATION */ 
      hint, then I do get a core dump......

      If I re-write the SQL as following, it works (but this isn't implementable in my meta-data driven framework):
      select nvl2(tp.crm_ext_ref_no, 'U', 'I') as STG_ACTION_TYP, 
             x.*
      from 
      (
      with xref as (select max(xref.data_object_id) as data_object_id, 
                                               xref.data_trans_id                     
                                          from tdata_trans_data_object_xref xref,
                                               tlstg_batch tlb
                                         where TLB.LSTG_BATCH_ID = :LSTG_BATCH_ID
                                           and tlb.data_trans_id = xref.data_trans_id
                                      group by xref.data_trans_id)
      select xml.CONTACT_EXT_REF_NO,
             xml.TRANS_REF_ID,
             xml.TITLE,
             xml.RETAILER_ROLE_NO,
             xml.REL_TRANS_REF_ID,
             xml.LSTG_UPDATE_USER,
             xml.LSTG_UPDATE_DATE,
             :LSTG_CREATE_USER,
             xml.LSTG_CREATE_DATE,
             :LSTG_BATCH_ID,
             xml.LAST_NAME,
             xml.FIRST_NAME,
             xml.ERR_MSG 
      from tdata_object_version vdoc, 
           xref, 
           xmltable('for $i in /TransactionDocument/DistinctContacts/DistinctContact
                      return element row {
                                          $i/*[(not(name()="STG_ACTION_TYP") and @Common="Y") or @Lookup="Y"],
                                          element CONTACT_EXT_REF_NO {data($i/RetailerContactID)},
                                          element TITLE {data($i/SALUTATION)},
                                          element FIRST_NAME {data($i/FirstName)},
                                          element LAST_NAME {data($i/LastName)},
                                          element STG_ACTION_TYP {string("OracleSelectOverride")}
                                         }'
      passing vdoc.xml_content
      columns
      CONTACT_EXT_REF_NO VARCHAR2(255)    path './CONTACT_EXT_REF_NO'
      ,TRANS_REF_ID NUMBER(22,0)          path './TRANS_REF_ID'
      ,TITLE VARCHAR2(10)                 path './TITLE'
      ,STG_ACTION_TYP VARCHAR2(1)         path 'string("OracleSelectOverride")'
      ,RETAILER_ROLE_NO VARCHAR2(255)     path './RETAILER_ROLE_NO'
      ,REL_TRANS_REF_ID NUMBER(22,0)      path './REL_TRANS_REF_ID'
      ,LSTG_UPDATE_USER VARCHAR2(30)      path './LSTG_UPDATE_USER'
      ,LSTG_UPDATE_DATE DATE              path './LSTG_UPDATE_DATE'
      ,LSTG_CREATE_DATE DATE              path './LSTG_CREATE_DATE'
      ,LAST_NAME VARCHAR2(30)             path './LAST_NAME'
      ,FIRST_NAME VARCHAR2(30)            path './FIRST_NAME'
      ,ERR_MSG VARCHAR2(4000)             path './ERR_MSG'
      ) xml 
      where xref.data_object_id = VDOC.DATA_OBJECT_ID
      ) x, tparty tp
      where tp.crm_ext_ref_no (+) = x.CONTACT_EXT_REF_NO;
      I can also work-around using an
      ora:view
      expression but I really want to avoid XQuery: My preference is to sue pure XPath.

      Incidentally, I know that the above XMLTable can be simplified to pure xpath but this also doesn't solve the problem:
      XMLTABLE ('/TransactionDocument/DistinctContacts/DistinctContact'
                  PASSING vdoc.xml_content
                  COLUMNS CONTACT_EXT_REF_NO VARCHAR2 (255)    PATH 'RetailerContactID',
                         TRANS_REF_ID NUMBER (22, 0)          PATH 'TRANS_REF_ID',
                         TITLE VARCHAR2 (10)                  PATH 'SALUTATION',
                         RETAILER_ROLE_NO VARCHAR2 (255)      PATH 'RETAILER_ROLE_NO',
                         REL_TRANS_REF_ID NUMBER (22, 0)      PATH 'REL_TRANS_REF_ID',
                         LSTG_UPDATE_USER VARCHAR2 (30)       PATH 'LSTG_UPDATE_USER',
                         LSTG_UPDATE_DATE DATE                PATH 'LSTG_UPDATE_DATE',
                         LSTG_CREATE_DATE DATE                PATH 'LSTG_CREATE_DATE',
                         LAST_NAME VARCHAR2 (30)              PATH 'LastName',
                         FIRST_NAME VARCHAR2 (30)             PATH 'FirstName',
                         ERR_MSG VARCHAR2 (4000)              PATH 'ERR_MSG')
      What's going on?

      Edited by: Pollocks01 on Apr 3, 2013 7:49 AM
        • 1. Re: Correlated subquery in SELECT clause against XMLTable produces wrong result
          Pollocks01
          If I drop the XMLIndex on TDATA_OBJECT_VERSION(XML_CONTENT), then the query behaves as expected.

          So, there's an issue with my XMLIndex?

          I created it as follows:
          create index idx_TDATA_OBJECT_VERSION_XML on TDATA_OBJECT_VERSION(XML_CONTENT) 
              indextype is xdb.xmlindex PARAMETERS ( 'PATHS ( INCLUDE (/)) 
                                                      PATH TABLE TDATA_OBJ_VER_XML_PATH
                                                      PATH ID INDEX   TDATA_OBJ_VER_XML_PATH_IDX1
                                                      ORDER KEY INDEX TDATA_OBJ_VER_XML_PATH_IDX2
                                                      VALUE INDEX     TDATA_OBJ_VER_XML_PATH_IDX3' );
                     
          alter index idx_TDATA_OBJECT_VERSION_XML parameters ('PATHS(INDEX_ALL_PATHS)');
          • 2. Re: Correlated subquery in SELECT clause against XMLTable produces wrong result
            Marco Gralike
            On first glance, I don't see why you would need the ALTER INDEX statement, this would be normally used in conjunction with the use of DBMS_XMLINDEX.
            If you index all values anyway you also could simplify the statement to
            create index idx_TDATA_OBJECT_VERSION_XML on TDATA_OBJECT_VERSION(XML_CONTENT) 
                indextype is xdb.xmlindex PARAMETERS ( 'PATH TABLE TDATA_OBJ_VER_XML_PATH
                                                        PATH ID INDEX   TDATA_OBJ_VER_XML_PATH_IDX1
                                                        ORDER KEY INDEX TDATA_OBJ_VER_XML_PATH_IDX2
                                                        VALUE INDEX     TDATA_OBJ_VER_XML_PATH_IDX3' );
            ...could you try if only that would work for you...?
            • 3. Re: Correlated subquery in SELECT clause against XMLTable produces wrong result
              Pollocks01
              Thanks Marco - I didn't know that the default is to index all paths, if that's what you're inferring.

              I dropped the XMLIndex and recreated it as you suggested without any PATHS parameter and no consequent ALTER INDEX command, but the same holds true:

              With an XMLIndex in place with all paths indexed, the correlated sub-query returns the wrong data.

              I verified by again dropping the XMLIndex and observed the correct results from the query.

              Any other thoughts?

              thanks,

              -P
              • 4. Re: Correlated subquery in SELECT clause against XMLTable produces wrong result
                odie_63
                Any other thoughts?
                Any material you can give us to reproduce the issue?
                • 5. Re: Correlated subquery in SELECT clause against XMLTable produces wrong result
                  Marco Gralike
                  With a "full blown" XMLIndex, that is using the default without any restrictions (path subsetting clause), you should get the correct answer.

                  I am guessing it is time to create a service request via support.oracle.com.
                  • 6. Re: Correlated subquery in SELECT clause against XMLTable produces wrong result
                    Pollocks01
                    Thanks "Usual Suspects" :-) I'm going to try to put together a simple test case to present to Oracle support because this really looks like a bug. Of course, again, I likely have to work around this.

                    I'll keep this posting open in case I find something out or this helps someone else.

                    @Mark Drake - any ideas? This seems to be a pretty fundamental bug - other customers have surely encountered this?

                    Thanks!
                    • 7. Re: Correlated subquery in SELECT clause against XMLTable produces wrong result
                      Pollocks01
                      I think that the following simple test case illustrates the issue:
                      --create table that will be source of a lookup via correlated sub query
                      create table tdata(data_id number, lookup_column varchar2(255));
                      
                      --seed the lookup table
                      insert into tdata(data_id, lookup_column)
                      values(1, 'ORACLE');
                      
                      insert into tdata(data_id, lookup_column)
                      values(2, 'XDB');
                      
                      --create a table from which we'll read our xml
                      create table txml(xml_id number, xml_content xmltype)
                      XMLTYPE COLUMN xml_content STORE AS BINARY XML;
                      
                      --put XMLIndex with default INDEX_ALL_PATHS onto the table with the xml
                      create index idx_txml on txml(XML_CONTENT) 
                          indextype is xdb.xmlindex PARAMETERS ( 'PATH TABLE TXML_PATH
                                                                  PATH ID INDEX   TXML_PATH_IDX1
                                                                  ORDER KEY INDEX TXML_PATH_IDX2
                                                                  VALUE INDEX     TXML_PATH_IDX3' );
                      
                      --seed some XML data                                            
                      insert into txml(xml_id, xml_content)
                      values (1, xmltype('<ROWSET>
                                              <ROW>
                                                  <E>ORACLE</E>
                                              </ROW>
                                              <ROW>
                                                  <E>XDB</E>
                                              </ROW>
                                              <ROW>
                                                  <E>SQL</E>
                                              </ROW>
                                          </ROWSET>'));
                                      
                      --query the XML doing lookups against tdata    
                      select x.*,
                             (select nvl2(min(data_id), 'Update', 'Insert')
                              from   tdata t
                              where  t.lookup_column = x.E) as TXN_TYP 
                      from txml,
                           xmltable('/ROWSET/ROW'
                                    passing txml.xml_content
                                    columns
                                    E varchar2(255) path './E'
                                   ) x
                      where txml.xml_id=1;     
                                                                                                  
                      E       TXN_TYP
                      -       -------
                      ORACLE  Update
                      XDB     Update
                      SQL     Update - WRONG - should come back as Insert because the value''s not matched. 
                      
                      
                      Plan shows the XMLIndex is used:
                      
                      Plan hash value: 1798004232
                       
                      ------------------------------------------------------------------------------------------------
                      | Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
                      ------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT              |                |     1 |  1547 |     3   (0)| 00:00:01 |
                      |   1 |  SORT GROUP BY                |                |     1 |  3524 |            |          |
                      |*  2 |   FILTER                      |                |       |       |            |          |
                      |*  3 |    TABLE ACCESS BY INDEX ROWID| TXML_PATH      |     1 |  3524 |     2   (0)| 00:00:01 |
                      |*  4 |     INDEX RANGE SCAN          | TXML_PATH_IDX1 |     2 |       |     1   (0)| 00:00:01 |
                      |   5 |  SORT AGGREGATE               |                |     1 |   142 |            |          |
                      |   6 |   TABLE ACCESS FULL           | TDATA          |     2 |   284 |     3   (0)| 00:00:01 |
                      |   7 |  NESTED LOOPS                 |                |     1 |  1547 |     3   (0)| 00:00:01 |
                      |*  8 |   TABLE ACCESS BY INDEX ROWID | TXML_PATH      |     1 |  1522 |     2   (0)| 00:00:01 |
                      |*  9 |    INDEX RANGE SCAN           | TXML_PATH_IDX1 |     1 |       |     1   (0)| 00:00:01 |
                      |* 10 |   TABLE ACCESS BY USER ROWID  | TXML           |     1 |    25 |     1   (0)| 00:00:01 |
                      ------------------------------------------------------------------------------------------------
                       
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                       
                         2 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
                         3 - filter("SYS_P3"."ORDER_KEY">:B1 AND "SYS_P3"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B
                                    2) AND SYS_ORDERKEY_DEPTH("SYS_P3"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                                    SYS_XMLI_LOC_ISNODE("SYS_P3"."LOCATOR")=1)
                         4 - access("SYS_P3"."PATHID"=HEXTORAW('2C14')  AND "SYS_P3"."RID"=:B1)
                         8 - filter(SYS_XMLI_LOC_ISNODE("SYS_P1"."LOCATOR")=1)
                         9 - access("SYS_P1"."PATHID"=HEXTORAW('5F8F') )
                        10 - filter("TXML"."XML_ID"=1)
                       
                      Note
                      -----
                         - dynamic sampling used for this statement (level=2)
                      
                      
                      --break down the stmt, just to make sure:
                      select x.*
                      from txml,
                           xmltable('/ROWSET/ROW'
                                    passing txml.xml_content
                                    columns
                                    E varchar2(255) path './E'
                                   ) x
                      where txml.xml_id=1;   
                      
                      E
                      -
                      ORACLE
                      XDB
                      SQL
                      
                      
                      select nvl2(min(data_id), 'Update', 'Insert')
                      from   tdata t
                      where  t.lookup_column = 'ORACLE';
                      
                      Update
                      
                      select nvl2(min(data_id), 'Update', 'Insert')
                      from   tdata t
                      where  t.lookup_column = 'XDB';
                      
                      Update
                      
                      select nvl2(min(data_id), 'Update', 'Insert')
                      from   tdata t
                      where  t.lookup_column = 'SQL';
                      
                      Insert - CORRECT, the value 'SQL' is not found in the tdata table
                      
                      
                      --does the query work when the XMLIndex has been dropped?
                      drop index idx_txml;
                      
                      select x.*,
                             (select nvl2(min(data_id), 'Update', 'Insert')
                              from   tdata t
                              where  t.lookup_column = x.E) as TXN_TYP 
                      from txml,
                           xmltable('/ROWSET/ROW'
                                    passing txml.xml_content
                                    columns
                                    E varchar2(255) path './E'
                                   ) x
                      where txml.xml_id=1; 
                      
                      E       TXN_TYP
                      -       -------
                      ORACLE  Update
                      XDB     Update
                      SQL     Insert - CORRECT
                      
                      Plan shows that XMLIndex is no longer used:
                      
                      Plan hash value: 633217268
                       
                      ----------------------------------------------------------------------------
                      | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
                      ----------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT   |       |  8168 |    15M|    32   (0)| 00:00:01 |
                      |   1 |  SORT AGGREGATE    |       |     1 |   142 |            |          |
                      |*  2 |   TABLE ACCESS FULL| TDATA |     1 |   142 |     3   (0)| 00:00:01 |
                      |   3 |  NESTED LOOPS      |       |  8168 |    15M|    32   (0)| 00:00:01 |
                      |*  4 |   TABLE ACCESS FULL| TXML  |     1 |  2015 |     3   (0)| 00:00:01 |
                      |   5 |   XPATH EVALUATION |       |       |       |            |          |
                      ----------------------------------------------------------------------------
                       
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                       
                         2 - filter("T"."LOOKUP_COLUMN"=CAST(:B1 AS varchar2(255) ))
                         4 - filter("TXML"."XML_ID"=1)
                       
                      Note
                      -----
                         - dynamic sampling used for this statement (level=2)
                      • 8. Re: Correlated subquery in SELECT clause against XMLTable produces wrong result
                        mdrake-Oracle
                        Bug 16603434

                        Please open an SR with Oracle Support to request a patch.
                        • 9. Re: Correlated subquery in SELECT clause against XMLTable produces wrong result
                          odie_63
                          A couple of workarounds in the meantime...

                          Looks like the filter predicate from X is never pushed into the correlated scalar subquery.
                          "Materializing" the XMLTable query block as an inline view seems to solve the issue :
                          SQL> REM: Using an OUTER JOIN
                          SQL> select x.*
                            2      , (
                            3          select nvl2(min(t.data_id),'U','I')
                            4          from tdata t
                            5          where t.lookup_column = x.E
                            6        ) TXN_TYP
                            7  from txml tt,
                            8       xmltable('/ROWSET/ROW'
                            9                passing tt.xml_content
                           10                columns
                           11                E varchar2(255) path './E'
                           12               )  (+) x
                           13  where tt.xml_id = 1 ;
                           
                          E                         TXN_TYP
                          ------------------------- -------
                          ORACLE                    U
                          XDB                       U
                          SQL                       I
                           
                          SQL> REM: NO_MERGE hint
                          SQL> select e
                            2      , (
                            3          select nvl2(min(data_id),'U','I')
                            4          from tdata t
                            5          where t.lookup_column = v.e
                            6        ) txn_typ
                            7  from (
                            8    select /*+ no_merge */ x.e
                            9    from txml tt,
                           10         xmltable('/ROWSET/ROW'
                           11                  passing tt.xml_content
                           12                  columns
                           13                  E varchar2(255) path './E'
                           14                 ) x
                           15    where tt.xml_id = 1
                           16  ) v ;
                           
                          E                         TXN_TYP
                          ------------------------- -------
                          ORACLE                    U
                          XDB                       U
                          SQL                       I
                           
                          SQL> 
                          SQL> select e
                            2       , case when exists ( select null
                            3                            from tdata t
                            4                            where t.lookup_column = v.e )
                            5              then 'U'
                            6              else 'I'
                            7         end txn_typ
                            8  from (
                            9    select /*+ no_merge */ x.e
                           10    from txml tt,
                           11         xmltable('/ROWSET/ROW'
                           12                  passing tt.xml_content
                           13                  columns
                           14                  E varchar2(255) path './E'
                           15                 ) x
                           16    where tt.xml_id = 1
                           17  ) v ;
                           
                          E                         TXN_TYP
                          ------------------------- -------
                          ORACLE                    U
                          XDB                       U
                          SQL                       I
                           
                          • 10. Re: Correlated subquery in SELECT clause against XMLTable produces wrong result
                            Marco Gralike
                            Mark / just double checking in case you haven't yet; In my version it works...
                            SQL> create user otn identified by otn account unlock;
                            
                            User created.
                            
                            SQL> grant dba, xdbadmin to otn;
                            
                            Grant succeeded.
                            
                            SQL> conn otn/otn
                            Connected.
                            
                            SQL> create table tdata(data_id number, lookup_column varchar2(255));
                            
                            Table created.
                            
                            SQL> insert into tdata(data_id, lookup_column) values(1, 'ORACLE');  
                            
                            1 row created.
                            
                            SQL> insert into tdata(data_id, lookup_column) values(2, 'XDB');  
                            
                            1 row created.
                            
                            SQL> create table txml(xml_id number, xml_content xmltype) XMLTYPE COLUMN xml_content STORE AS BINARY XML;  
                            
                            Table created.
                            
                            SQL> create index idx_txml on txml(XML_CONTENT)
                                indextype is xdb.xmlindex PARAMETERS ( 'PATH TABLE TXML_PATH
                                                                        PATH ID INDEX   TXML_PATH_IDX1
                                                                        ORDER KEY INDEX TXML_PATH_ID    X2
                                                                        VALUE INDEX     TXML_PATH_IDX3' );  
                            
                            Index created.
                            
                            SQL> insert into txml(xml_id, xml_content)
                               values (1, xmltype('<ROWSET>
                                                    <ROW>
                                                        <E>ORACLE</E>
                                                    </ROW>
                                                    <ROW>
                                                        <E>XDB</E>
                                               </ROW>
                                                    <ROW>
                                                        <E>SQL</E>
                                                    </ROW>
                                                </ROWSET>'));
                                           
                            1 row created.
                            
                            
                            SQL> select x.*,
                                   (select nvl2(min(data_id), 'Update', 'Insert')
                                    from   tdata t
                                    where  t.lookup_column = x.E) as TXN_TYP
                                 from txml,
                                 xmltable('/ROWSET/ROW'
                                          passing txml.xml_content
                                          columns
                                          E varchar2(255) path './E'
                                         ) x
                            where txml.xml_id=1;                   
                            
                            E
                            --------------------------------------------------------------------------------
                            TXN_TY
                            ------
                            ORACLE
                            Update
                            
                            XDB
                            Update
                            
                            SQL
                            Insert
                            
                            SQL> conn / as sysdba
                            Connected.
                            
                            SQL> drop user otn cascade;
                            
                            User dropped.
                            Edited by: Marco Gralike on Apr 5, 2013 1:44 AM
                            • 11. Re: Correlated subquery in SELECT clause against XMLTable produces wrong result
                              Pollocks01
                              @Marco: What version are you on? Did you check the explain plans to verify that the index is being used?

                              Thanks!

                              -P
                              • 12. Re: Correlated subquery in SELECT clause against XMLTable produces wrong result
                                Pollocks01
                                @Odie_63: As usual, THANKS for your help here. I tried rewriting the query in many different ways including several hints for join order, no_rewrite, no_query_transformation etc. I have never used NO_MERGE before - this is a really useful one!

                                Both approaches you suggest work in my env and both are do-able in my meta-data driven framework. I must admit that I don't really understand the use of an outer join here, but I'll give that some more thought :-)

                                EDIT So the use of the outer join doesn't work for me here because it results in an empty rowset from the xmltable where the xpath/xquery yields no results.

                                EDIT - to illustrate my above comment:
                                BUT, in case that path not found in XML we don''t get desired result:
                                
                                insert into txml(xml_id, xml_content)
                                values (2, xmltype('<DATA>
                                                        <ROW>
                                                            <E>ORACLE</E>
                                                        </ROW>
                                                        <ROW>
                                                            <E>XDB</E>
                                                        </ROW>
                                                        <ROW>
                                                            <E>SQL</E>
                                                        </ROW>
                                                    </DATA>'));
                                                    
                                select x.*,
                                       (select nvl2(min(data_id), 'Update', 'Insert')
                                        from   tdata t
                                        where  t.lookup_column = x.E) as TXN_TYP 
                                from txml,
                                     xmltable('/ROWSET/ROW'
                                              passing txml.xml_content
                                              columns
                                              E varchar2(255) path './E'
                                             ) (+) x
                                where txml.xml_id=2;                    
                                
                                E   TXN_TYP
                                -   -------
                                    Insert
                                Above, 'E' from the XML is empty. So, I think I have to go with the NO_MERGE hint approach.

                                Another reason to say "Thank Goodness it's Friday!" - I've been struggling with this all week.

                                -P

                                Edited by: Pollocks01 on Apr 5, 2013 6:31 AM

                                Edited by: Pollocks01 on Apr 5, 2013 6:56 AM
                                • 13. Re: Correlated subquery in SELECT clause against XMLTable produces wrong result
                                  Marco Gralike
                                  The latest, very latest (greatest) version although maybe one behind development.

                                  :-)