mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-23 07:05:36 +03:00
339 lines
13 KiB
Plaintext
339 lines
13 KiB
Plaintext
-- source ../include/have_columnstore.inc
|
|
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcol641_delete_db;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE mcol641_delete_db;
|
|
USE mcol641_delete_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();
|
|
|
|
# Full table delete
|
|
DELETE FROM cs1;
|
|
DELETE FROM cs2;
|
|
DELETE FROM cs3;
|
|
SELECT "signedtest1", d1, d2 FROM cs1;
|
|
SELECT "signedtest1", d1, d2 FROM cs2;
|
|
SELECT "signedtest1", d1, d2 FROM cs3;
|
|
|
|
# Deletes with WHERE clause
|
|
# Delete a single row with a constant value in WHERE clause
|
|
CALL signedinsertproc();
|
|
DELETE FROM cs1 WHERE d1=-99999999999999999999999999999999999998;
|
|
DELETE FROM cs1 WHERE d2=9999999999999999999;
|
|
DELETE FROM cs2 WHERE d1=-9999999999999999999999999999.9999999998;
|
|
DELETE FROM cs2 WHERE d2=999999999.9999999999;
|
|
DELETE FROM cs3 WHERE d1=-0.99999999999999999999999999999999999998;
|
|
DELETE FROM cs3 WHERE d2=0.9999999999999999999;
|
|
SELECT "signedtest2", d1, d2 FROM cs1;
|
|
SELECT "signedtest2", d1, d2 FROM cs2;
|
|
SELECT "signedtest2", d1, d2 FROM cs3;
|
|
|
|
# Delete multiple rows
|
|
# Deletes with multiple constant values in WHERE clause
|
|
CALL signeddeleteproc();
|
|
CALL signedinsertproc();
|
|
DELETE FROM cs1 WHERE d1 in (-99999999999999999999999999999999999998, 99999999999999999999999999999999999999);
|
|
DELETE FROM cs2 WHERE d1 in (-9999999999999999999999999999.9999999998, 9999999999999999999999999999.9999999999);
|
|
DELETE FROM cs3 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;
|
|
|
|
# Deletes with comparison operators in WHERE clause
|
|
CALL signeddeleteproc();
|
|
CALL signedinsertproc();
|
|
DELETE FROM cs1 WHERE d1 <= -99999999999999999999999999999999999998 OR d2 >= 9999999999999999998;
|
|
DELETE FROM cs2 WHERE d1 <= -9999999999999999999999999999.9999999998 OR d2 >= 999999999.9999999998;
|
|
DELETE FROM cs3 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();
|
|
DELETE FROM cs1 WHERE d1 < -99999999999999999999999999999999999998 OR d2 > 9999999999999999998;
|
|
DELETE FROM cs2 WHERE d1 < -9999999999999999999999999999.9999999998 OR d2 > 999999999.9999999998;
|
|
DELETE FROM cs3 WHERE d1 < -0.99999999999999999999999999999999999998 OR d2 > 0.9999999999999999998;
|
|
SELECT "signedtest4", d1, d2 FROM cs1;
|
|
SELECT "signedtest4", d1, d2 FROM cs2;
|
|
SELECT "signedtest4", d1, d2 FROM cs3;
|
|
|
|
# Delete with NULL in the WHERE clause
|
|
DELETE FROM cs1 WHERE d1 is NULL;
|
|
DELETE FROM cs2 WHERE d1 is NULL;
|
|
DELETE FROM cs3 WHERE d1 is NULL;
|
|
SELECT "signedtest5", d1, d2 FROM cs1;
|
|
SELECT "signedtest5", d1, d2 FROM cs2;
|
|
SELECT "signedtest5", d1, d2 FROM cs3;
|
|
|
|
# Delete with != operator
|
|
DELETE FROM cs1 WHERE d1 != 125;
|
|
DELETE FROM cs2 WHERE d1 != 1.25;
|
|
DELETE FROM cs3 WHERE d1 != 0.125;
|
|
SELECT "signedtest6", d1, d2 FROM cs1;
|
|
SELECT "signedtest6", d1, d2 FROM cs2;
|
|
SELECT "signedtest6", d1, d2 FROM cs3;
|
|
|
|
# Delete with non-constant value in the WHERE clause
|
|
CALL signeddeleteproc();
|
|
CALL signedinsertproc();
|
|
DELETE FROM cs1 WHERE d1 = d2;
|
|
DELETE FROM cs2 WHERE d1 = d2;
|
|
DELETE FROM cs3 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();
|
|
DELETE FROM cs1 WHERE d1 != d2;
|
|
DELETE FROM cs2 WHERE d1 != d2;
|
|
DELETE FROM cs3 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();
|
|
DELETE FROM cs1 WHERE d1 < d2;
|
|
DELETE FROM cs2 WHERE d1 < d2;
|
|
DELETE FROM cs3 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();
|
|
DELETE FROM cs1 WHERE d1 > d2;
|
|
DELETE FROM cs2 WHERE d1 > d2;
|
|
DELETE FROM cs3 WHERE d1 > d2;
|
|
SELECT "signedtest10", d1, d2 FROM cs1;
|
|
SELECT "signedtest10", d1, d2 FROM cs2;
|
|
SELECT "signedtest10", d1, d2 FROM cs3;
|
|
|
|
# Deletes with functions and expressions in the WHERE clause
|
|
|
|
|
|
# Repeat above tests for the unsigned case
|
|
CALL unsignedinsertproc();
|
|
|
|
# Full table delete
|
|
DELETE FROM cs4;
|
|
DELETE FROM cs5;
|
|
DELETE FROM cs6;
|
|
SELECT "unsignedtest1", d1, d2 FROM cs4;
|
|
SELECT "unsignedtest1", d1, d2 FROM cs5;
|
|
SELECT "unsignedtest1", d1, d2 FROM cs6;
|
|
|
|
# Deletes with WHERE clause
|
|
# Delete a single row with a constant value in WHERE clause
|
|
CALL unsignedinsertproc();
|
|
DELETE FROM cs4 WHERE d1=99999999999999999999999999999999999998;
|
|
DELETE FROM cs4 WHERE d2=9999999999999999999;
|
|
DELETE FROM cs5 WHERE d1=9999999999999999999999999999.9999999998;
|
|
DELETE FROM cs5 WHERE d2=999999999.9999999999;
|
|
DELETE FROM cs6 WHERE d1=0.99999999999999999999999999999999999998;
|
|
DELETE FROM cs6 WHERE d2=0.9999999999999999999;
|
|
SELECT "unsignedtest2", d1, d2 FROM cs4;
|
|
SELECT "unsignedtest2", d1, d2 FROM cs5;
|
|
SELECT "unsignedtest2", d1, d2 FROM cs6;
|
|
|
|
# Delete multiple rows
|
|
# Deletes with multiple constant values in WHERE clause
|
|
CALL unsigneddeleteproc();
|
|
CALL unsignedinsertproc();
|
|
DELETE FROM cs4 WHERE d1 in (99999999999999999999999999999999999998, 99999999999999999999999999999999999999);
|
|
DELETE FROM cs5 WHERE d1 in (9999999999999999999999999999.9999999998, 9999999999999999999999999999.9999999999);
|
|
DELETE FROM cs6 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;
|
|
|
|
# Deletes with comparison operators in WHERE clause
|
|
CALL unsigneddeleteproc();
|
|
CALL unsignedinsertproc();
|
|
DELETE FROM cs4 WHERE d1 >= 99999999999999999999999999999999999998 OR d2 >= 9999999999999999998;
|
|
DELETE FROM cs5 WHERE d1 >= 9999999999999999999999999999.9999999998 OR d2 >= 999999999.9999999998;
|
|
DELETE FROM cs6 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();
|
|
DELETE FROM cs4 WHERE d1 > 99999999999999999999999999999999999998 OR d2 > 9999999999999999998;
|
|
DELETE FROM cs5 WHERE d1 > 9999999999999999999999999999.9999999998 OR d2 > 999999999.9999999998;
|
|
DELETE FROM cs6 WHERE d1 > 0.99999999999999999999999999999999999998 OR d2 > 0.9999999999999999998;
|
|
SELECT "unsignedtest4", d1, d2 FROM cs4;
|
|
SELECT "unsignedtest4", d1, d2 FROM cs5;
|
|
SELECT "unsignedtest4", d1, d2 FROM cs6;
|
|
|
|
# Delete with NULL in the WHERE clause
|
|
DELETE FROM cs4 WHERE d1 is NULL;
|
|
DELETE FROM cs5 WHERE d1 is NULL;
|
|
DELETE FROM cs6 WHERE d1 is NULL;
|
|
SELECT "unsignedtest5", d1, d2 FROM cs4;
|
|
SELECT "unsignedtest5", d1, d2 FROM cs5;
|
|
SELECT "unsignedtest5", d1, d2 FROM cs6;
|
|
|
|
# Delete with != operator
|
|
DELETE FROM cs4 WHERE d1 != 125;
|
|
DELETE FROM cs5 WHERE d1 != 1.25;
|
|
DELETE FROM cs6 WHERE d1 != 0.125;
|
|
SELECT "unsignedtest6", d1, d2 FROM cs4;
|
|
SELECT "unsignedtest6", d1, d2 FROM cs5;
|
|
SELECT "unsignedtest6", d1, d2 FROM cs6;
|
|
|
|
# Delete with non-constant value in the WHERE clause
|
|
CALL unsigneddeleteproc();
|
|
CALL unsignedinsertproc();
|
|
DELETE FROM cs4 WHERE d1 = d2;
|
|
DELETE FROM cs5 WHERE d1 = d2;
|
|
DELETE FROM cs6 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();
|
|
DELETE FROM cs4 WHERE d1 != d2;
|
|
DELETE FROM cs5 WHERE d1 != d2;
|
|
DELETE FROM cs6 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();
|
|
DELETE FROM cs4 WHERE d1 < d2;
|
|
DELETE FROM cs5 WHERE d1 < d2;
|
|
DELETE FROM cs6 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();
|
|
DELETE FROM cs4 WHERE d1 > d2;
|
|
DELETE FROM cs5 WHERE d1 > d2;
|
|
DELETE FROM cs6 WHERE d1 > d2;
|
|
SELECT "unsignedtest10", d1, d2 FROM cs4;
|
|
SELECT "unsignedtest10", d1, d2 FROM cs5;
|
|
SELECT "unsignedtest10", d1, d2 FROM cs6;
|
|
|
|
# Deletes 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_delete_db;
|
|
--enable_warnings
|
|
|