You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Yuming Wang (JIRA)" <ji...@apache.org> on 2019/07/14 04:59:00 UTC
[jira] [Commented] (SPARK-28377) Fully support correlation names in
the FROM clause
[ https://issues.apache.org/jira/browse/SPARK-28377?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16884555#comment-16884555 ]
Yuming Wang commented on SPARK-28377:
-------------------------------------
Postgres will fill in with underlying names:
PostgreSQL:
{noformat}
-- currently, Postgres will fill in with underlying names
SELECT '' AS "xxx", *
FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a);
xxx | a | b | t | k
-----+---+---+-------+----
| 0 | | zero |
| 1 | 4 | one | -1
| 2 | 3 | two | 2
| 2 | 3 | two | 4
| 3 | 2 | three | -3
| 5 | 0 | five | -5
| 5 | 0 | five | -5
(7 rows){noformat}
Spark SQL:
{noformat}
SELECT '' AS `xxx`, *
FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a)
-- !query 44 schema
struct<>
-- !query 44 output
org.apache.spark.sql.AnalysisException
Number of column aliases does not match number of columns. Number of column aliases: 2; number of columns: 3.; line 2 pos 7
{noformat}
> 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
> Priority: Major
>
> 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: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org