[ 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)