Firebird 5.0.3.1622
The optimizer selects the wrong join order when using CROSS JOIN LATERAL with complex table expressions.
SELECT
T.NAME
FROM
RDB$RELATIONS R
CROSS JOIN LATERAL (
SELECT R.RDB$RELATION_NAME AS NAME FROM RDB$DATABASE
UNION ALL
SELECT R.RDB$OWNER_NAME AS NAME FROM RDB$DATABASE
) TThe cursor identified in the UPDATE or DELETE statement is not positioned on a row.
no current record for fetch operation.
-----------------------------------------------------------------------------------
SQLCODE: -508
SQLSTATE: 22000
GDSCODE: 335544348
Explain plan:
Select Expression
-> Nested Loop Join (inner)
-> Union
-> Table "RDB$DATABASE" as "T RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" as "T RDB$DATABASE" Full Scan
-> Table "RDB$RELATIONS" as "R" Full Scan
Obviously, the derived table T depends on the outer stream and cannot be the first in the join.
Using LEFT JOIN LATERAL fixes the error.
SELECT
T.NAME
FROM
RDB$RELATIONS R
LEFT JOIN LATERAL (
SELECT R.RDB$RELATION_NAME AS NAME FROM RDB$DATABASE
UNION ALL
SELECT R.RDB$OWNER_NAME AS NAME FROM RDB$DATABASE
) T ON TRUEExplain plan:
Select Expression
-> Nested Loop Join (outer)
-> Table "RDB$RELATIONS" as "R" Full Scan
-> Filter
-> Union
-> Table "RDB$DATABASE" as "T RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" as "T RDB$DATABASE" Full Scan
Firebird 5.0.3.1622
The optimizer selects the wrong join order when using CROSS JOIN LATERAL with complex table expressions.
Explain plan:
Obviously, the derived table T depends on the outer stream and cannot be the first in the join.
Using LEFT JOIN LATERAL fixes the error.
Explain plan: