Zoltan Haindrich created CALCITE-6435:
-----------------------------------------
Summary: SqlToRel conversion of IN expressions may lead to
incorrect simplifications
Key: CALCITE-6435
URL: https://issues.apache.org/jira/browse/CALCITE-6435
Project: Calcite
Issue Type: Bug
Reporter: Zoltan Haindrich
Assignee: Zoltan Haindrich
the query must have the following features:
* not all columns are selected
** to enable `RelFieldTrimmer` to start a cycle
* two equivalent eq filters
** one in `IN` form (`ename in ( 'Sebastian' )`)
** a regular `=` (`ename = 'Sebastian'`)
* an unrelated filter like `deptno < 100`
the optimizer should more-or-less start with the `RelFieldTrimmer`
the issue happens like:
* at parse time both literals are parsed as `CHAR(n)`
* the number of values in the `IN` is below `inSubqueryThreshold` - so it gets
converted to a set of `=` filters
** expression is converted to OR form
** during conversion
[SqlToRelConverter#ensureSqlType|#ensureSqlType]([https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1777-L1779])
is called
*** which skips the conversion for `CHAR` / `VARCHAR`
* the `=` filter goes thru the "regular" rex conversion - which involves
calling `rexBuilder#ensureType`
* the filter condition contains `ename = 'Sebastian'` twice; however the types
differ
* `RelFieldTrimmer` start a change cycle ; which induces the simplification of
the filter condition
* `RexSimplify` is executed with predicate elimination disabled (this will be
important)
* simplification compares the two literals with
[equals]([https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1685])
and returns `false`
workarounds:
* disable the conversion by setting `inSubqueryThreshold` to `1`
* run a rule which executes `RexSimplify` with predicate elimination enabled
earlier than the trimmer (ex: `ReduceExpressionsRule`)
** I think this bug remained hidden because this might happen easily
testcase for `RelOptRulesTest`
{code:java}
@Test void testIncorrectInType() {
final String sql = "select ename from emp "
+ " where ename in ( 'Sebastian' ) and ename = 'Sebastian' and deptno
< 100";
sql(sql)
.withTrim(true)
.withRule()
.checkUnchanged();
}
{code}
results in plan
{code:java}
LogicalProject(ENAME=[$0])
LogicalValues(tuples=[[]])
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
