-- source ../include/have_columnstore.inc

--disable_warnings
DROP DATABASE IF EXISTS mcol641_update_db;
--enable_warnings

CREATE DATABASE mcol641_update_db;
USE mcol641_update_db;

--disable_warnings
DROP PROCEDURE IF EXISTS signedinsertproc;
DROP PROCEDURE IF EXISTS signeddeleteproc;
DROP PROCEDURE IF EXISTS unsignedinsertproc;
DROP PROCEDURE IF EXISTS unsigneddeleteproc;
--enable_warnings

DELIMITER //;

CREATE PROCEDURE signedinsertproc ()
 BEGIN
  INSERT INTO cs1 VALUES (125, 125);
  INSERT INTO cs1 VALUES (-125, -125);
  INSERT INTO cs1 values (99999999999999999999999999999999999998, 9999999999999999998);
  INSERT INTO cs1 values (99999999999999999999999999999999999999, 9999999999999999999);
  INSERT INTO cs1 values (-99999999999999999999999999999999999998, -9999999999999999998);
  INSERT INTO cs1 values (-99999999999999999999999999999999999999, -9999999999999999999);
  INSERT INTO cs1 VALUES (0, 0);
  INSERT INTO cs1 VALUES (NULL, NULL);
  
  INSERT INTO cs2 VALUES (1.25, 1.25);
  INSERT INTO cs2 VALUES (-1.25, -1.25);
  INSERT INTO cs2 values (9999999999999999999999999999.9999999998, 999999999.9999999998);
  INSERT INTO cs2 values (9999999999999999999999999999.9999999999, 999999999.9999999999);
  INSERT INTO cs2 values (-9999999999999999999999999999.9999999998, -999999999.9999999998);
  INSERT INTO cs2 values (-9999999999999999999999999999.9999999999, -999999999.9999999999);
  INSERT INTO cs2 VALUES (1.1234567891, 1.1234567891);
  INSERT INTO cs2 VALUES (1.9999999999, 1.9999999999);
  INSERT INTO cs2 VALUES (-1.1234567891, -1.1234567891);
  INSERT INTO cs2 VALUES (-1.9999999999, -1.9999999999);
  INSERT INTO cs2 VALUES (1.0000000009, 1.0000000009);
  INSERT INTO cs2 VALUES (-1.0000000009, -1.0000000009);
  INSERT INTO cs2 VALUES (0, 0);
  INSERT INTO cs2 VALUES (NULL, NULL);
  
  INSERT INTO cs3 VALUES (0.125, 0.125);
  INSERT INTO cs3 VALUES (-0.125, -0.125);
  INSERT INTO cs3 values (0.99999999999999999999999999999999999998, 0.9999999999999999998);
  INSERT INTO cs3 values (0.99999999999999999999999999999999999999, 0.9999999999999999999);
  INSERT INTO cs3 values (-0.99999999999999999999999999999999999998, -0.9999999999999999998);
  INSERT INTO cs3 values (-0.99999999999999999999999999999999999999, -0.9999999999999999999);
  INSERT INTO cs3 VALUES (0.12345678901234567890123456789012345678, 0.1234567890123456789);
  INSERT INTO cs3 VALUES (0.19999999999999999999999999999999999999, 0.1999999999999999999);
  INSERT INTO cs3 VALUES (-0.12345678901234567890123456789012345678, -0.1234567890123456789);
  INSERT INTO cs3 VALUES (-0.19999999999999999999999999999999999999, -0.1999999999999999999);
  INSERT INTO cs3 VALUES (0.00000000000000000000000000000000000009, 0.0000000000000000009);
  INSERT INTO cs3 VALUES (-0.00000000000000000000000000000000000009, -0.0000000000000000009);
  INSERT INTO cs3 VALUES (0, 0);
  INSERT INTO cs3 VALUES (NULL, NULL);
 END//

