date:20230927

[jira] [Commented] (CALCITE-6009) Add optimization to remove redundant Limit when its input's row number is less or equal to Limit's fetch

2023-09-27 Thread LakeShen (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6009?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769891#comment-17769891
 ] 

LakeShen commented on CALCITE-6009:
---

We have already added the `SortRemoveRedundantRule` in  CALCITE-5994.

I will extend this optimization logic in this rule.

> Add optimization to remove redundant Limit when its input's row number is 
> less or equal to Limit's fetch
> 
>
> Key: CALCITE-6009
> URL: https://issues.apache.org/jira/browse/CALCITE-6009
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
> Fix For: 1.36.0
>
>
> In calcite,Limit would be represented by Sort,such as `LogicalSort[fetch = 
> 5]`.
> When the Limit' source row number is less than the Limit's fetch,we could 
> remove the the redundant Limit.
> For example:
> {code:java}
> SELECT * FROM (VALUES 1,2,3,4,5,6) AS t1 LIMIT 10 {code}
> The plan tree is :
> {code:java}
>  LogicalSort(fetch=[10])
>   LogicalProject(t1=[$0])
>     LogicalValues(tuples=[[{ 1 }, { 2 }, { 3 }, { 4 }, { 5 }, { 6 }]]) {code}
> Because the Limit's source max row number is 6,the Limit's fetch is 10,so we 
> could remove the redundant Limit.
> Another example is :
> {code:java}
> SELECT count(*) FROM orders LIMIT 2 {code}
> The plan tree is :
> {code:java}
>  LogicalSort(fetch=[2])
>   LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
>     LogicalTableScan(table=[[tpch, ORDERS]]) {code}
> Because Limit's source max row number is 1,the Limit's fetch is 2, so we 
> could remove the redundant Limit.
> The logic is same as presto's RemoveRedundantLimit 
> rule:https://github.com/prestodb/presto/blob/50fbc07111ecca60a1a5e62755f095fa204120d0/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/RemoveRedundantLimit.java#L27



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6009) Add optimization to remove redundant Limit when its input's row number is less or equal to Limit's fetch

2023-09-27 Thread LakeShen (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-6009?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

LakeShen updated CALCITE-6009:
--
Fix Version/s: 1.36.0

> Add optimization to remove redundant Limit when its input's row number is 
> less or equal to Limit's fetch
> 
>
> Key: CALCITE-6009
> URL: https://issues.apache.org/jira/browse/CALCITE-6009
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
> Fix For: 1.36.0
>
>
> In calcite,Limit would be represented by Sort,such as `LogicalSort[fetch = 
> 5]`.
> When the Limit' source row number is less than the Limit's fetch,we could 
> remove the the redundant Limit.
> For example:
> {code:java}
> SELECT * FROM (VALUES 1,2,3,4,5,6) AS t1 LIMIT 10 {code}
> The plan tree is :
> {code:java}
>  LogicalSort(fetch=[10])
>   LogicalProject(t1=[$0])
>     LogicalValues(tuples=[[{ 1 }, { 2 }, { 3 }, { 4 }, { 5 }, { 6 }]]) {code}
> Because the Limit's source max row number is 6,the Limit's fetch is 10,so we 
> could remove the redundant Limit.
> Another example is :
> {code:java}
> SELECT count(*) FROM orders LIMIT 2 {code}
> The plan tree is :
> {code:java}
>  LogicalSort(fetch=[2])
>   LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
>     LogicalTableScan(table=[[tpch, ORDERS]]) {code}
> Because Limit's source max row number is 1,the Limit's fetch is 2, so we 
> could remove the redundant Limit.
> The logic is same as presto's RemoveRedundantLimit 
> rule:https://github.com/prestodb/presto/blob/50fbc07111ecca60a1a5e62755f095fa204120d0/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/RemoveRedundantLimit.java#L27



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5971) Add the RelRule to rewrite the bernoulli sample as Filter

2023-09-27 Thread LakeShen (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-5971?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769890#comment-17769890
 ] 

LakeShen commented on CALCITE-5971:
---

Hi [~zhoujira86] ,it's ok for you to contribute to flink.

> Add the RelRule to rewrite the bernoulli  sample as Filter
> --
>
> Key: CALCITE-5971
> URL: https://issues.apache.org/jira/browse/CALCITE-5971
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
>
> For the following SQL:
> {code:java}
> select deptno from "scott".dept tablesample bernoulli(50); {code}
> We could rewrite it to:
> {code:java}
> select deptno from "scott".dept where rand() < 0.5;  {code}
> The sql :
> {code:java}
> select deptno from "scott".dept tablesample bernoulli(50) REPEATABLE(10);  
> {code}
> We could rewrite it to:
> {code:java}
> select deptno from "scott".dept where rand(10) < 0.5;  {code}
> This rule only rewrite the tablesample bernoulli,and this rule is like 
> presto/trino's 
> [ImplementBernoulliSampleAsFilter|https://github.com/prestodb/presto/blob/6eef062bdd3777936fa29127e728edde86a681d4/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/ImplementBernoulliSampleAsFilter.java#L47C1-L48C35]
>  rule



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5971) Add the RelRule to rewrite the bernoulli sample as Filter

2023-09-27 Thread xiaogang zhou (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-5971?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769888#comment-17769888
 ] 

xiaogang zhou commented on CALCITE-5971:


[~shenlang] Hi Master, do you have a plan to contribute this issue to flink? 
otherwise I can take it

> Add the RelRule to rewrite the bernoulli  sample as Filter
> --
>
> Key: CALCITE-5971
> URL: https://issues.apache.org/jira/browse/CALCITE-5971
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
>
> For the following SQL:
> {code:java}
> select deptno from "scott".dept tablesample bernoulli(50); {code}
> We could rewrite it to:
> {code:java}
> select deptno from "scott".dept where rand() < 0.5;  {code}
> The sql :
> {code:java}
> select deptno from "scott".dept tablesample bernoulli(50) REPEATABLE(10);  
> {code}
> We could rewrite it to:
> {code:java}
> select deptno from "scott".dept where rand(10) < 0.5;  {code}
> This rule only rewrite the tablesample bernoulli,and this rule is like 
> presto/trino's 
> [ImplementBernoulliSampleAsFilter|https://github.com/prestodb/presto/blob/6eef062bdd3777936fa29127e728edde86a681d4/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/ImplementBernoulliSampleAsFilter.java#L47C1-L48C35]
>  rule



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769887#comment-17769887
 ] 

Ran Tao commented on CALCITE-6028:
--

thanks for explanations. you are right. 

we should fix it.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
> ... 25 more
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Benchao Li (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769882#comment-17769882
 ] 

Benchao Li commented on CALCITE-6028:
-

[~taoran] Calcite has many use cases, extended by other framework/software is 
just one of them. Calcite itself should also work out of box by default 
parse/convert/optimize/execute, and give correct result.

{{inSubQueryThreshold}} is a config that guides the optimization, it should not 
affect correctness and feature availability.

Back to the use case in Flink, the reason we set it to MAX_VALUE, is we do not 
want {{SqlToRelConverter}} optimize in list to {{Join}} by default. Actually, 
we'll translate them to {{IN}}/{{NOT IN}} in later stages again, see 
{{ConvertToNotInOrInRule}}.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
> ... 25 more
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread loukey_j (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769878#comment-17769878
 ] 

loukey_j commented on CALCITE-6028:
---

Thank you for your attention.

There is nothing wrong with the syntax of this SQL statement, and the 
expectation is that calcite should be supported without the user's knowledge. 
In addition, we cannot simply change in to where, which will cause the 
semantics of SQL to change.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
> ... 25 more
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6029) SqlOperatorTest cannot test operators that require the Babel parser

2023-09-27 Thread Mihai Budiu (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6029?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769858#comment-17769858
 ] 

Mihai Budiu commented on CALCITE-6029:
--

I want to write a test for an operator which can only be parsed with the Babel 
parser.
I don't want a parser test, I want an operator test.
In particular, I was trying to test the DATE_PART function. Loading the 
Postgres library is not enough, you also need the Babel parser for that. But I 
have discovered in this process 2 bugs, this one and [CALCITE-6030], which I 
have fixed separately.

But leaving this aside, I think that it's a bug that you can specify the 
parserConfig in the test fixture and the parserConfig is ignored by the test 
connection created by the tester. See the PR attached, which fixes this bug. 
With this change one should be able to test any operator using the 
SqlOperatorTest and its fixtures.

> SqlOperatorTest cannot test operators that require the Babel parser
> ---
>
> Key: CALCITE-6029
> URL: https://issues.apache.org/jira/browse/CALCITE-6029
> Project: Calcite
>  Issue Type: Bug
>  Components: babel, core
>Affects Versions: 1.35.0
>Reporter: Mihai Budiu
>Priority: Minor
>  Labels: pull-request-available
>
> In SqlOperatorTest one can write code like this:
> {code:java}
> @Test void testDatePart() {
> final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
> .withParserConfig(p -> 
> p.withParserFactory(SqlBabelParserImpl.FACTORY));
> {code}
> This almost works, but the SqlOperatorTest.check function makes a connection 
> ignores the parserFactory, so parsing will fail:
> {code:java}
> @Override public void check(SqlTestFactory factory, String query,
> SqlTester.TypeChecker typeChecker,
> SqlTester.ParameterChecker parameterChecker,
> SqlTester.ResultChecker resultChecker) {
>   super.check(factory, query, typeChecker, parameterChecker, 
> resultChecker);
>   final RelDataTypeSystem typeSystem =
>   factory.typeSystemTransform.apply(RelDataTypeSystem.DEFAULT);
>   final ConnectionFactory connectionFactory =
>   factory.connectionFactory
>   .with(CalciteConnectionProperty.TYPE_SYSTEM, uri(FIELD));  /// 
> NO PARSER_FACTORY HERE
> {code}
> I am trying to fix this by adding a PARSER_FACTORY argument to the 
> connection, but then I get a class loader error from 
> AvaticaUtils.instantiatePlugin, which, in this case, cannot find the 
> SqlBabelParserImpl#FACTORY in the classpath.
> I would appreciate some help solving this last bit.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-6029) SqlOperatorTest cannot test operators that require the Babel parser

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6029?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769857#comment-17769857
 ] 

Ran Tao edited comment on CALCITE-6029 at 9/28/23 12:35 AM:


hi [~mbudiu] I'm wondering why you test DATE_PART by using BabelParserFactory?

why don't use
{code:java}
@Test void testDatePart() {
final SqlOperatorFixture f = fixture()
  .setFor(SqlLibraryOperators.DATE_PART)
  .withLibrary(SqlLibrary.POSTGRESQL);{code}
I think if we test a Function in SqlOperatorTest, just withLibrary is enough. 
Just like other Spark/PG functions.

If you decide to test some special syntax for postgresql, i think you can test 
it in babel module, not testkit module.

If i'm wrong, pls correct me.

 


was (Author: lemonjing):
hi [~mbudiu] I'm wondering why you test DATE_PART by using BabelParserFactory?

why don't use

 
{code:java}
@Test void testDatePart() {
final SqlOperatorFixture f = fixture()
  .setFor(SqlLibraryOperators.DATE_PART)
  .withLibrary(SqlLibrary.POSTGRESQL);{code}
I think if we test a Function in SqlOperatorTest, just withLibrary is enough. 
Just like other Spark/PG functions.

If you decide to test some special syntax for postgresql, i think you can test 
it in babel module, not testkit module.

If i'm wrong, pls correct me.

 

> SqlOperatorTest cannot test operators that require the Babel parser
> ---
>
> Key: CALCITE-6029
> URL: https://issues.apache.org/jira/browse/CALCITE-6029
> Project: Calcite
>  Issue Type: Bug
>  Components: babel, core
>Affects Versions: 1.35.0
>Reporter: Mihai Budiu
>Priority: Minor
>  Labels: pull-request-available
>
> In SqlOperatorTest one can write code like this:
> {code:java}
> @Test void testDatePart() {
> final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
> .withParserConfig(p -> 
> p.withParserFactory(SqlBabelParserImpl.FACTORY));
> {code}
> This almost works, but the SqlOperatorTest.check function makes a connection 
> ignores the parserFactory, so parsing will fail:
> {code:java}
> @Override public void check(SqlTestFactory factory, String query,
> SqlTester.TypeChecker typeChecker,
> SqlTester.ParameterChecker parameterChecker,
> SqlTester.ResultChecker resultChecker) {
>   super.check(factory, query, typeChecker, parameterChecker, 
> resultChecker);
>   final RelDataTypeSystem typeSystem =
>   factory.typeSystemTransform.apply(RelDataTypeSystem.DEFAULT);
>   final ConnectionFactory connectionFactory =
>   factory.connectionFactory
>   .with(CalciteConnectionProperty.TYPE_SYSTEM, uri(FIELD));  /// 
> NO PARSER_FACTORY HERE
> {code}
> I am trying to fix this by adding a PARSER_FACTORY argument to the 
> connection, but then I get a class loader error from 
> AvaticaUtils.instantiatePlugin, which, in this case, cannot find the 
> SqlBabelParserImpl#FACTORY in the classpath.
> I would appreciate some help solving this last bit.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-6029) SqlOperatorTest cannot test operators that require the Babel parser

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6029?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769857#comment-17769857
 ] 

Ran Tao edited comment on CALCITE-6029 at 9/28/23 12:35 AM:


hi [~mbudiu] I'm wondering why you test DATE_PART by using BabelParserFactory?

why don't use

 
{code:java}
@Test void testDatePart() {
final SqlOperatorFixture f = fixture()
  .setFor(SqlLibraryOperators.DATE_PART)
  .withLibrary(SqlLibrary.POSTGRESQL);{code}
I think if we test a Function in SqlOperatorTest, just withLibrary is enough. 
Just like other Spark/PG functions.

If you decide to test some special syntax for postgresql, i think you can test 
it in babel module, not testkit module.

If i'm wrong, pls correct me.

 


was (Author: lemonjing):
hi [~mbudiu] I'm wondering why you test DATE_PART by using BabelParserFactory?

why don't use

 
{code:java}
@Test void testDatePart() {
final SqlOperatorFixture f = 
fixture().withLibrary(SqlLibrary.POSTGRESQL);{code}

I think if we test a Function in SqlOperatorTest, just withLibrary is enough. 
Just like other Spark/PG functions.



If you decide to test some special syntax for postgresql, i think you can test 
it in babel module, not testkit module.

If i'm wrong, pls correct me.

 

> SqlOperatorTest cannot test operators that require the Babel parser
> ---
>
> Key: CALCITE-6029
> URL: https://issues.apache.org/jira/browse/CALCITE-6029
> Project: Calcite
>  Issue Type: Bug
>  Components: babel, core
>Affects Versions: 1.35.0
>Reporter: Mihai Budiu
>Priority: Minor
>  Labels: pull-request-available
>
> In SqlOperatorTest one can write code like this:
> {code:java}
> @Test void testDatePart() {
> final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
> .withParserConfig(p -> 
> p.withParserFactory(SqlBabelParserImpl.FACTORY));
> {code}
> This almost works, but the SqlOperatorTest.check function makes a connection 
> ignores the parserFactory, so parsing will fail:
> {code:java}
> @Override public void check(SqlTestFactory factory, String query,
> SqlTester.TypeChecker typeChecker,
> SqlTester.ParameterChecker parameterChecker,
> SqlTester.ResultChecker resultChecker) {
>   super.check(factory, query, typeChecker, parameterChecker, 
> resultChecker);
>   final RelDataTypeSystem typeSystem =
>   factory.typeSystemTransform.apply(RelDataTypeSystem.DEFAULT);
>   final ConnectionFactory connectionFactory =
>   factory.connectionFactory
>   .with(CalciteConnectionProperty.TYPE_SYSTEM, uri(FIELD));  /// 
> NO PARSER_FACTORY HERE
> {code}
> I am trying to fix this by adding a PARSER_FACTORY argument to the 
> connection, but then I get a class loader error from 
> AvaticaUtils.instantiatePlugin, which, in this case, cannot find the 
> SqlBabelParserImpl#FACTORY in the classpath.
> I would appreciate some help solving this last bit.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6029) SqlOperatorTest cannot test operators that require the Babel parser

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6029?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769857#comment-17769857
 ] 

Ran Tao commented on CALCITE-6029:
--

hi [~mbudiu] I'm wondering why you test DATE_PART by using BabelParserFactory?

why don't use

 
{code:java}
@Test void testDatePart() {
final SqlOperatorFixture f = 
fixture().withLibrary(SqlLibrary.POSTGRESQL);{code}

I think if we test a Function in SqlOperatorTest, just withLibrary is enough. 
Just like other Spark/PG functions.



If you decide to test some special syntax for postgresql, i think you can test 
it in babel module, not testkit module.

If i'm wrong, pls correct me.

 

> SqlOperatorTest cannot test operators that require the Babel parser
> ---
>
> Key: CALCITE-6029
> URL: https://issues.apache.org/jira/browse/CALCITE-6029
> Project: Calcite
>  Issue Type: Bug
>  Components: babel, core
>Affects Versions: 1.35.0
>Reporter: Mihai Budiu
>Priority: Minor
>  Labels: pull-request-available
>
> In SqlOperatorTest one can write code like this:
> {code:java}
> @Test void testDatePart() {
> final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
> .withParserConfig(p -> 
> p.withParserFactory(SqlBabelParserImpl.FACTORY));
> {code}
> This almost works, but the SqlOperatorTest.check function makes a connection 
> ignores the parserFactory, so parsing will fail:
> {code:java}
> @Override public void check(SqlTestFactory factory, String query,
> SqlTester.TypeChecker typeChecker,
> SqlTester.ParameterChecker parameterChecker,
> SqlTester.ResultChecker resultChecker) {
>   super.check(factory, query, typeChecker, parameterChecker, 
> resultChecker);
>   final RelDataTypeSystem typeSystem =
>   factory.typeSystemTransform.apply(RelDataTypeSystem.DEFAULT);
>   final ConnectionFactory connectionFactory =
>   factory.connectionFactory
>   .with(CalciteConnectionProperty.TYPE_SYSTEM, uri(FIELD));  /// 
> NO PARSER_FACTORY HERE
> {code}
> I am trying to fix this by adding a PARSER_FACTORY argument to the 
> connection, but then I get a class loader error from 
> AvaticaUtils.instantiatePlugin, which, in this case, cannot find the 
> SqlBabelParserImpl#FACTORY in the classpath.
> I would appreciate some help solving this last bit.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6030) DATE_PART is not handled by the RexToLixTranslator

2023-09-27 Thread ASF GitHub Bot (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-6030?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated CALCITE-6030:

Labels: pull-request-available  (was: )

> DATE_PART is not handled by the RexToLixTranslator
> --
>
> Key: CALCITE-6030
> URL: https://issues.apache.org/jira/browse/CALCITE-6030
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Mihai Budiu
>Priority: Minor
>  Labels: pull-request-available
>
> The following test, when added to SqlOperatorTest, causes a RuntimeException:
> {code:java}
> @Test void testDatePart() {
> final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
> .withParserConfig(p -> 
> p.withParserFactory(SqlBabelParserImpl.FACTORY));
> f.checkScalar("DATE_PART(second, TIME '10:10:10')",
> "10", "BIGINT NOT NULL");
>   }
> {code}
> Note that this needs https://github.com/apache/calcite/pull/3445 to execute 
> correctly.
> The stack trace is:
> {code:java}
> Suppressed: java.lang.RuntimeException: cannot translate call DATE_PART($t1, 
> $t2)
>   at 
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1160)
>   at 
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:101)
>   at org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
> {code}
> According to the documentation DATE_PART is just an alias for EXTRACT, which 
> is (mostly) implemented, so this should work.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Created] (CALCITE-6030) DATE_PART is not handled by the RexToLixTranslator

2023-09-27 Thread Mihai Budiu (Jira)

Mihai Budiu created CALCITE-6030:


 Summary: DATE_PART is not handled by the RexToLixTranslator
 Key: CALCITE-6030
 URL: https://issues.apache.org/jira/browse/CALCITE-6030
 Project: Calcite
  Issue Type: Bug
  Components: core
Affects Versions: 1.35.0
Reporter: Mihai Budiu


The following test, when added to SqlOperatorTest, causes a RuntimeException:
{code:java}
@Test void testDatePart() {
final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
.withParserConfig(p -> p.withParserFactory(SqlBabelParserImpl.FACTORY));
f.checkScalar("DATE_PART(second, TIME '10:10:10')",
"10", "BIGINT NOT NULL");
  }
{code}

Note that this needs https://github.com/apache/calcite/pull/3445 to execute 
correctly.

The stack trace is:

{code:java}
Suppressed: java.lang.RuntimeException: cannot translate call DATE_PART($t1, 
$t2)
at 
org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1160)
at 
org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:101)
at org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
{code}

According to the documentation DATE_PART is just an alias for EXTRACT, which is 
(mostly implemented), so this should work.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6030) DATE_PART is not handled by the RexToLixTranslator

2023-09-27 Thread Mihai Budiu (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-6030?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Mihai Budiu updated CALCITE-6030:
-
Description: 
The following test, when added to SqlOperatorTest, causes a RuntimeException:
{code:java}
@Test void testDatePart() {
final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
.withParserConfig(p -> p.withParserFactory(SqlBabelParserImpl.FACTORY));
f.checkScalar("DATE_PART(second, TIME '10:10:10')",
"10", "BIGINT NOT NULL");
  }
{code}

Note that this needs https://github.com/apache/calcite/pull/3445 to execute 
correctly.

The stack trace is:

{code:java}
Suppressed: java.lang.RuntimeException: cannot translate call DATE_PART($t1, 
$t2)
at 
org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1160)
at 
org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:101)
at org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
{code}

According to the documentation DATE_PART is just an alias for EXTRACT, which is 
(mostly) implemented, so this should work.

  was:
The following test, when added to SqlOperatorTest, causes a RuntimeException:
{code:java}
@Test void testDatePart() {
final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
.withParserConfig(p -> p.withParserFactory(SqlBabelParserImpl.FACTORY));
f.checkScalar("DATE_PART(second, TIME '10:10:10')",
"10", "BIGINT NOT NULL");
  }
{code}

Note that this needs https://github.com/apache/calcite/pull/3445 to execute 
correctly.

The stack trace is:

{code:java}
Suppressed: java.lang.RuntimeException: cannot translate call DATE_PART($t1, 
$t2)
at 
org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1160)
at 
org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:101)
at org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
{code}

According to the documentation DATE_PART is just an alias for EXTRACT, which is 
(mostly implemented), so this should work.


> DATE_PART is not handled by the RexToLixTranslator
> --
>
> Key: CALCITE-6030
> URL: https://issues.apache.org/jira/browse/CALCITE-6030
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> The following test, when added to SqlOperatorTest, causes a RuntimeException:
> {code:java}
> @Test void testDatePart() {
> final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
> .withParserConfig(p -> 
> p.withParserFactory(SqlBabelParserImpl.FACTORY));
> f.checkScalar("DATE_PART(second, TIME '10:10:10')",
> "10", "BIGINT NOT NULL");
>   }
> {code}
> Note that this needs https://github.com/apache/calcite/pull/3445 to execute 
> correctly.
> The stack trace is:
> {code:java}
> Suppressed: java.lang.RuntimeException: cannot translate call DATE_PART($t1, 
> $t2)
>   at 
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1160)
>   at 
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:101)
>   at org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
> {code}
> According to the documentation DATE_PART is just an alias for EXTRACT, which 
> is (mostly) implemented, so this should work.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6029) SqlOperatorTest cannot test operators that require the Babel parser

2023-09-27 Thread ASF GitHub Bot (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-6029?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated CALCITE-6029:

Labels: pull-request-available  (was: )

> SqlOperatorTest cannot test operators that require the Babel parser
> ---
>
> Key: CALCITE-6029
> URL: https://issues.apache.org/jira/browse/CALCITE-6029
> Project: Calcite
>  Issue Type: Bug
>  Components: babel, core
>Affects Versions: 1.35.0
>Reporter: Mihai Budiu
>Priority: Minor
>  Labels: pull-request-available
>
> In SqlOperatorTest one can write code like this:
> {code:java}
> @Test void testDatePart() {
> final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
> .withParserConfig(p -> 
> p.withParserFactory(SqlBabelParserImpl.FACTORY));
> {code}
> This almost works, but the SqlOperatorTest.check function makes a connection 
> ignores the parserFactory, so parsing will fail:
> {code:java}
> @Override public void check(SqlTestFactory factory, String query,
> SqlTester.TypeChecker typeChecker,
> SqlTester.ParameterChecker parameterChecker,
> SqlTester.ResultChecker resultChecker) {
>   super.check(factory, query, typeChecker, parameterChecker, 
> resultChecker);
>   final RelDataTypeSystem typeSystem =
>   factory.typeSystemTransform.apply(RelDataTypeSystem.DEFAULT);
>   final ConnectionFactory connectionFactory =
>   factory.connectionFactory
>   .with(CalciteConnectionProperty.TYPE_SYSTEM, uri(FIELD));  /// 
> NO PARSER_FACTORY HERE
> {code}
> I am trying to fix this by adding a PARSER_FACTORY argument to the 
> connection, but then I get a class loader error from 
> AvaticaUtils.instantiatePlugin, which, in this case, cannot find the 
> SqlBabelParserImpl#FACTORY in the classpath.
> I would appreciate some help solving this last bit.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6029) SqlOperatorTest cannot test operators that require the Babel parser

2023-09-27 Thread Mihai Budiu (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6029?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769849#comment-17769849
 ] 

Mihai Budiu commented on CALCITE-6029:
--

I think I have figured out the classloader problem. I will submit a fix for 
this bug.

> SqlOperatorTest cannot test operators that require the Babel parser
> ---
>
> Key: CALCITE-6029
> URL: https://issues.apache.org/jira/browse/CALCITE-6029
> Project: Calcite
>  Issue Type: Bug
>  Components: babel, core
>Affects Versions: 1.35.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> In SqlOperatorTest one can write code like this:
> {code:java}
> @Test void testDatePart() {
> final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
> .withParserConfig(p -> 
> p.withParserFactory(SqlBabelParserImpl.FACTORY));
> {code}
> This almost works, but the SqlOperatorTest.check function makes a connection 
> ignores the parserFactory, so parsing will fail:
> {code:java}
> @Override public void check(SqlTestFactory factory, String query,
> SqlTester.TypeChecker typeChecker,
> SqlTester.ParameterChecker parameterChecker,
> SqlTester.ResultChecker resultChecker) {
>   super.check(factory, query, typeChecker, parameterChecker, 
> resultChecker);
>   final RelDataTypeSystem typeSystem =
>   factory.typeSystemTransform.apply(RelDataTypeSystem.DEFAULT);
>   final ConnectionFactory connectionFactory =
>   factory.connectionFactory
>   .with(CalciteConnectionProperty.TYPE_SYSTEM, uri(FIELD));  /// 
> NO PARSER_FACTORY HERE
> {code}
> I am trying to fix this by adding a PARSER_FACTORY argument to the 
> connection, but then I get a class loader error from 
> AvaticaUtils.instantiatePlugin, which, in this case, cannot find the 
> SqlBabelParserImpl#FACTORY in the classpath.
> I would appreciate some help solving this last bit.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6029) SqlOperatorTest cannot test operators that require the Babel parser

2023-09-27 Thread Mihai Budiu (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6029?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769847#comment-17769847
 ] 

Mihai Budiu commented on CALCITE-6029:
--

I have added this to build.gradle.kts for testkit:
{code}
dependencies {
api(project(":core"))
api(project(":babel")) // added this
{code}

but it's not enough.

> SqlOperatorTest cannot test operators that require the Babel parser
> ---
>
> Key: CALCITE-6029
> URL: https://issues.apache.org/jira/browse/CALCITE-6029
> Project: Calcite
>  Issue Type: Bug
>  Components: babel, core
>Affects Versions: 1.35.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> In SqlOperatorTest one can write code like this:
> {code:java}
> @Test void testDatePart() {
> final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
> .withParserConfig(p -> 
> p.withParserFactory(SqlBabelParserImpl.FACTORY));
> {code}
> This almost works, but the SqlOperatorTest.check function makes a connection 
> ignores the parserFactory, so parsing will fail:
> {code:java}
> @Override public void check(SqlTestFactory factory, String query,
> SqlTester.TypeChecker typeChecker,
> SqlTester.ParameterChecker parameterChecker,
> SqlTester.ResultChecker resultChecker) {
>   super.check(factory, query, typeChecker, parameterChecker, 
> resultChecker);
>   final RelDataTypeSystem typeSystem =
>   factory.typeSystemTransform.apply(RelDataTypeSystem.DEFAULT);
>   final ConnectionFactory connectionFactory =
>   factory.connectionFactory
>   .with(CalciteConnectionProperty.TYPE_SYSTEM, uri(FIELD));  /// 
> NO PARSER_FACTORY HERE
> {code}
> I am trying to fix this by adding a PARSER_FACTORY argument to the 
> connection, but then I get a class loader error from 
> AvaticaUtils.instantiatePlugin, which, in this case, cannot find the 
> SqlBabelParserImpl#FACTORY in the classpath.
> I would appreciate some help solving this last bit.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Created] (CALCITE-6029) SqlOperatorTest cannot test operators that require the Babel parser

2023-09-27 Thread Mihai Budiu (Jira)

Mihai Budiu created CALCITE-6029:


 Summary: SqlOperatorTest cannot test operators that require the 
Babel parser
 Key: CALCITE-6029
 URL: https://issues.apache.org/jira/browse/CALCITE-6029
 Project: Calcite
  Issue Type: Bug
  Components: babel, core
Affects Versions: 1.35.0
Reporter: Mihai Budiu


In SqlOperatorTest one can write code like this:
{code:java}
@Test void testDatePart() {
final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
.withParserConfig(p -> p.withParserFactory(SqlBabelParserImpl.FACTORY));
{code}

This almost works, but the SqlOperatorTest.check function makes a connection 
ignores the parserFactory, so parsing will fail:

{code:java}
@Override public void check(SqlTestFactory factory, String query,
SqlTester.TypeChecker typeChecker,
SqlTester.ParameterChecker parameterChecker,
SqlTester.ResultChecker resultChecker) {
  super.check(factory, query, typeChecker, parameterChecker, resultChecker);
  final RelDataTypeSystem typeSystem =
  factory.typeSystemTransform.apply(RelDataTypeSystem.DEFAULT);
  final ConnectionFactory connectionFactory =
  factory.connectionFactory
  .with(CalciteConnectionProperty.TYPE_SYSTEM, uri(FIELD));  /// NO 
PARSER_FACTORY HERE
{code}

I am trying to fix this by adding a PARSER_FACTORY argument to the connection, 
but then I get a class loader error from AvaticaUtils.instantiatePlugin, which, 
in this case, cannot find the SqlBabelParserImpl#FACTORY in the classpath.

I would appreciate some help solving this last bit.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6007) CTE as subquery without alias doesn't have correct alias setup

2023-09-27 Thread Wenrui Meng (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6007?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769811#comment-17769811
 ] 

Wenrui Meng commented on CALCITE-6007:
--

[~julianhyde] thanks for help reviewing and merging the pr.

> CTE as subquery without alias doesn't have correct alias setup
> --
>
> Key: CALCITE-6007
> URL: https://issues.apache.org/jira/browse/CALCITE-6007
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.34.0, 1.35.0
>Reporter: Wenrui Meng
>Assignee: Wenrui Meng
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
>
> {code:java}
> SELECT
>   a,
>   b
> FROM (
>   WITH
> sub AS (
> SELECT
>   1 AS a,
>   2 AS b)
>   SELECT
> *
>   FROM
> sub)
> WHERE
>   a IS NOT null
> {code}
> It will generate the following SQL statement after validation
> {code:java}
> SELECT
>   EXPR$0.a,
>   EXPR$0.b
> FROM (
>   WITH
> sub AS (
> SELECT
>   1 AS a,
>   2 AS b)
>   SELECT
> sub.a AS a, sub.b AS b
>   FROM
> sub)
> WHERE
>   EXPR$0.a IS NOT null
> {code}
> The validated SQL become invalid since there is no EXPR$0 alias append for 
> the SqlWith sub query but used in the expression outside. 



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Resolved] (CALCITE-5982) Allow overloading the created enumerable in Calcite when calling getTables() or getColumns()

2023-09-27 Thread Julian Hyde (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-5982?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Julian Hyde resolved CALCITE-5982.
--
Fix Version/s: 1.36.0
   Resolution: Fixed

Fixed in 
[436ae7d|https://github.com/apache/calcite/commit/436ae7d296dfcac321ec461feaf0f645ec980993];
 thanks for the PR, [~oliverlee]!

> Allow overloading the created enumerable in Calcite when calling getTables() 
> or getColumns() 
> -
>
> Key: CALCITE-5982
> URL: https://issues.apache.org/jira/browse/CALCITE-5982
> Project: Calcite
>  Issue Type: New Feature
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
>
> The goal is to introduce a mechanism that allows overloading the enumerable 
> type that is created when {{getTables()}} and {{getColumns()}} is called. If 
> a user provides an overloaded {{{}MetaTable{}}}/ {{MetaColumn}} class, then 
> they can add in additional metadata fields that they would like to be 
> transferred. 
>  
> Currently, {{CalciteMetaImpl}} in {{getTables()}} and {{getColumns()}} calls 
> are hardcoded to do reflection on {{MetaTable.class}} and 
> {{MetaColumn.class}} ‘ fields, matched with the list of column names that are 
> passed in. Reflection is important here, as it creates the proper 
> {{ColumnMetaData}} and {{Signature}} that the client needs to deserialize. 
> See here for {{[getTables()|#L270]]}} and here for 
> [{{getColumns()}}|https://github.com/apache/calcite/blob/164ff0a27e243850d294908dc5cff90760d0a35a/core/src/main/java/org/apache/calcite/jdbc/CalciteMetaImpl.java#L320]
>  
>  
> I would like to introduce fields ( {{metaTableClass}} , {{metaColumnClass}} ) 
> on {{CalciteMetaImpl}} that determine which class to use for each of these. 
> This will be configured as a {{CalciteConnectionProperty}} when making the 
> {{jdbc:calcite}} connection, and default to MetaTable.class and 
> MetaColumn.class if not provided. 
>  
>  
> Requirements:
>  * User can specify in {{Properties}} new {{CalciteConnectionProperty}} ’s to 
> specify which overloaded class of {{CalciteMetaTable}} and {{MetaColumn}} to 
> use
>  * If not specified, it will default to {{CalciteMetaTable.class}} and 
> {{MetaColumn.class}}
>  
>  
> The provided overloaded class will create a subclass of {{CalciteMetaTable}} 
> / {{MetaColumn}} that has the same shape constructor and also provide an 
> override for the function {{{}getColumnNames(){}}}.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Resolved] (CALCITE-6007) CTE as subquery without alias doesn't have correct alias setup

2023-09-27 Thread Julian Hyde (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-6007?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Julian Hyde resolved CALCITE-6007.
--
Fix Version/s: 1.36.0
   Resolution: Fixed

Fixed in 
[1245db6|https://github.com/apache/calcite/commit/1245db620114ee5174641ad540af6b27dcd772e3];
 thanks for the PR, [~wenruimeng]!

> CTE as subquery without alias doesn't have correct alias setup
> --
>
> Key: CALCITE-6007
> URL: https://issues.apache.org/jira/browse/CALCITE-6007
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.34.0, 1.35.0
>Reporter: Wenrui Meng
>Assignee: Wenrui Meng
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
>
> {code:java}
> SELECT
>   a,
>   b
> FROM (
>   WITH
> sub AS (
> SELECT
>   1 AS a,
>   2 AS b)
>   SELECT
> *
>   FROM
> sub)
> WHERE
>   a IS NOT null
> {code}
> It will generate the following SQL statement after validation
> {code:java}
> SELECT
>   EXPR$0.a,
>   EXPR$0.b
> FROM (
>   WITH
> sub AS (
> SELECT
>   1 AS a,
>   2 AS b)
>   SELECT
> sub.a AS a, sub.b AS b
>   FROM
> sub)
> WHERE
>   EXPR$0.a IS NOT null
> {code}
> The validated SQL become invalid since there is no EXPR$0 alias append for 
> the SqlWith sub query but used in the expression outside. 



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6007) CTE as subquery without alias doesn't have correct alias setup

2023-09-27 Thread Wenrui Meng (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6007?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769742#comment-17769742
 ] 

Wenrui Meng commented on CALCITE-6007:
--

Anyone can help to review this small change?

> CTE as subquery without alias doesn't have correct alias setup
> --
>
> Key: CALCITE-6007
> URL: https://issues.apache.org/jira/browse/CALCITE-6007
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.34.0, 1.35.0
>Reporter: Wenrui Meng
>Assignee: Wenrui Meng
>Priority: Major
>  Labels: pull-request-available
>
> {code:java}
> SELECT
>   a,
>   b
> FROM (
>   WITH
> sub AS (
> SELECT
>   1 AS a,
>   2 AS b)
>   SELECT
> *
>   FROM
> sub)
> WHERE
>   a IS NOT null
> {code}
> It will generate the following SQL statement after validation
> {code:java}
> SELECT
>   EXPR$0.a,
>   EXPR$0.b
> FROM (
>   WITH
> sub AS (
> SELECT
>   1 AS a,
>   2 AS b)
>   SELECT
> sub.a AS a, sub.b AS b
>   FROM
> sub)
> WHERE
>   EXPR$0.a IS NOT null
> {code}
> The validated SQL become invalid since there is no EXPR$0 alias append for 
> the SqlWith sub query but used in the expression outside. 



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-5990) Explicit cast to numeric type doesn't check overflow

2023-09-27 Thread xinyu liu (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-5990?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769230#comment-17769230
 ] 

xinyu liu edited comment on CALCITE-5990 at 9/27/23 4:07 PM:
-

Hi [~Runking],  I am a researcher in field of SE and I am interested in this 
regression because it seems to demonstrate a case wherein the existing test 
suites are not able to catch the regression when the commit is pushed to the 
main branch. Is my understanding correct?

I assume the entire existing test suites get run before each commit is pushed? 
Thank you so much for your input! 


was (Author: JIRAUSER302376):
Hi, I am interested in this regression. Does this case demonstrates there is 
still room of improvement for the existing test suites (so that the bug can be 
caught before being pushed to the main branch)?

> Explicit cast to numeric type doesn't check overflow
> 
>
> Key: CALCITE-5990
> URL: https://issues.apache.org/jira/browse/CALCITE-5990
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Runkang He
>Assignee: Runkang He
>Priority: Blocker
> Fix For: 1.36.0
>
>
> Explicit cast to numeric type doesn't check overflow, and this issue can be 
> reproduced by sqlline:
> {code:sql}
> select cast(empno as tinyint), cast(130 as tinyint) from emps where 
> name='Alice'; -- empno is 130
> {code}
> The empno is INT type. The result is:
> {code:sql}
> -126, -126{code}
> I think it should throw exception when overflow.
> At last, this issue was found when to turn on runtime check for 
> CalciteSqlOperatorTest in CALCITE-5921.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6020) SqlToRelConverter should not replace windowed SUM with equivalent expression using SUM0

2023-09-27 Thread Julian Hyde (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6020?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769671#comment-17769671
 ] 

Julian Hyde commented on CALCITE-6020:
--

I agree that the subset of algebra we target should be more configurable (as 
long as the configuration is structured so as not be bewildering). 

Recently I have been making SqlToRelConverter lean ever more heavily on 
RelBuilder. We should continue in that direction. RelBuilder is nicely 
configurable, and if we want SqlToRelConverter to perform simplifications as it 
goes, it is better that that code lives in RelBuilder where it can be reused, 
better tested, and disabled. 

> SqlToRelConverter should not replace windowed SUM with equivalent expression 
> using SUM0
> ---
>
> Key: CALCITE-6020
> URL: https://issues.apache.org/jira/browse/CALCITE-6020
> Project: Calcite
>  Issue Type: Improvement
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>  Labels: pull-request-available
>
> {{SqlToRelConverter}} replaces {{SUM}} with {{SUM0}} around 
> [here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L5885]
> This might have been needed at some point in the past - but I think it will 
> be better to leave it as {{SUM}} - as in case there is no {{SUM0}} in the 
> system that will be replaced with a {{COALESCE(SUM(...) , 0 )}} to provide it 
> - as see 
> [here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java#L1288]



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769658#comment-17769658
 ] 

Ran Tao edited comment on CALCITE-6028 at 9/27/23 2:48 PM:
---

>From debugging, just like the screenshot from [~shenlang] above. it causes 
>exception because bb.root == null, we can not reach to the convertExists. 

but my point is that if upper-layer engine/application just want to translate 
IN to OR what we should do ? actually if upper-layer engine/application set 
their customized inSubQueryThreshold config, it will works.

 
{code:java}
if (valueList.size() < config.getInSubQueryThreshold()
|| valueList.accept(new SqlIdentifierFinder())) {
  subQuery.expr =
  convertInToOr(
  bb,
  leftKeys,
  valueList,
  (SqlInOperator) call.getOperator());
  return;
} {code}
I'm asking this because some engines such as apache flink just set 
inSubQueryThreshold = Integer.MAX_VALUE to let this sub-clause just translate 
to OR forever.

Of course I agree that we can fix it and still support the correct conversion 
when the above if condition is not satisfied.

 


was (Author: lemonjing):
>From debugging, just like the screenshot from [~shenlang] above. it causes 
>exception because bb.root == null, we can not reach to the convertExists. 

but my point is that if upper-layer engine/application just want to translate 
IN to OR what we should do ? actually if upper-layer engine/application set 
their customized inSubQueryThreshold config, it will works.

 
{code:java}
if (valueList.size() < config.getInSubQueryThreshold()
|| valueList.accept(new SqlIdentifierFinder())) {
  subQuery.expr =
  convertInToOr(
  bb,
  leftKeys,
  valueList,
  (SqlInOperator) call.getOperator());
  return;
} {code}

I'm asking this because some engines such as apache flink just set 
inSubQueryThreshold = Integer.MAX_VALUE to let this sub-clause just translate 
to OR forever.

Of course I agree that we can fix it and still support the correct conversion 
when the above if is not satisfied.

 

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
>

[jira] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769658#comment-17769658
 ] 

Ran Tao commented on CALCITE-6028:
--

>From debugging, just like the screenshot from [~shenlang] above. it causes 
>exception because bb.root == null, we can not reach to the convertExists. 

but my point is that if upper-layer engine/application just want to translate 
IN to OR what we should do ? actually if upper-layer engine/application set 
their customized inSubQueryThreshold config, it will works.

 
{code:java}
if (valueList.size() < config.getInSubQueryThreshold()
|| valueList.accept(new SqlIdentifierFinder())) {
  subQuery.expr =
  convertInToOr(
  bb,
  leftKeys,
  valueList,
  (SqlInOperator) call.getOperator());
  return;
} {code}

I'm asking this because some engines such as apache flink just set 
inSubQueryThreshold = Integer.MAX_VALUE to let this sub-clause just translate 
to OR forever.

Of course I agree that we can fix it and still support the correct conversion 
when the above if is not satisfied.

 

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
>

[jira] [Comment Edited] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769622#comment-17769622
 ] 

Ran Tao edited comment on CALCITE-6028 at 9/27/23 2:15 PM:
---

[~libenchao] thanks for respond, of course, this is a correct syntax. 
But my point is what should we fix? calcite does support this syntax already.

My point is that calcite is a data framework, and the specific config should be 
implemented/overrided by the upper-layer framework, such as flink or drill, or 
user-defined application. In the implementation of the upper-layer 
engine/application, the config of inSubQueryThreshold of SqlToRelConverter can 
be customized. calcite itself already provides support for this capability, and 
we can only specify a default value for testing in our unit or integration 
tests.


was (Author: lemonjing):
[~libenchao] thanks for respond, of course, this is a correct syntax. 
But my point is what should we fix? calcite does support this syntax already.

My point is that calcite is a data framework, and the specific config should be 
implemented/overrided by the upper-layer framework, such as flink or drill, or 
user-defined application. In the implementation of the upper-layer engine, the 
config of inSubQueryThreshold of SqlToRelConverter can be customized. calcite 
itself already provides support for this capability, and we can only specify a 
default value for testing in our unit or integration tests.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at

[jira] [Comment Edited] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769622#comment-17769622
 ] 

Ran Tao edited comment on CALCITE-6028 at 9/27/23 2:14 PM:
---

[~libenchao] thanks for respond, of course, this is a correct syntax. 
But my point is what should we fix? calcite does support this syntax already.

My point is that calcite is a data framework, and the specific config should be 
implemented/overrided by the upper-layer framework, such as flink or drill, or 
user-defined application. In the implementation of the upper-layer engine, the 
config of inSubQueryThreshold of SqlToRelConverter can be customized. calcite 
itself already provides support for this capability, and we can only specify a 
default value for testing in our unit or integration tests.


was (Author: lemonjing):
[~libenchao] thanks for respond, of course, this is a correct syntax. 
But my point is what should we fix? calcite does support this syntax already.

My point is that calcite is a data framework, and the specific config should be 
implemented by the upper-layer framework, such as flink or drill, or 
user-defined application. In the implementation of the upper-layer engine, the 
config of inSubQueryThreshold of SqlToRelConverter can be customized. calcite 
itself already provides support for this capability, and we can only specify a 
default value for testing in our unit or integration tests.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at

[jira] [Assigned] (CALCITE-5918) Add MAP function (enabled in Spark library)

2023-09-27 Thread Ran Tao (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Ran Tao reassigned CALCITE-5918:


Assignee: Ran Tao

> Add MAP function (enabled in Spark library)
> ---
>
> Key: CALCITE-5918
> URL: https://issues.apache.org/jira/browse/CALCITE-5918
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Guillaume Massé
>Assignee: Ran Tao
>Priority: Minor
>
> Apache Spark map constructor is different than the standard SQL:
> [https://spark.apache.org/docs/3.4.0/api/sql/index.html#map]
>  
> {code:java}
> SELECT map(1.0, '2', 3.0, '4');
> // {1.0:"2",3.0:"4"} {code}
>  
> related PR that can help implement this: 
> https://github.com/apache/calcite/pull/3141



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769534#comment-17769534
 ] 

Ran Tao edited comment on CALCITE-6028 at 9/27/23 2:12 PM:
---

Hi [~loukey_j] I think this is not a bug.  Your second condition `t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)` is a inSubquery, the 
default threshold is 20. So it will cause exception when > 20.

Secondly, if use left-join on xxx and yyy.  It will get unexpected result in 
RDBMS.

You can switch to 'where' like below:

```

select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) left join (values (1, 
'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x where t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)

```

It will works. 

Of course, there is no syntax error in your sql. 

in a word. you can adjust inSubqueryThreshold or switch to 'where' to use in 
second condition. They both works.


was (Author: lemonjing):
Hi [~loukey_j] I think this is not a bug.  Your second condition `t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)` is a inSubquery, the 
default threshold is 20. So it will cause exception when > 20.

Secondly, if use left-join on xxx and yyy.  It will get unexpected result in 
RDBMS.

You can switch to 'where' like below:

```

select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) left join (values (1, 
'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x where t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)

```

It will works. 

Of course, there is no syntax error in your sql. 

in a word. you can adjust inSubqueryThreshold or switch to where to use in 
second condition. They both works.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
>

[jira] [Commented] (CALCITE-5918) Add MAP function (enabled in Spark library)

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769631#comment-17769631
 ] 

Ran Tao commented on CALCITE-5918:
--

thanks, then i will support this.

> Add MAP function (enabled in Spark library)
> ---
>
> Key: CALCITE-5918
> URL: https://issues.apache.org/jira/browse/CALCITE-5918
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Guillaume Massé
>Priority: Minor
>
> Apache Spark map constructor is different than the standard SQL:
> [https://spark.apache.org/docs/3.4.0/api/sql/index.html#map]
>  
> {code:java}
> SELECT map(1.0, '2', 3.0, '4');
> // {1.0:"2",3.0:"4"} {code}
>  
> related PR that can help implement this: 
> https://github.com/apache/calcite/pull/3141



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769534#comment-17769534
 ] 

Ran Tao edited comment on CALCITE-6028 at 9/27/23 2:04 PM:
---

Hi [~loukey_j] I think this is not a bug.  Your second condition `t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)` is a inSubquery, the 
default threshold is 20. So it will cause exception when > 20.

Secondly, if use left-join on xxx and yyy.  It will get unexpected result in 
RDBMS.

You can switch to 'where' like below:

```

select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) left join (values (1, 
'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x where t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)

```

It will works. 

Of course, there is no syntax error in your sql. 

in a word. you can adjust inSubqueryThreshold or switch to where to use in 
second condition. They both works.


was (Author: lemonjing):
Hi [~loukey_j] I think this is not a bug.  Your second condition `t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)` is a inSubquery, the 
default threshold is 20. So it will cause exception when > 20.

Secondly, if use left-join on xxx and yyy.  It will get unexpected result in 
RDBMS.

You can switch to 'where' like below:

```

select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) left join (values (1, 
'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x where t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)

```

It will works. 

Of course, there is no grammatical restriction in your sql. 

in a word. you can adjust inSubqueryThreshold or switch to where to use in 
second condition. They both works.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
>

[jira] [Comment Edited] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769534#comment-17769534
 ] 

Ran Tao edited comment on CALCITE-6028 at 9/27/23 2:03 PM:
---

Hi [~loukey_j] I think this is not a bug.  Your second condition `t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)` is a inSubquery, the 
default threshold is 20. So it will cause exception when > 20.

Secondly, if use left-join on xxx and yyy.  It will get unexpected result in 
RDBMS.

You can switch to 'where' like below:

```

select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) left join (values (1, 
'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x where t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)

```

It will works. 

Of course, there is no grammatical restriction in your sql. 

in a word. you can adjust inSubqueryThreshold or switch to where to use in 
second condition. They both works.


was (Author: lemonjing):
Hi [~loukey_j] I think this is not a bug.  Your second condition `t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)` is a inSubquery, the 
default threshold is 20. So it will cause exception when > 20.

Secondly, if use left-join on xxx and yyy.  It will get unexpected result in 
RDBMS.

You can switch to 'where' like below:

```

select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) left join (values (1, 
'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x where t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)

```

It will works. 

Of course, there is no grammatical restriction on using it this way. 

in a word. you can adjust inSubqueryThreshold or switch to where to use in 
second condition. They both works.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
>

[jira] [Comment Edited] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769622#comment-17769622
 ] 

Ran Tao edited comment on CALCITE-6028 at 9/27/23 2:01 PM:
---

[~libenchao] thanks for respond, of course, this is a correct syntax. 
But my point is what should we fix? calcite does support this syntax already.

My point is that calcite is a data framework, and the specific config should be 
implemented by the upper-layer framework, such as flink or drill, or 
user-defined application. In the implementation of the upper-layer engine, the 
config of inSubQueryThreshold of SqlToRelConverter can be customized. calcite 
itself already provides support for this capability, and we can only specify a 
default value for testing in our unit or integration tests.


was (Author: lemonjing):
[~libenchao] thanks for respond, of cause, this is a correct syntax. 
But my point is what should we fix? calcite does support this syntax already.

My point is that calcite is a data framework, and the specific config should be 
implemented by the upper-layer framework, such as flink or drill, or 
user-defined application. In the implementation of the upper-layer engine, the 
config of inSubQueryThreshold of SqlToRelConverter can be customized. calcite 
itself already provides support for this capability, and we can only specify a 
default value for testing in our unit or integration tests.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at

[jira] [Comment Edited] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769622#comment-17769622
 ] 

Ran Tao edited comment on CALCITE-6028 at 9/27/23 2:00 PM:
---

[~libenchao] thanks for respond, of cause, this is a correct syntax. 
But my point is what should we fix? calcite does support this syntax already.

My point is that calcite is a data framework, and the specific config should be 
implemented by the upper-layer framework, such as flink or drill, or 
user-defined application. In the implementation of the upper-layer engine, the 
config of inSubQueryThreshold of SqlToRelConverter can be customized. calcite 
itself already provides support for this capability, and we can only specify a 
default value for testing in our unit or integration tests.


was (Author: lemonjing):
[~libenchao] thanks for respond, agree with you, this is a correct syntax. 
But my point is what should we fix? calcite does support this syntax already.

My point is that calcite is a data framework, and the specific config should be 
implemented by the upper-layer framework, such as flink or drill, or 
user-defined application. In the implementation of the upper-layer engine, the 
config of inSubQueryThreshold of SqlToRelConverter can be customized. calcite 
itself already provides support for this capability, and we can only specify a 
default value for testing in our unit or integration tests.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at

[jira] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769622#comment-17769622
 ] 

Ran Tao commented on CALCITE-6028:
--

[~libenchao] thanks for respond, agree with you, this is a correct syntax. 
But my point is what should we fix? calcite does support this syntax already.

My point is that calcite is a data framework, and the specific config should be 
implemented by the upper-layer framework, such as flink or drill, or 
user-defined application. In the implementation of the upper-layer engine, the 
config of inSubQueryThreshold of SqlToRelConverter can be customized. calcite 
itself already provides support for this capability, and we can only specify a 
default value for testing in our unit or integration tests.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
> ... 25 more
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread LakeShen (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769587#comment-17769587
 ] 

LakeShen edited comment on CALCITE-6028 at 9/27/23 1:16 PM:


+1 with [~libenchao].

After my debug, the reason is that the logic doesn't reach when converting to 
Join with a LogicalValues when the number of elements in the `in subquery` is 
greater than 20.So the `subQuery.expr` is null.The above sql has 21 elements at 
the `in subquery`.

!image-2023-09-27-21-03-21-074.png|width=579,height=228!


was (Author: shenlang):
+1 with [~libenchao].

After my debug, the reason is that we did not implement the logic of converting 
to Join with a LogicalValues when the number of elements in the `in subquery` 
is greater than 20.So the `subQuery.expr` is null.The above sql has 21 elements 
at the `in subquery`.

!image-2023-09-27-20-59-27-654.png|width=578,height=261!

 

!image-2023-09-27-21-03-21-074.png|width=579,height=228!

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
>

[jira] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread LakeShen (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769599#comment-17769599
 ] 

LakeShen commented on CALCITE-6028:
---

I believe it is the following code that causes the in subquery above not to 
reach the convertExists logic

!image-2023-09-27-21-10-43-760.png|width=592,height=360!

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
> ... 25 more
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread LakeShen (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

LakeShen updated CALCITE-6028:
--
Attachment: image-2023-09-27-21-10-43-760.png

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png, image-2023-09-27-21-10-43-760.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
> ... 25 more
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread LakeShen (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769587#comment-17769587
 ] 

LakeShen edited comment on CALCITE-6028 at 9/27/23 1:03 PM:


+1 with [~libenchao].

After my debug, the reason is that we did not implement the logic of converting 
to Join with a LogicalValues when the number of elements in the `in subquery` 
is greater than 20.So the `subQuery.expr` is null.The above sql has 21 elements 
at the `in subquery`.

!image-2023-09-27-20-59-27-654.png|width=578,height=261!

 

!image-2023-09-27-21-03-21-074.png|width=579,height=228!


was (Author: shenlang):
+1 with [~libenchao].

After my debug, the reason is that we did not implement the logic of converting 
to Join with a LogicalValues when the number of elements in the `in subquery` 
is greater than 20.So the `subQuery.expr` is null.The above sql has 21 elements 
at the `in subquery`.

!image-2023-09-27-20-59-27-654.png|width=578,height=261!

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
> ... 25 more
> Caused by:

[jira] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ruben Q L (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769591#comment-17769591
 ] 

Ruben Q L commented on CALCITE-6028:


I agree with [~libenchao], if this is a valid SQL, then Calcite should return 
the expected result, independently of the number of values inside the IN or the 
{{DEFAULT_IN_SUB_QUERY_THRESHOLD}}. The fact that there is a workaround (moving 
the IN condition from the ON to the WHERE) does not hide that there is a bug on 
the original query.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png, 
> image-2023-09-27-21-03-21-074.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
> ... 25 more
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread LakeShen (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769587#comment-17769587
 ] 

LakeShen edited comment on CALCITE-6028 at 9/27/23 1:02 PM:


+1 with [~libenchao].

After my debug, the reason is that we did not implement the logic of converting 
to Join with a LogicalValues when the number of elements in the `in subquery` 
is greater than 20.So the `subQuery.expr` is null.The above sql has 21 elements 
at the `in subquery`.

!image-2023-09-27-20-59-27-654.png|width=578,height=261!


was (Author: shenlang):
+1 with [~libenchao].

After my debug, the reason is that we did not implement the logic of converting 
to Join with a LogicalValues when the number of elements in the `in subquery` 
is greater than 20.The above sql has 21 elements at the `in subquery`.

!image-2023-09-27-20-59-27-654.png|width=578,height=261!

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
> ... 25 more
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)



--
This message was sent by Atlassian Jira

[jira] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread LakeShen (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769587#comment-17769587
 ] 

LakeShen commented on CALCITE-6028:
---

+1 with [~libenchao].

After my debug, the reason is that we did not implement the logic of converting 
to Join with a LogicalValues when the number of elements in the `in subquery` 
is greater than 20.The above sql has 21 elements at the `in subquery`.

!image-2023-09-27-20-59-27-654.png|width=578,height=261!

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
> ... 25 more
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread LakeShen (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

LakeShen updated CALCITE-6028:
--
Attachment: image-2023-09-27-20-59-27-654.png

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
> Attachments: image-2023-09-27-20-59-27-654.png
>
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
> ... 25 more
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5918) Add MAP function (enabled in Spark library)

2023-09-27 Thread Jira



[ 
https://issues.apache.org/jira/browse/CALCITE-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769585#comment-17769585
 ] 

Guillaume Massé commented on CALCITE-5918:
--

Hi [~taoran] I won't have time to work on this. Feel free to assign this issue 
to yourself.

> Add MAP function (enabled in Spark library)
> ---
>
> Key: CALCITE-5918
> URL: https://issues.apache.org/jira/browse/CALCITE-5918
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Guillaume Massé
>Priority: Minor
>
> Apache Spark map constructor is different than the standard SQL:
> [https://spark.apache.org/docs/3.4.0/api/sql/index.html#map]
>  
> {code:java}
> SELECT map(1.0, '2', 3.0, '4');
> // {1.0:"2",3.0:"4"} {code}
>  
> related PR that can help implement this: 
> https://github.com/apache/calcite/pull/3141



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-6023) Error in SqlToRelConverter.convertQuery, possibly due to unqualified column reference

2023-09-27 Thread Jordan Hannel (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6023?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17768690#comment-17768690
 ] 

Jordan Hannel edited comment on CALCITE-6023 at 9/27/23 11:32 AM:
--

Thanks both for the quick responses! I have updated the issue subject and body 
as requested.

Re. "calling the parser and sql-to-rel-converter by hand", could you elaborate 
on this? I've updated the issue description above to include the code I'm using 
to call the parser and converter, is there a different recommended way?

Thanks for the attempted repro, I wonder if the issue has something to do with 
a subquery in the from clause - maybe the below query might repro the issue?
{code:java}
 SELECT "dim4" AS "dim7", LEAD(COUNT( * ), -1) OVER (ORDER BY "dim4") AS 
"measure10"
FROM (
  SELECT "deptno" AS "dim4" FROM emp) AS "t0"
GROUP BY "dim4"{code}


was (Author: JIRAUSER302326):
Thanks both for the quick responses! I have updated the issue subject and body 
as requested.

Re. "calling the parser and sql-to-rel-converter by hand", could you elaborate 
on this? I've updated the issue description above to include the code I'm using 
to call the parser and converter, is there a different recommended way?

Thanks for the attempted repro, I wonder if the issue has something to do with 
a subquery in the from clause - maybe the below query might repro the 
issue?}}{}}}

