[jira] [Commented] (CALCITE-4484) Add UNIQUE_VALUE(x) aggregate function, that throws if x is not unique
[ https://issues.apache.org/jira/browse/CALCITE-4484?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17282889#comment-17282889 ] Julian Hyde commented on CALCITE-4484: -- Work in progress: [julianhyde/4484-unique-value|https://github.com/julianhyde/calcite/tree/4484-unique-value]. It turns out that {{UNIQUE_VALUE}} was not necessary for {{WITHIN DISTINCT}}. We tried {{UNIQUE_VALUE}} but it executed inside an {{Aggregate}} with two grouping sets \(x) and (x, y), we only needed the value for (x, y), but it would throw when being computed for \(x). So we used a different strategy based on {{$THROW_UNLESS(MIN\(x) IS NOT DISTINCT FROM MAX\(x), ...)}}. > Add UNIQUE_VALUE(x) aggregate function, that throws if x is not unique > -- > > Key: CALCITE-4484 > URL: https://issues.apache.org/jira/browse/CALCITE-4484 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Priority: Major > > Add a {{UNIQUE_VALUE\(x)}} aggregate function, that throws if {{x}} is not > unique. > {{UNIQUE_VALUE\(x)}} would throw if {{x}} has values [1, 2], or has values > [1, NULL]; but would not throw if x has values [1, 1, 1] or [] or [NULL, > NULL]. Like {{ANY_VALUE}} it behaves as if {{RESPECT NULLS}} is specified. > There are similar functions: > * {{ANY_VALUE\(x)}} non-deterministically picks a value. (It is present in > BigQuery, MySQL, Snowflake, MSSQL and perhaps others.) > * {{SINGLE_VALUE\(x)}} returns the value of x if there is just one value > (e.g. [1] or [NULL]), NULL if there are no values, throws if there is more > than one value (e.g. [NULL, NULL] or [1, 1, 1] or [1, 2]). {{SINGLE_VALUE}} > is in Calcite, no other DBs that I am aware of, not documented, but available > through SQL. Calcite uses it internally to enforce scalar sub-queries. > BigQuery has an internal function "{{$ANY_AND_CHECK\(x)}}" that is equivalent > to {{UNIQUE_VALUE\(x)}}. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3574) Add RLIKE operator (similar to LIKE, but Hive- and Spark-specific)
[ https://issues.apache.org/jira/browse/CALCITE-3574?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17282828#comment-17282828 ] Julian Hyde commented on CALCITE-3574: -- Reviewing and testing now. > Add RLIKE operator (similar to LIKE, but Hive- and Spark-specific) > -- > > Key: CALCITE-3574 > URL: https://issues.apache.org/jira/browse/CALCITE-3574 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Aitozi >Assignee: Julian Hyde >Priority: Major > Fix For: 1.27.0 > > > Now rlike is not supported in calcite, i think it's useful to support this so > that it can be used in other engine depending on calcite. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Assigned] (CALCITE-3574) Add RLIKE operator (similar to LIKE, but Hive- and Spark-specific)
[ https://issues.apache.org/jira/browse/CALCITE-3574?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde reassigned CALCITE-3574: Assignee: Julian Hyde > Add RLIKE operator (similar to LIKE, but Hive- and Spark-specific) > -- > > Key: CALCITE-3574 > URL: https://issues.apache.org/jira/browse/CALCITE-3574 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Aitozi >Assignee: Julian Hyde >Priority: Major > Fix For: 1.27.0 > > > Now rlike is not supported in calcite, i think it's useful to support this so > that it can be used in other engine depending on calcite. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-4491) Aggregation of window function produces invalid SQL for PostgreSQL
[ https://issues.apache.org/jira/browse/CALCITE-4491?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17282806#comment-17282806 ] Julian Hyde commented on CALCITE-4491: -- Fixed in [b38f|https://github.com/apache/calcite/commit/b38f3eb09a5f6a00ccb4ead6daaaf800faa0]; thanks for the PR! > Aggregation of window function produces invalid SQL for PostgreSQL > -- > > Key: CALCITE-4491 > URL: https://issues.apache.org/jira/browse/CALCITE-4491 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.26.0 >Reporter: Dominik Labuda >Assignee: Julian Hyde >Priority: Major > Labels: pull-request-available > Fix For: 1.27.0 > > Time Spent: 40m > Remaining Estimate: 0h > > h2. Issue > We tested the following behavior against PostgreSQL database, however more of > the supported dialects may be affected. > When aggregating the results of a window function an invalid SQL is > generated. I was able to replicate the behavior in tests with: > {code:java} > @Test void testAggregatingWindowFunction() { > final RelBuilder builder = relBuilder(); > final RelNode root = builder > .scan("EMP") > .project( > builder.alias( > builder.getRexBuilder().makeOver( > > builder.getTypeFactory().createSqlType(SqlTypeName.INTEGER), > SqlStdOperatorTable.RANK, > new ArrayList<>(), > new ArrayList<>(), > ImmutableList.of(new > RexFieldCollation(builder.field("SAL"), ImmutableSet.of())), > RexWindowBounds.UNBOUNDED_PRECEDING, > RexWindowBounds.UNBOUNDED_FOLLOWING, > true, > true, > false, > false, > false > ), > "rank" > ) > ) > .as("tmp") > .aggregate( > builder.groupKey(), > builder.count( > true, > "cnt", > builder.field("tmp", "rank") > ) > ) > .build(); > final String expectedSql = "SELECT COUNT(DISTINCT \"rank\") AS \"cnt\"\n" > + > "FROM (SELECT RANK() OVER (ORDER BY \"SAL\") AS \"rank\"\n" + > "FROM \"scott\".\"EMP\") AS \"t\""; > assertThat( > toSql(root, PostgresqlSqlDialect.DEFAULT), > isLinux(expectedSql) > ); > } > {code} > The code above fails, since it produces this SQL instead of the expected one, > which cannot be executed on PGSQL: > {noformat} > SELECT COUNT(DISTINCT RANK() OVER (ORDER BY "SAL")) AS "cnt" > FROM "scott"."EMP" > {noformat} > In that case I am getting these kinds of errors from DB: > {noformat} > ERROR: aggregate function calls cannot contain window function calls > {noformat} > h2. Suggested solution > Since _SqlDialect_ already contains support for determining whether the > databases support nested aggregations via _public boolean > supportsNestedAggregations()_ we could either add another method like _public > boolean supportsNestedWindows()_ maybe _supportNestedWindowsInAggregation()_ > to be more verbose. Or we could reuse the existing method for the window > purposes (which seems non-transparent). Then we will be able to handle the > behavior in _SqlImplementor.needNewSubQuery()_ similarly as it already does > with the aggregations. > Please let me know if you agree and I will be more than happy to provide you > with PR to review, thanks! -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Assigned] (CALCITE-4491) Aggregation of window function produces invalid SQL for PostgreSQL
[ https://issues.apache.org/jira/browse/CALCITE-4491?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde reassigned CALCITE-4491: Assignee: Julian Hyde > Aggregation of window function produces invalid SQL for PostgreSQL > -- > > Key: CALCITE-4491 > URL: https://issues.apache.org/jira/browse/CALCITE-4491 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.26.0 >Reporter: Dominik Labuda >Assignee: Julian Hyde >Priority: Major > Labels: pull-request-available > Time Spent: 0.5h > Remaining Estimate: 0h > > h2. Issue > We tested the following behavior against PostgreSQL database, however more of > the supported dialects may be affected. > When aggregating the results of a window function an invalid SQL is > generated. I was able to replicate the behavior in tests with: > {code:java} > @Test void testAggregatingWindowFunction() { > final RelBuilder builder = relBuilder(); > final RelNode root = builder > .scan("EMP") > .project( > builder.alias( > builder.getRexBuilder().makeOver( > > builder.getTypeFactory().createSqlType(SqlTypeName.INTEGER), > SqlStdOperatorTable.RANK, > new ArrayList<>(), > new ArrayList<>(), > ImmutableList.of(new > RexFieldCollation(builder.field("SAL"), ImmutableSet.of())), > RexWindowBounds.UNBOUNDED_PRECEDING, > RexWindowBounds.UNBOUNDED_FOLLOWING, > true, > true, > false, > false, > false > ), > "rank" > ) > ) > .as("tmp") > .aggregate( > builder.groupKey(), > builder.count( > true, > "cnt", > builder.field("tmp", "rank") > ) > ) > .build(); > final String expectedSql = "SELECT COUNT(DISTINCT \"rank\") AS \"cnt\"\n" > + > "FROM (SELECT RANK() OVER (ORDER BY \"SAL\") AS \"rank\"\n" + > "FROM \"scott\".\"EMP\") AS \"t\""; > assertThat( > toSql(root, PostgresqlSqlDialect.DEFAULT), > isLinux(expectedSql) > ); > } > {code} > The code above fails, since it produces this SQL instead of the expected one, > which cannot be executed on PGSQL: > {noformat} > SELECT COUNT(DISTINCT RANK() OVER (ORDER BY "SAL")) AS "cnt" > FROM "scott"."EMP" > {noformat} > In that case I am getting these kinds of errors from DB: > {noformat} > ERROR: aggregate function calls cannot contain window function calls > {noformat} > h2. Suggested solution > Since _SqlDialect_ already contains support for determining whether the > databases support nested aggregations via _public boolean > supportsNestedAggregations()_ we could either add another method like _public > boolean supportsNestedWindows()_ maybe _supportNestedWindowsInAggregation()_ > to be more verbose. Or we could reuse the existing method for the window > purposes (which seems non-transparent). Then we will be able to handle the > behavior in _SqlImplementor.needNewSubQuery()_ similarly as it already does > with the aggregations. > Please let me know if you agree and I will be more than happy to provide you > with PR to review, thanks! -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-4491) Aggregation of window function produces invalid SQL for PostgreSQL
[ https://issues.apache.org/jira/browse/CALCITE-4491?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17282715#comment-17282715 ] Julian Hyde commented on CALCITE-4491: -- Reviewing and testing now. > Aggregation of window function produces invalid SQL for PostgreSQL > -- > > Key: CALCITE-4491 > URL: https://issues.apache.org/jira/browse/CALCITE-4491 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.26.0 >Reporter: Dominik Labuda >Assignee: Julian Hyde >Priority: Major > Labels: pull-request-available > Fix For: 1.27.0 > > Time Spent: 0.5h > Remaining Estimate: 0h > > h2. Issue > We tested the following behavior against PostgreSQL database, however more of > the supported dialects may be affected. > When aggregating the results of a window function an invalid SQL is > generated. I was able to replicate the behavior in tests with: > {code:java} > @Test void testAggregatingWindowFunction() { > final RelBuilder builder = relBuilder(); > final RelNode root = builder > .scan("EMP") > .project( > builder.alias( > builder.getRexBuilder().makeOver( > > builder.getTypeFactory().createSqlType(SqlTypeName.INTEGER), > SqlStdOperatorTable.RANK, > new ArrayList<>(), > new ArrayList<>(), > ImmutableList.of(new > RexFieldCollation(builder.field("SAL"), ImmutableSet.of())), > RexWindowBounds.UNBOUNDED_PRECEDING, > RexWindowBounds.UNBOUNDED_FOLLOWING, > true, > true, > false, > false, > false > ), > "rank" > ) > ) > .as("tmp") > .aggregate( > builder.groupKey(), > builder.count( > true, > "cnt", > builder.field("tmp", "rank") > ) > ) > .build(); > final String expectedSql = "SELECT COUNT(DISTINCT \"rank\") AS \"cnt\"\n" > + > "FROM (SELECT RANK() OVER (ORDER BY \"SAL\") AS \"rank\"\n" + > "FROM \"scott\".\"EMP\") AS \"t\""; > assertThat( > toSql(root, PostgresqlSqlDialect.DEFAULT), > isLinux(expectedSql) > ); > } > {code} > The code above fails, since it produces this SQL instead of the expected one, > which cannot be executed on PGSQL: > {noformat} > SELECT COUNT(DISTINCT RANK() OVER (ORDER BY "SAL")) AS "cnt" > FROM "scott"."EMP" > {noformat} > In that case I am getting these kinds of errors from DB: > {noformat} > ERROR: aggregate function calls cannot contain window function calls > {noformat} > h2. Suggested solution > Since _SqlDialect_ already contains support for determining whether the > databases support nested aggregations via _public boolean > supportsNestedAggregations()_ we could either add another method like _public > boolean supportsNestedWindows()_ maybe _supportNestedWindowsInAggregation()_ > to be more verbose. Or we could reuse the existing method for the window > purposes (which seems non-transparent). Then we will be able to handle the > behavior in _SqlImplementor.needNewSubQuery()_ similarly as it already does > with the aggregations. > Please let me know if you agree and I will be more than happy to provide you > with PR to review, thanks! -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-4491) Aggregation of window function produces invalid SQL for PostgreSQL
[ https://issues.apache.org/jira/browse/CALCITE-4491?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde updated CALCITE-4491: - Fix Version/s: 1.27.0 > Aggregation of window function produces invalid SQL for PostgreSQL > -- > > Key: CALCITE-4491 > URL: https://issues.apache.org/jira/browse/CALCITE-4491 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.26.0 >Reporter: Dominik Labuda >Assignee: Julian Hyde >Priority: Major > Labels: pull-request-available > Fix For: 1.27.0 > > Time Spent: 0.5h > Remaining Estimate: 0h > > h2. Issue > We tested the following behavior against PostgreSQL database, however more of > the supported dialects may be affected. > When aggregating the results of a window function an invalid SQL is > generated. I was able to replicate the behavior in tests with: > {code:java} > @Test void testAggregatingWindowFunction() { > final RelBuilder builder = relBuilder(); > final RelNode root = builder > .scan("EMP") > .project( > builder.alias( > builder.getRexBuilder().makeOver( > > builder.getTypeFactory().createSqlType(SqlTypeName.INTEGER), > SqlStdOperatorTable.RANK, > new ArrayList<>(), > new ArrayList<>(), > ImmutableList.of(new > RexFieldCollation(builder.field("SAL"), ImmutableSet.of())), > RexWindowBounds.UNBOUNDED_PRECEDING, > RexWindowBounds.UNBOUNDED_FOLLOWING, > true, > true, > false, > false, > false > ), > "rank" > ) > ) > .as("tmp") > .aggregate( > builder.groupKey(), > builder.count( > true, > "cnt", > builder.field("tmp", "rank") > ) > ) > .build(); > final String expectedSql = "SELECT COUNT(DISTINCT \"rank\") AS \"cnt\"\n" > + > "FROM (SELECT RANK() OVER (ORDER BY \"SAL\") AS \"rank\"\n" + > "FROM \"scott\".\"EMP\") AS \"t\""; > assertThat( > toSql(root, PostgresqlSqlDialect.DEFAULT), > isLinux(expectedSql) > ); > } > {code} > The code above fails, since it produces this SQL instead of the expected one, > which cannot be executed on PGSQL: > {noformat} > SELECT COUNT(DISTINCT RANK() OVER (ORDER BY "SAL")) AS "cnt" > FROM "scott"."EMP" > {noformat} > In that case I am getting these kinds of errors from DB: > {noformat} > ERROR: aggregate function calls cannot contain window function calls > {noformat} > h2. Suggested solution > Since _SqlDialect_ already contains support for determining whether the > databases support nested aggregations via _public boolean > supportsNestedAggregations()_ we could either add another method like _public > boolean supportsNestedWindows()_ maybe _supportNestedWindowsInAggregation()_ > to be more verbose. Or we could reuse the existing method for the window > purposes (which seems non-transparent). Then we will be able to handle the > behavior in _SqlImplementor.needNewSubQuery()_ similarly as it already does > with the aggregations. > Please let me know if you agree and I will be more than happy to provide you > with PR to review, thanks! -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-4493) Postgres String Literal Equality Creates incorrect results.
James Starr created CALCITE-4493: Summary: Postgres String Literal Equality Creates incorrect results. Key: CALCITE-4493 URL: https://issues.apache.org/jira/browse/CALCITE-4493 Project: Calcite Issue Type: Bug Reporter: James Starr Calcite always interprets String Literals as CHAR type. This is normally not an issue since if are literal is being compared to VARCHAR column, then the literal is cast to a VARCHAR. VARCHAR and CHAR equality have slight different behavior. CHAR ignores trailing whitespace when comparing, where VARCHAR does not. Postgres defaults the a strings literals type as unkown in a given expression, but will default them to text if there evaluated to row. Postgres treats string literals types as unknown or TEXT. Postgres TEXT follows VARCHAR semantics. RelToSqlConverterTest.java {code:java} @Test void testDefaultTypeStringLiteralType() { RuleSet rules = RuleSets.ofList(CoreRules.PROJECT_VALUES_MERGE, CoreRules.PROJECT_REDUCE_EXPRESSIONS); HepProgramBuilder builder = new HepProgramBuilder(); builder.addRuleClass(ProjectToWindowRule.class); HepPlanner hepPlanner = new HepPlanner(builder.build()); sql("SELECT 'foo ' = 'foo'") .optimize(rules, hepPlanner) .withPostgresql().ok("SELECT *\nFROM (VALUES (TRUE)) AS \"t\" (\"EXPR$0\")"); } {code} This is generating incorrect results since postgres would treat the literals TEXT which follow the VARCHAR semantics and treats trailing whitespace as significant. {code:sql} postgres=# SELECT pg_typeof(a), pg_typeof('b') FROM (VALUES('foo')) AS t(a); pg_typeof | pg_typeof ---+--- text | unknown (1 row) {code} {code:sql} postgres=# SELECT 'foo ' = 'foo'; ?column? -- f (1 row) {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Assigned] (CALCITE-4492) ExceptionInInitializerError caused by ImmutableBeans
[ https://issues.apache.org/jira/browse/CALCITE-4492?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ruben Q L reassigned CALCITE-4492: -- Assignee: Ruben Q L > ExceptionInInitializerError caused by ImmutableBeans > > > Key: CALCITE-4492 > URL: https://issues.apache.org/jira/browse/CALCITE-4492 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.26.0 >Reporter: Ruben Q L >Assignee: Ruben Q L >Priority: Major > > While testing an application that uses Calcite, the following error is > observed at start-up on a Websphere9 environment (IBM J9 VM, Java8, Ubuntu): > {noformat} > Caused by: java.lang.ExceptionInInitializerError > at java.lang.J9VMInternals.ensureError(J9VMInternals.java:141) > at > java.lang.J9VMInternals.recordInitializationFailure(J9VMInternals.java:130) > ... > Caused by: java.lang.RuntimeException: while binding method public default > java.lang.Object > com.onwbp.org.apache.calcite.plan.RelRule$Config.as(java.lang.Class) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.makeDef(ImmutableBeans.java:285) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.access$000(ImmutableBeans.java:51) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans$1.load(ImmutableBeans.java:64) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans$1.load(ImmutableBeans.java:61) > at > com.onwbp.com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045) > at com.onwbp.com.google.common.cache.LocalCache.get(LocalCache.java:3951) > at > com.onwbp.com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974) > at > com.onwbp.com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4958) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.create_(ImmutableBeans.java:91) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.create(ImmutableBeans.java:72) > at > com.onwbp.org.apache.calcite.plan.RelRule$Config.(RelRule.java:121) > > ... 73 more > Caused by: java.lang.IllegalAccessException: > 'com.onwbp.org.apache.calcite.plan.RelRule$Config' no access to: > 'com.onwbp.org.apache.calcite.plan.RelRule$Config.as:(Config,Class)Object/invokeSpecial' > at > java.lang.invoke.MethodHandles$Lookup.checkAccess(MethodHandles.java:333) > at > java.lang.invoke.MethodHandles$Lookup.unreflectSpecial(MethodHandles.java:970) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.makeDef(ImmutableBeans.java:283) > ... 86 more > {noformat} > It would seem there is an issue around > {{org.apache.calcite.util.ImmutableBeans}} and the reflection mechanisms that > it uses, in the context of {{RelRule#Config}} (and specifically the default > method {{RelRule$Config.as}}). > Googling a bit, I found the following Flink ticket FLINK-19820, which has a > very similar exception message. In that case it was seen with Java9 in a > Debian environment, and the problem is also around {{ImmutableBeans}} in > {{RelBuilder#Config}}, and again the issue seems generated by a default > method ({{RelBuilder$Config.toBuilder}}). > The Flink ticket was closed as "Won't Fix" just because Flink does not > support Java9 (and apparently the issue did not occur with the > Flink-supported Java versions), but the underlying Calcite problem was never > tackled (and Calcite is supposed to work on Java9). -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-4492) ExceptionInInitializerError caused by ImmutableBeans
[ https://issues.apache.org/jira/browse/CALCITE-4492?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17282599#comment-17282599 ] Ruben Q L edited comment on CALCITE-4492 at 2/10/21, 5:50 PM: -- Not sure about shading's fault [~julianhyde]; however, maybe WebSphere is to blame here. WebSphere environment in this test was: {noformat} WebSphere Platform 9.0.0.1 ... [JAVA8 8.0.3.11] {noformat} I have found several issues on IBM involving {{IllegalAccessException}} around {{java.lang.invoke.MethodHandles$Lookup.checkAccess}} Particularly this one seems very, very similar at stack trace level: https://www.ibm.com/support/pages/apar/IV96259 According to IBM's ticket, this got fixed on *8.0.4.10*, so maybe a WebSphere update is just the solution, and there is no issue on Calcite side. I'll try to confirm this point. was (Author: rubenql): Not sure [~julianhyde], but maybe WebSphere is to blame here. WebSphere environment in this test was: {noformat} WebSphere Platform 9.0.0.1 ... [JAVA8 8.0.3.11] {noformat} I have found several issues on IBM involving {{IllegalAccessException}} around {{java.lang.invoke.MethodHandles$Lookup.checkAccess}} Particularly this one seems very, very similar at stack trace level: https://www.ibm.com/support/pages/apar/IV96259 According to IBM's ticket, this got fixed on *8.0.4.10*, so maybe a WebSphere update is just the solution, and there is no issue on Calcite side. I'll try to confirm this point. > ExceptionInInitializerError caused by ImmutableBeans > > > Key: CALCITE-4492 > URL: https://issues.apache.org/jira/browse/CALCITE-4492 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.26.0 >Reporter: Ruben Q L >Priority: Major > > While testing an application that uses Calcite, the following error is > observed at start-up on a Websphere9 environment (IBM J9 VM, Java8, Ubuntu): > {noformat} > Caused by: java.lang.ExceptionInInitializerError > at java.lang.J9VMInternals.ensureError(J9VMInternals.java:141) > at > java.lang.J9VMInternals.recordInitializationFailure(J9VMInternals.java:130) > ... > Caused by: java.lang.RuntimeException: while binding method public default > java.lang.Object > com.onwbp.org.apache.calcite.plan.RelRule$Config.as(java.lang.Class) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.makeDef(ImmutableBeans.java:285) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.access$000(ImmutableBeans.java:51) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans$1.load(ImmutableBeans.java:64) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans$1.load(ImmutableBeans.java:61) > at > com.onwbp.com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045) > at com.onwbp.com.google.common.cache.LocalCache.get(LocalCache.java:3951) > at > com.onwbp.com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974) > at > com.onwbp.com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4958) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.create_(ImmutableBeans.java:91) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.create(ImmutableBeans.java:72) > at > com.onwbp.org.apache.calcite.plan.RelRule$Config.(RelRule.java:121) > > ... 73 more > Caused by: java.lang.IllegalAccessException: > 'com.onwbp.org.apache.calcite.plan.RelRule$Config' no access to: > 'com.onwbp.org.apache.calcite.plan.RelRule$Config.as:(Config,Class)Object/invokeSpecial' > at > java.lang.invoke.MethodHandles$Lookup.checkAccess(MethodHandles.java:333) > at > java.lang.invoke.MethodHandles$Lookup.unreflectSpecial(MethodHandles.java:970) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.makeDef(ImmutableBeans.java:283) > ... 86 more > {noformat} > It would seem there is an issue around > {{org.apache.calcite.util.ImmutableBeans}} and the reflection mechanisms that > it uses, in the context of {{RelRule#Config}} (and specifically the default > method {{RelRule$Config.as}}). > Googling a bit, I found the following Flink ticket FLINK-19820, which has a > very similar exception message. In that case it was seen with Java9 in a > Debian environment, and the problem is also around {{ImmutableBeans}} in > {{RelBuilder#Config}}, and again the issue seems generated by a default > method ({{RelBuilder$Config.toBuilder}}). > The Flink ticket was closed as "Won't Fix" just because Flink does not >
[jira] [Comment Edited] (CALCITE-4492) ExceptionInInitializerError caused by ImmutableBeans
[ https://issues.apache.org/jira/browse/CALCITE-4492?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17282599#comment-17282599 ] Ruben Q L edited comment on CALCITE-4492 at 2/10/21, 5:48 PM: -- Not sure [~julianhyde], but maybe WebSphere is to blame here. WebSphere environment in this test was: {noformat} WebSphere Platform 9.0.0.1 ... [JAVA8 8.0.3.11] {noformat} I have found several issues on IBM involving {{IllegalAccessException}} around {{java.lang.invoke.MethodHandles$Lookup.checkAccess}} Particularly this one seems very, very similar at stack trace level: https://www.ibm.com/support/pages/apar/IV96259 According to IBM's ticket, this got fixed on *8.0.4.10*, so maybe a WebSphere update is just the solution, and there is no issue on Calcite side. I'll try to confirm this point. was (Author: rubenql): Not sure [~julianhyde], but maybe WebSphere is to blame here. WebSphere environment in this test was: {noformat} WebSphere Platform 9.0.0.1 ... [JAVA8 8.0.3.11] {noformat} I have found several issues on IBM involving {{IllegalAccessException}} around {{java.lang.invoke.MethodHandles$Lookup.checkAccess}} Particularly this one seems very, very similar at stack trace level: https://www.ibm.com/support/pages/apar/IV96259 According to IBM's ticket, this got fixed on 8.0.4.10, so maybe a WebSphere update is just the solution, and there is no issue on Calcite side. I'll try to confirm this point. > ExceptionInInitializerError caused by ImmutableBeans > > > Key: CALCITE-4492 > URL: https://issues.apache.org/jira/browse/CALCITE-4492 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.26.0 >Reporter: Ruben Q L >Priority: Major > > While testing an application that uses Calcite, the following error is > observed at start-up on a Websphere9 environment (IBM J9 VM, Java8, Ubuntu): > {noformat} > Caused by: java.lang.ExceptionInInitializerError > at java.lang.J9VMInternals.ensureError(J9VMInternals.java:141) > at > java.lang.J9VMInternals.recordInitializationFailure(J9VMInternals.java:130) > ... > Caused by: java.lang.RuntimeException: while binding method public default > java.lang.Object > com.onwbp.org.apache.calcite.plan.RelRule$Config.as(java.lang.Class) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.makeDef(ImmutableBeans.java:285) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.access$000(ImmutableBeans.java:51) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans$1.load(ImmutableBeans.java:64) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans$1.load(ImmutableBeans.java:61) > at > com.onwbp.com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045) > at com.onwbp.com.google.common.cache.LocalCache.get(LocalCache.java:3951) > at > com.onwbp.com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974) > at > com.onwbp.com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4958) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.create_(ImmutableBeans.java:91) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.create(ImmutableBeans.java:72) > at > com.onwbp.org.apache.calcite.plan.RelRule$Config.(RelRule.java:121) > > ... 73 more > Caused by: java.lang.IllegalAccessException: > 'com.onwbp.org.apache.calcite.plan.RelRule$Config' no access to: > 'com.onwbp.org.apache.calcite.plan.RelRule$Config.as:(Config,Class)Object/invokeSpecial' > at > java.lang.invoke.MethodHandles$Lookup.checkAccess(MethodHandles.java:333) > at > java.lang.invoke.MethodHandles$Lookup.unreflectSpecial(MethodHandles.java:970) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.makeDef(ImmutableBeans.java:283) > ... 86 more > {noformat} > It would seem there is an issue around > {{org.apache.calcite.util.ImmutableBeans}} and the reflection mechanisms that > it uses, in the context of {{RelRule#Config}} (and specifically the default > method {{RelRule$Config.as}}). > Googling a bit, I found the following Flink ticket FLINK-19820, which has a > very similar exception message. In that case it was seen with Java9 in a > Debian environment, and the problem is also around {{ImmutableBeans}} in > {{RelBuilder#Config}}, and again the issue seems generated by a default > method ({{RelBuilder$Config.toBuilder}}). > The Flink ticket was closed as "Won't Fix" just because Flink does not > support Java9 (and apparently the
[jira] [Commented] (CALCITE-4492) ExceptionInInitializerError caused by ImmutableBeans
[ https://issues.apache.org/jira/browse/CALCITE-4492?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17282599#comment-17282599 ] Ruben Q L commented on CALCITE-4492: Not sure [~julianhyde], but maybe WebSphere is to blame here. WebSphere environment in this test was: {noformat} WebSphere Platform 9.0.0.1 ... [JAVA8 8.0.3.11] {noformat} I have found several issues on IBM involving {{IllegalAccessException}} around {{java.lang.invoke.MethodHandles$Lookup.checkAccess}} Particularly this one seems very, very similar at stack trace level: https://www.ibm.com/support/pages/apar/IV96259 According to IBM's ticket, this got fixed on 8.0.4.10, so maybe a WebSphere update is just the solution, and there is no issue on Calcite side. I'll try to confirm this point. > ExceptionInInitializerError caused by ImmutableBeans > > > Key: CALCITE-4492 > URL: https://issues.apache.org/jira/browse/CALCITE-4492 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.26.0 >Reporter: Ruben Q L >Priority: Major > > While testing an application that uses Calcite, the following error is > observed at start-up on a Websphere9 environment (IBM J9 VM, Java8, Ubuntu): > {noformat} > Caused by: java.lang.ExceptionInInitializerError > at java.lang.J9VMInternals.ensureError(J9VMInternals.java:141) > at > java.lang.J9VMInternals.recordInitializationFailure(J9VMInternals.java:130) > ... > Caused by: java.lang.RuntimeException: while binding method public default > java.lang.Object > com.onwbp.org.apache.calcite.plan.RelRule$Config.as(java.lang.Class) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.makeDef(ImmutableBeans.java:285) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.access$000(ImmutableBeans.java:51) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans$1.load(ImmutableBeans.java:64) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans$1.load(ImmutableBeans.java:61) > at > com.onwbp.com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045) > at com.onwbp.com.google.common.cache.LocalCache.get(LocalCache.java:3951) > at > com.onwbp.com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974) > at > com.onwbp.com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4958) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.create_(ImmutableBeans.java:91) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.create(ImmutableBeans.java:72) > at > com.onwbp.org.apache.calcite.plan.RelRule$Config.(RelRule.java:121) > > ... 73 more > Caused by: java.lang.IllegalAccessException: > 'com.onwbp.org.apache.calcite.plan.RelRule$Config' no access to: > 'com.onwbp.org.apache.calcite.plan.RelRule$Config.as:(Config,Class)Object/invokeSpecial' > at > java.lang.invoke.MethodHandles$Lookup.checkAccess(MethodHandles.java:333) > at > java.lang.invoke.MethodHandles$Lookup.unreflectSpecial(MethodHandles.java:970) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.makeDef(ImmutableBeans.java:283) > ... 86 more > {noformat} > It would seem there is an issue around > {{org.apache.calcite.util.ImmutableBeans}} and the reflection mechanisms that > it uses, in the context of {{RelRule#Config}} (and specifically the default > method {{RelRule$Config.as}}). > Googling a bit, I found the following Flink ticket FLINK-19820, which has a > very similar exception message. In that case it was seen with Java9 in a > Debian environment, and the problem is also around {{ImmutableBeans}} in > {{RelBuilder#Config}}, and again the issue seems generated by a default > method ({{RelBuilder$Config.toBuilder}}). > The Flink ticket was closed as "Won't Fix" just because Flink does not > support Java9 (and apparently the issue did not occur with the > Flink-supported Java versions), but the underlying Calcite problem was never > tackled (and Calcite is supposed to work on Java9). -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-4492) ExceptionInInitializerError caused by ImmutableBeans
[ https://issues.apache.org/jira/browse/CALCITE-4492?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17282591#comment-17282591 ] Julian Hyde commented on CALCITE-4492: -- Shading might be a key factor here. > ExceptionInInitializerError caused by ImmutableBeans > > > Key: CALCITE-4492 > URL: https://issues.apache.org/jira/browse/CALCITE-4492 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.26.0 >Reporter: Ruben Q L >Priority: Major > > While testing an application that uses Calcite, the following error is > observed at start-up on a Websphere9 environment (IBM J9 VM, Java8, Ubuntu): > {noformat} > Caused by: java.lang.ExceptionInInitializerError > at java.lang.J9VMInternals.ensureError(J9VMInternals.java:141) > at > java.lang.J9VMInternals.recordInitializationFailure(J9VMInternals.java:130) > ... > Caused by: java.lang.RuntimeException: while binding method public default > java.lang.Object > com.onwbp.org.apache.calcite.plan.RelRule$Config.as(java.lang.Class) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.makeDef(ImmutableBeans.java:285) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.access$000(ImmutableBeans.java:51) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans$1.load(ImmutableBeans.java:64) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans$1.load(ImmutableBeans.java:61) > at > com.onwbp.com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155) > at > com.onwbp.com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045) > at com.onwbp.com.google.common.cache.LocalCache.get(LocalCache.java:3951) > at > com.onwbp.com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974) > at > com.onwbp.com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4958) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.create_(ImmutableBeans.java:91) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.create(ImmutableBeans.java:72) > at > com.onwbp.org.apache.calcite.plan.RelRule$Config.(RelRule.java:121) > > ... 73 more > Caused by: java.lang.IllegalAccessException: > 'com.onwbp.org.apache.calcite.plan.RelRule$Config' no access to: > 'com.onwbp.org.apache.calcite.plan.RelRule$Config.as:(Config,Class)Object/invokeSpecial' > at > java.lang.invoke.MethodHandles$Lookup.checkAccess(MethodHandles.java:333) > at > java.lang.invoke.MethodHandles$Lookup.unreflectSpecial(MethodHandles.java:970) > at > com.onwbp.org.apache.calcite.util.ImmutableBeans.makeDef(ImmutableBeans.java:283) > ... 86 more > {noformat} > It would seem there is an issue around > {{org.apache.calcite.util.ImmutableBeans}} and the reflection mechanisms that > it uses, in the context of {{RelRule#Config}} (and specifically the default > method {{RelRule$Config.as}}). > Googling a bit, I found the following Flink ticket FLINK-19820, which has a > very similar exception message. In that case it was seen with Java9 in a > Debian environment, and the problem is also around {{ImmutableBeans}} in > {{RelBuilder#Config}}, and again the issue seems generated by a default > method ({{RelBuilder$Config.toBuilder}}). > The Flink ticket was closed as "Won't Fix" just because Flink does not > support Java9 (and apparently the issue did not occur with the > Flink-supported Java versions), but the underlying Calcite problem was never > tackled (and Calcite is supposed to work on Java9). -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-4491) Aggregation of window function produces invalid SQL for PostgreSQL
[ https://issues.apache.org/jira/browse/CALCITE-4491?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17282459#comment-17282459 ] Dominik Labuda commented on CALCITE-4491: - Thanks for the initial review [~julianhyde], I've commited the changes that address the issues above, except for the commit message, I will leave that to the final squash/rebase stage. > Aggregation of window function produces invalid SQL for PostgreSQL > -- > > Key: CALCITE-4491 > URL: https://issues.apache.org/jira/browse/CALCITE-4491 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.26.0 >Reporter: Dominik Labuda >Priority: Major > Labels: pull-request-available > Time Spent: 0.5h > Remaining Estimate: 0h > > h2. Issue > We tested the following behavior against PostgreSQL database, however more of > the supported dialects may be affected. > When aggregating the results of a window function an invalid SQL is > generated. I was able to replicate the behavior in tests with: > {code:java} > @Test void testAggregatingWindowFunction() { > final RelBuilder builder = relBuilder(); > final RelNode root = builder > .scan("EMP") > .project( > builder.alias( > builder.getRexBuilder().makeOver( > > builder.getTypeFactory().createSqlType(SqlTypeName.INTEGER), > SqlStdOperatorTable.RANK, > new ArrayList<>(), > new ArrayList<>(), > ImmutableList.of(new > RexFieldCollation(builder.field("SAL"), ImmutableSet.of())), > RexWindowBounds.UNBOUNDED_PRECEDING, > RexWindowBounds.UNBOUNDED_FOLLOWING, > true, > true, > false, > false, > false > ), > "rank" > ) > ) > .as("tmp") > .aggregate( > builder.groupKey(), > builder.count( > true, > "cnt", > builder.field("tmp", "rank") > ) > ) > .build(); > final String expectedSql = "SELECT COUNT(DISTINCT \"rank\") AS \"cnt\"\n" > + > "FROM (SELECT RANK() OVER (ORDER BY \"SAL\") AS \"rank\"\n" + > "FROM \"scott\".\"EMP\") AS \"t\""; > assertThat( > toSql(root, PostgresqlSqlDialect.DEFAULT), > isLinux(expectedSql) > ); > } > {code} > The code above fails, since it produces this SQL instead of the expected one, > which cannot be executed on PGSQL: > {noformat} > SELECT COUNT(DISTINCT RANK() OVER (ORDER BY "SAL")) AS "cnt" > FROM "scott"."EMP" > {noformat} > In that case I am getting these kinds of errors from DB: > {noformat} > ERROR: aggregate function calls cannot contain window function calls > {noformat} > h2. Suggested solution > Since _SqlDialect_ already contains support for determining whether the > databases support nested aggregations via _public boolean > supportsNestedAggregations()_ we could either add another method like _public > boolean supportsNestedWindows()_ maybe _supportNestedWindowsInAggregation()_ > to be more verbose. Or we could reuse the existing method for the window > purposes (which seems non-transparent). Then we will be able to handle the > behavior in _SqlImplementor.needNewSubQuery()_ similarly as it already does > with the aggregations. > Please let me know if you agree and I will be more than happy to provide you > with PR to review, thanks! -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Assigned] (CALCITE-4437) The Sort rel should be decorrelated even though it has fetch or limit when it is not inside a Correlate
[ https://issues.apache.org/jira/browse/CALCITE-4437?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ruben Q L reassigned CALCITE-4437: -- Assignee: Thomas Rebele (was: Ruben Q L) > The Sort rel should be decorrelated even though it has fetch or limit when it > is not inside a Correlate > --- > > Key: CALCITE-4437 > URL: https://issues.apache.org/jira/browse/CALCITE-4437 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.26.0 >Reporter: Ruben Q L >Assignee: Thomas Rebele >Priority: Major > Labels: pull-request-available > Fix For: 1.27.0 > > Time Spent: 10m > Remaining Estimate: 0h > > The fix applied for CALCITE-4206 was "too drastic" and it resulted in Sort > with fetch/offset being impossible to decorrelate in all cases. > CALCITE-4333 addressed this issue but only partially (when the Sort with > fetch/offset is on top on the plan). However, this solution is insufficient, > because any Sort with fetch/offset that is not inside a Correlate can be > decorrelated. > Check this test in SqlToRelConverterTest (same test as CALCITE-4333, just > with an extra LogicalProject on top of the LogicalSort): > {code} > @Test void testProjectSortLimitWithCorrelateInput() { > final String sql = "" > + "SELECT ename||deptno FROM\n" > + "(SELECT deptno, ename\n" > + "FROM\n" > + "(SELECT DISTINCT deptno FROM emp) t1,\n" > + " LATERAL (\n" > + "SELECT ename, sal\n" > + "FROM emp\n" > + "WHERE deptno = t1.deptno)\n" > + "ORDER BY ename DESC\n" > + "LIMIT 3)"; > sql(sql).ok(); > } > {code} > The current plan is: > {noformat} > LogicalProject(EXPR$0=[||($1, CAST($0):VARCHAR NOT NULL)]) > LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3]) > LogicalProject(DEPTNO=[$0], ENAME=[$1]) > LogicalCorrelate(correlation=[$cor0], joinType=[inner], > requiredColumns=[{0}]) > LogicalAggregate(group=[{0}]) > LogicalProject(DEPTNO=[$7]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalProject(ENAME=[$1], SAL=[$5]) > LogicalFilter(condition=[=($7, $cor0.DEPTNO)]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > {noformat} > It can actually be decorrelated as: > {noformat} > LogicalProject(EXPR$0=[||($1, CAST($0):VARCHAR NOT NULL)]) > LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3]) > LogicalProject(DEPTNO=[$0], ENAME=[$1]) > LogicalJoin(condition=[=($0, $3)], joinType=[inner]) > LogicalAggregate(group=[{0}]) > LogicalProject(DEPTNO=[$7]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > {noformat} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Assigned] (CALCITE-4437) The Sort rel should be decorrelated even though it has fetch or limit when it is not inside a Correlate
[ https://issues.apache.org/jira/browse/CALCITE-4437?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ruben Q L reassigned CALCITE-4437: -- Assignee: Ruben Q L > The Sort rel should be decorrelated even though it has fetch or limit when it > is not inside a Correlate > --- > > Key: CALCITE-4437 > URL: https://issues.apache.org/jira/browse/CALCITE-4437 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.26.0 >Reporter: Ruben Q L >Assignee: Ruben Q L >Priority: Major > Labels: pull-request-available > Fix For: 1.27.0 > > Time Spent: 10m > Remaining Estimate: 0h > > The fix applied for CALCITE-4206 was "too drastic" and it resulted in Sort > with fetch/offset being impossible to decorrelate in all cases. > CALCITE-4333 addressed this issue but only partially (when the Sort with > fetch/offset is on top on the plan). However, this solution is insufficient, > because any Sort with fetch/offset that is not inside a Correlate can be > decorrelated. > Check this test in SqlToRelConverterTest (same test as CALCITE-4333, just > with an extra LogicalProject on top of the LogicalSort): > {code} > @Test void testProjectSortLimitWithCorrelateInput() { > final String sql = "" > + "SELECT ename||deptno FROM\n" > + "(SELECT deptno, ename\n" > + "FROM\n" > + "(SELECT DISTINCT deptno FROM emp) t1,\n" > + " LATERAL (\n" > + "SELECT ename, sal\n" > + "FROM emp\n" > + "WHERE deptno = t1.deptno)\n" > + "ORDER BY ename DESC\n" > + "LIMIT 3)"; > sql(sql).ok(); > } > {code} > The current plan is: > {noformat} > LogicalProject(EXPR$0=[||($1, CAST($0):VARCHAR NOT NULL)]) > LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3]) > LogicalProject(DEPTNO=[$0], ENAME=[$1]) > LogicalCorrelate(correlation=[$cor0], joinType=[inner], > requiredColumns=[{0}]) > LogicalAggregate(group=[{0}]) > LogicalProject(DEPTNO=[$7]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalProject(ENAME=[$1], SAL=[$5]) > LogicalFilter(condition=[=($7, $cor0.DEPTNO)]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > {noformat} > It can actually be decorrelated as: > {noformat} > LogicalProject(EXPR$0=[||($1, CAST($0):VARCHAR NOT NULL)]) > LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3]) > LogicalProject(DEPTNO=[$0], ENAME=[$1]) > LogicalJoin(condition=[=($0, $3)], joinType=[inner]) > LogicalAggregate(group=[{0}]) > LogicalProject(DEPTNO=[$7]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > {noformat} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-4437) The Sort rel should be decorrelated even though it has fetch or limit when it is not inside a Correlate
[ https://issues.apache.org/jira/browse/CALCITE-4437?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ruben Q L updated CALCITE-4437: --- Description: The fix applied for CALCITE-4206 was "too drastic" and it resulted in Sort with fetch/offset being impossible to decorrelate in all cases. CALCITE-4333 addressed this issue but only partially (when the Sort with fetch/offset is on top on the plan). However, this solution is insufficient, because any Sort with fetch/offset that is not inside a Correlate can be decorrelated. Check this test in SqlToRelConverterTest (same test as CALCITE-4333, just with an extra LogicalProject on top of the LogicalSort): {code} @Test void testProjectSortLimitWithCorrelateInput() { final String sql = "" + "SELECT ename||deptno FROM\n" + "(SELECT deptno, ename\n" + "FROM\n" + "(SELECT DISTINCT deptno FROM emp) t1,\n" + " LATERAL (\n" + "SELECT ename, sal\n" + "FROM emp\n" + "WHERE deptno = t1.deptno)\n" + "ORDER BY ename DESC\n" + "LIMIT 3)"; sql(sql).ok(); } {code} The current plan is: {noformat} LogicalProject(EXPR$0=[||($1, CAST($0):VARCHAR NOT NULL)]) LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3]) LogicalProject(DEPTNO=[$0], ENAME=[$1]) LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}]) LogicalAggregate(group=[{0}]) LogicalProject(DEPTNO=[$7]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalProject(ENAME=[$1], SAL=[$5]) LogicalFilter(condition=[=($7, $cor0.DEPTNO)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {noformat} It can actually be decorrelated as: {noformat} LogicalProject(EXPR$0=[||($1, CAST($0):VARCHAR NOT NULL)]) LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3]) LogicalProject(DEPTNO=[$0], ENAME=[$1]) LogicalJoin(condition=[=($0, $3)], joinType=[inner]) LogicalAggregate(group=[{0}]) LogicalProject(DEPTNO=[$7]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {noformat} was: The fix applied for CALCITE-4206 was "too drastic" and it resulted in Sort with fetch/offset being impossible to decorrelate in all cases. CALCITE-4333 addressed this issue but only partially (when the Sort with fetch/offset is on top on the plan). However, this solution is insufficient, because any Sort with fetch/offset that is not inside a Correlate can be decorrelated. Check this test in SqlToRelConverterTest (same test as CALCITE-4333, just with an extra LogicalProject on top of the LogicalSort): {code} @Test void testProjectSortLimitWithCorrelateInput() { final String sql = "" + "SELECT ename||deptno FROM\n" + "(SELECT deptno, ename\n" + "FROM\n" + "(SELECT DISTINCT deptno FROM emp) t1,\n" + " LATERAL (\n" + "SELECT ename, sal\n" + "FROM emp\n" + "WHERE deptno = t1.deptno)\n" + "ORDER BY ename DESC\n" + "LIMIT 3)"; sql(sql).ok(); } {code} The current plan is: {noformat} LogicalProject(EXPR$0=[||($1, CAST($0):VARCHAR NOT NULL)]) LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3]) LogicalProject(DEPTNO=[$0], ENAME=[$1]) LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}]) LogicalAggregate(group=[{0}]) LogicalProject(DEPTNO=[$7]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalProject(ENAME=[$1], SAL=[$5]) LogicalFilter(condition=[=($7, $cor0.DEPTNO)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {noformat} It can actually decorrelated as: {noformat} LogicalProject(EXPR$0=[||($1, CAST($0):VARCHAR NOT NULL)]) LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3]) LogicalProject(DEPTNO=[$0], ENAME=[$1]) LogicalJoin(condition=[=($0, $3)], joinType=[inner]) LogicalAggregate(group=[{0}]) LogicalProject(DEPTNO=[$7]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {noformat} > The Sort rel should be decorrelated even though it has fetch or limit when it > is not inside a Correlate > --- > > Key: CALCITE-4437 > URL: https://issues.apache.org/jira/browse/CALCITE-4437 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.26.0 >Reporter: Ruben Q L >Priority: Major >
[jira] [Created] (CALCITE-4492) ExceptionInInitializerError caused by ImmutableBeans
Ruben Q L created CALCITE-4492: -- Summary: ExceptionInInitializerError caused by ImmutableBeans Key: CALCITE-4492 URL: https://issues.apache.org/jira/browse/CALCITE-4492 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.26.0 Reporter: Ruben Q L While testing an application that uses Calcite, the following error is observed at start-up on a Websphere9 environment (IBM J9 VM, Java8, Ubuntu): {noformat} Caused by: java.lang.ExceptionInInitializerError at java.lang.J9VMInternals.ensureError(J9VMInternals.java:141) at java.lang.J9VMInternals.recordInitializationFailure(J9VMInternals.java:130) ... Caused by: java.lang.RuntimeException: while binding method public default java.lang.Object com.onwbp.org.apache.calcite.plan.RelRule$Config.as(java.lang.Class) at com.onwbp.org.apache.calcite.util.ImmutableBeans.makeDef(ImmutableBeans.java:285) at com.onwbp.org.apache.calcite.util.ImmutableBeans.access$000(ImmutableBeans.java:51) at com.onwbp.org.apache.calcite.util.ImmutableBeans$1.load(ImmutableBeans.java:64) at com.onwbp.org.apache.calcite.util.ImmutableBeans$1.load(ImmutableBeans.java:61) at com.onwbp.com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529) at com.onwbp.com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278) at com.onwbp.com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155) at com.onwbp.com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045) at com.onwbp.com.google.common.cache.LocalCache.get(LocalCache.java:3951) at com.onwbp.com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974) at com.onwbp.com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4958) at com.onwbp.org.apache.calcite.util.ImmutableBeans.create_(ImmutableBeans.java:91) at com.onwbp.org.apache.calcite.util.ImmutableBeans.create(ImmutableBeans.java:72) at com.onwbp.org.apache.calcite.plan.RelRule$Config.(RelRule.java:121) ... 73 more Caused by: java.lang.IllegalAccessException: 'com.onwbp.org.apache.calcite.plan.RelRule$Config' no access to: 'com.onwbp.org.apache.calcite.plan.RelRule$Config.as:(Config,Class)Object/invokeSpecial' at java.lang.invoke.MethodHandles$Lookup.checkAccess(MethodHandles.java:333) at java.lang.invoke.MethodHandles$Lookup.unreflectSpecial(MethodHandles.java:970) at com.onwbp.org.apache.calcite.util.ImmutableBeans.makeDef(ImmutableBeans.java:283) ... 86 more {noformat} It would seem there is an issue around {{org.apache.calcite.util.ImmutableBeans}} and the reflection mechanisms that it uses, in the context of {{RelRule#Config}} (and specifically the default method {{RelRule$Config.as}}). Googling a bit, I found the following Flink ticket FLINK-19820, which has a very similar exception message. In that case it was seen with Java9 in a Debian environment, and the problem is also around {{ImmutableBeans}} in {{RelBuilder#Config}}, and again the issue seems generated by a default method ({{RelBuilder$Config.toBuilder}}). The Flink ticket was closed as "Won't Fix" just because Flink does not support Java9 (and apparently the issue did not occur with the Flink-supported Java versions), but the underlying Calcite problem was never tackled (and Calcite is supposed to work on Java9). -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Resolved] (CALCITE-3221) Add MergeUnion operator in Enumerable convention
[ https://issues.apache.org/jira/browse/CALCITE-3221?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ruben Q L resolved CALCITE-3221. Resolution: Fixed Done via https://github.com/apache/calcite/commit/4cd90f36c3cf9a012e34f14129a907a4ce99c6f5 Thanks everyone for the feedback, especially [~vladimirsitnikov] for his suggestions to improve the patch. > Add MergeUnion operator in Enumerable convention > > > Key: CALCITE-3221 > URL: https://issues.apache.org/jira/browse/CALCITE-3221 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.19.0 >Reporter: Stamatis Zampetakis >Assignee: Ruben Q L >Priority: Minor > Labels: pull-request-available > Fix For: 1.27.0 > > Attachments: screenshot-1.png > > Time Spent: 15h 10m > Remaining Estimate: 0h > > Currently, the union operation offered by Calcite (see > [EnumerableDefaults.union|https://github.com/apache/calcite/blob/d98856bf1a5f5c151d004b769e14bdd368a67234/linq4j/src/main/java/org/apache/calcite/linq4j/EnumerableDefaults.java#L2747]) > "breaks" the collation (if any) of its inputs. > The goal of this issue is to create a new union algorithm in Enumerable > convention (EnumerableMergeUnion) that, given the fact that its inputs are > sorted by the same collation, will return the union / union all result > respecting this collation. > Most likely the implementation of the merge join can be useful. -- This message was sent by Atlassian Jira (v8.3.4#803005)