CREATE PROCEDURE unsignedinsertproc ()
 BEGIN
  INSERT INTO cs4 VALUES (125, 125);
  INSERT INTO cs4 values (99999999999999999999999999999999999998, 9999999999999999998);
  INSERT INTO cs4 values (99999999999999999999999999999999999999, 9999999999999999999);
  INSERT INTO cs4 VALUES (0, 0);
  INSERT INTO cs4 VALUES (NULL, NULL);

  INSERT INTO cs5 VALUES (1.25, 1.25);
  INSERT INTO cs5 values (9999999999999999999999999999.9999999998, 999999999.9999999998);
  INSERT INTO cs5 values (9999999999999999999999999999.9999999999, 999999999.9999999999);
  INSERT INTO cs5 VALUES (1.1234567891, 1.1234567891);
  INSERT INTO cs5 VALUES (1.9999999999, 1.9999999999);
  INSERT INTO cs5 VALUES (1.0000000009, 1.0000000009);
  INSERT INTO cs5 VALUES (0, 0);
  INSERT INTO cs5 VALUES (NULL, NULL);

  INSERT INTO cs6 VALUES (0.125, 0.125);
  INSERT INTO cs6 values (0.99999999999999999999999999999999999998, 0.9999999999999999998);
  INSERT INTO cs6 values (0.99999999999999999999999999999999999999, 0.9999999999999999999);
  INSERT INTO cs6 VALUES (0.12345678901234567890123456789012345678, 0.1234567890123456789);
  INSERT INTO cs6 VALUES (0.19999999999999999999999999999999999999, 0.1999999999999999999);
  INSERT INTO cs6 VALUES (0.00000000000000000000000000000000000009, 0.0000000000000000009);
  INSERT INTO cs6 VALUES (0, 0);
  INSERT INTO cs6 VALUES (NULL, NULL);
 END//

CREATE PROCEDURE signeddeleteproc ()
 BEGIN
  DELETE FROM cs1;
  DELETE FROM cs2;
  DELETE FROM cs3;
 END//

CREATE PROCEDURE unsigneddeleteproc ()
 BEGIN
  DELETE FROM cs4;
  DELETE FROM cs5;
  DELETE FROM cs6;
 END//

DELIMITER ;//

--disable_warnings
DROP TABLE IF EXISTS cs1;
DROP TABLE IF EXISTS cs2;
DROP TABLE IF EXISTS cs3;
DROP TABLE IF EXISTS cs4;
DROP TABLE IF EXISTS cs5;
DROP TABLE IF EXISTS cs6;
--enable_warnings

CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(19)) ENGINE=columnstore;
CREATE TABLE cs2 (d1 DECIMAL(38,10), d2 DECIMAL(19,10)) ENGINE=columnstore;
CREATE TABLE cs3 (d1 DECIMAL(38,38), d2 DECIMAL(19,19)) ENGINE=columnstore;
CREATE TABLE cs4 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(19) UNSIGNED) ENGINE=columnstore;
CREATE TABLE cs5 (d1 DECIMAL(38,10) UNSIGNED, d2 DECIMAL(19,10) UNSIGNED) ENGINE=columnstore;
CREATE TABLE cs6 (d1 DECIMAL(38,38) UNSIGNED, d2 DECIMAL(19,19) UNSIGNED) ENGINE=columnstore;

CALL signedinsertproc();

# Updates with constant values without a WHERE clause
UPDATE cs1 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789;
UPDATE cs2 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789;
UPDATE cs3 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789;
SELECT "signedtest1", d1, d2 FROM cs1;
SELECT "signedtest1", d1, d2 FROM cs2;
SELECT "signedtest1", d1, d2 FROM cs3;

# Updates with WHERE clause
# Update a single row with a constant value in WHERE clause
CALL signeddeleteproc();
CALL signedinsertproc();
UPDATE cs1 SET d1=12345678901234567890123456789012345678, d2=0 WHERE d1=-99999999999999999999999999999999999998;
UPDATE cs1 SET d1=0, d2=123456789012345678 WHERE d2=9999999999999999999;
UPDATE cs2 SET d1=1234567890123456789012345678.9012345678, d2=0 WHERE d1=-9999999999999999999999999999.9999999998;
UPDATE cs2 SET d1=0, d2=123456789.012345678 WHERE d2=999999999.9999999999;
UPDATE cs3 SET d1=0.12345678901234567890123456789012345678, d2=0 WHERE d1=-0.99999999999999999999999999999999999998;
UPDATE cs3 SET d1=0, d2=0.123456789012345678 WHERE d2=0.9999999999999999999;
SELECT "signedtest2", d1, d2 FROM cs1;
SELECT "signedtest2", d1, d2 FROM cs2;
SELECT "signedtest2", d1, d2 FROM cs3;