{{SELECT "dim4" AS "dim7", LEAD(COUNT( * ), -1) OVER (ORDER BY "dim4") AS 
"measure10"}}
{{FROM (}}
{{  SELECT "deptno" AS "dim4" FROM emp) AS "t0"}}
{{GROUP BY "dim4"}}

> Error in SqlToRelConverter.convertQuery, possibly due to unqualified column 
> reference
> -
>
> Key: CALCITE-6023
> URL: https://issues.apache.org/jira/browse/CALCITE-6023
> Project: Calcite
>  Issue Type: Bug
>Affects Versions: 1.35.0
>Reporter: Jordan Hannel
>Priority: Major
> Attachments: stacktrace.txt
>
>
> Hello all, I believe I have found a bug in SqlToRelConverter. The symptom and 
> repro are outlined below, and the full stacktrace is attached. This is on 
> calcite version 1.35.0. Any insight would be much appreciated, thanks!
> When I parse the below SQL string to get a SqlNode, and call 
> SqlToRelConverter.convertQuery on this SqlNode, I get error:
> {noformat}UnsupportedOperationException: class 
> org.apache.calcite.sql.SqlBasicCall: LEAD(COUNT( * )) OVER (ORDER BY `dim4`)
> {noformat}
> SQL:
> {code}
> SELECT "dim4" AS "dim7", LEAD(COUNT( * ), -1) OVER (ORDER BY "dim4") AS 
> "measure10"
> FROM (
>   SELECT "timestamp" AS "dim4" FROM "Shared.factDataset") AS "t0"
> GROUP BY "dim4"
> {code}
> I have confirmed that my SqlToRelConverter instance seems generally ok, 
> because it is able to convert many other SqlNodes to RelNodes. After some 
> experimentation, I noticed that just changing `ORDER BY "dim4"` to `ORDER BY 
> "t0"."dim4"` fixes the issue - after that, the SqlNode successfully is 
> translated to a RelNode.
>  
> Below is the way I am invoking convertQuery. The error comes in the last 
> line, on invocation of convertQuery
> {code}
> var config = Frameworks.newConfigBuilder()
>   .operatorTable(getOperatorTable())
>   .parserConfig(defaultParserConfig().build())
>   .build();
> var sqlNode = Frameworks.getPlanner(config).parse(sqlString);
> var typeFactory = new JavaTypeFactoryImpl();
> var calciteSchema = buildCalciteSchema(typeFactory, schema);
> var catalogReader = new CalciteCatalogReader(
>   calciteSchema,
>   List.of(),
>   typeFactory,
>   CalciteConnectionConfig.DEFAULT);
> var validator = new SqlValidatorImpl(
>   getOperatorTable(),
>   catalogReader,
>   typeFactory,
>   SqlValidator.Config.DEFAULT.withCallRewrite(false)) {};
> var vol = new VolcanoPlanner();
> vol.addRelTraitDef(ConventionTraitDef.INSTANCE);
> var cluster = RelOptCluster.create(vol, new RexBuilder(typeFactory));
> var sqlToRel = new SqlToRelConverter(
>   NOOP_EXPANDER,
>   validator,
>   catalogReader,
>   cluster,
>   StandardConvertletTable.INSTANCE,
>   SqlToRelConverter.config());
> return sqlToRel.convertQuery(sqlNode, true, true);
> {code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6023) Error in SqlToRelConverter.convertQuery, possibly due to unqualified column reference

