[
https://issues.apache.org/jira/browse/HIVE-26320?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17554115#comment-17554115
]
Chiran Ravani commented on HIVE-26320:
--------------------------------------
[~zabetak] Apologies, I forgot to mention that explain appears to be same for
ORC vs Parquet table. Sharing the explain extended and results for query
reported in description with table type parquet vs orc.
*Parquet*
{code:java}
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| OPTIMIZED SQL: SELECT CAST(CASE WHEN ROW(`kob`, `enhanced_type_code`) IN
(ROW('BB', 18), ROW('BC', 18)) THEN 1 ELSE 0 END AS INTEGER) AS `logic_check` |
| FROM `ascend_lite`.`case_test_parquet` |
| STAGE DEPENDENCIES: |
| Stage-0 is a root stage |
| |
| STAGE PLANS: |
| Stage: Stage-0 |
| Fetch Operator |
| limit: -1 |
| Processor Tree: |
| TableScan |
| alias: case_test_parquet |
| GatherStats: false |
| Select Operator |
| expressions: CASE WHEN ((struct(kob,enhanced_type_code)) IN
(const struct('BB',18), const struct('BC',18))) THEN (1) ELSE (0) END (type:
int) |
| outputColumnNames: _col0 |
| ListSink |
| |
+----------------------------------------------------+
{code}
Results
{code:java}
INFO : Compiling
command(queryId=hive_20220614134848_0c726af4-d554-4cbf-b3be-0573cbf39125):
select case when (
(kob='BB' and enhanced_type_code='18')
or (kob='BC' and enhanced_type_code='18')
)
then 1
else 0
end as logic_check
from case_test_parquet
INFO : Semantic Analysis Completed (retrial = false)
INFO : Created Hive schema: Schema(fieldSchemas:[FieldSchema(name:logic_check,
type:int, comment:null)], properties:null)
INFO : Completed compiling
command(queryId=hive_20220614134848_0c726af4-d554-4cbf-b3be-0573cbf39125); Time
taken: 0.15 seconds
INFO : Executing
command(queryId=hive_20220614134848_0c726af4-d554-4cbf-b3be-0573cbf39125):
select case when (
(kob='BB' and enhanced_type_code='18')
or (kob='BC' and enhanced_type_code='18')
)
then 1
else 0
end as logic_check
from case_test_parquet
INFO : Completed executing
command(queryId=hive_20220614134848_0c726af4-d554-4cbf-b3be-0573cbf39125); Time
taken: 0.006 seconds
INFO : OK
+--------------+
| logic_check |
+--------------+
| 0 |
| 0 |
| 0 |
+--------------+
3 rows selected (0.56 seconds)
{code}
*ORC*
{code:java}
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| OPTIMIZED SQL: SELECT CAST(CASE WHEN ROW(`kob`, `enhanced_type_code`) IN
(ROW('BB', 18), ROW('BC', 18)) THEN 1 ELSE 0 END AS INTEGER) AS `logic_check` |
| FROM `ascend_lite`.`case_test_orc` |
| STAGE DEPENDENCIES: |
| Stage-0 is a root stage |
| |
| STAGE PLANS: |
| Stage: Stage-0 |
| Fetch Operator |
| limit: -1 |
| Processor Tree: |
| TableScan |
| alias: case_test_orc |
| GatherStats: false |
| Select Operator |
| expressions: CASE WHEN ((struct(kob,enhanced_type_code)) IN
(const struct('BB',18), const struct('BC',18))) THEN (1) ELSE (0) END (type:
int) |
| outputColumnNames: _col0 |
| ListSink |
| |
+----------------------------------------------------+
{code}
Results:
{code:java}
INFO : Compiling
command(queryId=hive_20220614134807_2ae11d4a-34bd-4c68-a5c0-b36cc0bbe350):
select case when (
(kob='BB' and enhanced_type_code='18')
or (kob='BC' and enhanced_type_code='18')
)
then 1
else 0
end as logic_check
from case_test_orc
INFO : Semantic Analysis Completed (retrial = false)
INFO : Created Hive schema: Schema(fieldSchemas:[FieldSchema(name:logic_check,
type:int, comment:null)], properties:null)
INFO : Completed compiling
command(queryId=hive_20220614134807_2ae11d4a-34bd-4c68-a5c0-b36cc0bbe350); Time
taken: 0.161 seconds
INFO : Executing
command(queryId=hive_20220614134807_2ae11d4a-34bd-4c68-a5c0-b36cc0bbe350):
select case when (
(kob='BB' and enhanced_type_code='18')
or (kob='BC' and enhanced_type_code='18')
)
then 1
else 0
end as logic_check
from case_test_orc
INFO : Completed executing
command(queryId=hive_20220614134807_2ae11d4a-34bd-4c68-a5c0-b36cc0bbe350); Time
taken: 0.006 seconds
INFO : OK
+--------------+
| logic_check |
+--------------+
| 1 |
| 1 |
| 0 |
+--------------+
{code}
> Incorrect case evaluation for Parquet based table
> -------------------------------------------------
>
> Key: HIVE-26320
> URL: https://issues.apache.org/jira/browse/HIVE-26320
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2, Query Planning
> Affects Versions: 4.0.0-alpha-1
> Reporter: Chiran Ravani
> Priority: Major
>
> Query involving case statement with two or more conditions leads to incorrect
> result for tables with parquet format, The problem is not observed with ORC
> or TextFile.
> *Steps to reproduce*:
> {code:java}
> create external table case_test_parquet(kob varchar(2),enhanced_type_code
> int) stored as parquet;
> insert into case_test_parquet values('BB',18),('BC',18),('AB',18);
> select case when (
> (kob='BB' and enhanced_type_code='18')
> or (kob='BC' and enhanced_type_code='18')
> )
> then 1
> else 0
> end as logic_check
> from case_test_parquet;
> {code}
> Result:
> {code}
> 0
> 0
> 0
> {code}
> Expected result:
> {code}
> 1
> 1
> 0
> {code}
> The problem does not appear when setting hive.optimize.point.lookup=false.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
