Uploaded image for project: 'CarbonData'
  1. CarbonData
  2. CARBONDATA-4134

MERGE INTO SQL Command is successful with negative scenarios

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.1.0
    • Fix Version/s: None
    • Component/s: data-query
    • Labels:
      None
    • Environment:
      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

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              PURUJIT PURUJIT CHAUGULE
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: