[
https://issues.apache.org/jira/browse/HIVE-29084?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18005518#comment-18005518
]
Stamatis Zampetakis commented on HIVE-29084:
--------------------------------------------
Thanks for reporting this [~konstantinb]!
Is the problem specific to the MR execution engine or it is also reproducible
with Tez? Note that MR is deprecated and the community is not actively
fixing/improving code around this area.
If the problem is due to CBO then most likely the query plan is incorrect. Can
you share the result of EXPLAIN CBO for the query that returns wrong results?
> 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.1.0, 4.0.1
> 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
> Priority: Major
> 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)
