[jira] [Comment Edited] (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&focusedCommentId=17778641#comment-17778641 ] Ran Tao edited comment on CALCITE-6028 at 10/24/23 2:45 AM: [~loukey_j] hi, loukey, i will try to fix it when i'm free, but i'm not sure whether i can get it done in up-coming calcite-1.36. so also welcome if anyone else can fix it. was (Author: lemonjing): [~loukey_j] hi, loukey, 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] [Comment Edited] (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&focusedCommentId=17778641#comment-17778641 ] Ran Tao edited comment on CALCITE-6028 at 10/23/23 12:20 PM: - [~loukey_j] hi, loukey, 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. was (Author: lemonjing): [~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] [Comment Edited] (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&focusedCommentId=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 > org.apache.calcite.jdbc.CalciteConnectionImpl.p
[jira] [Comment Edited] (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&focusedCommentId=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.InvocationTarg
[jira] [Comment Edited] (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&focusedCommentId=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 sun.re
[jira] [Comment Edited] (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&focusedCommentId=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) >
[jira] [Comment Edited] (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&focusedCommentId=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.ja
[jira] [Comment Edited] (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&focusedCommentId=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 > org.apache.calcite.avatica.AvaticaStatement.executeQuery
[jira] [Comment Edited] (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&focusedCommentId=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 sun.reflect.Nativ
[jira] [Comment Edited] (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&focusedCommentId=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 sun.reflect.
[jira] [Comment Edited] (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&focusedCommentId=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(ReflectiveConvertletTab
[jira] [Comment Edited] (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&focusedCommentId=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 > Ca
[jira] [Comment Edited] (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&focusedCommentId=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 Atla
[jira] [Comment Edited] (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&focusedCommentId=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 > org.apache.calcite.examples.foodmart.java.JdbcExampl
[jira] [Comment Edited] (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&focusedCommentId=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 > org.apache.calcite.e