You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Daniel Ståhl (Jira)" <ji...@apache.org> on 2021/12/01 12:28:00 UTC
[jira] [Updated] (CALCITE-4904) Unnest correlate broken in some SqlDialects
[ https://issues.apache.org/jira/browse/CALCITE-4904?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Daniel Ståhl updated CALCITE-4904:
----------------------------------
Description:
There seems to be a bug when generating SQL using 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.
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]
{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.
> 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 generating SQL using 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)