[SPARK-28377] Fully support correlation names in the FROM clause

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Yuming Wang
• Created: 13/Jul/19 03:46
• Updated: 29/Mar/20 02:10

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