# Update multiple rows
# Updates with multiple constant values in WHERE clause
CALL signeddeleteproc();
CALL signedinsertproc();
UPDATE cs1 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789 WHERE d1 in (-99999999999999999999999999999999999998, 99999999999999999999999999999999999999);
UPDATE cs2 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789 WHERE d1 in (-9999999999999999999999999999.9999999998, 9999999999999999999999999999.9999999999);
UPDATE cs3 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789 WHERE d1 in (-0.99999999999999999999999999999999999998, 0.99999999999999999999999999999999999999);
SELECT "signedtest3", d1, d2 FROM cs1;
SELECT "signedtest3", d1, d2 FROM cs2;
SELECT "signedtest3", d1, d2 FROM cs3;

# Updates with comparison operators in WHERE clause
CALL signeddeleteproc();
CALL signedinsertproc();
UPDATE cs1 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789 WHERE d1 < -99999999999999999999999999999999999998 OR d2 > 9999999999999999998;
UPDATE cs2 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789 WHERE d1 < -9999999999999999999999999999.9999999998 OR d2 > 999999999.9999999998;
UPDATE cs3 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789 WHERE d1 < -0.99999999999999999999999999999999999998 OR d2 > 0.9999999999999999998;
SELECT "signedtest4a", d1, d2 FROM cs1;
SELECT "signedtest4a", d1, d2 FROM cs2;
SELECT "signedtest4a", d1, d2 FROM cs3;
CALL signeddeleteproc();
CALL signedinsertproc();
UPDATE cs1 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789 WHERE d1 <= -99999999999999999999999999999999999998 OR d2 >= 9999999999999999998;
UPDATE cs2 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789 WHERE d1 <= -9999999999999999999999999999.9999999998 OR d2 >= 999999999.9999999998;
UPDATE cs3 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789 WHERE d1 <= -0.99999999999999999999999999999999999998 OR d2 >= 0.9999999999999999998;
SELECT "signedtest4b", d1, d2 FROM cs1;
SELECT "signedtest4b", d1, d2 FROM cs2;
SELECT "signedtest4b", d1, d2 FROM cs3;

# Update with NULL in the WHERE clause
CALL signeddeleteproc();
CALL signedinsertproc();
UPDATE cs1 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789 WHERE d1 is NULL;
UPDATE cs2 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789 WHERE d1 is NULL;
UPDATE cs3 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789 WHERE d1 is NULL;
SELECT "signedtest5", d1, d2 FROM cs1;
SELECT "signedtest5", d1, d2 FROM cs2;
SELECT "signedtest5", d1, d2 FROM cs3;

# Update with != operator
CALL signeddeleteproc();
CALL signedinsertproc();
UPDATE cs1 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789 WHERE d1 != 125;
UPDATE cs2 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789 WHERE d1 != 1.25;
UPDATE cs3 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789 WHERE d1 != 0.125;
SELECT "signedtest6", d1, d2 FROM cs1;
SELECT "signedtest6", d1, d2 FROM cs2;
SELECT "signedtest6", d1, d2 FROM cs3;

# Update with non-constant value in the WHERE clause
CALL signeddeleteproc();
CALL signedinsertproc();
UPDATE cs1 SET d2=d1 WHERE d1 = d2;
UPDATE cs2 SET d2=d1 WHERE d1 = d2;
UPDATE cs3 SET d2=d1 WHERE d1 = d2;
SELECT "signedtest7", d1, d2 FROM cs1;
SELECT "signedtest7", d1, d2 FROM cs2;
SELECT "signedtest7", d1, d2 FROM cs3;
CALL signeddeleteproc();
CALL signedinsertproc();
UPDATE cs1 SET d1=d2 WHERE d1 != d2;
UPDATE cs2 SET d1=d2 WHERE d1 != d2;
UPDATE cs3 SET d1=d2 WHERE d1 != d2;
SELECT "signedtest8", d1, d2 FROM cs1;
SELECT "signedtest8", d1, d2 FROM cs2;
SELECT "signedtest8", d1, d2 FROM cs3;
CALL signeddeleteproc();
CALL signedinsertproc();
UPDATE cs1 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789 WHERE d1 < d2;
UPDATE cs2 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789 WHERE d1 < d2;
UPDATE cs3 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789 WHERE d1 < d2;
SELECT "signedtest9", d1, d2 FROM cs1;
SELECT "signedtest9", d1, d2 FROM cs2;
SELECT "signedtest9", d1, d2 FROM cs3;
CALL signeddeleteproc();
CALL signedinsertproc();
UPDATE cs1 SET d1=d2 WHERE d1 > d2;
UPDATE cs2 SET d1=d2 WHERE d1 > d2;
UPDATE cs3 SET d1=d2 WHERE d1 > d2;
SELECT "signedtest10", d1, d2 FROM cs1;
SELECT "signedtest10", d1, d2 FROM cs2;
SELECT "signedtest10", d1, d2 FROM cs3;

