[
https://issues.apache.org/jira/browse/HIVE-29084?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18007344#comment-18007344
]
Konstantin Bereznyakov commented on HIVE-29084:
-----------------------------------------------
[~zabetak] the explain results appear the same for MR or Tez
> 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)
