goldmedal commented on PR #12896:
URL: https://github.com/apache/datafusion/pull/12896#issuecomment-2408609989
After this changed, I found another issue about the anonymous subquery.
```sql
SELECT
*
FROM
(
SELECT
customer.c_custkey AS custkey,
customer.c_name AS "name"
FROM
(
SELECT
customer.c_custkey,
customer.c_name
FROM
customer
)
) AS c
```
This SQL is valid for DataFusion but it isn't valid for Postgres or DuckDB.
The deepest-level subquery is anonymous, but its outer projection uses its
column with the qualifier `customer.c_custkey AS custkey, `. For DuckDB and
Postgres, this kind of anonymous subquery (a.k.a unnamed subquery) is invalid.
## DataFusion
```
DataFusion CLI v42.0.0
> create table customer(c_custkey int, c_name varchar);
0 row(s) fetched.
Elapsed 0.009 seconds.
> SELECT
*
FROM
(
SELECT
customer.c_custkey AS custkey,
customer.c_name AS "name"
FROM
(
SELECT
customer.c_custkey,
customer.c_name
FROM
customer
)
) AS c;
+---------+------+
| custkey | name |
+---------+------+
+---------+------+
0 row(s) fetched.
Elapsed 0.012 seconds.
```
## DuckDB
```
D create table customer(c_custkey int, c_name varchar);
D SELECT
*
FROM
(
SELECT
customer.c_custkey AS custkey,
customer.c_name AS "name"
FROM
(
SELECT
customer.c_custkey,
customer.c_name
FROM
customer
)
) AS c;
Binder Error: Referenced table "customer" not found!
Candidate tables: "unnamed_subquery"
LINE 6: customer.c_custkey AS custkey,
```
## Postgres
```
test=# create table customer(c_custkey int, c_name varchar);
CREATE TABLE
test=# SELECT
*
FROM
(
SELECT
customer.c_custkey AS custkey,
customer.c_name AS "name"
FROM
(
SELECT
customer.c_custkey,
customer.c_name
FROM
customer
)
) AS c;
ERROR: subquery in FROM must have an alias
LINE 9: (
^
HINT: For example, FROM (SELECT ...) [AS] foo.
```
For some SQL pushdown purposes, we are better off generating SQL with the
common syntax. I'll file an issue and try to fix it.
--
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]