# Updates with functions and expressions in the WHERE clause


# Repeat above tests for the unsigned case
CALL unsigneddeleteproc();
CALL unsignedinsertproc();

# Updates with constant values without a WHERE clause
UPDATE cs4 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789;
UPDATE cs5 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789;
UPDATE cs6 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789;
SELECT "unsignedtest1", d1, d2 FROM cs4;
SELECT "unsignedtest1", d1, d2 FROM cs5;
SELECT "unsignedtest1", d1, d2 FROM cs6;

#
# Updates with WHERE clause
#
# Update a single row with a constant value in WHERE clause
CALL unsigneddeleteproc();
CALL unsignedinsertproc();
UPDATE cs4 SET d1=12345678901234567890123456789012345678, d2=0 WHERE d1=99999999999999999999999999999999999998;
UPDATE cs4 SET d1=0, d2=123456789012345678 WHERE d2=9999999999999999999;
UPDATE cs5 SET d1=1234567890123456789012345678.9012345678, d2=0 WHERE d1=9999999999999999999999999999.9999999998;
UPDATE cs5 SET d1=0, d2=123456789.012345678 WHERE d2=999999999.9999999999;
UPDATE cs6 SET d1=0.12345678901234567890123456789012345678, d2=0 WHERE d1=0.99999999999999999999999999999999999998;
UPDATE cs6 SET d1=0, d2=0.123456789012345678 WHERE d2=0.9999999999999999999;
SELECT "unsignedtest2", d1, d2 FROM cs4;
SELECT "unsignedtest2", d1, d2 FROM cs5;
SELECT "unsignedtest2", d1, d2 FROM cs6;

# Update multiple rows
# Updates with multiple constant values in WHERE clause
CALL unsigneddeleteproc();
CALL unsignedinsertproc();
UPDATE cs4 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789 WHERE d1 in (99999999999999999999999999999999999998, 99999999999999999999999999999999999999);
UPDATE cs5 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789 WHERE d1 in (9999999999999999999999999999.9999999998, 9999999999999999999999999999.9999999999);
UPDATE cs6 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789 WHERE d1 in (0.99999999999999999999999999999999999998, 0.99999999999999999999999999999999999999);
SELECT "unsignedtest3", d1, d2 FROM cs4;
SELECT "unsignedtest3", d1, d2 FROM cs5;
SELECT "unsignedtest3", d1, d2 FROM cs6;

#
# Updates with comparison operators in WHERE clause
#
CALL unsigneddeleteproc();
CALL unsignedinsertproc();
UPDATE cs4 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789 WHERE d1 > 99999999999999999999999999999999999998 OR d2 > 9999999999999999998;
UPDATE cs5 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789 WHERE d1 > 9999999999999999999999999999.9999999998 OR d2 > 999999999.9999999998;
UPDATE cs6 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789 WHERE d1 > 0.99999999999999999999999999999999999998 OR d2 > 0.9999999999999999998;
SELECT "unsignedtest4a", d1, d2 FROM cs4;
SELECT "unsignedtest4a", d1, d2 FROM cs5;
SELECT "unsignedtest4a", d1, d2 FROM cs6;
CALL unsigneddeleteproc();
CALL unsignedinsertproc();
UPDATE cs4 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789 WHERE d1 >= 99999999999999999999999999999999999998 OR d2 >= 9999999999999999998;
UPDATE cs5 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789 WHERE d1 >= 9999999999999999999999999999.9999999998 OR d2 >= 999999999.9999999998;
UPDATE cs6 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789 WHERE d1 >= 0.99999999999999999999999999999999999998 OR d2 >= 0.9999999999999999998;
SELECT "unsignedtest4b", d1, d2 FROM cs4;
SELECT "unsignedtest4b", d1, d2 FROM cs5;
SELECT "unsignedtest4b", d1, d2 FROM cs6;

