[
https://issues.apache.org/jira/browse/PHOENIX-1431?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Fenny Thomas updated PHOENIX-1431:
----------------------------------
Description:
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 |
was:
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 |
> 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_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)
