[jira] [Created] (CALCITE-5023) JOIN whose inputs projecting same field names shouldn't generate SELECT STAR when converting rel to sql

["yanjing.wang (Jira)" <ji...@apache.org>]

yanjing.wang created CALCITE-5023:
-------------------------------------

             Summary: JOIN whose inputs projecting same field names shouldn't generate SELECT STAR when converting rel to sql
                 Key: CALCITE-5023
                 URL: https://issues.apache.org/jira/browse/CALCITE-5023
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.29.0
         Environment: jdk8
            Reporter: yanjing.wang


{code:java}
@Test void testJoinInputsProjectingSameFieldName() {
  final String query = "SELECT \"t\".\"id\", \"t0\".\"id\" AS \"id0\"\n"
      + "FROM (VALUES (NULL)) AS \"t\" (\"id\"),\n"
      + "(VALUES (NULL)) AS \"t0\" (\"id\")";
  final String expected = query;
  sql(query).ok(expected);
} {code}
The Join left and right have same field name id, we should generate an expected statement which equals to query, but Calcite generates SELECT \* rather SELECT expression list.

 

In many dialects such as MYSQL, SPARK SQL, SELECT \* which contains same field name can't be used in CTAS, for example
{code:java}
CREATE TABLE SELECT * FROM (VALUES (NULL)) AS \"t\" (\"id\"),
(VALUES (NULL)) AS \"t0\" (\"id\"){code}
This will cause exception: Found duplicate column(s) in the table definition.

 



--
This message was sent by Atlassian Jira
(v8.20.1#820001)