Bettle created FLINK-40673:
------------------------------
Summary: INTERSECT ALL and EXCEPT ALL fail with mixed INT/BIGINT
inputs
Key: FLINK-40673
URL: https://issues.apache.org/jira/browse/FLINK-40673
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 2.4.0
Reporter: Bettle
Following the discussion in FLINK-40420, which has now been merged, I am
opening this ticket to track the mixed INT/BIGINT execution failures of
intersect_all and minus_all, as requested by the maintainer.
The issue was reproduced through direct Table API calls on baseline
e01bbcacd96, without importing or using DataFrame.
h3. Reproduction
Run the following in a configured PyFlink environment:
{code:python}
from py4j.protocol import Py4JJavaError
from pyflink.table import EnvironmentSettings, TableEnvironment
t_env = TableEnvironment.create(EnvironmentSettings.in_batch_mode())
t_env.get_config().set("parallelism.default", "1")
left = t_env.sql_query(
"SELECT * FROM (VALUES (1), (2), (2), (3)) AS T(id)"
)
right = t_env.sql_query(
"SELECT * FROM (VALUES (CAST(2 AS BIGINT)), "
"(CAST(2147483648 AS BIGINT))) AS T(other_id)"
)
for method in ("intersect_all", "minus_all"):
try:
result = getattr(left, method)(right)
print(method, result.get_resolved_schema())
with result.execute().collect() as rows:
print(method, sorted(row[0] for row in rows))
except Py4JJavaError as error:
cause = error.java_exception
while cause.getCause() is not None:
cause = cause.getCause()
print(method, "FAILED:", cause.toString())
{code}
h3. Expected and actual results
The compatible INT/BIGINT inputs should be coerced to a common type and execute
successfully, preserving ALL semantics.
||Operation||Expected values, sorted||Actual behavior||
|intersect_all|[2]|Execution fails|
|minus_all|[1, 2, 3]|Execution fails|
Both operations resolve a BIGINT result schema but fail during execution with:
{code}
java.lang.UnsupportedOperationException: Type mismatch in input
UnionTransformation{...}
{code}
h3. Workaround and controls
Explicitly casting the INT input to BIGINT makes both operations succeed. For
example, replace the left input with:
{code:python}
left = t_env.sql_query(
"SELECT CAST(id AS BIGINT) AS id "
"FROM (VALUES (1), (2), (2), (3)) AS T(id)"
)
{code}
Same-type INT/INT inputs also pass. Mixed-type union and union_all pass with
the original inputs.
h3. Comparison with existing work
On 2026-09-13, I applied PR #28533 (FLINK-39991) and PR #28522 (FLINK-39985)
separately to baseline e01bbcacd96, rebuilt each Planner, and ran the same 18
direct Table API execution cases for each version.
Results below refer to mixed INT/BIGINT inputs:
||Tested version||intersect||minus||intersect_all||minus_all||
|Baseline, no patches|FAIL|FAIL|FAIL|FAIL|
|Baseline + PR #28533|PASS|PASS|FAIL|FAIL|
|Baseline + PR #28522|FAIL|PASS|FAIL|FAIL|
The tested PR #28533 patch replaces both DISTINCT rules, explaining why both
intersect and minus pass with that patch. Neither tested patch resolves the ALL
failures. Same-type and explicit-cast control cases pass in all three versions.
Tested patch snapshots:
* [PR #28533|https://github.com/apache/flink/pull/28533]:
25cd57d694a4830f5b1294e82fe0d982e86c647e
* [PR #28522|https://github.com/apache/flink/pull/28522]:
4048d6f83d12203fbe09e14b5933c1d241b7f80e
h3. Validation scope
These were targeted batch-mode execution tests on Flink 2.4-SNAPSHOT, baseline
e01bbcacd96, with Calcite 1.41.0, Java 17.0.14 and Python 3.12.11. Only
formatting adjustments to the Scala test files were made when applying the
patches.
The results describe the tested baseline and patch snapshots, not a fresh
validation of the latest master. Released versions and the full Planner
regression suite were not tested.
h3. Related issues
FLINK-40420, FLINK-39991 and FLINK-39985.
This issue is independent of the DataFrame wrappers merged in FLINK-40420.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
