[jira] [Updated] (CALCITE-4904) For dialects such as BigQuery, JDBC adapter translates a correlated UNNEST query to invalid SQL

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

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

Julian Hyde updated CALCITE-4904:
---------------------------------
    Summary: For dialects such as BigQuery, JDBC adapter translates a correlated UNNEST query to invalid SQL  (was: Unnest correlate broken in some SqlDialects)

> For dialects such as BigQuery, JDBC adapter translates a correlated UNNEST query to invalid SQL
> -----------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-4904
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4904
>             Project: Calcite
>          Issue Type: Bug
>          Components: core, jdbc-adapter
>    Affects Versions: 1.28.0
>            Reporter: Daniel Ståhl
>            Priority: Minor
>
> There seems to be a bug when generating SQL in the JDBC adapter and doing unnest in some SqlDialects. 
> Some SQL dialects throw UnsupportedOperationException via @see SqlImplementor.Context#getAliasContext(RexCorrelVariable)
> Here is a PR that illustrates the bug. [https://github.com/bergman/calcite/pull/2]
> {code}
> @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);
>     for (DatabaseProduct databaseProduct : DatabaseProduct.values()) {
>       final SqlDialect sqlDialect = databaseProduct.getDialect();
>       try {
>         sql(sql).dialect(sqlDialect).exec();
>       } catch (UnsupportedOperationException e) {
>         System.out.println("sqlDialect throws: " + sqlDialect.getClass().getSimpleName());
>         e.printStackTrace();
>       }
>     }
>   }
> {code}
> Here is the start of the stacktrace.
> {noformat}
> 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)
> {noformat}
> 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)