Brandon Chong created CALCITE-6545:
--------------------------------------
Summary: SqlToRel does not honor expand = false for IN Subquery
with literals
Key: CALCITE-6545
URL: https://issues.apache.org/jira/browse/CALCITE-6545
Project: Calcite
Issue Type: Bug
Reporter: Brandon Chong
If a query has
`SELECT * FROM EMP WHERE EMPNO IN (1, 2)`
and the literal list is longer than InSubQueryThreshold, then I think it should
rewrite to a RexSubquery with Values RelNode like so:
```
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[IN($0, \{ LogicalValues(tuples=[[{ 1 }, \{ 2 }]])
})])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
```
I understand that the docs explicitly calls out that it will rewrite to a JOIN:
/** Returns the \{@code inSubQueryThreshold} option,
* default \{@link #DEFAULT_IN_SUB_QUERY_THRESHOLD}. Controls the list size
* threshold under which \{@link #convertInToOr} is used. Lists of this size
* or greater will instead be converted to use a join against an inline
* table (\{@link org.apache.calcite.rel.logical.LogicalValues}) rather than
* a predicate. A threshold of 0 forces usage of an inline table in all
* cases; a threshold of \{@link Integer#MAX_VALUE} forces usage of OR in all
* cases. */
int getInSubQueryThreshold();
But I feel this contradicts:
/** Returns the \{@code expand} option. Controls whether to expand
* sub-queries. If false, each sub-query becomes a
* \{@link org.apache.calcite.rex.RexSubQuery}. */
boolean isExpand();
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
