Why can I cross join XMLTABLE? Why don't I have to cross apply?

Question

I saw examples using XMLTABLE to sort comma-separated strings in Oracle. While we should never have to do this with a proper database design, it made me curious and there is one thing I don't understand at all:

Why am I allowed to cross join XMLTABLE referencing columns from the other table? I would expect to have to apply a lateral join (CROSS APPLY), but this doesn't seem needed. This query works:

select *
from 
(
  select 'b,a,d' as csv from dual
  union all
  select 'w,o,r,s,e' as csv from dual
) t
cross join xmltable
(
  'if (contains($csv, ",")) then string-join(for $str in ora:tokenize($csv, ",") order by $str return $str, ",") else $csv'
   passing t.csv as "csv"
   columns sorted varchar2(4000) path '.'
) x

Result:

+-----------+-----------+
|    CSV    |  SORTED   |
+-----------+-----------+
| b,a,d     | a,b,d     |
| w,o,r,s,e | e,o,r,s,w |
+-----------+-----------+

I am passing t.csv which should not be accessible on the right side of the cross join in my opinion.

Can anybody explain what is happening here? I have come to think that Oracle muddles its way through here for some reason, thus violating the SQL standard. Am I right?

If you can explain what Oracle does here, this will certainly also explain why adding this

where sorted <> 'x'

leads to unexpected results. Unexpected to me that is :-)

Result with WHERE clause:

+-----------+--------+
|    CSV    | SORTED |
+-----------+--------+
| b,a,d     | a,b,d  |
| w,o,r,s,e | a,b,d  |
+-----------+--------+

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=a9497bec423a3facbd29b49b3a40a350

Answer 1

Your "why" question might be difficult to answer; it is likely because that is how Oracle performed correlated queries using its legacy comma-join syntax and then adding ANSI syntax later and then in Oracle 12c added CROSS APPLY but finding documentation to back this up will be challenging.

However, if you can force the evaluation of the correlated CROSS JOIN (by performing a useless row specific operation, like generating the ROWNUM pseudo-column) before applying the WHERE filter clause then you can get the query to work:

WITH t ( csv ) AS (
  select 'b,a,d'       from dual union all
  select 'w,o,r,s,e'   from dual union all
  select 'w,o,r,s,e,r' from dual
)
SELECT csv,
       sorted
FROM   (
  select ROWNUM as id,
         t.csv,
         s.sorted
  from   t
         CROSS JOIN xmltable (
           'if (contains($csv, ",")) then string-join(for $str in ora:tokenize($csv, ",") order by $str return $str, ",") else $csv'
           passing t.csv as "csv"
           columns sorted varchar2(4000) path '.'
         ) s
)
WHERE  sorted <> 'x';

Outputs:

CSV         | SORTED     
:---------- | :----------
b,a,d       | a,b,d      
w,o,r,s,e   | e,o,r,s,w  
w,o,r,s,e,r | e,o,r,r,s,w

db<>fiddle here