[
https://issues.apache.org/jira/browse/CALCITE-4844?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17430538#comment-17430538
]
Xurenhe edited comment on CALCITE-4844 at 10/19/21, 1:19 PM:
-------------------------------------------------------------
{code:java}
SELECT empno
FROM emp AS e
WHERE deptno in (empno, 110){code}
Hi [~julianhyde]
It's ok in the stage of validation, which is that both identifiers of column
and literals are in the value list of
`org.apache.calcite.sql.fun.SqlStdOperatorTable#IN`.
But this case is meeting the same error, which is mentioned in the current
issue.
was (Author: wojustme):
{code:java}
SELECT empno
FROM emp AS e
WHERE deptno in (empno, 110){code}
It's ok in the stage of validation, which is that both identifiers of column
and literals are in the value list of
`org.apache.calcite.sql.fun.SqlStdOperatorTable#IN`.
But this case is meeting the same error, which is mentioned in the current
issue.
> when the value of sub-query threshold smaller than IN list size the In list
> include columns return wrong result.
> ----------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-4844
> URL: https://issues.apache.org/jira/browse/CALCITE-4844
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.27.0
> Reporter: duan xiong
> Assignee: duan xiong
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> According CALCITE-1155[Support columns for IN list]. We support
> {code:java}
> 'x in (a, b, c)' {code}
> by expands to
> {code:java}
> 'x = a or x = b or x = c'{code}
> In Calcite. Only the list size smaller than the value of the sub-query
> threshold can convert. We should ignore the the value of the sub-query
> threshold to convert this.
>
> This issue can reproduce by add a unit test in SqlToRelConverterTest:
> {code:java}
> @Test void testInListIncludeColumns() {
> final String sql = "SELECT empno\n"
> + "FROM emp AS e\n"
> + "WHERE 130 in (empno, deptno)";
> sql(sql).withConfig(b -> b.withInSubQueryThreshold(3))
> .convertsTo(
> "\n" +
> "LogicalProject(EMPNO=[$0])\n" +
> " LogicalFilter(condition=[OR(=(130, $0), =(130, $7))])\n" +
> " LogicalTableScan(table=[[CATALOG, SALES, EMP]])\n");
> sql(sql).withConfig(b -> b.withInSubQueryThreshold(1))
> .throws_("java.lang.NullPointerException: scope");
> }{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