# Update with NULL in the WHERE clause
CALL unsigneddeleteproc();
CALL unsignedinsertproc();
UPDATE cs4 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789 WHERE d1 is NULL;
UPDATE cs5 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789 WHERE d1 is NULL;
UPDATE cs6 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789 WHERE d1 is NULL;
SELECT "unsignedtest5", d1, d2 FROM cs4;
SELECT "unsignedtest5", d1, d2 FROM cs5;
SELECT "unsignedtest5", d1, d2 FROM cs6;

# Update with != operator
CALL unsigneddeleteproc();
CALL unsignedinsertproc();
UPDATE cs4 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789 WHERE d1 != 125;
UPDATE cs5 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789 WHERE d1 != 1.25;
UPDATE cs6 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789 WHERE d1 != 0.125;
SELECT "unsignedtest6", d1, d2 FROM cs4;
SELECT "unsignedtest6", d1, d2 FROM cs5;
SELECT "unsignedtest6", d1, d2 FROM cs6;

# Update with non-constant value in the WHERE clause
CALL unsigneddeleteproc();
CALL unsignedinsertproc();
UPDATE cs4 SET d2=d1 WHERE d1 = d2;
UPDATE cs5 SET d2=d1 WHERE d1 = d2;
UPDATE cs6 SET d2=d1 WHERE d1 = d2;
SELECT "unsignedtest7", d1, d2 FROM cs4;
SELECT "unsignedtest7", d1, d2 FROM cs5;
SELECT "unsignedtest7", d1, d2 FROM cs6;
CALL unsigneddeleteproc();
CALL unsignedinsertproc();
UPDATE cs4 SET d1=d2 WHERE d1 != d2;
UPDATE cs5 SET d1=d2 WHERE d1 != d2;
UPDATE cs6 SET d1=d2 WHERE d1 != d2;
SELECT "unsignedtest8", d1, d2 FROM cs4;
SELECT "unsignedtest8", d1, d2 FROM cs5;
SELECT "unsignedtest8", d1, d2 FROM cs6;
CALL unsigneddeleteproc();
CALL unsignedinsertproc();
UPDATE cs4 SET d1=12345678901234567890123456789012345678, d2=1234567890123456789 WHERE d1 < d2;
UPDATE cs5 SET d1=1234567890123456789012345678.9012345678, d2=123456789.0123456789 WHERE d1 < d2;
UPDATE cs6 SET d1=0.12345678901234567890123456789012345678, d2=0.1234567890123456789 WHERE d1 < d2;
SELECT "unsignedtest9", d1, d2 FROM cs4;
SELECT "unsignedtest9", d1, d2 FROM cs5;
SELECT "unsignedtest9", d1, d2 FROM cs6;
CALL unsigneddeleteproc();
CALL unsignedinsertproc();
UPDATE cs4 SET d1=d2 WHERE d1 > d2;
UPDATE cs5 SET d1=d2 WHERE d1 > d2;
UPDATE cs6 SET d1=d2 WHERE d1 > d2;
SELECT "unsignedtest10a", d1, d2 FROM cs4;
SELECT "unsignedtest10a", d1, d2 FROM cs5;
SELECT "unsignedtest10a", d1, d2 FROM cs6;

# Following test fails due to MCOL-3892
# Uncomment the test when the issue is fixed.
#CALL unsigneddeleteproc();
#CALL unsignedinsertproc();
#UPDATE cs4 SET d2=d1 WHERE d1 < d2;
#UPDATE cs5 SET d2=d1 WHERE d1 < d2;
#UPDATE cs6 SET d2=d1 WHERE d1 < d2;
#SELECT "unsignedtest10b", d1, d2 FROM cs4;
#SELECT "unsignedtest10b", d1, d2 FROM cs5;
#SELECT "unsignedtest10b", d1, d2 FROM cs6;

# Updates with functions and expressions in the WHERE clause

# Clean UP
--disable_warnings
DROP PROCEDURE IF EXISTS signedinsertproc;
DROP PROCEDURE IF EXISTS signeddeleteproc;
DROP PROCEDURE IF EXISTS unsignedinsertproc;
DROP PROCEDURE IF EXISTS unsigneddeleteproc;
DROP DATABASE mcol641_update_db;
--enable_warnings
#