[
https://issues.apache.org/jira/browse/HIVE-27754?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17771151#comment-17771151
]
Simhadri Govindappa commented on HIVE-27754:
--------------------------------------------
{quote}
{noformat}
set hive.cbo.fallback.strategy=NEVER;
{noformat}
Can be used to prevent running these statements.
see also:
[https://github.com/apache/hive/blob/2dbfbeefc1a73d6a50f1c829658846fc827fc780/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java#L687-L688]
{quote}
Here is a qfile which can repro the issue even with
'hive.cbo.fallback.strategy=NEVER;'
[https://github.com/simhadri-g/hive/commit/9520fff464c9d1bf400e5e8f43b5f00bf9615825]
{quote}If the expression in the where clause has logical operators ({{{}OR{}}},
{{{}AND{}}}, ...) the operands are implicitly casted to boolean
[https://github.com/apache/hive/blob/85f6162becb8723ff6c9f85875048ced6ca7ae89/ql/src/java/org/apache/hadoop/hive/ql/parse/type/TypeCheckProcFactory.java#L842-L847]
{quote}
Will debug through this.
Thanks!
> Query Filter with OR condition updates every record in the table
> ----------------------------------------------------------------
>
> Key: HIVE-27754
> URL: https://issues.apache.org/jira/browse/HIVE-27754
> Project: Hive
> Issue Type: Bug
> Reporter: Simhadri Govindappa
> Assignee: Simhadri Govindappa
> Priority: Major
>
>
> {noformat}
> UPDATE customers_man SET customer_id=22 WHERE last_name='Pierce' OR 'Taylor'
> ;{noformat}
> After the above statement, all the records are updated. The condition
> {{'Taylor'}} is a constant string, and it will always evaluate to true
> because it's a non-empty string. So, effectively, {{UPDATE}} statement is
> updating all rows in the {{customers_man.}}
> {{}}
> {{Repro: }}
> {noformat}
> create table customers_man (customer_id bigint, first_name string)
> PARTITIONED BY (last_name string) STORED AS orc TBLPROPERTIES
> ('transactional'='true');
> insert into customers_man values(1, "Joanna", "Pierce"),(1, "Sharon",
> "Taylor"), (2, "Joanna", "Silver"), (2, "Bob", "Silver"), (2, "Susan",
> "Morrison") ,(2, "Jake", "Donnel") , (3, "Blake", "Burr"), (3, "Trudy",
> "Johnson"), (3, "Trudy", "Henderson");
> select * from customers_man;
>
> +----------------------------+---------------------------+--------------------------+
> | customers_man.customer_id | customers_man.first_name |
> customers_man.last_name |
>
> +----------------------------+---------------------------+--------------------------+
> | 3 | Blake | Burr
> |
> | 2 | Jake | Donnel
> |
> | 3 | Trudy | Henderson
> |
> | 3 | Trudy | Johnson
> |
> | 2 | Susan | Morrison
> |
> | 1 | Joanna | Pierce
> |
> | 2 | Joanna | Silver
> |
> | 2 | Bob | Silver
> |
> | 1 | Sharon | Taylor
> |
>
> +----------------------------+---------------------------+--------------------------+
> UPDATE customers_man SET customer_id=22 WHERE last_name='Pierce' OR
> last_name='Taylor' ;
> select * from customers_man;
>
> +----------------------------+---------------------------+--------------------------+
> | customers_man.customer_id | customers_man.first_name |
> customers_man.last_name |
>
> +----------------------------+---------------------------+--------------------------+
> | 3 | Blake | Burr
> |
> | 2 | Jake | Donnel
> |
> | 3 | Trudy | Henderson
> |
> | 3 | Trudy | Johnson
> |
> | 2 | Susan | Morrison
> |
> | 22 | Joanna | Pierce
> |
> | 2 | Joanna | Silver
> |
> | 2 | Bob | Silver
> |
> | 22 | Sharon | Taylor
> |
>
> +----------------------------+---------------------------+--------------------------+
> UPDATE customers_man SET customer_id=22 WHERE last_name='Pierce' OR
> 'Taylor' ;
> select * from customers_man;
>
> +----------------------------+---------------------------+--------------------------+
> | customers_man.customer_id | customers_man.first_name |
> customers_man.last_name |
>
> +----------------------------+---------------------------+--------------------------+
> | 22 | Blake | Burr
> |
> | 22 | Jake | Donnel
> |
> | 22 | Trudy | Henderson
> |
> | 22 | Trudy | Johnson
> |
> | 22 | Susan | Morrison
> |
> | 22 | Joanna | Pierce
> |
> | 22 | Joanna | Silver
> |
> | 22 | Bob | Silver
> |
> | 22 | Sharon | Taylor
> |
>
> +----------------------------+---------------------------+--------------------------+
> --- simpler repro
> UPDATE customers_man SET customer_id=23 WHERE true;
> select * from customers_man;
> +----------------------------+---------------------------+--------------------------+
> | customers_man.customer_id | customers_man.first_name |
> customers_man.last_name |
> +----------------------------+---------------------------+--------------------------+
> | 23 | Blake | Burr
> |
> | 23 | Jake | Donnel
> |
> | 23 | Trudy | Henderson
> |
> | 23 | Trudy | Johnson
> |
> | 23 | Susan | Morrison
> |
> | 23 | Joanna | Pierce
> |
> | 23 | Joanna | Silver
> |
> | 23 | Bob | Silver
> |
> | 23 | Sharon | Taylor
> |
> +----------------------------+---------------------------+--------------------------+{noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
