Oracle version: 12.2.0.1
Test problem: I have a table with an ID column (PK) and two columns that store positive integers. I want to write a query that will return the result of a cross join for each row of the input table.
For example, table T with columns ID, X, Y with a single row, with values (1001, 2, 3). I want the result to show, for each row, the row ID and the result of the cross join of {1, 2, ... , X} and {1, 2, ... , Y}.
Like this (in this trivial example):
ID A B
---- ---- ----
1001 1 1
1001 1 2
1001 1 3
1001 2 1
1001 2 2
1001 2 3
Of course, if I start with a single row, I don't need a lateral join; but a single row does suffice to demonstrate the issue I have run into.
with t (id, x, y) as (select 1001, 2, 3 from dual)
select id, a, b
from t, lateral ( select a, b from (select level as a from dual connect by level <= t.x)
cross join
(select level as b from dual connect by level <= t.y)
)
;
ID A B
---- ---- ----
1001 1 1
As you can see, I only get the first row, instead of all six.
Weirder still: If I include columns X and Y in the output, I get more rows. (Really odd: how many rows I get depends on what columns I select??) I get all the expected values in column A, but still only value 1 in column B.
with t (id, x, y) as (select 1001, 2, 3 from dual)
select id, x, y, a, b
from t, lateral ( select a, b from (select level as a from dual connect by level <= t.x)
cross join
(select level as b from dual connect by level <= t.y)
)
;
ID X Y A B
---- ---- ---- ---- ----
1001 2 3 1 1
1001 2 3 2 1
I get the same behavior (in both cases) with CROSS APPLY instead of LATERAL.
Perhaps there's an issue because LATERAL is similar to a join using Oracle comma syntax and CROSS JOIN uses ANSI syntax. But that's not it; I changed CROSS JOIN to a comma and I get the same results in all cases. Also, CROSS APPLY is similar to ANSI join syntax, I still get the same results regardless of which syntax I use for the cross join.
* * * * *
If I create a table T with the same columns (instead of having the data in a WITH clause - which is known to cause problems in various ways) I only get one row regardless of what I select - ID, A, B or ID, X, Y, A, B.
create table t (id, x, y) as select 1001, 2, 3 from dual;
select id, x, y, a, b
from t, lateral ( select a, b from (select level as a from dual connect by level <= t.x)
cross join
(select level as b from dual connect by level <= t.y)
)
;
ID X Y A B
---- ---- ---- ---- ----
1001 2 3 1 1
* * * * *
If I hard-code the values 2 and 3 in the CONNECT BY clauses (in the member subqueries of the cross join) I get all the rows as expected. So the issue is not simply having a cross join in the LATERAL clause; it is related to the correlation to the outer table (view).
* * * * *
Is this a known limitation (or bug) of (in) LATERAL? Does this issue persist in later Oracle versions?
Note - I am not looking for a workaround, I have already found and used one (here: ); I just want to understand what is going on with cross joins in LATERAL/CROSS APPLY.