[
https://issues.apache.org/jira/browse/CALCITE-4904?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-4904:
---------------------------------
Description:
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 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.
was:
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 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.
> 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 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)
