[jira] [Updated] (CARBONDATA-4134) MERGE INTO SQL Command is successful with negative scenarios

[Akash R Nilugal (Jira)]

     [ https://issues.apache.org/jira/browse/CARBONDATA-4134?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

PURUJIT CHAUGULE updated CARBONDATA-4134:
-----------------------------------------
    Description:
*STEPS:-*

DROP TABLE IF EXISTS A;
 DROP TABLE IF EXISTS B;
 CREATE TABLE IF NOT EXISTS A(id Int,price Int, state String) STORED AS carbondata;
 CREATE TABLE IF NOT EXISTS B(id Int, price Int,state String ) STORED AS carbondata;
 INSERT INTO A VALUES (1,100,"MA");
 INSERT INTO A VALUES (2,200,"NY");
 INSERT INTO A VALUES (3,300,"NH");
 INSERT INTO A VALUES (4,400,"FL");

INSERT INTO B VALUES (1,1,"MA (updated)");
 INSERT INTO B VALUES (2,3,"NY (updated)");
 INSERT INTO B VALUES (3,3,"CA (updated)");
 INSERT INTO B VALUES (5,5,"TX (updated)");
 INSERT INTO B VALUES (7,7,"LO (updated)");

*SCENARIO 1:*

*(Wrong Input for _ACTION_ for _MATCHED/NOT MATCHED_ is getting accepted and Command is executed successfully. Data Gets Updated in target table using data from source table.)*

0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN NOT MATCHED THEN;
 +---------+
|Result|

+---------+
 +---------+
 No rows selected 
 0: jdbc:hive2://linux1:22550/>
 0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN NOT MATCHED THEN X;
 +---------+
|Result|

+---------+
 +---------+
 No rows selected 
 0: jdbc:hive2://linux1:22550/>
 0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN MATCHED THEN a.b;
 +---------+
|Result|

+---------+
 +---------+
 No rows selected 
 0: jdbc:hive2://linux1:22550/>
 0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN MATCHED THEN 1;
 +---------+
|Result|

+---------+
 +---------+

No rows selected 

*SCENARIO 2:*

*(No validation for correct ACTION of each CLAUSE i.e. MATCHED as well as NOT_MATCHED.WHEN MATCHED should not accept any other Action than DELETE, UPDATE SET * and UPDATE SET column1 = value1 [, column2 = value2 ...]. Similarly WHEN NOT_MATCHED clause should only accept INSERT * | INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]).  )*

 

 *MATCHED Clause accepting _only_ UPDATE:*

*(Data gets deleted from target table based on source table)*

0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN MATCHED THEN UPDATE;
+---------+
| Result |
+---------+
+---------+
No rows selected

*SCENARIO 3:***

 *MATCHED Clause accepting INSERT :*

*(Data gets deleted from target table based on source table)***

0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN MATCHED THEN INSERT;
+---------+
| Result |
+---------+
+---------+

No rows selected

*SCENARIO 4:***

*NOT_MATCHED Clause accepting DELETE:*

*(Data not matching from source table get appended to target table)***

0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN NOT MATCHED THEN DELETE;
+---------+
| Result |
+---------+
+---------+
No rows selected

 

*SCENARIO 5:***

*NOT_MATCHED Clause accepting UPDATE:*

*(Data not matching from source table get appended to target table)***

0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN NOT MATCHED THEN UPDATE;
+---------+
| Result |
+---------+
+---------+
No rows selected

 

*SCENARIO 6:***

*NOT_MATCHED Clause accepting _only_ INSERT :***

0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN NOT MATCHED THEN INSERT;
+---------+
| Result |
+---------+
+---------+
No rows selected

 

*SCENARIO 7:*

*(Wrong input i.e. _NOT USING_ is getting accepted as _USING and_ MERGE INTO Command is successful. Changes are reflected in the target table.)*

0: jdbc:hive2://linux1:22550/> MERGE INTO A NOT USING B ON A.ID=B.ID WHEN MATCHED THEN DELETE;
 +---------+
|Result|

+---------+
 +---------+
 No rows selected

  was:
*STEPS:-*

DROP TABLE IF EXISTS A;
 DROP TABLE IF EXISTS B;
 CREATE TABLE IF NOT EXISTS A(id Int,price Int, state String) STORED AS carbondata;
 CREATE TABLE IF NOT EXISTS B(id Int, price Int,state String ) STORED AS carbondata;
 INSERT INTO A VALUES (1,100,"MA");
 INSERT INTO A VALUES (2,200,"NY");
 INSERT INTO A VALUES (3,300,"NH");
 INSERT INTO A VALUES (4,400,"FL");

INSERT INTO B VALUES (1,1,"MA (updated)");
 INSERT INTO B VALUES (2,3,"NY (updated)");
 INSERT INTO B VALUES (3,3,"CA (updated)");
 INSERT INTO B VALUES (5,5,"TX (updated)");
 INSERT INTO B VALUES (7,7,"LO (updated)");

*SCENARIO 1:*

*(Wrong Input for _ACTION_ for _MATCHED/NOT MATCHED_ is getting accepted and Command is executed successfully)*

0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN NOT MATCHED THEN;
 +---------+
|Result|

+---------+
 +---------+
 No rows selected 
 0: jdbc:hive2://linux1:22550/>
 0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN NOT MATCHED THEN X;
 +---------+
|Result|

+---------+
 +---------+
 No rows selected 
 0: jdbc:hive2://linux1:22550/>
 0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN MATCHED THEN a.b;
 +---------+
|Result|

+---------+
 +---------+
 No rows selected 
 0: jdbc:hive2://linux1:22550/>
 0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN MATCHED THEN 1;
 +---------+
|Result|

+---------+
 +---------+

No rows selected 

 

*SCENARIO 2:*

*(Wrong input i.e. _NOT USING_ is getting accepted as _USING and_ MERGE INTO Command is successful. Changes are reflected in the target table.)*

0: jdbc:hive2://linux1:22550/> MERGE INTO A NOT USING B ON A.ID=B.ID WHEN MATCHED THEN DELETE;
 +---------+
|Result|

+---------+
 +---------+
 No rows selected

> MERGE INTO SQL Command is successful with negative scenarios
> ------------------------------------------------------------
>
>                 Key: CARBONDATA-4134
>                 URL: https://issues.apache.org/jira/browse/CARBONDATA-4134
>             Project: CarbonData
>          Issue Type: Bug
>          Components: data-query
>    Affects Versions: 2.1.0
>         Environment: Spark 2.4.5
>            Reporter: PURUJIT CHAUGULE
>            Priority: Minor
>
> *STEPS:-*
> DROP TABLE IF EXISTS A;
>  DROP TABLE IF EXISTS B;
>  CREATE TABLE IF NOT EXISTS A(id Int,price Int, state String) STORED AS carbondata;
>  CREATE TABLE IF NOT EXISTS B(id Int, price Int,state String ) STORED AS carbondata;
>  INSERT INTO A VALUES (1,100,"MA");
>  INSERT INTO A VALUES (2,200,"NY");
>  INSERT INTO A VALUES (3,300,"NH");
>  INSERT INTO A VALUES (4,400,"FL");
> INSERT INTO B VALUES (1,1,"MA (updated)");
>  INSERT INTO B VALUES (2,3,"NY (updated)");
>  INSERT INTO B VALUES (3,3,"CA (updated)");
>  INSERT INTO B VALUES (5,5,"TX (updated)");
>  INSERT INTO B VALUES (7,7,"LO (updated)");
> *SCENARIO 1:*
> *(Wrong Input for _ACTION_ for _MATCHED/NOT MATCHED_ is getting accepted and Command is executed successfully. Data Gets Updated in target table using data from source table.)*
> 0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN NOT MATCHED THEN;
>  +---------+
> |Result|
> +---------+
>  +---------+
>  No rows selected 
>  0: jdbc:hive2://linux1:22550/>
>  0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN NOT MATCHED THEN X;
>  +---------+
> |Result|
> +---------+
>  +---------+
>  No rows selected 
>  0: jdbc:hive2://linux1:22550/>
>  0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN MATCHED THEN a.b;
>  +---------+
> |Result|
> +---------+
>  +---------+
>  No rows selected 
>  0: jdbc:hive2://linux1:22550/>
>  0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN MATCHED THEN 1;
>  +---------+
> |Result|
> +---------+
>  +---------+
> No rows selected 
> *SCENARIO 2:*
> *(No validation for correct ACTION of each CLAUSE i.e. MATCHED as well as NOT_MATCHED.WHEN MATCHED should not accept any other Action than DELETE, UPDATE SET * and UPDATE SET column1 = value1 [, column2 = value2 ...]. Similarly WHEN NOT_MATCHED clause should only accept INSERT * | INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]).  )*
>  
>  *MATCHED Clause accepting _only_ UPDATE:*
> *(Data gets deleted from target table based on source table)*
> 0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN MATCHED THEN UPDATE;
> +---------+
> | Result |
> +---------+
> +---------+
> No rows selected
> *SCENARIO 3:***
>  *MATCHED Clause accepting INSERT :*
> *(Data gets deleted from target table based on source table)***
> 0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN MATCHED THEN INSERT;
> +---------+
> | Result |
> +---------+
> +---------+
> No rows selected
> *SCENARIO 4:***
> *NOT_MATCHED Clause accepting DELETE:*
> *(Data not matching from source table get appended to target table)***
> 0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN NOT MATCHED THEN DELETE;
> +---------+
> | Result |
> +---------+
> +---------+
> No rows selected
>  
> *SCENARIO 5:***
> *NOT_MATCHED Clause accepting UPDATE:*
> *(Data not matching from source table get appended to target table)***
> 0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN NOT MATCHED THEN UPDATE;
> +---------+
> | Result |
> +---------+
> +---------+
> No rows selected
>  
> *SCENARIO 6:***
> *NOT_MATCHED Clause accepting _only_ INSERT :***
> 0: jdbc:hive2://linux1:22550/> MERGE INTO A USING B ON A.ID=B.ID WHEN NOT MATCHED THEN INSERT;
> +---------+
> | Result |
> +---------+
> +---------+
> No rows selected
>  
> *SCENARIO 7:*
> *(Wrong input i.e. _NOT USING_ is getting accepted as _USING and_ MERGE INTO Command is successful. Changes are reflected in the target table.)*
> 0: jdbc:hive2://linux1:22550/> MERGE INTO A NOT USING B ON A.ID=B.ID WHEN MATCHED THEN DELETE;
>  +---------+
> |Result|
> +---------+
>  +---------+
>  No rows selected

--
This message was sent by Atlassian Jira
(v8.3.4#803005)