Konstantin Bereznyakov created HIVE-29084:
---------------------------------------------
Summary: CBO returns incorrect results when WHERE clause filters
multiple LATERAL VIEW explode cross-products
Key: HIVE-29084
URL: https://issues.apache.org/jira/browse/HIVE-29084
Project: Hive
Issue Type: Bug
Components: CBO
Affects Versions: 4.0.1, 4.2.0
Environment: - Affected Versions: Hive 4.0.1, 4.2.0-SNAPSHOT (likely
all CBO+lateral view versions)
- Execution Engine: MapReduce (local mode)
- Metastore: Derby embedded
Reporter: Konstantin Bereznyakov
Assignee: Konstantin Bereznyakov
Attachments: hive_cbo_lateral_view_where_bug.log,
hive_cbo_lateral_view_where_bug.sql
When CBO is enabled, queries with multiple LATERAL VIEW explode operations and
WHERE clause filtering return incorrect results. The WHERE clause selectivity
is not properly applied during optimization, causing the query to return rows
that should be filtered out.
I can reliably reproduce the bug with 2+ occurrences of "LATERAL VIEW
explode()" *and* a WHERE clause; it does not seem to happen with only one
"LATERAL VIEW explode()" or without a WHERE clause.
Originally reproduced with published 4.0.1 container, then verified with
locally built current master branch.
I was *not* able to reproduce it with unit tests.
{{*Minimal Reproduction Case:*}}
{{ }}
{{ DROP TABLE IF EXISTS cbo_lateral_view_where_bug;}}
{{ CREATE TABLE cbo_lateral_view_where_bug (values array<string>);}}
{{ INSERT INTO cbo_lateral_view_where_bug SELECT array('a', 'b');}}
{{ ANALYZE TABLE cbo_lateral_view_where_bug COMPUTE STATISTICS;}}{{ ** }}
{{ *-- Correct result (CBO OFF)*}}
{{ SET hive.cbo.enable=false;}}
{{ SELECT first_val, second_val}}
{{ FROM cbo_lateral_view_where_bug}}
{{ LATERAL VIEW explode(values) lv1 AS first_val}}
{{ LATERAL VIEW explode(values) lv2 AS second_val}}
{{ WHERE first_val != second_val;}}{{ *-- Incorrect result (CBO ON)*}}
{{ SET hive.cbo.enable=true;}}
{{ SELECT first_val, second_val}}
{{ FROM cbo_lateral_view_where_bug}}
{{ LATERAL VIEW explode(values) lv1 AS first_val}}
{{ LATERAL VIEW explode(values) lv2 AS second_val}}
{{ WHERE first_val != second_val;}}{{ ** }}
{{ *–- Expected vs Actual Results:*}}
{{ - CBO OFF (correct): 2 rows: ('a','b') and ('b','a')}}
{{ - CBO ON (incorrect): 4 rows: ('a','b'), ('b','a'), ('a','a'), ('b','b')}}
*Root Cause:*
CBO fails to properly estimate the selectivity of WHERE clauses that filter
cross-products created by multiple lateral views on the same array. The
optimizer appears to ignore or miscalculate the filtering effect, allowing rows
that should be excluded.
*Impact:*
- Data Correctness: Queries return wrong results, not just performance issues
- Scope: Requires 2+ LATERAL VIEW explode operations with WHERE filtering
- Severity: Silent data corruption - users may not notice incorrect results
{*}Workaround{*}:
Set {*}hive.cbo.enable{*}=false for queries with multiple lateral view
cross-products and WHERE clause filtering.
Attachments:
- [^hive_cbo_lateral_view_where_bug.sql] - SQL to reproduce
- Hive log: [^hive_cbo_lateral_view_where_bug.log]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
