Details

    • Sub-task
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 3.1.0
    • None
    • SQL
    • 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

      Attachments

        Activity

          yumwang Yuming Wang added a comment -

          Postgres will fill in with underlying names:

          PostgreSQL:

          -- 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)

          Spark SQL:

          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
          
          yumwang Yuming Wang added a comment - Postgres will fill in with underlying names: PostgreSQL: -- 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) Spark SQL: 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
          yumwang Yuming Wang added a comment -

          younggyuchun maropu I think this ticket has a higher priority.

          yumwang Yuming Wang added a comment - younggyuchun maropu I think this ticket has a higher priority.

          In the other systems, this column resolution works well?

          btw, "correlation names" in the title is a general term for this issue?

          maropu Takeshi Yamamuro added a comment - In the other systems, this column resolution works well? btw, "correlation names" in the title is a general term for this issue?
          yumwang Yuming Wang added a comment -

          It’s SQL standard.  Feature ID: E051-08

          yumwang Yuming Wang added a comment - It’s SQL standard.  Feature ID : E051-08

          ur, I see.

          maropu Takeshi Yamamuro added a comment - ur, I see.
          dongjoon Dongjoon Hyun added a comment -

          You can increase the priority if you want, yumwang.

          dongjoon Dongjoon Hyun added a comment - You can increase the priority if you want, yumwang .

          People

            Unassigned Unassigned
            yumwang Yuming Wang
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated: