[jira] [Updated] (CALCITE-4904) Unnest correlate broken in some SqlDialects

["Daniel Ståhl (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-4904?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Daniel Ståhl updated CALCITE-4904:
----------------------------------
    Affects Version/s: 1.28.0
                           (was: 1.27.0)

> Unnest correlate broken in some SqlDialects
> -------------------------------------------
>
>                 Key: CALCITE-4904
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4904
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.28.0
>            Reporter: Daniel Ståhl
>            Priority: Minor
>
> There seems to be a bug when doing unnest in some SqlDialects. 
> Some SQL dialects throw UnsupportedOperationException via @see SqlImplementor.Context#getAliasContext(RexCorrelVariable)
> Here is a test in RelToSqlConverterTest that illustrates the bug.
> {{@Test void testUnnestCorrelateBrokenInSomeDialects() {}}
> {{    final String sql = "select did + 1\n"}}
> {{        + "from \"department\", unnest(array[\"department_id\", 1]) as t(did)";}}{{    final String expected = "SELECT \"$cor0\".\"DID\" + 1\n"}}
> {{        + "FROM (SELECT \"department_id\", \"department_description\", ARRAY[\"department_id\", 1] AS \"$f2\"\n"}}
> {{        + "FROM \"foodmart\".\"department\") AS \"$cor0\",\n"}}
> {{        + "LATERAL (SELECT \"col_0\" AS \"DID\"\n"}}
> {{        + "FROM UNNEST (SELECT \"$cor0\".\"$f2\"\n"}}
> {{        + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")) AS \"t1\" (\"col_0\")) AS \"t2\"";}}
> {{    sql(sql).ok(expected);}}{{    dialects().forEach((sqlDialect, databaseProduct) -> {}}
> {{      try {}}
> {{        sql(sql).dialect(sqlDialect).exec();}}
> {{      } catch (UnsupportedOperationException e) {}}
> {{        System.out.println("sqlDialect throws: " + sqlDialect.getClass().getSimpleName());}}
> {{        e.printStackTrace();}}
> {{      }}}
> {{    });}}
> {{  }}}
> Here is the start of the stacktrace.
> {{java.lang.UnsupportedOperationException}}
> {{    at org.apache.calcite.rel.rel2sql.SqlImplementor$Context.getAliasContext(SqlImplementor.java:950)}}
> {{    at org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:628)}}
> {{    at org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:440)}}
> {{    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)}}
> {{    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)}}
> {{    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)}}
> {{    at java.base/java.lang.reflect.Method.invoke(Method.java:566)}}
> {{    at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)}}
> {{    at org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:139)}}
> {{    at org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:147)}}
> We discovered that it is related to public boolean supportsAliasedValues() in SqlDialect. For those dialects that it returns false it fails. 
> For BigQueryDialect, if you return true it will success but the generated SQL is not  compatible with BigQuery. One issue is that LATERAL is not understood. 



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