[
https://issues.apache.org/jira/browse/HIVE-29084?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18007629#comment-18007629
]
Konstantin Bereznyakov commented on HIVE-29084:
-----------------------------------------------
Hi Stamatis, I apologize for overlooking "CBO" in your comment; I am still
learning Hive. Here is a sample output of "EXPLAIN CBO":
{code:java}
Connecting to jdbc:hive2://localhost:10000
Connected to: Apache Hive (version 4.2.0-SNAPSHOT)
Driver: Hive JDBC (version 4.2.0-SNAPSHOT)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 4.2.0-SNAPSHOT by Apache Hive
0: jdbc:hive2://localhost:10000> EXPLAIN CBO SELECT first_val, second_val
. . . . . . . . . . . . . . . .> FROM (SELECT array('a', 'b') as val_array)
inline_data
. . . . . . . . . . . . . . . .> LATERAL VIEW explode(val_array) lv1 AS
first_val
. . . . . . . . . . . . . . . .> LATERAL VIEW explode(val_array) lv2 AS
second_val
. . . . . . . . . . . . . . . .> WHERE first_val != second_val;
INFO : Compiling
command(queryId=root_20250716163641_81bf2a03-71e1-4a1d-b3e9-3919a3e6f12c):
EXPLAIN CBO SELECT first_val, second_val
FROM (SELECT array('a', 'b') as val_array) inline_data
LATERAL VIEW explode(val_array) lv1 AS first_val
LATERAL VIEW explode(val_array) lv2 AS second_val
WHERE first_val != second_val
INFO : Semantic Analysis Completed (retrial = false)
INFO : Created Hive schema: Schema(fieldSchemas:[FieldSchema(name:Explain,
type:string, comment:null)], properties:null)
INFO : Completed compiling
command(queryId=root_20250716163641_81bf2a03-71e1-4a1d-b3e9-3919a3e6f12c); Time
taken: 1.976 seconds
INFO : Concurrency mode is disabled, not creating a lock manager
INFO : Executing
command(queryId=root_20250716163641_81bf2a03-71e1-4a1d-b3e9-3919a3e6f12c):
EXPLAIN CBO SELECT first_val, second_val
FROM (SELECT array('a', 'b') as val_array) inline_data
LATERAL VIEW explode(val_array) lv1 AS first_val
LATERAL VIEW explode(val_array) lv2 AS second_val
WHERE first_val != second_val
INFO : Starting task [Stage-1:EXPLAIN] in serial mode
INFO : Completed executing
command(queryId=root_20250716163641_81bf2a03-71e1-4a1d-b3e9-3919a3e6f12c); Time
taken: 0.036 seconds
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| CBO PLAN: |
| HiveProject(first_val=[$1], second_val=[$2]) |
| HiveFilter(condition=[<>($1, $2)]) |
| HiveTableFunctionScan(invocation=[LATERAL(explode($0), $0, $1)],
rowType=[RecordType(VARCHAR(2147483647) ARRAY val_array, VARCHAR(2147483647)
lv1.first_val, VARCHAR(2147483647) lv2.second_val)]) |
| HiveProject(val_array=[$0], lv1.first_val=[$1]) |
| HiveTableFunctionScan(invocation=[LATERAL(explode($0), $0)],
rowType=[RecordType(VARCHAR(2147483647) ARRAY val_array, VARCHAR(2147483647)
lv1.first_val)]) |
| HiveProject(val_array=[ARRAY(_UTF-16LE'a':VARCHAR(2147483647)
CHARACTER SET "UTF-16LE", _UTF-16LE'b':VARCHAR(2147483647) CHARACTER SET
"UTF-16LE")]) |
| HiveTableScan(table=[[_dummy_database, _dummy_table]],
table:alias=[_dummy_table]) |
| |
+----------------------------------------------------+
9 rows selected (2.217 seconds) {code}
I thought I will also add an "EXPLAIN CBO" output for an unaffected query for
comparison:
{code:java}
EXPLAIN CBO SELECT first_val
. . . . . . . . . . . . . . . .> FROM (SELECT array('a', 'b') as val_array)
inline_data
. . . . . . . . . . . . . . . .> LATERAL VIEW explode(val_array) lv1 AS
first_val
. . . . . . . . . . . . . . . .> WHERE first_val != 'b';
INFO : Compiling
command(queryId=root_20250716164955_916bcfc7-7080-45a4-bdbc-b0c3d4921781):
EXPLAIN CBO SELECT first_val
FROM (SELECT array('a', 'b') as val_array) inline_data
LATERAL VIEW explode(val_array) lv1 AS first_val
WHERE first_val != 'b'
INFO : Semantic Analysis Completed (retrial = false)
INFO : Created Hive schema: Schema(fieldSchemas:[FieldSchema(name:Explain,
type:string, comment:null)], properties:null)
INFO : Completed compiling
command(queryId=root_20250716164955_916bcfc7-7080-45a4-bdbc-b0c3d4921781); Time
taken: 0.134 seconds
INFO : Concurrency mode is disabled, not creating a lock manager
INFO : Executing
command(queryId=root_20250716164955_916bcfc7-7080-45a4-bdbc-b0c3d4921781):
EXPLAIN CBO SELECT first_val
FROM (SELECT array('a', 'b') as val_array) inline_data
LATERAL VIEW explode(val_array) lv1 AS first_val
WHERE first_val != 'b'
INFO : Starting task [Stage-1:EXPLAIN] in serial mode
INFO : Completed executing
command(queryId=root_20250716164955_916bcfc7-7080-45a4-bdbc-b0c3d4921781); Time
taken: 0.03 seconds
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| CBO PLAN: |
| HiveProject(first_val=[$1]) |
| HiveFilter(condition=[<>($1, _UTF-16LE'b')]) |
| HiveTableFunctionScan(invocation=[LATERAL(explode($0), $0)],
rowType=[RecordType(VARCHAR(2147483647) ARRAY val_array, VARCHAR(2147483647)
lv1.first_val)]) |
| HiveProject(val_array=[ARRAY(_UTF-16LE'a':VARCHAR(2147483647) CHARACTER
SET "UTF-16LE", _UTF-16LE'b':VARCHAR(2147483647) CHARACTER SET "UTF-16LE")]) |
| HiveTableScan(table=[[_dummy_database, _dummy_table]],
table:alias=[_dummy_table]) |
| |
+----------------------------------------------------+
7 rows selected (0.185 seconds) {code}
> 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_29084.q, hive_29084.q.out,
> 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)
