Smruti Biswal created HIVE-29447:
------------------------------------
Summary: Calcite AssertionError during Hive CBO Optimization
Key: HIVE-29447
URL: https://issues.apache.org/jira/browse/HIVE-29447
Project: Hive
Issue Type: Improvement
Components: CBO
Affects Versions: 4.2.0
Reporter: Smruti Biswal
CBO incorrectly drops the NOT NULL constraint during the transformation into a
SEARCH argument.
The issue was present in older version. I suppose it started after upgrading
calcite to 1.33.0(HIVE-27102)
{{Repro:}}
{code:java}
CREATE TABLE main_table (
id_col STRING,
join_key STRING,
int_col_to_test INT,
misc_attr STRING
);{code}
{code:java}
CREATE TABLE lookup_table (
lookup_key STRING,
replacement_val INT
);{code}
{code:java}
SELECT
base.id_col,
base.int_col_to_test,
CASE
WHEN base.int_col_to_test IS NULL
OR base.int_col_to_test = ''
OR base.int_col_to_test = 0
THEN COALESCE(lkup.replacement_val, 0)
ELSE base.int_col_to_test
END AS final_calculated_column
FROM (
SELECT
m.*,
ROW_NUMBER() OVER (PARTITION BY id_col ORDER BY int_col_to_test) as rn
FROM main_table m
) base
LEFT JOIN lookup_table lkup ON base.join_key = lkup.lookup_key
WHERE base.rn = 1; {code}
{{{}ERROR:
Error running query: java.lang.AssertionError: Cannot add expression of
different type to set: set type is RecordType(VARCHAR(2147483647) CHARACTER SET
"UTF-16LE" id_col, VARCHAR(2147483647) CHARACTER SET "UTF-16LE" join_key,
INTEGER int_col_to_test, BOOLEAN NOT NULL EXPR$0) NOT NULL expression type is
RecordType(VARCHAR(2147483647) CHARACTER SET "UTF-16LE" id_col,
VARCHAR(2147483647) CHARACTER SET "UTF-16LE" join_key, INTEGER int_col_to_test,
BOOLEAN EXPR$0) NOT NULL set is
rel#9030:HiveProject.HIVE.[].any(input=HepRelVertex#9029,inputs=0..2,exprs=[SEARCH($2,
Sarg[0; NULL AS TRUE])],synthetic=false) expression is
HiveProject(id_col=[$0], join_key=[$1], int_col_to_test=[$2], EXPR$0=[OR(IS
NULL($2), =($2, 0))]) HiveFilter(condition=[=($3, 1)]) HiveProject(id_col=[$0],
join_key=[$1], int_col_to_test=[$2], ROW_NUMBER_window_0=[ROW_NUMBER() OVER
(PARTITION BY $0 ORDER BY $2 NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)]) HiveTableScan(table=[[default, main_table]],
table:alias=[m]) ; Query ID:
hive_20260207064924_d5cb3449-a1a6-4b88-ab36-b95bf07ff179
Workaround:
{}}}Change the filter condition and use COALESCE as follows.
{code:java}
CASE
WHEN COALESCE(base.int_col_to_test, '') = ''
OR base.int_col_to_test = 0
THEN COALESCE(lkup.replacement_val, 0)
ELSE base.int_col_to_test
END AS final_calculated_column {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
