[
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.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)
