You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Konstantin Bereznyakov (Jira)" <ji...@apache.org> on 2025/07/15 19:13:00 UTC
[jira] [Comment Edited] (HIVE-29084) CBO returns incorrect results when WHERE clause filters multiple LATERAL VIEW explode cross-products
[ https://issues.apache.org/jira/browse/HIVE-29084?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18007313#comment-18007313 ]
Konstantin Bereznyakov edited comment on HIVE-29084 at 7/15/25 7:12 PM:
------------------------------------------------------------------------
EXPLAIN EXTENDED with MR, CBO ON:
{code:java}
SET hive.cbo.enable=true;
No rows affected (0.016 seconds)
0: jdbc:hive2://localhost:10000> SET hive.cbo.enable;
+-----------------------+
| set |
+-----------------------+
| hive.cbo.enable=true |
+-----------------------+
1 row selected (0.01 seconds)
0: jdbc:hive2://localhost:10000> EXPLAIN EXTENDED 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_20250715190945_ae126f57-d8e6-4cac-86ef-e6429d6be0a6): EXPLAIN EXTENDED 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_20250715190945_ae126f57-d8e6-4cac-86ef-e6429d6be0a6); Time taken: 0.21 seconds
INFO : Concurrency mode is disabled, not creating a lock manager
INFO : Executing command(queryId=root_20250715190945_ae126f57-d8e6-4cac-86ef-e6429d6be0a6): EXPLAIN EXTENDED 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_20250715190945_ae126f57-d8e6-4cac-86ef-e6429d6be0a6); Time taken: 0.041 seconds
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| STAGE DEPENDENCIES: |
| Stage-0 is a root stage |
| |
| STAGE PLANS: |
| Stage: Stage-0 |
| Fetch Operator |
| limit: -1 |
| Processor Tree: |
| TableScan |
| alias: _dummy_table |
| Row Limit Per Split: 1 |
| GatherStats: false |
| Select Operator |
| expressions: array('a','b') (type: array<string>) |
| outputColumnNames: _col0 |
| Lateral View Forward |
| Select Operator |
| expressions: _col0 (type: array<string>) |
| outputColumnNames: _col0 |
| Lateral View Join Operator |
| outputColumnNames: _col0, _col1 |
| Lateral View Forward |
| Select Operator |
| expressions: _col1 (type: string) |
| outputColumnNames: _col1 |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| Select Operator |
| expressions: _col0 (type: array<string>) |
| outputColumnNames: _col0 |
| UDTF Operator |
| function name: explode |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| Select Operator |
| expressions: Const array<string> [a, b] (type: array<string>) |
| outputColumnNames: _col0 |
| UDTF Operator |
| function name: explode |
| Lateral View Join Operator |
| outputColumnNames: _col0, _col1 |
| Lateral View Forward |
| Select Operator |
| expressions: _col1 (type: string) |
| outputColumnNames: _col1 |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| Select Operator |
| expressions: _col0 (type: array<string>) |
| outputColumnNames: _col0 |
| UDTF Operator |
| function name: explode |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| |
+----------------------------------------------------+
71 rows selected (0.273 seconds) {code}
EXPLAIN EXTENDED with MR, CBO off
{code:java}
SET hive.cbo.enable=false;
No rows affected (0.006 seconds)
0: jdbc:hive2://localhost:10000> SET hive.cbo.enable;
+------------------------+
| set |
+------------------------+
| hive.cbo.enable=false |
+------------------------+
1 row selected (0.009 seconds)
0: jdbc:hive2://localhost:10000> EXPLAIN EXTENDED 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_20250715191149_61301196-bb0d-4706-94a5-b131a19691aa): EXPLAIN EXTENDED 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_20250715191149_61301196-bb0d-4706-94a5-b131a19691aa); Time taken: 0.1 seconds
INFO : Concurrency mode is disabled, not creating a lock manager
INFO : Executing command(queryId=root_20250715191149_61301196-bb0d-4706-94a5-b131a19691aa): EXPLAIN EXTENDED 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_20250715191149_61301196-bb0d-4706-94a5-b131a19691aa); Time taken: 0.035 seconds
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| STAGE DEPENDENCIES: |
| Stage-0 is a root stage |
| |
| STAGE PLANS: |
| Stage: Stage-0 |
| Fetch Operator |
| limit: -1 |
| Processor Tree: |
| TableScan |
| alias: _dummy_table |
| Row Limit Per Split: 1 |
| GatherStats: false |
| Select Operator |
| expressions: array('a','b') (type: array<string>) |
| outputColumnNames: _col0 |
| Lateral View Forward |
| Select Operator |
| expressions: _col0 (type: array<string>) |
| outputColumnNames: _col0 |
| Lateral View Join Operator |
| outputColumnNames: _col0, _col1 |
| Lateral View Forward |
| Select Operator |
| expressions: _col1 (type: string) |
| outputColumnNames: _col1 |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Filter Operator |
| isSamplingPred: false |
| predicate: (_col1 <> _col2) (type: boolean) |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| Select Operator |
| expressions: _col0 (type: array<string>) |
| outputColumnNames: _col0 |
| UDTF Operator |
| function name: explode |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Filter Operator |
| isSamplingPred: false |
| predicate: (_col1 <> _col2) (type: boolean) |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| Select Operator |
| expressions: Const array<string> [a, b] (type: array<string>) |
| outputColumnNames: _col0 |
| UDTF Operator |
| function name: explode |
| Lateral View Join Operator |
| outputColumnNames: _col0, _col1 |
| Lateral View Forward |
| Select Operator |
| expressions: _col1 (type: string) |
| outputColumnNames: _col1 |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Filter Operator |
| isSamplingPred: false |
| predicate: (_col1 <> _col2) (type: boolean) |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| Select Operator |
| expressions: _col0 (type: array<string>) |
| outputColumnNames: _col0 |
| UDTF Operator |
| function name: explode |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Filter Operator |
| isSamplingPred: false |
| predicate: (_col1 <> _col2) (type: boolean) |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| |
+----------------------------------------------------+
83 rows selected (0.149 seconds) {code}
was (Author: JIRAUSER308328):
EXPLAIN EXTENDED with CBO ON:
{code:java}
SET hive.cbo.enable=true;
No rows affected (0.016 seconds)
0: jdbc:hive2://localhost:10000> SET hive.cbo.enable;
+-----------------------+
| set |
+-----------------------+
| hive.cbo.enable=true |
+-----------------------+
1 row selected (0.01 seconds)
0: jdbc:hive2://localhost:10000> EXPLAIN EXTENDED 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_20250715190945_ae126f57-d8e6-4cac-86ef-e6429d6be0a6): EXPLAIN EXTENDED 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_20250715190945_ae126f57-d8e6-4cac-86ef-e6429d6be0a6); Time taken: 0.21 seconds
INFO : Concurrency mode is disabled, not creating a lock manager
INFO : Executing command(queryId=root_20250715190945_ae126f57-d8e6-4cac-86ef-e6429d6be0a6): EXPLAIN EXTENDED 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_20250715190945_ae126f57-d8e6-4cac-86ef-e6429d6be0a6); Time taken: 0.041 seconds
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| STAGE DEPENDENCIES: |
| Stage-0 is a root stage |
| |
| STAGE PLANS: |
| Stage: Stage-0 |
| Fetch Operator |
| limit: -1 |
| Processor Tree: |
| TableScan |
| alias: _dummy_table |
| Row Limit Per Split: 1 |
| GatherStats: false |
| Select Operator |
| expressions: array('a','b') (type: array<string>) |
| outputColumnNames: _col0 |
| Lateral View Forward |
| Select Operator |
| expressions: _col0 (type: array<string>) |
| outputColumnNames: _col0 |
| Lateral View Join Operator |
| outputColumnNames: _col0, _col1 |
| Lateral View Forward |
| Select Operator |
| expressions: _col1 (type: string) |
| outputColumnNames: _col1 |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| Select Operator |
| expressions: _col0 (type: array<string>) |
| outputColumnNames: _col0 |
| UDTF Operator |
| function name: explode |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| Select Operator |
| expressions: Const array<string> [a, b] (type: array<string>) |
| outputColumnNames: _col0 |
| UDTF Operator |
| function name: explode |
| Lateral View Join Operator |
| outputColumnNames: _col0, _col1 |
| Lateral View Forward |
| Select Operator |
| expressions: _col1 (type: string) |
| outputColumnNames: _col1 |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| Select Operator |
| expressions: _col0 (type: array<string>) |
| outputColumnNames: _col0 |
| UDTF Operator |
| function name: explode |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| |
+----------------------------------------------------+
71 rows selected (0.273 seconds) {code}
EXPLAIN EXTENDED with CBO off
{code:java}
SET hive.cbo.enable=false;
No rows affected (0.006 seconds)
0: jdbc:hive2://localhost:10000> SET hive.cbo.enable;
+------------------------+
| set |
+------------------------+
| hive.cbo.enable=false |
+------------------------+
1 row selected (0.009 seconds)
0: jdbc:hive2://localhost:10000> EXPLAIN EXTENDED 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_20250715191149_61301196-bb0d-4706-94a5-b131a19691aa): EXPLAIN EXTENDED 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_20250715191149_61301196-bb0d-4706-94a5-b131a19691aa); Time taken: 0.1 seconds
INFO : Concurrency mode is disabled, not creating a lock manager
INFO : Executing command(queryId=root_20250715191149_61301196-bb0d-4706-94a5-b131a19691aa): EXPLAIN EXTENDED 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_20250715191149_61301196-bb0d-4706-94a5-b131a19691aa); Time taken: 0.035 seconds
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| STAGE DEPENDENCIES: |
| Stage-0 is a root stage |
| |
| STAGE PLANS: |
| Stage: Stage-0 |
| Fetch Operator |
| limit: -1 |
| Processor Tree: |
| TableScan |
| alias: _dummy_table |
| Row Limit Per Split: 1 |
| GatherStats: false |
| Select Operator |
| expressions: array('a','b') (type: array<string>) |
| outputColumnNames: _col0 |
| Lateral View Forward |
| Select Operator |
| expressions: _col0 (type: array<string>) |
| outputColumnNames: _col0 |
| Lateral View Join Operator |
| outputColumnNames: _col0, _col1 |
| Lateral View Forward |
| Select Operator |
| expressions: _col1 (type: string) |
| outputColumnNames: _col1 |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Filter Operator |
| isSamplingPred: false |
| predicate: (_col1 <> _col2) (type: boolean) |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| Select Operator |
| expressions: _col0 (type: array<string>) |
| outputColumnNames: _col0 |
| UDTF Operator |
| function name: explode |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Filter Operator |
| isSamplingPred: false |
| predicate: (_col1 <> _col2) (type: boolean) |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| Select Operator |
| expressions: Const array<string> [a, b] (type: array<string>) |
| outputColumnNames: _col0 |
| UDTF Operator |
| function name: explode |
| Lateral View Join Operator |
| outputColumnNames: _col0, _col1 |
| Lateral View Forward |
| Select Operator |
| expressions: _col1 (type: string) |
| outputColumnNames: _col1 |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Filter Operator |
| isSamplingPred: false |
| predicate: (_col1 <> _col2) (type: boolean) |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| Select Operator |
| expressions: _col0 (type: array<string>) |
| outputColumnNames: _col0 |
| UDTF Operator |
| function name: explode |
| Lateral View Join Operator |
| outputColumnNames: _col1, _col2 |
| Filter Operator |
| isSamplingPred: false |
| predicate: (_col1 <> _col2) (type: boolean) |
| Select Operator |
| expressions: _col1 (type: string), _col2 (type: string) |
| outputColumnNames: _col0, _col1 |
| ListSink |
| |
+----------------------------------------------------+
83 rows selected (0.149 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_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)