[jira] [Created] (CALCITE-4915) Query with unqualified common column and NATURAL JOIN fails
Aleksey Plekhanov created CALCITE-4915: -- Summary: Query with unqualified common column and NATURAL JOIN fails Key: CALCITE-4915 URL: https://issues.apache.org/jira/browse/CALCITE-4915 Project: Calcite Issue Type: Bug Reporter: Aleksey Plekhanov Assignee: Aleksey Plekhanov Query with unqualified common column and NATURAL JOIN: {noformat} SELECT deptno FROM emp NATURAL JOIN dept{noformat} Fails with "Column 'DEPTNO' is ambiguous" error. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Assigned] (CALCITE-4913) Correlated variables in a select list are not deduplicated
[ https://issues.apache.org/jira/browse/CALCITE-4913?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Konstantin Orlov reassigned CALCITE-4913: - Assignee: Konstantin Orlov > Correlated variables in a select list are not deduplicated > -- > > Key: CALCITE-4913 > URL: https://issues.apache.org/jira/browse/CALCITE-4913 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.28.0 >Reporter: Konstantin Orlov >Assignee: Konstantin Orlov >Priority: Major > > Currently, the deduplication of correlated variables ain't done for > correlates in select list. Looks like the root cause here is same as for > CALCITE-4673, but, unfortunately, suggested patch has addressed only the > particular problem with a table function. It would be great to provide a more > gereric fix. > Besides, the project rel doesn't expose any correlated variables that should > be set by this node -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4914) Prep existing interfaces to support making RelMetadataQuery abstract
[ https://issues.apache.org/jira/browse/CALCITE-4914?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-4914: Labels: pull-request-available (was: ) > Prep existing interfaces to support making RelMetadataQuery abstract > > > Key: CALCITE-4914 > URL: https://issues.apache.org/jira/browse/CALCITE-4914 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Jacques Nadeau >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Some field/constructors are currently exposed in such a way that abstracting > RelMetdataQuery must be done in two steps. This is the first step of marking > interfaces as deprecated and/or internal so that a later release can make > these abstract. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Assigned] (CALCITE-4879) Make RelMetadataQuery abstract
[ https://issues.apache.org/jira/browse/CALCITE-4879?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jacques Nadeau reassigned CALCITE-4879: --- Assignee: Jacques Nadeau > Make RelMetadataQuery abstract > -- > > Key: CALCITE-4879 > URL: https://issues.apache.org/jira/browse/CALCITE-4879 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Jacques Nadeau >Assignee: Jacques Nadeau >Priority: Major > > The RelOptCluster.setMetadataQuerySupplier() and RelMedataQuery abstraction > are great at separating how planners and rules consume metadata versus how > metadata is produced. While details about how metadata is produced is > (mostly) not leaked in the api of RelMetadataQuery, the class does assume > that metadata will be produced via the current mechanisms surrounding > RelMetadataProviders and MetadataHandlers. This ticket targets separating the > production of metadata from the consumption interface, by making > RelMetadataQuery abstract (either as an abstract class or as a interface) and > moving the handler and provider specific implementations to an implementation > of RelMetadataQuery. This will allow a broader breadth of experimentation to > be undertaken. For example, one example people have been evaluating is > whether a lambda based system would be easier to understand and debug, as > performant and more AOT friendly than the existing systems of chains and > janino compilation. > To accomplish this task, the first step will be to deprecate the existing > constructors and inform people to use a concrete subtype. Once deprecated, > the actual logic that currently exists in RelMetadataQuery can be extracted > into the concrete subtype and the base class can be made either abstract or > an interface (depending on what seems most appropriate at the time). -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Created] (CALCITE-4914) Prep existing interfaces to support making RelMetadataQuery abstract
Jacques Nadeau created CALCITE-4914: --- Summary: Prep existing interfaces to support making RelMetadataQuery abstract Key: CALCITE-4914 URL: https://issues.apache.org/jira/browse/CALCITE-4914 Project: Calcite Issue Type: Improvement Components: core Reporter: Jacques Nadeau Some field/constructors are currently exposed in such a way that abstracting RelMetdataQuery must be done in two steps. This is the first step of marking interfaces as deprecated and/or internal so that a later release can make these abstract. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4879) Make RelMetadataQuery abstract
[ https://issues.apache.org/jira/browse/CALCITE-4879?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17452168#comment-17452168 ] Jacques Nadeau commented on CALCITE-4879: - > If people had been allowed to sub-class RelMetadataQuery they would have People can and do sub-class RelMetadataQuery in its entirety. Nothing in the code/construction of RelMetadataQuery or how RelOptCluster exposes a query supplier restricts that. I've actually done it when implementing a new AOT friendly RelMetadataQuery external to Calcite. > RelMetadataQuery is a facade. It doesn't contain state or behavior. I would disagree about it not containing state and behavior. It contains both. It holds state around current versions of each handler as well as thread local caches of janino compiled code and a cache of past metadata results. It also contains behavior including: null canonicalization, cache management and concepts around handler creation and revision. The latter is something that is very specific concept to a runtime compilation pattern that doesn't match well to an AOT pattern. > and we would have a mess today I think things are a bit of a mess now. This isn't a dig or a negative statement (and I'm not trying to start a flame war here). It's an observation after spending several weeks working with the existing code and building a complete compatible system (passes all the same tests as the existing one) that performs well and functions well in AOT environments. From my pov, there are many good things that exist: * it is relatively straightforward to add new metadata types (albeit verbose), * performance is good if you're in an environment that can support runtime compilation * Virtually all of planning-side consumption is limited to the a constrained portion of RelMetadataQuery that should be public. But there are also multiple real challenges: * Excessive runtime code that is not runtime specific/runtime compiled required * Bindings between components are brittle and implicit (javac/ide won't help identify many issues) * Several internal concepts are exposed as public on RelMetadataQuery (see the map, metadataProvider and THREAD_PROVIDER fields) * There is a large amount of deprecated code supporting old patterns heavily intertwined with the existing system * Major caching bugs (according to comments by others, I haven't validated) * Poor documentation * Debugging and understanding the code is quite difficult I agree that lack of freedom can be a feature. I don't think I'm altering the current state of freedom, just cleaning up and formalizing the freedom that already exists. > Make RelMetadataQuery abstract > -- > > Key: CALCITE-4879 > URL: https://issues.apache.org/jira/browse/CALCITE-4879 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Jacques Nadeau >Priority: Major > > The RelOptCluster.setMetadataQuerySupplier() and RelMedataQuery abstraction > are great at separating how planners and rules consume metadata versus how > metadata is produced. While details about how metadata is produced is > (mostly) not leaked in the api of RelMetadataQuery, the class does assume > that metadata will be produced via the current mechanisms surrounding > RelMetadataProviders and MetadataHandlers. This ticket targets separating the > production of metadata from the consumption interface, by making > RelMetadataQuery abstract (either as an abstract class or as a interface) and > moving the handler and provider specific implementations to an implementation > of RelMetadataQuery. This will allow a broader breadth of experimentation to > be undertaken. For example, one example people have been evaluating is > whether a lambda based system would be easier to understand and debug, as > performant and more AOT friendly than the existing systems of chains and > janino compilation. > To accomplish this task, the first step will be to deprecate the existing > constructors and inform people to use a concrete subtype. Once deprecated, > the actual logic that currently exists in RelMetadataQuery can be extracted > into the concrete subtype and the base class can be made either abstract or > an interface (depending on what seems most appropriate at the time). -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4907) JdbcJoin does not convert Join with always true/false RexNode
[ https://issues.apache.org/jira/browse/CALCITE-4907?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17452100#comment-17452100 ] duan xiong commented on CALCITE-4907: - How about this summary "When Join condition is TRUE or FALSE The Calcite can't push down this condition to JdbcJoin" > JdbcJoin does not convert Join with always true/false RexNode > - > > Key: CALCITE-4907 > URL: https://issues.apache.org/jira/browse/CALCITE-4907 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter >Affects Versions: 1.28.0 >Reporter: Francesco Gini >Assignee: Francesco Gini >Priority: Major > Labels: pull-request-available > Fix For: 1.29.0 > > Time Spent: 10m > Remaining Estimate: 0h > > Cross joins (LogicalJoin with condition {_}true{_}) are not pushed down in > the database when using the JdbcAdapter. This results in implementing the > join in memory via _EnumerableNestedLoopJoin_ which is a less efficient > implementation. > > JdbcJoin does not handle cases where the condtion is a RexNode that it is > always true or false. However, SqlImplementor is coded to handle those > conditions (see SqlImplementor.convertConditionToSqlNode). -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4913) Correlated variables in a select list are not deduplicated
[ https://issues.apache.org/jira/browse/CALCITE-4913?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17451952#comment-17451952 ] Julian Hyde commented on CALCITE-4913: -- Sorry, I'm too busy to review. > Correlated variables in a select list are not deduplicated > -- > > Key: CALCITE-4913 > URL: https://issues.apache.org/jira/browse/CALCITE-4913 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.28.0 >Reporter: Konstantin Orlov >Priority: Major > > Currently, the deduplication of correlated variables ain't done for > correlates in select list. Looks like the root cause here is same as for > CALCITE-4673, but, unfortunately, suggested patch has addressed only the > particular problem with a table function. It would be great to provide a more > gereric fix. > Besides, the project rel doesn't expose any correlated variables that should > be set by this node -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4913) Correlated variables in a select list are not deduplicated
[ https://issues.apache.org/jira/browse/CALCITE-4913?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17451926#comment-17451926 ] Konstantin Orlov commented on CALCITE-4913: --- [~julianhyde], [~zabetak], I've prepared a PR. Could you please take a look? > Correlated variables in a select list are not deduplicated > -- > > Key: CALCITE-4913 > URL: https://issues.apache.org/jira/browse/CALCITE-4913 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.28.0 >Reporter: Konstantin Orlov >Priority: Major > > Currently, the deduplication of correlated variables ain't done for > correlates in select list. Looks like the root cause here is same as for > CALCITE-4673, but, unfortunately, suggested patch has addressed only the > particular problem with a table function. It would be great to provide a more > gereric fix. > Besides, the project rel doesn't expose any correlated variables that should > be set by this node -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4913) Correlated variables in a select list are not deduplicated
[ https://issues.apache.org/jira/browse/CALCITE-4913?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Konstantin Orlov updated CALCITE-4913: -- Component/s: core > Correlated variables in a select list are not deduplicated > -- > > Key: CALCITE-4913 > URL: https://issues.apache.org/jira/browse/CALCITE-4913 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.28.0 >Reporter: Konstantin Orlov >Priority: Major > > Currently, the deduplication of correlated variables ain't done for > correlates in select list. Looks like the root cause here is same as for > CALCITE-4673, but, unfortunately, suggested patch has addressed only the > particular problem with a table function. It would be great to provide a more > gereric fix. > Besides, the project rel doesn't expose any correlated variables that should > be set by this node -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Created] (CALCITE-4913) Correlated variables in a select list are not deduplicated
Konstantin Orlov created CALCITE-4913: - Summary: Correlated variables in a select list are not deduplicated Key: CALCITE-4913 URL: https://issues.apache.org/jira/browse/CALCITE-4913 Project: Calcite Issue Type: Bug Affects Versions: 1.28.0 Reporter: Konstantin Orlov Currently, the deduplication of correlated variables ain't done for correlates in select list. Looks like the root cause here is same as for CALCITE-4673, but, unfortunately, suggested patch has addressed only the particular problem with a table function. It would be great to provide a more gereric fix. Besides, the project rel doesn't expose any correlated variables that should be set by this node -- This message was sent by Atlassian Jira (v8.20.1#820001)
[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 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. was: 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.a
[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: -- Component/s: jdbc-adapter > 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, jdbc-adapter >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)
[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 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 or
[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(
[jira] [Updated] (CALCITE-4912) Confusing javadoc of RexSimplify.simplify
[ https://issues.apache.org/jira/browse/CALCITE-4912?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Alexander Trushev updated CALCITE-4912: --- Description: Javadoc of RexSimplify.simplify says that {code} simplify(x = 1 AND y = 2 AND NOT x = 1) returns y = 2 {code} but it is wrong for any policy: x <- 1 y <- 2 x = 1 AND y = 2 AND NOT x = 1 == false y = 2 == true I guess it should have been like {code} simplify(y = 2 AND (x = 1 OR NOT x = 1 OR x IS NULL)) returns y = 2 {code} which is true for any policy: {code} Origin: AND(=($1, CAST(2):INTEGER), OR(=($0, CAST(1):INTEGER), NOT(=($0, CAST(1):INTEGER)), IS NULL($0))) Simplify(Unknown): =($1, 2) Simplify(False): =($1, 2) Simplify(True): =($1, 2) {code} was: Javadoc of RexSimplify.simplify says that {code} simplify(x = 1 AND y = 2 AND NOT x = 1) returns y = 2 {code} but it is wrong for any policy: x <- 1 y <- 2 x = 1 AND y = 2 AND NOT x = 1 == false y = 2 == true I guess it should have been like {code} simplify(y = 2 AND (x = 1 OR NOT x = 1 OR x IS NULL)) returns y = 2 {code} which is true for any policy > Confusing javadoc of RexSimplify.simplify > - > > Key: CALCITE-4912 > URL: https://issues.apache.org/jira/browse/CALCITE-4912 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.28.0 >Reporter: Alexander Trushev >Priority: Minor > > Javadoc of RexSimplify.simplify says that > {code} > simplify(x = 1 AND y = 2 AND NOT x = 1) returns y = 2 > {code} > but it is wrong for any policy: > x <- 1 > y <- 2 > x = 1 AND y = 2 AND NOT x = 1 == false > y = 2 == true > I guess it should have been like > {code} > simplify(y = 2 AND (x = 1 OR NOT x = 1 OR x IS NULL)) returns y = 2 > {code} > which is true for any policy: > {code} > Origin: AND(=($1, CAST(2):INTEGER), OR(=($0, CAST(1):INTEGER), > NOT(=($0, CAST(1):INTEGER)), IS NULL($0))) > Simplify(Unknown): =($1, 2) > Simplify(False): =($1, 2) > Simplify(True): =($1, 2) > {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4912) Confusing javadoc of RexSimplify.simplify
[ https://issues.apache.org/jira/browse/CALCITE-4912?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Alexander Trushev updated CALCITE-4912: --- Affects Version/s: 1.28.0 > Confusing javadoc of RexSimplify.simplify > - > > Key: CALCITE-4912 > URL: https://issues.apache.org/jira/browse/CALCITE-4912 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.28.0 >Reporter: Alexander Trushev >Priority: Minor > > Javadoc of RexSimplify.simplify says that > {code} > simplify(x = 1 AND y = 2 AND NOT x = 1) returns y = 2 > {code} > but it is wrong for any policy: > x <- 1 > y <- 2 > x = 1 AND y = 2 AND NOT x = 1 == false > y = 2 == true > I guess it should have been like > {code} > simplify(y = 2 AND (x = 1 OR NOT x = 1 OR x IS NULL)) returns y = 2 > {code} > which is true for any policy -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Created] (CALCITE-4912) Confusing javadoc of RexSimplify.simplify
Alexander Trushev created CALCITE-4912: -- Summary: Confusing javadoc of RexSimplify.simplify Key: CALCITE-4912 URL: https://issues.apache.org/jira/browse/CALCITE-4912 Project: Calcite Issue Type: Bug Components: core Reporter: Alexander Trushev Javadoc of RexSimplify.simplify says that {code} simplify(x = 1 AND y = 2 AND NOT x = 1) returns y = 2 {code} but it is wrong for any policy: x <- 1 y <- 2 x = 1 AND y = 2 AND NOT x = 1 == false y = 2 == true I guess it should have been like {code} simplify(y = 2 AND (x = 1 OR NOT x = 1 OR x IS NULL)) returns y = 2 {code} which is true for any policy -- This message was sent by Atlassian Jira (v8.20.1#820001)