Mihai Budiu created CALCITE-7228:
------------------------------------
Summary: Validator rejects legal ASOF JOIN program
Key: CALCITE-7228
URL: https://issues.apache.org/jira/browse/CALCITE-7228
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.40.0
Reporter: Mihai Budiu
Assignee: Mihai Budiu
The following legal program involving ASOF JOIN is rejected by the validator:
{code:sql}
CREATE TABLE source_stream_a (
common_join_attribute SMALLINT,
join_key_a2 VARCHAR,
join_key_a1 VARCHAR,
event_timestamp TIMESTAMP
);
CREATE TABLE reference_table_b (
join_key_b1 VARCHAR NOT NULL PRIMARY KEY,
common_join_attribute SMALLINT,
event_timestamp TIMESTAMP
);
CREATE TABLE source_stream_c (
join_key_b1 VARCHAR NOT NULL,
join_key_c2 VARCHAR NOT NULL,
common_join_attribute SMALLINT,
epoch_ts_c BIGINT LATENESS 10::BIGINT,
timestamp_c TIMESTAMP,
event_timestamp TIMESTAMP,
PRIMARY KEY (join_key_b1, join_key_c2)
);
SELECT t1.* FROM source_stream_a AS t1
LEFT JOIN reference_table_b AS t2
ON t1.common_join_attribute = t2.common_join_attribute AND
t1.join_key_a1 = t2.join_key_b1
LEFT ASOF JOIN source_stream_c AS t3
MATCH_CONDITION ( t1.event_timestamp >= t3.event_timestamp )
ON t1.common_join_attribute = t3.common_join_attribute AND
t1.join_key_a2 = t3.join_key_c2
AND t1.join_key_a1 = t3.join_key_b1;
{code}
The error reported is:
{code}
Compilation error: From line 31, column 4 to line 31, column 54: ASOF JOIN
condition must be a conjunction of equality comparisons
31|ON t1.common_join_attribute = t3.common_join_attribute AND t1.join_key_a2
= t3.join_key_c2
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
{code}
I wrote the broken code; it's the CompareFromBothSides visitor, so it's up to
me to fix it. The bug is in the code which checks the well-formedness of the
ASOF JOIN condition; it doesn't work if an input is another JOIN.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
