[
https://issues.apache.org/jira/browse/CALCITE-7850?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18126365#comment-18126365
]
Julian Hyde commented on CALCITE-7850:
--------------------------------------
It would be nice if RexSimplify made this simplification, but it’s not a bug
that it doesn’t. I would only accept a fix that improves the general case (e.g.
not just arrays, not just equals) and doesn’t have a quadratic running time (as
a simplistic fix for this case would).
> RexSimplify does not simplify "a = CAST(ARRAY[...]) AND a = CAST(ARRAY[...])"
> to FALSE when the arrays differ
> -------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7850
> URL: https://issues.apache.org/jira/browse/CALCITE-7850
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.42.0
> Reporter: Vladimir Sitnikov
> Priority: Major
>
> A filter on a {{VARCHAR ARRAY}} column that compares the column with two
> different array constructors is not simplified to {{FALSE}}:
> {code:sql}
> select *
> from (values (cast(array['a', 'b'] as varchar array))) as t(u)
> where u = array['/flink.jpg', '/flink_1.jpg']
> and u = array['/f.jpg', '/f_1.jpg']
> {code}
> After {{CoreRules.FILTER_REDUCE_EXPRESSIONS}} the plan keeps both conjuncts:
> {noformat}
> LogicalProject(U=[$0])
> LogicalFilter(condition=[AND(=($0, CAST(ARRAY('/flink.jpg ',
> '/flink_1.jpg')):VARCHAR NOT NULL ARRAY NOT NULL), =($0, CAST(ARRAY('/f.jpg
> ', '/f_1.jpg')):VARCHAR NOT NULL ARRAY NOT NULL))])
> LogicalProject(U=[CAST(ARRAY('a', 'b')):VARCHAR NOT NULL ARRAY NOT NULL])
> LogicalValues(tuples=[[{ 0 }]])
> {noformat}
> Each array constructor is cast to the column type, {{VARCHAR NOT NULL ARRAY
> NOT NULL}}. The trailing spaces come from the {{CHAR(12)}} and {{CHAR(8)}}
> element types of the constructors; the reproducer below uses unpadded
> literals and fails the same way. This is the shape from FLINK-32188, which
> CALCITE-5733 was filed for (see [this
> comment|https://issues.apache.org/jira/browse/CALCITE-5733?focusedCommentId=17727467#comment-17727467]).
> When the array constructor already has the column type, no cast is added and
> the filter is simplified: for an {{INTEGER ARRAY}} column, {{where u =
> array[1,2] and u = array[2,3]}} becomes {{LogicalValues(tuples=[[]])}}, and
> {{RexProgramTest.testSimplifyArrayEquality}} covers {{a = ARRAY[1,2] AND a =
> ARRAY[2,3]}} since CALCITE-5733.
> *Expected:* the SQL above plans to {{LogicalValues(tuples=[[]])}}, and
> {{RexSimplify}} in unknown-as-false mode simplifies the cast condition in the
> test below to {{false}}, as it does for the uncast {{VARCHAR ARRAY}}
> constructors in the same test.
> Add this test to {{RexProgramTest}}; it uses that class's helpers. On
> 843f468405 (1.43.0-SNAPSHOT) the first assertion passes and the second fails:
> {code:java}
> @Test void testSimplifyCastArrayEquality() {
> final RelDataType varcharArray = tArray(tVarchar());
> final RexNode u = input(varcharArray, 0);
> // Control: constructors of the column type, no cast
> final RexNode b1 =
> rexBuilder.makeCall(varcharArray,
> SqlStdOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
> ImmutableList.of(literal("/flink.jpg"), literal("/flink_1.jpg")));
> final RexNode b2 =
> rexBuilder.makeCall(varcharArray,
> SqlStdOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
> ImmutableList.of(literal("/f.jpg"), literal("/f_1.jpg")));
> checkSimplifyFilter(and(eq(u, b1), eq(u, b2)), "false");
> // Fails: constructors cast to the column type
> final RexNode a1 =
> rexBuilder.makeCast(varcharArray,
> rexBuilder.makeCall(SqlStdOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
> literal("/flink.jpg"), literal("/flink_1.jpg")));
> final RexNode a2 =
> rexBuilder.makeCast(varcharArray,
> rexBuilder.makeCall(SqlStdOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
> literal("/f.jpg"), literal("/f_1.jpg")));
> checkSimplifyFilter(and(eq(u, a1), eq(u, a2)), "false");
> }
> {code}
> {noformat}
> java.lang.AssertionError: simplify(unknown as FALSE): AND(=($0,
> CAST(ARRAY('/flink.jpg', '/flink_1.jpg')):VARCHAR NOT NULL ARRAY NOT NULL),
> =($0, CAST(ARRAY('/f.jpg', '/f_1.jpg')):VARCHAR NOT NULL ARRAY NOT NULL))
> Expected: with toString() is "false"
> but: toString() was "AND(=($0, CAST(ARRAY('/flink.jpg',
> '/flink_1.jpg')):VARCHAR NOT NULL ARRAY NOT NULL), =($0, CAST(ARRAY('/f.jpg',
> '/f_1.jpg')):VARCHAR NOT NULL ARRAY NOT NULL))"
> {noformat}
> *Possible cause (a guess; the report above stands without it):*
> {{RexSimplify.isConstant}}, added in CALCITE-5733, accepts a literal or an
> {{ARRAY}} or {{MULTISET}} constructor of constants, and rejects a {{CAST}}
> around one. Accepting the cast is not enough by itself: a cast can map
> different constructors to equal values, for example {{CAST(ARRAY[1.1] AS
> INTEGER ARRAY)}} and {{CAST(ARRAY[1.2] AS INTEGER ARRAY)}} both evaluate to
> {{ARRAY[1]}}, so the two constants have to be compared after the cast, not as
> expressions.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
