[ https://issues.apache.org/jira/browse/CALCITE-6435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17865022#comment-17865022 ]
Zoltan Haindrich commented on CALCITE-6435: ------------------------------------------- merged into master with [73846cceb1841a3c7f7ab5cbe3c40611db07c148](https://github.com/apache/calcite/commit/73846cceb1841a3c7f7ab5cbe3c40611db07c148) Thank you Mihai and [~caicancai] for reviewing the changes! > 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 > Priority: Major > Labels: pull-request-available > > 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|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* starts 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=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)