2023-09-27 Thread Jordan Hannel (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6023?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769551#comment-17769551
 ] 

Jordan Hannel commented on CALCITE-6023:


Thanks [~julianhyde] , I appreciate it, will do that going forward.

Do you have any insight regarding my questions in my last comment above? Thanks

> Error in SqlToRelConverter.convertQuery, possibly due to unqualified column 
> reference
> -
>
> Key: CALCITE-6023
> URL: https://issues.apache.org/jira/browse/CALCITE-6023
> Project: Calcite
>  Issue Type: Bug
>Affects Versions: 1.35.0
>Reporter: Jordan Hannel
>Priority: Major
> Attachments: stacktrace.txt
>
>
> Hello all, I believe I have found a bug in SqlToRelConverter. The symptom and 
> repro are outlined below, and the full stacktrace is attached. This is on 
> calcite version 1.35.0. Any insight would be much appreciated, thanks!
> When I parse the below SQL string to get a SqlNode, and call 
> SqlToRelConverter.convertQuery on this SqlNode, I get error:
> {noformat}UnsupportedOperationException: class 
> org.apache.calcite.sql.SqlBasicCall: LEAD(COUNT( * )) OVER (ORDER BY `dim4`)
> {noformat}
> SQL:
> {code}
> SELECT "dim4" AS "dim7", LEAD(COUNT( * ), -1) OVER (ORDER BY "dim4") AS 
> "measure10"
> FROM (
>   SELECT "timestamp" AS "dim4" FROM "Shared.factDataset") AS "t0"
> GROUP BY "dim4"
> {code}
> I have confirmed that my SqlToRelConverter instance seems generally ok, 
> because it is able to convert many other SqlNodes to RelNodes. After some 
> experimentation, I noticed that just changing `ORDER BY "dim4"` to `ORDER BY 
> "t0"."dim4"` fixes the issue - after that, the SqlNode successfully is 
> translated to a RelNode.
>  
> Below is the way I am invoking convertQuery. The error comes in the last 
> line, on invocation of convertQuery
> {code}
> var config = Frameworks.newConfigBuilder()
>   .operatorTable(getOperatorTable())
>   .parserConfig(defaultParserConfig().build())
>   .build();
> var sqlNode = Frameworks.getPlanner(config).parse(sqlString);
> var typeFactory = new JavaTypeFactoryImpl();
> var calciteSchema = buildCalciteSchema(typeFactory, schema);
> var catalogReader = new CalciteCatalogReader(
>   calciteSchema,
>   List.of(),
>   typeFactory,
>   CalciteConnectionConfig.DEFAULT);
> var validator = new SqlValidatorImpl(
>   getOperatorTable(),
>   catalogReader,
>   typeFactory,
>   SqlValidator.Config.DEFAULT.withCallRewrite(false)) {};
> var vol = new VolcanoPlanner();
> vol.addRelTraitDef(ConventionTraitDef.INSTANCE);
> var cluster = RelOptCluster.create(vol, new RexBuilder(typeFactory));
> var sqlToRel = new SqlToRelConverter(
>   NOOP_EXPANDER,
>   validator,
>   catalogReader,
>   cluster,
>   StandardConvertletTable.INSTANCE,
>   SqlToRelConverter.config());
> return sqlToRel.convertQuery(sqlNode, true, true);
> {code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Benchao Li (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769549#comment-17769549
 ] 

Benchao Li commented on CALCITE-6028:
-

I would take this as a bug. {{DEFAULT_IN_SUB_QUERY_THRESHOLD}} is just a 
implementation mechanism, not a restriction in SQL semantic. The sql is valid 
and should be supported.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
> ... 25 more
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6020) SqlToRelConverter should not replace windowed SUM with equivalent expression using SUM0

