Unparsing of CROSS JOINs with filters is generating incorrect queries #17359
Description
Describe the bug
The unparsing of CROSS JOINS with filters is generating invalid SQL, when building from an optimized plan.
To Reproduce
Example:
-- original
select t1.k, t2.v
from t1, t2
where t2.k = 0;
-- optimized plan
Cross Join:
TableScan: t1 projection=[k]
Projection: t2.v
Filter: t2.k = Int32(0)
TableScan: t2 projection=[k, v]
-- generated; the repeated filter is not valid since k is not projected from the subquery
SELECT t1.k, t2.v FROM t1 CROSS JOIN (SELECT t2.v FROM t2 WHERE (t2.k = 0)) WHERE (t2.k = 0)If we filter by a projected column (t2.v) it works correctly (although it would make more sense for the filter to be done on a WHERE instead of on the JOIN, but it works):
-- original
select t1.k, t2.v
from t1, t2
where t2.v = 0;
-- optimized plan
Cross Join:
TableScan: t1 projection=[k]
Filter: t2.v = Int32(0)
TableScan: t2 projection=[v]
-- generated
SELECT t1.k, t2.v FROM t1 INNER JOIN t2 ON (t2.v = 0)Expected behavior
Generate valid SQL.
Additional context
No response