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

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.28.0
• Component/s: core, jdbc-adapter
• Assignee: Unassigned
• Reporter: Daniel Ståhl
• Created: 26/Nov/21 09:30
• Updated: 02/Dec/21 22:22

Description

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

@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();
      }
    }
  }

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.