[ https://issues.apache.org/jira/browse/PHOENIX-1431?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14226634#comment-14226634 ]
Hudson commented on PHOENIX-1431: --------------------------------- SUCCESS: Integrated in Phoenix-3.0-hadoop1 #324 (See [https://builds.apache.org/job/Phoenix-3.0-hadoop1/324/]) PHOENIX-1431 DELETE using Subqueries (maryannxue: rev 518cff04637f646c60d70cfd6c54e6fb5a9c9b57) * phoenix-core/src/main/java/org/apache/phoenix/compile/DeleteCompiler.java * phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java > DELETE using Subqueries > ----------------------- > > Key: PHOENIX-1431 > URL: https://issues.apache.org/jira/browse/PHOENIX-1431 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.2 > Environment: hdfs 2.5.0 > hbase-0.98.7 > phoenix 4.2.0 > Reporter: Fenny Thomas > Assignee: Maryann Xue > Priority: Critical > Fix For: 4.3, 3.3, 3.2.2, 4.2.2 > > Attachments: 1431.patch > > > DELETE's using a subquery deletes *all* the rows in the table igonoring any > condition set by the where clause, here's an example - > CREATE TABLE IF NOT EXISTS CUSTOMER ( > CUSTOMER_ID INTEGER NOT NULL, > CUSTOMER_NAME VARCHAR NOT NULL, > CUSTOMER_CITY VARCHAR > CONSTRAINT pk PRIMARY KEY (CUSTOMER_ID,CUSTOMER_NAME) > ); > CREATE TABLE IF NOT EXISTS SALES ( > SALE_ID INTEGER NOT NULL, > CUSTOMER_NAME VARCHAR NOT NULL, > SALE_AMOUNT INTEGER > CONSTRAINT pk PRIMARY KEY (SALE_ID,CUSTOMER_NAME) > ); > UPSERT INTO CUSTOMER VALUES(1,'MSFT','SEATTLE'); > UPSERT INTO CUSTOMER VALUES(2,'AMZN','SEATTLE'); > UPSERT INTO CUSTOMER VALUES(3,'APPL','CUPERTINO'); > UPSERT INTO CUSTOMER VALUES(4,'TSLA','PALO ALTO'); > UPSERT INTO SALES VALUES(1,'MSFT',1000); > UPSERT INTO SALES VALUES(2,'AMZN',1000); > UPSERT INTO SALES VALUES(3,'APPL',1000); > UPSERT INTO SALES VALUES(4,'TSLA',1000); > DELETE FROM SALES WHERE CUSTOMER_NAME IN (SELECT CUSTOMER_NAME FROM CUSTOMER > WHERE CUSTOMER_CITY = 'SEATTLE' ) => Deletes all the rows > EXPLAIN DELETE FROM SALES WHERE CUSTOMER_NAME IN (SELECT CUSTOMER_NAME FROM > CUSTOMER WHERE CUSTOMER_CITY = 'SEATTLE' ) > +------------+ > | PLAN | > +------------+ > | DELETE ROWS | > | CLIENT PARALLEL 1-WAY FULL SCAN OVER SALES | > | SERVER FILTER BY FIRST KEY ONLY | > EXPLAIN DELETE FROM SALES; > +------------+ > | PLAN | > +------------+ > | DELETE ROWS | > | CLIENT PARALLEL 1-WAY FULL SCAN OVER SALES | > | SERVER FILTER BY FIRST KEY ONLY | -- This message was sent by Atlassian JIRA (v6.3.4#6332)