[jira] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.
[ https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17780032#comment-17780032 ] Ran Tao commented on CALCITE-6028: -- I'm working on this now. almost done, maybe we can add it in 1.36. > 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 >Assignee: Ran Tao >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.
[ https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17778641#comment-17778641 ] Ran Tao commented on CALCITE-6028: -- [~loukey_j] i will try to fix it when i'm free, but i'm not sure whether i can get it done in coming calcite-1.36. so also welcome if anyone else can 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.
[ https://issues.apache.org/jira/browse/CALCITE-6028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17778574#comment-17778574 ] loukey_j commented on CALCITE-6028: --- Does the community have any plans to fix this issue? [~taoran] [~libenchao] [~shenlang] [~rubenql] > 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.
[ 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.
[
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.
[ 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-6028) Join on with more than 20 in conditions will report a null pointer error.
[
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] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.
[ 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] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.
[ 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] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.
[
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] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.
[ 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] [Commented] (CALCITE-6028) Join on with more than 20 in conditions will report a null pointer error.
[
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-6028) Join on with more than 20 in conditions will report a null pointer error.
[ 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)
