Vladimir Sitnikov created CALCITE-7850:
------------------------------------------
Summary: 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
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)
