Hi Mihai,
Thank you so much for your response.
I had 2 issues , one around Booleans not resolving to an equi condition, the
other around arrays. It seems that Booleans are optimized to $9 or Not($9) for
true and false literals, so we do not see the equi. In the array case we have a
tree of rexliterals which cannot be instanceof’ed for the equi types.
I think this is working as designed. It is unfortunate that the Flink lookup
join is looking for an equality. These conditions should come through as a
filters. But I need to refactor the code to see these filters.
Kind regards, David.
From: Mihai Budiu <mbu...@gmail.com>
Date: Wednesday, 18 June 2025 at 18:42
To: dev <dev@calcite.apache.org>
Subject: [EXTERNAL] Re: nonEquiConditions set with a lookup keys of array
literals in a view.
It's not easy to debug a problem that spans repositories and uses an old
version of Calcite.
There were some issues fixed with equijoin conditions and casts in some of the
latest releases, e.g., https://github.com/apache/calcite/pull/4131 (but this PR
affects only NATURAL joins, which does not seem to be the case in your program).
I would expect that the cast expression itself is pulled in a projection before
the join, and you are left with a pure equijoin. I thought that the validator
will do that. Maybe you are missing an optimization step which can do that?
Mihai
________________________________
From: David Radley <david_rad...@uk.ibm.com>
Sent: Wednesday, June 18, 2025 1:22 AM
To: dev <dev@calcite.apache.org>
Subject: FW: nonEquiConditions set with a lookup keys of array literals in a
view.
Hi,
On the 6th time through this code [1].
((operands.get(0) instanceof RexInputRef) is false
((operands.get(0) is
CAST(ARRAY(_UTF-16LE'red':VARCHAR(5) CHARACTER SET "UTF-16LE",
_UTF-16LE'green':VARCHAR(5) CHARACTER SET "UTF-16LE")):VARCHAR(2147483647)
CHARACTER SET "UTF-16LE" ARRAY NOT NULL
Kind regards, David.
[1]
https://github.com/apache/calcite/blob/597b1fd54fe5b8586525aed2bc4518ca54a25523/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java#L1702
From: David Radley <david_rad...@uk.ibm.com>
Date: Tuesday, 17 June 2025 at 17:59
To: dev <dev@calcite.apache.org>
Subject: [EXTERNAL] FW: nonEquiConditions set with a lookup keys of array
literals in a view.
Hi,
Fyi the stack trace when it hits [2] is
splitJoinCondition:1698, RelOptUtil (org.apache.calcite.plan)
splitJoinCondition:1075, RelOptUtil (org.apache.calcite.plan)
of:65, JoinInfo (org.apache.calcite.rel.core)
<init>:105, Join (org.apache.calcite.rel.core)
<init>:106, LogicalJoin (org.apache.calcite.rel.logical)
copy:185, LogicalJoin (org.apache.calcite.rel.logical)
1 hidden frame
onMatch:388, ReduceExpressionsRule$JoinReduceExpressionsRule
(org.apache.calcite.rel.rules)
fireRule:337, AbstractRelOptPlanner (org.apache.calcite.plan)
applyRule:556, HepPlanner (org.apache.calcite.plan.hep)
applyRules:420, HepPlanner (org.apache.calcite.plan.hep)
executeRuleInstance:243, HepPlanner (org.apache.calcite.plan.hep)
execute:178, HepInstruction$RuleInstance$State (org.apache.calcite.plan.hep)
lambda$executeProgram$0:211, HepPlanner (org.apache.calcite.plan.hep)
2 hidden frames
executeProgram:210, HepPlanner (org.apache.calcite.plan.hep)
execute:118, HepProgram$State (org.apache.calcite.plan.hep)
executeProgram:205, HepPlanner (org.apache.calcite.plan.hep)
findBestExp:191, HepPlanner (org.apache.calcite.plan.hep)
41 hidden frames
From: David Radley <david_rad...@uk.ibm.com>
Date: Tuesday, 17 June 2025 at 17:26
To: dev <dev@calcite.apache.org>
Subject: [EXTERNAL] nonEquiConditions set with a lookup keys of array literals
in a view.
Hi,
I am going a Flink lookup join with view based on a Apache Flink Kafka table
and a GetInData Flink HTTP connector table. The lookup is successful for many
types of lookup keys. It is not successful for arrays of primitives or booleans
when a value it specified in a view.
For example
CREATE TEMPORARY VIEW orders_view AS
SELECT
*,
CAST(ARRAY['red','green'] AS ARRAY<STRING>) AS `stringArray`,
PROCTIME() AS `proc_time`
FROM
orders;
And a table for the http connector
CREATE TEMPORARY TABLE api_table_array
(
customerId STRING,
str1 STRING,
int1 INTEGER, arr1 array<string>
Then I issue a lookup join:
SELECT * FROM orders_view AS o JOIN api_table_array FOR SYSTEM_TIME AS OF
o.proc_time as a ON o.const_requestBody_stringArray = a.arr1;
This fails with
org.apache.flink.table.api.TableException: Temporal table join requires an
equality condition on fields of table
[default_catalog.default_database.api_table_array].
The FlinkFilterJoin rule issues issue a joinRel.analyzeCondition() [1] , this
ends up in Calcite code sets the joinInfo to have no leftKeys or rightKeys and
has a nonEquiConditions of:
=(CAST(ARRAY(_UTF-16LE'red':VARCHAR(5) CHARACTER SET "UTF-16LE",
_UTF-16LE'green':VARCHAR(5) CHARACTER SET "UTF-16LE")):VARCHAR(2147483647)
CHARACTER SET "UTF-16LE" ARRAY NOT NULL, $9)
It looks like because this code has identified a non equality condition, the
Flink lookup join cannot proceed as it needs an equals condition.
If I define the array as a column in a file or a Kafka table the join works. It
is only when I define the array literal in the view it fails. It also fails for
Boolean. All the other primitive types (Sting , int etc) work.
I am trying to find the code that causes this and why, as all the other types
work. We are hoping to make this work for Booleans and arrays. I am happy to
contribute a change with a pointer as to what might need changing.
I am recreating this with Flink 1.20.1 which is using Calcite 1.32. I see that
the non equality condition is set [2] , breakpointing on this line I see the
condition object has:
op=”=”
operands 0 is
CAST(ARRAY(_UTF-16LE'red':VARCHAR(5) CHARACTER SET "UTF-16LE",
_UTF-16LE'green':VARCHAR(5) CHARACTER SET "UTF-16LE")):VARCHAR(2147483647)
CHARACTER SET "UTF-16LE" ARRAY NOT NULL
Operands 1 ls
$9
In goes through this code a few times, in the failing case the debugger does
not resolve op0 or op1, and ends up at [2].
Any thoughts on why this is happening and how to resolve this would be greatly
appreciated. I am happy to code a resolution but would need pointers,
Kind regards, David.
[1]
https://github.com/apache/flink/blob/8616d6d811e73979328607db03028ae0220d8491/flink-table/flink-table-planner/src/main/java/org/apache/flink/table/planner/plan/rules/logical/FlinkFilterJoinRule.java#L349
[2]
https://github.com/apache/calcite/blob/597b1fd54fe5b8586525aed2bc4518ca54a25523/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java#L1709
Unless otherwise stated above:
IBM United Kingdom Limited
Registered in England and Wales with number 741598
Registered office: Building C, IBM Hursley Office, Hursley Park Road,
Winchester, Hampshire SO21 2JN
Unless otherwise stated above:
IBM United Kingdom Limited
Registered in England and Wales with number 741598
Registered office: Building C, IBM Hursley Office, Hursley Park Road,
Winchester, Hampshire SO21 2JN
Unless otherwise stated above:
IBM United Kingdom Limited
Registered in England and Wales with number 741598
Registered office: Building C, IBM Hursley Office, Hursley Park Road,
Winchester, Hampshire SO21 2JN
Unless otherwise stated above:
IBM United Kingdom Limited
Registered in England and Wales with number 741598
Registered office: Building C, IBM Hursley Office, Hursley Park Road,
Winchester, Hampshire SO21 2JN
