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

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

Daniel Ståhl created CALCITE-4904:
-------------------------------------

             Summary: 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.27.0
            Reporter: Daniel Ståhl


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)