[jira] [Commented] (CALCITE-5023) If the inputs to a join have duplicate filed names, JDBC adapter should not generate “SELECT *”

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5023?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17498235#comment-17498235 ] 

Julian Hyde commented on CALCITE-5023:
--------------------------------------

* Your code and test case don’t deal with a join with 3 or more inputs. That’s a problem, because calcite will assign unique names to the output of the intermediate join.
 * You should take the dialects case sensitivity into account.
 * There are existing methods to check uniqueness. One in Util I think. Maybe one could be added to SqlNameMatcher
 * Your javadoc says “have the same names” but should say “have any duplicate names”

> If the inputs to a join have duplicate filed names, JDBC adapter should not generate “SELECT *”
> -----------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-5023
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5023
>             Project: Calcite
>          Issue Type: Bug
>          Components: jdbc-adapter
>    Affects Versions: 1.29.0
>         Environment: jdk8
>            Reporter: yanjing.wang
>            Assignee: yanjing.wang
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> {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)