Simhadri Govindappa created HIVE-27754:
------------------------------------------
Summary: 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
{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)
