[
https://issues.apache.org/jira/browse/PHOENIX-1431?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Anoop Sam John updated PHOENIX-1431:
------------------------------------
Comment: was deleted
(was: I got like the bug is it is ignoring the condition on select.
On a related item, so here we retrieve all the RKs back to client and issue
Deletes for RKs to server.
We have BulkDeleteEP in HBase side to deal with this way of bulk deletes. JFYI.)
> 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
> Priority: Critical
> Fix For: 4.2.1
>
>
> 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)
