Details
-
Sub-task
-
Status: Open
-
Major
-
Resolution: Unresolved
-
3.1.0
-
None
-
None
Description
Specifying a list of column names is not fully support. Example:
create or replace temporary view J1_TBL as select * from (values (1, 4, 'one'), (2, 3, 'two')) as v(i, j, t); create or replace temporary view J2_TBL as select * from (values (1, -1), (2, 2)) as v(i, k); SELECT '' AS xxx, t1.a, t2.e FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e) WHERE t1.a = t2.d;
PostgreSQL:
postgres=# SELECT '' AS xxx, t1.a, t2.e postgres-# FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e) postgres-# WHERE t1.a = t2.d; xxx | a | e -----+---+---- | 1 | -1 | 2 | 2 (2 rows)
Spark SQL:
spark-sql> SELECT '' AS xxx, t1.a, t2.e > FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e) > WHERE t1.a = t2.d; Error in query: cannot resolve '`t1.a`' given input columns: [a, b, c, d, e]; line 3 pos 8; 'Project [ AS xxx#21, 't1.a, 't2.e] +- 'Filter ('t1.a = 't2.d) +- Join Inner :- Project [i#14 AS a#22, j#15 AS b#23, t#16 AS c#24] : +- SubqueryAlias `t1` : +- SubqueryAlias `j1_tbl` : +- Project [i#14, j#15, t#16] : +- Project [col1#11 AS i#14, col2#12 AS j#15, col3#13 AS t#16] : +- SubqueryAlias `v` : +- LocalRelation [col1#11, col2#12, col3#13] +- Project [i#19 AS d#25, k#20 AS e#26] +- SubqueryAlias `t2` +- SubqueryAlias `j2_tbl` +- Project [i#19, k#20] +- Project [col1#17 AS i#19, col2#18 AS k#20] +- SubqueryAlias `v` +- LocalRelation [col1#17, col2#18]
Feature ID: E051-08
https://www.postgresql.org/docs/11/sql-expressions.html
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_correlationnames.html
Postgres will fill in with underlying names:
PostgreSQL:
Spark SQL: