Details
-
Bug
-
Status: Open
-
Minor
-
Resolution: Unresolved
-
2.1.0
-
None
-
None
-
Spark 2.4.5
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