[jira] [Comment Edited] (CALCITE-5598) Expand expression in a GROUP BY clause returns wrong result in case of expressions referenced by column index
[ https://issues.apache.org/jira/browse/CALCITE-5598?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716481#comment-17716481 ] Runkang He edited comment on CALCITE-5598 at 4/26/23 1:10 AM: -- [~julianhyde] I'm not sure how BigQuery uses Calcite, does it only use the parsing layer? If so, this problem is BigQuery's part, not Calcite's part. was (Author: JIRAUSER280488): [~julianhyde] I'm not sure how BigQuery uses calcite, does it only use the parsing layer? If so, this problem is BigQuery's part, not Calcite's part. > Expand expression in a GROUP BY clause returns wrong result in case of > expressions referenced by column index > - > > Key: CALCITE-5598 > URL: https://issues.apache.org/jira/browse/CALCITE-5598 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.34.0 >Reporter: Zine eddine Zidane >Priority: Blocker > > Given the following BigQuery query: > > {code:java} > SELECT CAST(user_id AS STRING) AS id, COUNT(*) FROM `users` GROUP BY 1 HAVING > COUNT (*)>1;{code} > > SqlValidatorImpl extendedExpand method returns "CAST(user_id AS STRING)" for > expression “1” in the GROUP BY clause resulting in the following validated > query: > > {code:java} > SELECT CAST(user_id AS STRING) AS id, COUNT(*) FROM `users` GROUP BY > CAST(user_id AS STRING) HAVING COUNT (*)>1;{code} > > This query fails on BigQuery with the error: > {code:java} > SELECT list expression references user_id which is neither grouped nor > aggregated at [1:24] on source.{code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5598) Expand expression in a GROUP BY clause returns wrong result in case of expressions referenced by column index
[ https://issues.apache.org/jira/browse/CALCITE-5598?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716481#comment-17716481 ] Runkang He edited comment on CALCITE-5598 at 4/26/23 1:10 AM: -- [~julianhyde] I'm not sure how BigQuery uses calcite, does it only use the parsing layer? If so, this problem is BigQuery's part, not Calcite's part. was (Author: JIRAUSER280488): [~julianhyde] I'm not sure how bigquery uses calcite, does it only use the parsing layer? If so, this problem is BigQuery's part, not Calcite's part. > Expand expression in a GROUP BY clause returns wrong result in case of > expressions referenced by column index > - > > Key: CALCITE-5598 > URL: https://issues.apache.org/jira/browse/CALCITE-5598 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.34.0 >Reporter: Zine eddine Zidane >Priority: Blocker > > Given the following BigQuery query: > > {code:java} > SELECT CAST(user_id AS STRING) AS id, COUNT(*) FROM `users` GROUP BY 1 HAVING > COUNT (*)>1;{code} > > SqlValidatorImpl extendedExpand method returns "CAST(user_id AS STRING)" for > expression “1” in the GROUP BY clause resulting in the following validated > query: > > {code:java} > SELECT CAST(user_id AS STRING) AS id, COUNT(*) FROM `users` GROUP BY > CAST(user_id AS STRING) HAVING COUNT (*)>1;{code} > > This query fails on BigQuery with the error: > {code:java} > SELECT list expression references user_id which is neither grouped nor > aggregated at [1:24] on source.{code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5598) Expand expression in a GROUP BY clause returns wrong result in case of expressions referenced by column index
[ https://issues.apache.org/jira/browse/CALCITE-5598?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716481#comment-17716481 ] Runkang He commented on CALCITE-5598: - [~julianhyde] I'm not sure how bigquery uses calcite, does it only use the parsing layer? If so, this problem is BigQuery's part, not Calcite's part. > Expand expression in a GROUP BY clause returns wrong result in case of > expressions referenced by column index > - > > Key: CALCITE-5598 > URL: https://issues.apache.org/jira/browse/CALCITE-5598 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.34.0 >Reporter: Zine eddine Zidane >Priority: Blocker > > Given the following BigQuery query: > > {code:java} > SELECT CAST(user_id AS STRING) AS id, COUNT(*) FROM `users` GROUP BY 1 HAVING > COUNT (*)>1;{code} > > SqlValidatorImpl extendedExpand method returns "CAST(user_id AS STRING)" for > expression “1” in the GROUP BY clause resulting in the following validated > query: > > {code:java} > SELECT CAST(user_id AS STRING) AS id, COUNT(*) FROM `users` GROUP BY > CAST(user_id AS STRING) HAVING COUNT (*)>1;{code} > > This query fails on BigQuery with the error: > {code:java} > SELECT list expression references user_id which is neither grouped nor > aggregated at [1:24] on source.{code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5614) Serialize and deserialize Sarg objects
[ https://issues.apache.org/jira/browse/CALCITE-5614?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716471#comment-17716471 ] Julian Hyde commented on CALCITE-5614: -- Here's where we convert to string: {code} // Test that toRex is the same as toJsonString -> readRex final String s = jsonBuilder.toJsonString(jsonRepresentation); RexNode deserialized2; try { deserialized2 = RelJsonReader.readRex(b.getCluster(), s); } catch (IOException e) { throw new RuntimeException(e); } {code} I converted the commented block to 'if (false)' so at least we know the code is valid. Before merge we should remove 'if (false)' or remove the code entirely. > Serialize and deserialize Sarg objects > -- > > Key: CALCITE-5614 > URL: https://issues.apache.org/jira/browse/CALCITE-5614 > Project: Calcite > Issue Type: Improvement >Reporter: Oliver Lee >Assignee: Oliver Lee >Priority: Major > Labels: pull-request-available > Time Spent: 1h 20m > Remaining Estimate: 0h > > Context: Bounded filter conditions (e.g. {{{}SUM(field> > 1 AND SUM(field) < > 10{}}}) are converted to calls to a [SEARCH > operator.|https://github.com/apache/calcite/blob/052a5cc724a889edf19c1d76ea166c0d0924a5d3/core/src/main/java/org/apache/calcite/rex/RexInterpreter.java#L224calls] > If this is then serialized and deserialized, it will throw an exception when > resolving {{{}SEARCH{}}}. > > > To complete this ticket: Add functionality such that {{Sarg}} literals can be > serialized and deserialized. > > {{RelJson.java}} should be updated so that {{toRex}} can serialize {{Sarg}} > objects > > {{RexBuilder.java}} should be updated to be able to successfully deserialize > a JSON string that includes {{Sarg}} literals -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5614) Serialize and deserialize Sarg objects
[ https://issues.apache.org/jira/browse/CALCITE-5614?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716466#comment-17716466 ] Oliver Lee commented on CALCITE-5614: - I've managed to read through all the commits, I do like the use of Handler and the simplification into {{{}all(){}}}, {{{}atLeast(){}}}, etc. I don't think I saw how the test is converting to a string in {{RelWriterTest.java}} I only see that it's refactored into the {{consumer}} instead of my {{{}for-loop{}}}, and the uncommenting of the block that is still under {{if (false)}} Do you mind pointing me to that? Everything else looks good and is much cleaner, thanks! > Serialize and deserialize Sarg objects > -- > > Key: CALCITE-5614 > URL: https://issues.apache.org/jira/browse/CALCITE-5614 > Project: Calcite > Issue Type: Improvement >Reporter: Oliver Lee >Assignee: Oliver Lee >Priority: Major > Labels: pull-request-available > Time Spent: 1h 20m > Remaining Estimate: 0h > > Context: Bounded filter conditions (e.g. {{{}SUM(field> > 1 AND SUM(field) < > 10{}}}) are converted to calls to a [SEARCH > operator.|https://github.com/apache/calcite/blob/052a5cc724a889edf19c1d76ea166c0d0924a5d3/core/src/main/java/org/apache/calcite/rex/RexInterpreter.java#L224calls] > If this is then serialized and deserialized, it will throw an exception when > resolving {{{}SEARCH{}}}. > > > To complete this ticket: Add functionality such that {{Sarg}} literals can be > serialized and deserialized. > > {{RelJson.java}} should be updated so that {{toRex}} can serialize {{Sarg}} > objects > > {{RexBuilder.java}} should be updated to be able to successfully deserialize > a JSON string that includes {{Sarg}} literals -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5549) Appropriately set `DATA_TYPE` and `IS_GENERATEDCOLUMN` metadata values for `MEASURE` types
[ https://issues.apache.org/jira/browse/CALCITE-5549?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716460#comment-17716460 ] TJ Banghart commented on CALCITE-5549: -- I opened [CALCITE-5676|https://issues.apache.org/jira/browse/CALCITE-5676] as a sub-task since {{DATA_TYPE}} and {{TYPE_NAME}} can be updated without a change to Avatica. {{IS_GENERATED}} will still require an Avatica bump. > Appropriately set `DATA_TYPE` and `IS_GENERATEDCOLUMN` metadata values for > `MEASURE` types > -- > > Key: CALCITE-5549 > URL: https://issues.apache.org/jira/browse/CALCITE-5549 > Project: Calcite > Issue Type: Improvement > Components: avatica, core >Reporter: TJ Banghart >Assignee: TJ Banghart >Priority: Minor > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > {{MEASURE}} types (introduced in > [CALCITE-5105|https://issues.apache.org/jira/browse/CALCITE-5105]) currently > present themselves as {{java.sql.Types#OTHER}} > ({{}})([ref|https://docs.oracle.com/javase/8/docs/api/java/sql/Types.html#OTHER]) > It would be advantageous to surface the underlying data type of the > {{MEASURE}} rather than catch all {{OTHER}} since the underlying data type is > returned in result sets. > For example a {{MEASURE}} (a measure of type integer) would appear > as {{java.sql.Types#INTEGER}} ({{4}}). > We should also set the {{IS_GENERATEDCOLUMN}} metadata value with this > change. All {{MEASURE}} types would populate the column with {{"YES"}} > otherwise {{"NO"}}. > [Here's|https://github.com/apache/calcite/blob/2dba40e7a0a5651eac5a30d9e0a72f178bd9bff2/core/src/main/java/org/apache/calcite/jdbc/CalciteMetaImpl.java#L466] > where the JDBC type gets set in {{CalciteMetaImpl}}. > For {{IS_GENERATEDCOLUMN}} we will need a change to the {{MetaColumn}} > [constructor in > Avatica|https://github.com/apache/calcite-avatica/blob/b57eb7cd31a90d3f46b65c13832b398be5b0dad9/core/src/main/java/org/apache/calcite/avatica/MetaImpl.java#L359-L386] > that will allow us to set this value. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5676) Appropriately set `DATA_TYPE` and `TYPE_NAME` metadata values for `MEASURE` types
TJ Banghart created CALCITE-5676: Summary: Appropriately set `DATA_TYPE` and `TYPE_NAME` metadata values for `MEASURE` types Key: CALCITE-5676 URL: https://issues.apache.org/jira/browse/CALCITE-5676 Project: Calcite Issue Type: Sub-task Components: core Reporter: TJ Banghart Assignee: TJ Banghart Subtask of CALCITE-5549 to focus on the metadata we can control in calcite-core. Ensure that schema metadata and result set metadata agree on {{DATA_TYPE}} and {{TYPE_NAME}} for a {{MEASURE}}. The challenging part will be writing tests for this. [{{CalciteRemoteDriverTest.java}}|https://github.com/apache/calcite/blob/03050674594152ea785af69517f33960d7e27dd5/core/src/test/java/org/apache/calcite/jdbc/CalciteRemoteDriverTest.java#L99] has some that might be useful but use {{class Employee}} for a schema. We'll need a way to set some test {{MEASURE}} types as well. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5669) Remove trivial correlates from the query plan
[ https://issues.apache.org/jira/browse/CALCITE-5669?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716451#comment-17716451 ] Stamatis Zampetakis commented on CALCITE-5669: -- I would like to finalize this in the next few days; if someone wants to have a look on the latest changes please let me know to keep it open for a while longer. > Remove trivial correlates from the query plan > - > > Key: CALCITE-5669 > URL: https://issues.apache.org/jira/browse/CALCITE-5669 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Stamatis Zampetakis >Assignee: Stamatis Zampetakis >Priority: Major > Labels: pull-request-available > > Consider the following query correlated query. > {code:sql} > select * from emp as e where exists (select 1 from dept as d where e.empno = > null) > {code} > The query basically returns an empty result because {{e.empno = null}} is > always false. > The plan for the query after applying the sub-query remove rule is shown > below: > {noformat} > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) > LogicalCorrelate(correlation=[$cor0], joinType=[inner], > requiredColumns=[{0}]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{0}]) > LogicalProject(i=[true]) > LogicalFilter(condition=[=($cor0.EMPNO, null)]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > {noformat} > After applying the reduce expressions rule the filter with the correlated > condition will become false and the resulting plan would be the following. > {noformat} > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) > LogicalCorrelate(correlation=[$cor0], joinType=[inner], > requiredColumns=[{0}]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{0}]) > LogicalProject(i=[true]) > LogicalValues(tuples=[[]]) > {noformat} > Observe that now we have a {{LogicalCorrelate}} but there is no real > correlation in the plan since the correlation variable on the right side > disappeared. Depending on how rules are applied and which rules are used > similar "trivial" correlates may appear. > The goal of this ticket is to provide the means to get rid of them. > One option would be to add a new rule (e.g., {{CorrelateToJoinRule}}) which > detects that a correlate does not have correlations in the right side and > turn the correlation to a join; then we could employ other existing rules > (such as PruneEmptyRules) for joins and remove the newly created join > altogether. > Another option, would be to introduce new pruning rule(s) for correlate > (similar to those for joins) that will remove the correlate when its input is > an empty values expression. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5390) RelDecorrelator throws NullPointerException
[ https://issues.apache.org/jira/browse/CALCITE-5390?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716357#comment-17716357 ] Stamatis Zampetakis commented on CALCITE-5390: -- I will also check this more in detail once I get the chance; sorry if I broke something in the process. A good measure of progress is the number of successful unit tests and code coverage metric. As long as we increase those I think we are moving in the right direction. > RelDecorrelator throws NullPointerException > --- > > Key: CALCITE-5390 > URL: https://issues.apache.org/jira/browse/CALCITE-5390 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Dmitry Sysolyatin >Assignee: Dan Zou >Priority: Major > > The current query throws NullPointerException > {code:java} > SELECT > (SELECT 1 FROM emp d WHERE d.job = a.job LIMIT 1) AS t1, > (SELECT a.job = 'PRESIDENT' FROM emp s LIMIT 1) as t2 > FROM emp a; > {code} > Test case - > [https://github.com/apache/calcite/commit/46fe9bc456f2d34cf7dccd29829c9e85abe69d5f] > Logical plan before it fails: > {code:java} > LogicalProject(T1=[$8], T2=[$9]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], $f0=[$8], $f09=[$9]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], $f0=[$8], $f00=[$10]) > LogicalCorrelate(correlation=[$cor0], joinType=[left], > requiredColumns=[{9}]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], > HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f0=[$8], $f9=[=($2, > 'PRESIDENT')]) > LogicalCorrelate(correlation=[$cor0], joinType=[left], > requiredColumns=[{2}]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]) > LogicalSort(fetch=[1]) > LogicalProject(EXPR$0=[1]) > LogicalFilter(condition=[=($2, $cor0.JOB)]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]) > LogicalSort(fetch=[1]) > LogicalProject(EXPR$0=[$cor0.$f9]) > LogicalTableScan(table=[[scott, EMP]]) {code} > Stack trace: > {code:java} > Caused by: java.lang.NullPointerException > at java.util.Objects.requireNonNull(Objects.java:203) > at > org.apache.calcite.sql2rel.RelDecorrelator.createValueGenerator(RelDecorrelator.java:833) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateInputWithValueGenerator(RelDecorrelator.java:1028) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:764) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:738) > at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531) > at > org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:464) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531) > at > org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:512) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:495) > at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531) > at > org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1187) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1169) > at sun.reflect.GeneratedMethodAccessor12.invoke(Unknown Source) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccesso
[jira] [Commented] (CALCITE-5390) RelDecorrelator throws NullPointerException
[ https://issues.apache.org/jira/browse/CALCITE-5390?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716354#comment-17716354 ] Julian Hyde commented on CALCITE-5390: -- Ok, I'll look into trimming. It seems that there are a lot of bugs in this area. I fear that one or more of the efforts to "improve" things - e.g. de-duplicate correlation variables, and your effort to move expressions - have made some cases better but some cases worse. The bug density is high enough that an attempt to fix one bug triggers other bugs. Therefore it's difficult to tell whether we are making progress. > RelDecorrelator throws NullPointerException > --- > > Key: CALCITE-5390 > URL: https://issues.apache.org/jira/browse/CALCITE-5390 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Dmitry Sysolyatin >Assignee: Dan Zou >Priority: Major > > The current query throws NullPointerException > {code:java} > SELECT > (SELECT 1 FROM emp d WHERE d.job = a.job LIMIT 1) AS t1, > (SELECT a.job = 'PRESIDENT' FROM emp s LIMIT 1) as t2 > FROM emp a; > {code} > Test case - > [https://github.com/apache/calcite/commit/46fe9bc456f2d34cf7dccd29829c9e85abe69d5f] > Logical plan before it fails: > {code:java} > LogicalProject(T1=[$8], T2=[$9]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], $f0=[$8], $f09=[$9]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], $f0=[$8], $f00=[$10]) > LogicalCorrelate(correlation=[$cor0], joinType=[left], > requiredColumns=[{9}]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], > HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f0=[$8], $f9=[=($2, > 'PRESIDENT')]) > LogicalCorrelate(correlation=[$cor0], joinType=[left], > requiredColumns=[{2}]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]) > LogicalSort(fetch=[1]) > LogicalProject(EXPR$0=[1]) > LogicalFilter(condition=[=($2, $cor0.JOB)]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]) > LogicalSort(fetch=[1]) > LogicalProject(EXPR$0=[$cor0.$f9]) > LogicalTableScan(table=[[scott, EMP]]) {code} > Stack trace: > {code:java} > Caused by: java.lang.NullPointerException > at java.util.Objects.requireNonNull(Objects.java:203) > at > org.apache.calcite.sql2rel.RelDecorrelator.createValueGenerator(RelDecorrelator.java:833) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateInputWithValueGenerator(RelDecorrelator.java:1028) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:764) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:738) > at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531) > at > org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:464) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531) > at > org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:512) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:495) > at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531) > at > org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1187) > at > org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1169) > at sun.reflect.G
[jira] [Updated] (CALCITE-5675) Infer predicates for anti-join
[ https://issues.apache.org/jira/browse/CALCITE-5675?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-5675: Labels: pull-request-available (was: ) > Infer predicates for anti-join > -- > > Key: CALCITE-5675 > URL: https://issues.apache.org/jira/browse/CALCITE-5675 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Stamatis Zampetakis >Assignee: Stamatis Zampetakis >Priority: Major > Labels: pull-request-available > > Enhance {{RelMdPredicates}} to be able to infer predicates for anti-joins. > Consider the following plans with an anti join between EMP and DEPT tables. > +PulledUpPredicates+ > {noformat} > LogicalJoin(condition=[=($7, $8)], joinType=[anti]) > LogicalFilter(condition=[=($1, 'Victor')]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalFilter(condition=[=($1, 'CSD')]) > LogicalTableScan(table=[[scott, DEPT]]) > {noformat} > We can infer that the {{>($1, 'Victor')}} predicate holds on the result of > the join. > +RightInferredPredicates+ > {noformat} > LogicalJoin(condition=[=($7, $8)], joinType=[anti]) > LogicalFilter(condition=[>($7, 10)]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalTableScan(table=[[scott, DEPT]]) > {noformat} > We can infer that the {{>($0, 10)}} predicate holds on the right relation > (DEPT). -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5639) RexSimplify should remove IS NOT NULL check when LIKE comparison is present
[ https://issues.apache.org/jira/browse/CALCITE-5639?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Alessandro Solimando updated CALCITE-5639: -- Component/s: core > RexSimplify should remove IS NOT NULL check when LIKE comparison is present > --- > > Key: CALCITE-5639 > URL: https://issues.apache.org/jira/browse/CALCITE-5639 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0 >Reporter: Leonid Chistov >Assignee: Leonid Chistov >Priority: Minor > Labels: pull-request-available > Fix For: 1.35.0 > > Time Spent: 3.5h > Remaining Estimate: 0h > > Consider query like > {code:java} > select r_reason_desc from reason > where r_reason_desc is not null and r_reason_desc like '%child%' > {code} > where "r_reason_desc" is a nullable field. > When RexSimplify::simplifyFilterPredicates is called on that conjunction of > expressions, expression is not simplified, meaning that redundant "is not > null" check is not removed. > In the same time, if query like > {code:java} > select r_reason_desc from reason > where r_reason_desc is not null and r_reason_desc >= 'A'{code} > is passed to optimizer, redundant "is not null" check is eliminated. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-5639) RexSimplify should remove IS NOT NULL check when LIKE comparison is present
[ https://issues.apache.org/jira/browse/CALCITE-5639?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Alessandro Solimando resolved CALCITE-5639. --- Fix Version/s: 1.35.0 Resolution: Fixed Fixed via [0305067|https://github.com/apache/calcite/commit/03050674594152ea785af69517f33960d7e27dd5], thanks [~lchistov1987] for the patch and [~julianhyde] [~Chunwei Lei] and [~libenchao] for your comments and reivews! > RexSimplify should remove IS NOT NULL check when LIKE comparison is present > --- > > Key: CALCITE-5639 > URL: https://issues.apache.org/jira/browse/CALCITE-5639 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.34.0 >Reporter: Leonid Chistov >Assignee: Leonid Chistov >Priority: Minor > Labels: pull-request-available > Fix For: 1.35.0 > > Time Spent: 3.5h > Remaining Estimate: 0h > > Consider query like > {code:java} > select r_reason_desc from reason > where r_reason_desc is not null and r_reason_desc like '%child%' > {code} > where "r_reason_desc" is a nullable field. > When RexSimplify::simplifyFilterPredicates is called on that conjunction of > expressions, expression is not simplified, meaning that redundant "is not > null" check is not removed. > In the same time, if query like > {code:java} > select r_reason_desc from reason > where r_reason_desc is not null and r_reason_desc >= 'A'{code} > is passed to optimizer, redundant "is not null" check is eliminated. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5675) Infer predicates for anti-join
Stamatis Zampetakis created CALCITE-5675: Summary: Infer predicates for anti-join Key: CALCITE-5675 URL: https://issues.apache.org/jira/browse/CALCITE-5675 Project: Calcite Issue Type: Improvement Components: core Reporter: Stamatis Zampetakis Assignee: Stamatis Zampetakis Enhance {{RelMdPredicates}} to be able to infer predicates for anti-joins. Consider the following plans with an anti join between EMP and DEPT tables. +PulledUpPredicates+ {noformat} LogicalJoin(condition=[=($7, $8)], joinType=[anti]) LogicalFilter(condition=[=($1, 'Victor')]) LogicalTableScan(table=[[scott, EMP]]) LogicalFilter(condition=[=($1, 'CSD')]) LogicalTableScan(table=[[scott, DEPT]]) {noformat} We can infer that the {{>($1, 'Victor')}} predicate holds on the result of the join. +RightInferredPredicates+ {noformat} LogicalJoin(condition=[=($7, $8)], joinType=[anti]) LogicalFilter(condition=[>($7, 10)]) LogicalTableScan(table=[[scott, EMP]]) LogicalTableScan(table=[[scott, DEPT]]) {noformat} We can infer that the {{>($0, 10)}} predicate holds on the right relation (DEPT). -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-5670) Assertion error in SemiJoinJoinTransposeRule when Semi-Join has keys from both tables of the bottom Join
[ https://issues.apache.org/jira/browse/CALCITE-5670?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ruben Q L resolved CALCITE-5670. Resolution: Fixed Fixed via https://github.com/apache/calcite/commit/cc3c5fed072dad2efc8d0961e66fea38ba8f3817 Thanks [~rkondakov] for the patch! > Assertion error in SemiJoinJoinTransposeRule when Semi-Join has keys from > both tables of the bottom Join > > > Key: CALCITE-5670 > URL: https://issues.apache.org/jira/browse/CALCITE-5670 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.34.0 >Reporter: Roman Kondakov >Assignee: Roman Kondakov >Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > > The following test will raise the assertion error: > {code:java} > @Test void testPushSemiJoinPastJoinRuleNotHappensJoinKeysDifferentOrigin() { > // tests the case where the semijoin is pushed to the right > final String sql = "select e.ename from emp e, dept d, bonus b\n" > + "where e.deptno = d.deptno and e.ename = b.ename and d.name = > b.job"; > sql(sql) > .withRule(CoreRules.FILTER_INTO_JOIN, > CoreRules.JOIN_ADD_REDUNDANT_SEMI_JOIN, > CoreRules.SEMI_JOIN_JOIN_TRANSPOSE) > .check(); > } > {code} > {noformat} > java.lang.AssertionError > at > org.apache.calcite.rel.rules.SemiJoinJoinTransposeRule.onMatch(SemiJoinJoinTransposeRule.java:112) > at > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:337) > at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:556) > at > org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:420) > at > org.apache.calcite.plan.hep.HepPlanner.executeRuleInstance(HepPlanner.java:243) > at > org.apache.calcite.plan.hep.HepInstruction$RuleInstance$State.execute(HepInstruction.java:178) > at > org.apache.calcite.plan.hep.HepPlanner.lambda$executeProgram$0(HepPlanner.java:211) > at > com.google.common.collect.ImmutableList.forEach(ImmutableList.java:422) > at > org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:210) > at > org.apache.calcite.plan.hep.HepProgram$State.execute(HepProgram.java:118) > at > org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:205) > at > org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:191) > at > org.apache.calcite.test.RelOptFixture.checkPlanning(RelOptFixture.java:379) > at org.apache.calcite.test.RelOptFixture.check(RelOptFixture.java:330) > at org.apache.calcite.test.RelOptFixture.check(RelOptFixture.java:314) > at > org.apache.calcite.test.RelOptRulesTest.testPushSemiJoinPastJoinRuleNotHappensJoinKeysDifferentOrigin(RelOptRulesTest.java:2650) > {noformat} > The problem here is that the case when top-most Semi-Join has join keys from > both tables of the bottom Join is considered impossible, though it happens in > practice. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5672) cast type with nullable result it not true
[ https://issues.apache.org/jira/browse/CALCITE-5672?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716163#comment-17716163 ] jackylau commented on CALCITE-5672: --- yeap [~thomas.rebele] i forgot i created yesterday because working on 2 commnuity we can talk about it in CALCITE-5674 which has more informations > cast type with nullable result it not true > -- > > Key: CALCITE-5672 > URL: https://issues.apache.org/jira/browse/CALCITE-5672 > Project: Calcite > Issue Type: Improvement >Affects Versions: 1.34.0 >Reporter: jackylau >Priority: Major > > {code:java} > // code placeholder > sql("select cast(array[1,null,2] as int array) from (values (1))") > .columnType("INTEGER NOT NULL ARRAY NOT NULL"); {code} > it should be INTEGER ARRAY NOT NULL, the nullable constraint is table level, > the expr's nullable property should not change -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Closed] (CALCITE-5672) cast type with nullable result it not true
[ https://issues.apache.org/jira/browse/CALCITE-5672?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] jackylau closed CALCITE-5672. - Resolution: Duplicate > cast type with nullable result it not true > -- > > Key: CALCITE-5672 > URL: https://issues.apache.org/jira/browse/CALCITE-5672 > Project: Calcite > Issue Type: Improvement >Affects Versions: 1.34.0 >Reporter: jackylau >Priority: Major > > {code:java} > // code placeholder > sql("select cast(array[1,null,2] as int array) from (values (1))") > .columnType("INTEGER NOT NULL ARRAY NOT NULL"); {code} > it should be INTEGER ARRAY NOT NULL, the nullable constraint is table level, > the expr's nullable property should not change -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5672) cast type with nullable result it not true
[ https://issues.apache.org/jira/browse/CALCITE-5672?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716160#comment-17716160 ] Thomas Rebele commented on CALCITE-5672: Duplicate of CALCITE-5674? > cast type with nullable result it not true > -- > > Key: CALCITE-5672 > URL: https://issues.apache.org/jira/browse/CALCITE-5672 > Project: Calcite > Issue Type: Improvement >Affects Versions: 1.34.0 >Reporter: jackylau >Priority: Major > > {code:java} > // code placeholder > sql("select cast(array[1,null,2] as int array) from (values (1))") > .columnType("INTEGER NOT NULL ARRAY NOT NULL"); {code} > it should be INTEGER ARRAY NOT NULL, the nullable constraint is table level, > the expr's nullable property should not change -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-5642) Add SHA256, SHA512 functions (enabled in BigQuery and PostgreSQL libraries)
[ https://issues.apache.org/jira/browse/CALCITE-5642?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ruben Q L resolved CALCITE-5642. Resolution: Fixed Done via https://github.com/apache/calcite/commit/febd06b2cc7815895c4d6aafc3712a63fe1c335f Thanks [~FrankZou] for the patch! > Add SHA256, SHA512 functions (enabled in BigQuery and PostgreSQL libraries) > --- > > Key: CALCITE-5642 > URL: https://issues.apache.org/jira/browse/CALCITE-5642 > Project: Calcite > Issue Type: New Feature >Reporter: Dan Zou >Assignee: Dan Zou >Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > Time Spent: 0.5h > Remaining Estimate: 0h > > Add SHA256, SHA512 functions and enable them for BigQuery. > SHA256: Computes the hash of the input using the [SHA-256 > algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 32 > bytes. > SHA512: Computes the hash of the input using the [SHA-512 > algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 64 > bytes. > See more details in [BigQuery > Doc|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#sha256] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5673) FilterIntoJoinRule cannot pushdown filter to TableScan
[ https://issues.apache.org/jira/browse/CALCITE-5673?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716141#comment-17716141 ] Stamatis Zampetakis commented on CALCITE-5673: -- Hey [~klchai] , did you look into JoinToCorrelateRule? It may be sufficient for what you want to achieve. > FilterIntoJoinRule cannot pushdown filter to TableScan > -- > > Key: CALCITE-5673 > URL: https://issues.apache.org/jira/browse/CALCITE-5673 > Project: Calcite > Issue Type: Bug > Components: core, spatial >Affects Versions: 1.26.0 >Reporter: Kelun Chai >Priority: Major > Labels: features > > The current rule can only push down the join condition from one side to table > scan. If the join condition comes from both sides, it cannot push down the > filter, but extracts the relevant columns to Join through scan for > calculation. > SQL Query: > {code:sql} > SELECT A.name AS cName, B.name AS fName FROM vehicles AS A JOIN dimTable as B > ON ST_Contains(B.fence, ST_MakePoint(A.x, A.y));{code} > Query Plan: > {code:bash} > == Abstract Syntax Tree == > LogicalProject(cName=[$1], fName=[$5]) > +- LogicalJoin(condition=[ST_Contains($6, ST_MakePoint($2, $3))], > joinType=[inner]) > :- LogicalTableScan(table=[[default_catalog, default_database, vehicles]]) > +- LogicalTableScan(table=[[default_catalog, default_database, > dimTable]]){code} > The same query in postgres behaves as follows (w/ & w/o index): > {code:bash} > Nested Loop (cost=0.00..18071570.38 rows=722 width=64) > Join Filter: st_contains(b.fence, a.location) > -> Seq Scan on vehicles a (cost=0.00..18.50 rows=850 width=64) > -> Materialize (cost=0.00..22.75 rows=850 width=64) > -> Seq Scan on fences b (cost=0.00..18.50 rows=850 width=64) > # Using GIST Index > Nested Loop (cost=0.13..84.50 rows=1 width=64) > -> Seq Scan on fences b (cost=0.00..1.03 rows=3 width=64) > -> Index Scan using g_idx on vehicles a (cost=0.13..27.81 rows=1 > width=64) > Index Cond: (location @ b.fence) > Filter: st_contains(b.fence, location){code} > We created an in-memory based index in TableScan, is there a way to convert > the spatial join condition to NestedLoopJoin (LogicalCorrelate) and push down > to the TableScan node? > If Calcite does not support such a design, can I ask what are the > considerations/concerns? -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5670) Assertion error in SemiJoinJoinTransposeRule when Semi-Join has keys from both tables of the bottom Join
[ https://issues.apache.org/jira/browse/CALCITE-5670?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Roman Kondakov updated CALCITE-5670: Summary: Assertion error in SemiJoinJoinTransposeRule when Semi-Join has keys from both tables of the bottom Join (was: Assertion error in SemiJoinJoinTransposeRule) > Assertion error in SemiJoinJoinTransposeRule when Semi-Join has keys from > both tables of the bottom Join > > > Key: CALCITE-5670 > URL: https://issues.apache.org/jira/browse/CALCITE-5670 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.34.0 >Reporter: Roman Kondakov >Assignee: Roman Kondakov >Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > > The following test will raise the assertion error: > {code:java} > @Test void testPushSemiJoinPastJoinRuleNotHappensJoinKeysDifferentOrigin() { > // tests the case where the semijoin is pushed to the right > final String sql = "select e.ename from emp e, dept d, bonus b\n" > + "where e.deptno = d.deptno and e.ename = b.ename and d.name = > b.job"; > sql(sql) > .withRule(CoreRules.FILTER_INTO_JOIN, > CoreRules.JOIN_ADD_REDUNDANT_SEMI_JOIN, > CoreRules.SEMI_JOIN_JOIN_TRANSPOSE) > .check(); > } > {code} > {noformat} > java.lang.AssertionError > at > org.apache.calcite.rel.rules.SemiJoinJoinTransposeRule.onMatch(SemiJoinJoinTransposeRule.java:112) > at > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:337) > at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:556) > at > org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:420) > at > org.apache.calcite.plan.hep.HepPlanner.executeRuleInstance(HepPlanner.java:243) > at > org.apache.calcite.plan.hep.HepInstruction$RuleInstance$State.execute(HepInstruction.java:178) > at > org.apache.calcite.plan.hep.HepPlanner.lambda$executeProgram$0(HepPlanner.java:211) > at > com.google.common.collect.ImmutableList.forEach(ImmutableList.java:422) > at > org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:210) > at > org.apache.calcite.plan.hep.HepProgram$State.execute(HepProgram.java:118) > at > org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:205) > at > org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:191) > at > org.apache.calcite.test.RelOptFixture.checkPlanning(RelOptFixture.java:379) > at org.apache.calcite.test.RelOptFixture.check(RelOptFixture.java:330) > at org.apache.calcite.test.RelOptFixture.check(RelOptFixture.java:314) > at > org.apache.calcite.test.RelOptRulesTest.testPushSemiJoinPastJoinRuleNotHappensJoinKeysDifferentOrigin(RelOptRulesTest.java:2650) > {noformat} > The problem here is that the case when top-most Semi-Join has join keys from > both tables of the bottom Join is considered impossible, though it happens in > practice. -- This message was sent by Atlassian Jira (v8.20.10#820010)