nuno-faria opened a new issue, #17359:
URL: https://github.com/apache/datafusion/issues/17359
### Describe the bug
The unparsing of CROSS JOINS with filters is generating invalid SQL, when
building from an optimized plan.
### To Reproduce
Example:
```sql
-- 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):
```sql
-- 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_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
