[jira] [Created] (PHOENIX-1431) DELETE using Subqueries

["Fenny Thomas (JIRA)" <ji...@apache.org>]

Fenny Thomas created PHOENIX-1431:
-------------------------------------

             Summary: 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: Blocker
             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_CITY FROM CUSTOMER WHERE CUSTOMER_CITY = 'SEATTLE' ) => Deletes all the rows

EXPLAIN DELETE FROM SALES WHERE CUSTOMER_NAME IN (SELECT CUSTOMER_CITY 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)