2023-09-27 Thread Stamatis Zampetakis (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6020?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769545#comment-17769545
 ] 

Stamatis Zampetakis commented on CALCITE-6020:
--

The code would be more modular if the SqlToRelConverter didn't perform directly 
optimizations/simplifications since that gives more flexibility to the users. I 
like the approach of having this in other places such as rules (e.g., 
[AggregateReduceFunctionsRule|https://github.com/apache/calcite/blob/aef9bdb5c091e4df84eeb47a9aa70770dd3d3fb8/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsRule.java]),
 convertlets (e.g., 
[SqlRexConvertletTable|https://github.com/apache/calcite/blob/aef9bdb5c091e4df84eeb47a9aa70770dd3d3fb8/core/src/main/java/org/apache/calcite/sql2rel/SqlRexConvertletTable.java]),
 etc. Maybe we could extract this reduction to another place as well.

SUM vs SUM0 has been a debatable topic lately so I am in favor of options that 
make this decision configurable/modular.

> SqlToRelConverter should not replace windowed SUM with equivalent expression 
> using SUM0
> ---
>
> Key: CALCITE-6020
> URL: https://issues.apache.org/jira/browse/CALCITE-6020
> Project: Calcite
>  Issue Type: Improvement
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>  Labels: pull-request-available
>
> {{SqlToRelConverter}} replaces {{SUM}} with {{SUM0}} around 
> [here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L5885]
> This might have been needed at some point in the past - but I think it will 
> be better to leave it as {{SUM}} - as in case there is no {{SUM0}} in the 
> system that will be replaced with a {{COALESCE(SUM(...) , 0 )}} to provide it 
> - as see 
> [here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java#L1288]



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769534#comment-17769534
 ] 

Ran Tao edited comment on CALCITE-6028 at 9/27/23 11:00 AM:


Hi [~loukey_j] I think this is not a bug.  Your second condition `t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)` is a inSubquery, the 
default threshold is 20. So it will cause exception when > 20.

Secondly, if use left-join on xxx and yyy.  It will get unexpected result in 
RDBMS.

You can switch to 'where' like below:

```

select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) left join (values (1, 
'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x where t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)

```

It will works. 

Of course, there is no grammatical restriction on using it this way. 

in a word. you can adjust inSubqueryThreshold or switch to where to use in 
second condition. They both works.


was (Author: lemonjing):
Hi [~loukey_j] I think this is not a bug.  Your second condition `t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)` is a inSubquery, the 
default threshold is 20. So it will cause exception when > 20.

Secondly, if use left-join on xxx and yyy.  It will get unexpected result in 
RDBMS if yyy is left-table's condition.

You can switch to 'where' like below:

```

select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) left join (values (1, 
'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x where t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)

```

It will works. 

Of course, there is no grammatical restriction on using it this way. 

in a word. you can adjust inSubqueryThreshold or switch to where to use in 
second condition. They both works.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
>

[jira] [Commented] (CALCITE-5940) Add the Rule to merge Limit

2023-09-27 Thread Jiajun Xie (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-5940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769535#comment-17769535
 ] 

Jiajun Xie commented on CALCITE-5940:
-

Fixed in 
[aef9bdb|https://github.com/apache/calcite/commit/aef9bdb5c091e4df84eeb47a9aa70770dd3d3fb8].

Thanks for your PR [~shenlang].

Thanks for your review  [~kgyrtkirk] ,  [~nobigo], [~taoran].

> Add the Rule to merge Limit
> ---
>
> Key: CALCITE-5940
> URL: https://issues.apache.org/jira/browse/CALCITE-5940
> Project: Calcite
>  Issue Type: New Feature
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
>
> Now in calcite,the Limit will be represented using 
> LogicalSort(fetch=[xx]),but there are few rules to optimize Limit.
> In trino and presto,there are many optimization rules to optimize Limit.
> For example,the sql:
> {code:java}
> select * from nation limit 0 {code}
> The limit 0 will use empty ValuesNode(Calcite LogicalValues) to optimize,so 
> the SQL is not delivered to the Worker compute,the rule could see: 
> [EvaluateZeroLimit|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/EvaluateZeroLimit.java#L28C1-L28C31]
> The sql:
> {code:java}
> select concat('-',N_REGIONKEY) from (select * from nation limit 1) limit 
> 10 {code}
> It would be optimized by 
> [MergeLimits|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimits.java#L26]
>  rule to:
> {code:java}
> select concat('-',N_REGIONKEY) from nation limit 10  {code}
> The value of limit takes the minimum of the outer limit and the inner limit.
> The sql:
> {code:java}
> select concat('-',N_REGIONKEY) from (SELECT * FROM nation order BY 
> N_REGIONKEY DESC LIMIT 1) limit 10 {code}
> It would be optimized by 
> [MergeLimitWithTopN|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimitWithTopN.java#L28C1-L28C31]
>  rule to:
> {code:java}
> SELECT concat('-',N_REGIONKEY) FROM nation order BY N_REGIONKEY DESC LIMIT 
> 10{code}
> So I propose to add these three rules to Calcite as well, to optimize the 
> Limit case.
>  
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Resolved] (CALCITE-5940) Add the Rule to merge Limit

2023-09-27 Thread Jiajun Xie (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-5940?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Jiajun Xie resolved CALCITE-5940.
-
Resolution: Fixed

> Add the Rule to merge Limit
> ---
>
> Key: CALCITE-5940
> URL: https://issues.apache.org/jira/browse/CALCITE-5940
> Project: Calcite
>  Issue Type: New Feature
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
>
> Now in calcite,the Limit will be represented using 
> LogicalSort(fetch=[xx]),but there are few rules to optimize Limit.
> In trino and presto,there are many optimization rules to optimize Limit.
> For example,the sql:
> {code:java}
> select * from nation limit 0 {code}
> The limit 0 will use empty ValuesNode(Calcite LogicalValues) to optimize,so 
> the SQL is not delivered to the Worker compute,the rule could see: 
> [EvaluateZeroLimit|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/EvaluateZeroLimit.java#L28C1-L28C31]
> The sql:
> {code:java}
> select concat('-',N_REGIONKEY) from (select * from nation limit 1) limit 
> 10 {code}
> It would be optimized by 
> [MergeLimits|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimits.java#L26]
>  rule to:
> {code:java}
> select concat('-',N_REGIONKEY) from nation limit 10  {code}
> The value of limit takes the minimum of the outer limit and the inner limit.
> The sql:
> {code:java}
> select concat('-',N_REGIONKEY) from (SELECT * FROM nation order BY 
> N_REGIONKEY DESC LIMIT 1) limit 10 {code}
> It would be optimized by 
> [MergeLimitWithTopN|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimitWithTopN.java#L28C1-L28C31]
>  rule to:
> {code:java}
> SELECT concat('-',N_REGIONKEY) FROM nation order BY N_REGIONKEY DESC LIMIT 
> 10{code}
> So I propose to add these three rules to Calcite as well, to optimize the 
> Limit case.
>  
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769534#comment-17769534
 ] 

Ran Tao commented on CALCITE-6028:
--

Hi [~loukey_j] I think this is not a bug.  You second condition `t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)` is a inSubquery, the 
default threshold is 20. So it will cause exception when > 20.

Secondly, if use left-join on xxx and yyy.  It will get unexpected result in 
RDBMS if yyy is left-table's condition.

You can switch to 'where' like below:

```

select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) left join (values (1, 
'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x where t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)

```

It will works. 

Of course, there is no grammatical restriction on using it this way. 

in a word. you can adjust inSubqueryThreshold or switch to where to use in 
second condition. They both works.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
> at 
> org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> Caused by: java.lang.reflect.InvocationTargetException
>  
> at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
> ... 25 more
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769534#comment-17769534
 ] 

Ran Tao edited comment on CALCITE-6028 at 9/27/23 10:42 AM:


Hi [~loukey_j] I think this is not a bug.  Your second condition `t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)` is a inSubquery, the 
default threshold is 20. So it will cause exception when > 20.

Secondly, if use left-join on xxx and yyy.  It will get unexpected result in 
RDBMS if yyy is left-table's condition.

You can switch to 'where' like below:

```

select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) left join (values (1, 
'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x where t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)

```

It will works. 

Of course, there is no grammatical restriction on using it this way. 

in a word. you can adjust inSubqueryThreshold or switch to where to use in 
second condition. They both works.


was (Author: lemonjing):
Hi [~loukey_j] I think this is not a bug.  You second condition `t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)` is a inSubquery, the 
default threshold is 20. So it will cause exception when > 20.

Secondly, if use left-join on xxx and yyy.  It will get unexpected result in 
RDBMS if yyy is left-table's condition.

You can switch to 'where' like below:

```

select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) left join (values (1, 
'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x where t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)

```

It will works. 

Of course, there is no grammatical restriction on using it this way. 

in a word. you can adjust inSubqueryThreshold or switch to where to use in 
second condition. They both works.

> Join on with more than 20 in conditions will report a null pointer error.
> -
>
> Key: CALCITE-6028
> URL: https://issues.apache.org/jira/browse/CALCITE-6028
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: loukey_j
>Priority: Critical
>
> final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
> left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
>  
>  
> java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
> IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
> at 
> org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
> at 
> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
> at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at 
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at 
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at 
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
> at 
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> at 
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at 
>

[jira] (CALCITE-5918) Add MAP function (enabled in Spark library)

2023-09-27 Thread Ran Tao (Jira)



[ https://issues.apache.org/jira/browse/CALCITE-5918 ]


Ran Tao deleted comment on CALCITE-5918:
--

was (Author: lemonjing):
Remind: The map function of spark is different from calcite in two points. The 
first point is that spark uses map(...), and the second point is that empty map 
is allowed in spark, such as 'map()', however it will cause exception in 
calcite.

> Add MAP function (enabled in Spark library)
> ---
>
> Key: CALCITE-5918
> URL: https://issues.apache.org/jira/browse/CALCITE-5918
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Guillaume Massé
>Priority: Minor
>
> Apache Spark map constructor is different than the standard SQL:
> [https://spark.apache.org/docs/3.4.0/api/sql/index.html#map]
>  
> {code:java}
> SELECT map(1.0, '2', 3.0, '4');
> // {1.0:"2",3.0:"4"} {code}
>  
> related PR that can help implement this: 
> https://github.com/apache/calcite/pull/3141



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-5918) Add MAP function (enabled in Spark library)

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769510#comment-17769510
 ] 

Ran Tao edited comment on CALCITE-5918 at 9/27/23 9:29 AM:
---

[~MasseGuillaume] hi, Guillaume, Are you still continuing this work? I'm glad 
to support this ticket.

I'm asking this because we have added many spark map functions such as 
map_keys/map_values in calcite 1.35.0. however the test cases of these 
functions are using the standard calcite map constructor. IMHO. if we have 
standard array/map, spark array/map, this would be consistent. And user can use 
all spark array/map functions.


was (Author: lemonjing):
[~MasseGuillaume] hi, Guillaume, Are you still continuing this work? I'm glad 
to support this ticket.

I'm asking this because we have added many spark map functions such as 
map_keys/map_values in calcite 1.35.0. however the test cases of these 
functions are using the standard calcite map constructor. IMHO. if we have 
standard array/map, spark array/map, this would be consistent. And user can use 
all spark map functions.

> Add MAP function (enabled in Spark library)
> ---
>
> Key: CALCITE-5918
> URL: https://issues.apache.org/jira/browse/CALCITE-5918
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Guillaume Massé
>Priority: Minor
>
> Apache Spark map constructor is different than the standard SQL:
> [https://spark.apache.org/docs/3.4.0/api/sql/index.html#map]
>  
> {code:java}
> SELECT map(1.0, '2', 3.0, '4');
> // {1.0:"2",3.0:"4"} {code}
>  
> related PR that can help implement this: 
> https://github.com/apache/calcite/pull/3141



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5918) Add MAP function (enabled in Spark library)

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769514#comment-17769514
 ] 

Ran Tao commented on CALCITE-5918:
--

Remind: The map function of spark is different from calcite in two points. The 
first point is that spark uses map(...), and the second point is that empty map 
is allowed in spark, such as 'map()', however it will cause exception in 
calcite.

> Add MAP function (enabled in Spark library)
> ---
>
> Key: CALCITE-5918
> URL: https://issues.apache.org/jira/browse/CALCITE-5918
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Guillaume Massé
>Priority: Minor
>
> Apache Spark map constructor is different than the standard SQL:
> [https://spark.apache.org/docs/3.4.0/api/sql/index.html#map]
>  
> {code:java}
> SELECT map(1.0, '2', 3.0, '4');
> // {1.0:"2",3.0:"4"} {code}
>  
> related PR that can help implement this: 
> https://github.com/apache/calcite/pull/3141



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Comment Edited] (CALCITE-5918) Add MAP function (enabled in Spark library)

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769510#comment-17769510
 ] 

Ran Tao edited comment on CALCITE-5918 at 9/27/23 9:24 AM:
---

[~MasseGuillaume] hi, Guillaume, Are you still continuing this work? I'm glad 
to support this ticket.

I'm asking this because we have added many spark map functions such as 
map_keys/map_values in calcite 1.35.0. however the test cases of these 
functions are using the standard calcite map constructor. IMHO. if we have 
standard array/map, spark array/map, this would be consistent. And user can use 
all spark map functions.


was (Author: lemonjing):
[~MasseGuillaume] hi, Guillaume, Are you still continuing this work? I'm glad 
to support this ticket.

I'm asking this because we have added many spark map functions such as 
map_keys/map_values. however the test cases of these functions are using the 
standard calcite map constructor. IMHO. if we have standard array/map, spark 
array/map, this would be consistent. And user can use all spark map functions.

> Add MAP function (enabled in Spark library)
> ---
>
> Key: CALCITE-5918
> URL: https://issues.apache.org/jira/browse/CALCITE-5918
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Guillaume Massé
>Priority: Minor
>
> Apache Spark map constructor is different than the standard SQL:
> [https://spark.apache.org/docs/3.4.0/api/sql/index.html#map]
>  
> {code:java}
> SELECT map(1.0, '2', 3.0, '4');
> // {1.0:"2",3.0:"4"} {code}
>  
> related PR that can help implement this: 
> https://github.com/apache/calcite/pull/3141



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5918) Add MAP function (enabled in Spark library)

2023-09-27 Thread Ran Tao (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769510#comment-17769510
 ] 

Ran Tao commented on CALCITE-5918:
--

[~MasseGuillaume] hi, Guillaume, Are you still continuing this work? I'm glad 
to support this ticket.

I'm asking this because we have added many spark map functions such as 
map_keys/map_values. however the test cases of these functions are using the 
standard calcite map constructor. IMHO. if we have standard array/map, spark 
array/map, this would be consistent. And user can use all spark map functions.

> Add MAP function (enabled in Spark library)
> ---
>
> Key: CALCITE-5918
> URL: https://issues.apache.org/jira/browse/CALCITE-5918
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Guillaume Massé
>Priority: Minor
>
> Apache Spark map constructor is different than the standard SQL:
> [https://spark.apache.org/docs/3.4.0/api/sql/index.html#map]
>  
> {code:java}
> SELECT map(1.0, '2', 3.0, '4');
> // {1.0:"2",3.0:"4"} {code}
>  
> related PR that can help implement this: 
> https://github.com/apache/calcite/pull/3141



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Assigned] (CALCITE-6027) Add the rule to rewrite cardinality on map_keys and map_values functions

2023-09-27 Thread Ran Tao (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-6027?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Ran Tao reassigned CALCITE-6027:


Assignee: Ran Tao

> Add the rule to rewrite cardinality on map_keys and map_values functions
> 
>
> Key: CALCITE-6027
> URL: https://issues.apache.org/jira/browse/CALCITE-6027
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
>
> when we call cardinality on map_keys or map_values, such as:
> {code:java}
> select cardinality(map_keys(map['foo', 1, 'bar', 2]));
> select cardinality(map_values(map['foo', 1, 'bar', 2]));{code}
> they can be safely simplified to
> {code:java}
> select cardinality(map['foo', 1, 'bar', 2]);
> select cardinality(map['foo', 1, 'bar', 2]); {code}
>  
> Some other mature engines such as presto/trino has same optimized logic.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6027) Add the rule to rewrite cardinality on map_keys and map_values functions

2023-09-27 Thread Ran Tao (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-6027?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Ran Tao updated CALCITE-6027:
-
Summary: Add the rule to rewrite cardinality on map_keys and map_values 
functions  (was: Add the rule to simplify cardinality on map_keys and 
map_values functions)

> Add the rule to rewrite cardinality on map_keys and map_values functions
> 
>
> Key: CALCITE-6027
> URL: https://issues.apache.org/jira/browse/CALCITE-6027
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Priority: Major
>
> when we call cardinality on map_keys or map_values, such as:
> {code:java}
> select cardinality(map_keys(map['foo', 1, 'bar', 2]));
> select cardinality(map_values(map['foo', 1, 'bar', 2]));{code}
> they can be safely simplified to
> {code:java}
> select cardinality(map['foo', 1, 'bar', 2]);
> select cardinality(map['foo', 1, 'bar', 2]); {code}
>  
> Some other mature engines such as presto/trino has same optimized logic.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5940) Add the Rule to merge Limit

2023-09-27 Thread LakeShen (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-5940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769467#comment-17769467
 ] 

LakeShen commented on CALCITE-5940:
---

Hi [~jiajunbernoulli] ,thanks for your suggestions,I have done for it.

> Add the Rule to merge Limit
> ---
>
> Key: CALCITE-5940
> URL: https://issues.apache.org/jira/browse/CALCITE-5940
> Project: Calcite
>  Issue Type: New Feature
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
>
> Now in calcite,the Limit will be represented using 
> LogicalSort(fetch=[xx]),but there are few rules to optimize Limit.
> In trino and presto,there are many optimization rules to optimize Limit.
> For example,the sql:
> {code:java}
> select * from nation limit 0 {code}
> The limit 0 will use empty ValuesNode(Calcite LogicalValues) to optimize,so 
> the SQL is not delivered to the Worker compute,the rule could see: 
> [EvaluateZeroLimit|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/EvaluateZeroLimit.java#L28C1-L28C31]
> The sql:
> {code:java}
> select concat('-',N_REGIONKEY) from (select * from nation limit 1) limit 
> 10 {code}
> It would be optimized by 
> [MergeLimits|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimits.java#L26]
>  rule to:
> {code:java}
> select concat('-',N_REGIONKEY) from nation limit 10  {code}
> The value of limit takes the minimum of the outer limit and the inner limit.
> The sql:
> {code:java}
> select concat('-',N_REGIONKEY) from (SELECT * FROM nation order BY 
> N_REGIONKEY DESC LIMIT 1) limit 10 {code}
> It would be optimized by 
> [MergeLimitWithTopN|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimitWithTopN.java#L28C1-L28C31]
>  rule to:
> {code:java}
> SELECT concat('-',N_REGIONKEY) FROM nation order BY N_REGIONKEY DESC LIMIT 
> 10{code}
> So I propose to add these three rules to Calcite as well, to optimize the 
> Limit case.
>  
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-5940) Add the Rule to merge Limit

2023-09-27 Thread LakeShen (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-5940?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

LakeShen updated CALCITE-5940:
--
Summary: Add the Rule to merge Limit  (was: Add the Rule to optimize Limit)

> Add the Rule to merge Limit
> ---
>
> Key: CALCITE-5940
> URL: https://issues.apache.org/jira/browse/CALCITE-5940
> Project: Calcite
>  Issue Type: New Feature
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
>
> Now in calcite,the Limit will be represented using 
> LogicalSort(fetch=[xx]),but there are few rules to optimize Limit.
> In trino and presto,there are many optimization rules to optimize Limit.
> For example,the sql:
> {code:java}
> select * from nation limit 0 {code}
> The limit 0 will use empty ValuesNode(Calcite LogicalValues) to optimize,so 
> the SQL is not delivered to the Worker compute,the rule could see: 
> [EvaluateZeroLimit|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/EvaluateZeroLimit.java#L28C1-L28C31]
> The sql:
> {code:java}
> select concat('-',N_REGIONKEY) from (select * from nation limit 1) limit 
> 10 {code}
> It would be optimized by 
> [MergeLimits|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimits.java#L26]
>  rule to:
> {code:java}
> select concat('-',N_REGIONKEY) from nation limit 10  {code}
> The value of limit takes the minimum of the outer limit and the inner limit.
> The sql:
> {code:java}
> select concat('-',N_REGIONKEY) from (SELECT * FROM nation order BY 
> N_REGIONKEY DESC LIMIT 1) limit 10 {code}
> It would be optimized by 
> [MergeLimitWithTopN|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimitWithTopN.java#L28C1-L28C31]
>  rule to:
> {code:java}
> SELECT concat('-',N_REGIONKEY) FROM nation order BY N_REGIONKEY DESC LIMIT 
> 10{code}
> So I propose to add these three rules to Calcite as well, to optimize the 
> Limit case.
>  
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5940) Add the Rule to optimize Limit

2023-09-27 Thread LakeShen (Jira)



[ 
https://issues.apache.org/jira/browse/CALCITE-5940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17769464#comment-17769464
 ] 

LakeShen commented on CALCITE-5940:
---

[~jiajunbernoulli] Yes,it makes sense for me, I will change the title and 
pr,unit test summary.

> Add the Rule to optimize Limit
> --
>
> Key: CALCITE-5940
> URL: https://issues.apache.org/jira/browse/CALCITE-5940
> Project: Calcite
>  Issue Type: New Feature
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
>
> Now in calcite,the Limit will be represented using 
> LogicalSort(fetch=[xx]),but there are few rules to optimize Limit.
> In trino and presto,there are many optimization rules to optimize Limit.
> For example,the sql:
> {code:java}
> select * from nation limit 0 {code}
> The limit 0 will use empty ValuesNode(Calcite LogicalValues) to optimize,so 
> the SQL is not delivered to the Worker compute,the rule could see: 
> [EvaluateZeroLimit|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/EvaluateZeroLimit.java#L28C1-L28C31]
> The sql:
> {code:java}
> select concat('-',N_REGIONKEY) from (select * from nation limit 1) limit 
> 10 {code}
> It would be optimized by 
> [MergeLimits|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimits.java#L26]
>  rule to:
> {code:java}
> select concat('-',N_REGIONKEY) from nation limit 10  {code}
> The value of limit takes the minimum of the outer limit and the inner limit.
> The sql:
> {code:java}
> select concat('-',N_REGIONKEY) from (SELECT * FROM nation order BY 
> N_REGIONKEY DESC LIMIT 1) limit 10 {code}
> It would be optimized by 
> [MergeLimitWithTopN|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimitWithTopN.java#L28C1-L28C31]
>  rule to:
> {code:java}
> SELECT concat('-',N_REGIONKEY) FROM nation order BY N_REGIONKEY DESC LIMIT 
> 10{code}
> So I propose to add these three rules to Calcite as well, to optimize the 
> Limit case.
>  
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Resolved] (CALCITE-6017) Update the GitHub link of released versions

2023-09-27 Thread Ruben Q L (Jira)



 [ 
https://issues.apache.org/jira/browse/CALCITE-6017?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Ruben Q L resolved CALCITE-6017.

Resolution: Fixed

Fixed via 
[{{56f7b82}}|https://github.com/apache/calcite/commit/56f7b8248352568539cbfe0222606903d69e521c]

Thanks [~taoran] for the patch!

> Update the GitHub link of released versions
> ---
>
> Key: CALCITE-6017
> URL: https://issues.apache.org/jira/browse/CALCITE-6017
> Project: Calcite
>  Issue Type: Improvement
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Minor
>  Labels: doc, pull-request-available
> Fix For: 1.36.0
>
> Attachments: image-2023-09-21-16-03-16-132.png, 
> image-2023-09-21-16-04-00-579.png, image-2023-09-21-16-04-54-398.png
>
>
> In calcite history page, we have a description:
> {noformat}
> For a full list of releases, see
> https://github.com/apache/calcite/releases;>github. 
> {noformat}
> however, there is nothing on this page, because calcite not use github to 
> manage the released versions. So this description may seem a bit strange to 
> end users.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Created] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.

2023-09-27 Thread loukey_j (Jira)

loukey_j created CALCITE-6028:
-

 Summary: Join on with more than 20 in conditions will report a 
null pointer error.
 Key: CALCITE-6028
 URL: https://issues.apache.org/jira/browse/CALCITE-6028
 Project: Calcite
  Issue Type: Bug
  Components: core
Reporter: loukey_j


final String sql = "select t1.x from (values (1, 'a'), (2, 'b')) as t1(x, y) 
left join (values (1, 'a'), (2, 'b')) as t2(x, y) on t1.x=t2.x and t1.x in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)";   
 
 
java.lang.RuntimeException: while converting `T1`.`X` = `T2`.`X` AND `T1`.`X` 
IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
at 
org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:99)
at 
org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:59)
at 
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5656)
at 
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4827)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
at 
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5469)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3261)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2401)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2285)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:698)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:679)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3748)
at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:599)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
at 
org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
at 
org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
at 
org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
at 
org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
at 
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:630)
at 
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
at 
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
at 
org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
at 
org.apache.calcite.examples.foodmart.java.JdbcExample.run(JdbcExample.java:52)
at 
org.apache.calcite.examples.foodmart.java.JdbcExample.main(JdbcExample.java:36)
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
Caused by: java.lang.reflect.InvocationTargetException
 
at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at 
org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$1(ReflectiveConvertletTable.java:95)
... 25 more
Caused by: java.lang.NullPointerException
at java.util.Objects.requireNonNull(Objects.java:203)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

66 matches

Mail list logo