Yuming Wang created SPARK-28377:
-----------------------------------
Summary: Fully support correlation names in the FROM clause
Key: SPARK-28377
URL: https://issues.apache.org/jira/browse/SPARK-28377
Project: Spark
Issue Type: Sub-task
Components: SQL
Affects Versions: 3.0.0
Reporter: Yuming Wang
Specifying a list of column names is not fully support. Example:
{code:sql}
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;
{code}
PostgreSQL:
{noformat}
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)
{noformat}
Spark SQL:
{noformat}
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]
{noformat}
*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]
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
