mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-05-01 08:44:47 +03:00
711 lines
32 KiB
Plaintext
711 lines
32 KiB
Plaintext
DROP DATABASE IF EXISTS mcol641_delete_db;
|
|
CREATE DATABASE mcol641_delete_db;
|
|
USE mcol641_delete_db;
|
|
DROP PROCEDURE IF EXISTS signedinsertproc;
|
|
DROP PROCEDURE IF EXISTS signeddeleteproc;
|
|
DROP PROCEDURE IF EXISTS unsignedinsertproc;
|
|
DROP PROCEDURE IF EXISTS unsigneddeleteproc;
|
|
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//
|
|
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;
|
|
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();
|
|
DELETE FROM cs1;
|
|
DELETE FROM cs2;
|
|
DELETE FROM cs3;
|
|
SELECT "signedtest1", d1, d2 FROM cs1;
|
|
signedtest1 d1 d2
|
|
SELECT "signedtest1", d1, d2 FROM cs2;
|
|
signedtest1 d1 d2
|
|
SELECT "signedtest1", d1, d2 FROM cs3;
|
|
signedtest1 d1 d2
|
|
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;
|
|
signedtest2 d1 d2
|
|
signedtest2 125 125
|
|
signedtest2 -125 -125
|
|
signedtest2 99999999999999999999999999999999999998 9999999999999999998
|
|
signedtest2 -99999999999999999999999999999999999999 -9999999999999999999
|
|
signedtest2 0 0
|
|
signedtest2 NULL NULL
|
|
SELECT "signedtest2", d1, d2 FROM cs2;
|
|
signedtest2 d1 d2
|
|
signedtest2 1.2500000000 1.2500000000
|
|
signedtest2 -1.2500000000 -1.2500000000
|
|
signedtest2 9999999999999999999999999999.9999999998 999999999.9999999998
|
|
signedtest2 -9999999999999999999999999999.9999999999 -999999999.9999999999
|
|
signedtest2 1.1234567891 1.1234567891
|
|
signedtest2 1.9999999999 1.9999999999
|
|
signedtest2 -1.1234567891 -1.1234567891
|
|
signedtest2 -1.9999999999 -1.9999999999
|
|
signedtest2 1.0000000009 1.0000000009
|
|
signedtest2 -1.0000000009 -1.0000000009
|
|
signedtest2 0.0000000000 0.0000000000
|
|
signedtest2 NULL NULL
|
|
SELECT "signedtest2", d1, d2 FROM cs3;
|
|
signedtest2 d1 d2
|
|
signedtest2 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
signedtest2 -0.12500000000000000000000000000000000000 -0.1250000000000000000
|
|
signedtest2 0.99999999999999999999999999999999999998 0.9999999999999999998
|
|
signedtest2 -0.99999999999999999999999999999999999999 -0.9999999999999999999
|
|
signedtest2 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
signedtest2 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
signedtest2 -0.12345678901234567890123456789012345678 -0.1234567890123456789
|
|
signedtest2 -0.19999999999999999999999999999999999999 -0.1999999999999999999
|
|
signedtest2 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
signedtest2 -0.00000000000000000000000000000000000009 -0.0000000000000000009
|
|
signedtest2 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
signedtest2 NULL NULL
|
|
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;
|
|
signedtest3 d1 d2
|
|
signedtest3 125 125
|
|
signedtest3 -125 -125
|
|
signedtest3 99999999999999999999999999999999999998 9999999999999999998
|
|
signedtest3 -99999999999999999999999999999999999999 -9999999999999999999
|
|
signedtest3 0 0
|
|
signedtest3 NULL NULL
|
|
SELECT "signedtest3", d1, d2 FROM cs2;
|
|
signedtest3 d1 d2
|
|
signedtest3 1.2500000000 1.2500000000
|
|
signedtest3 -1.2500000000 -1.2500000000
|
|
signedtest3 9999999999999999999999999999.9999999998 999999999.9999999998
|
|
signedtest3 -9999999999999999999999999999.9999999999 -999999999.9999999999
|
|
signedtest3 1.1234567891 1.1234567891
|
|
signedtest3 1.9999999999 1.9999999999
|
|
signedtest3 -1.1234567891 -1.1234567891
|
|
signedtest3 -1.9999999999 -1.9999999999
|
|
signedtest3 1.0000000009 1.0000000009
|
|
signedtest3 -1.0000000009 -1.0000000009
|
|
signedtest3 0.0000000000 0.0000000000
|
|
signedtest3 NULL NULL
|
|
SELECT "signedtest3", d1, d2 FROM cs3;
|
|
signedtest3 d1 d2
|
|
signedtest3 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
signedtest3 -0.12500000000000000000000000000000000000 -0.1250000000000000000
|
|
signedtest3 0.99999999999999999999999999999999999998 0.9999999999999999998
|
|
signedtest3 -0.99999999999999999999999999999999999999 -0.9999999999999999999
|
|
signedtest3 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
signedtest3 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
signedtest3 -0.12345678901234567890123456789012345678 -0.1234567890123456789
|
|
signedtest3 -0.19999999999999999999999999999999999999 -0.1999999999999999999
|
|
signedtest3 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
signedtest3 -0.00000000000000000000000000000000000009 -0.0000000000000000009
|
|
signedtest3 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
signedtest3 NULL NULL
|
|
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;
|
|
signedtest4a d1 d2
|
|
signedtest4a 125 125
|
|
signedtest4a -125 -125
|
|
signedtest4a 0 0
|
|
signedtest4a NULL NULL
|
|
SELECT "signedtest4a", d1, d2 FROM cs2;
|
|
signedtest4a d1 d2
|
|
signedtest4a 1.2500000000 1.2500000000
|
|
signedtest4a -1.2500000000 -1.2500000000
|
|
signedtest4a 1.1234567891 1.1234567891
|
|
signedtest4a 1.9999999999 1.9999999999
|
|
signedtest4a -1.1234567891 -1.1234567891
|
|
signedtest4a -1.9999999999 -1.9999999999
|
|
signedtest4a 1.0000000009 1.0000000009
|
|
signedtest4a -1.0000000009 -1.0000000009
|
|
signedtest4a 0.0000000000 0.0000000000
|
|
signedtest4a NULL NULL
|
|
SELECT "signedtest4a", d1, d2 FROM cs3;
|
|
signedtest4a d1 d2
|
|
signedtest4a 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
signedtest4a -0.12500000000000000000000000000000000000 -0.1250000000000000000
|
|
signedtest4a 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
signedtest4a 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
signedtest4a -0.12345678901234567890123456789012345678 -0.1234567890123456789
|
|
signedtest4a -0.19999999999999999999999999999999999999 -0.1999999999999999999
|
|
signedtest4a 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
signedtest4a -0.00000000000000000000000000000000000009 -0.0000000000000000009
|
|
signedtest4a 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
signedtest4a NULL NULL
|
|
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;
|
|
signedtest4 d1 d2
|
|
signedtest4 125 125
|
|
signedtest4 -125 -125
|
|
signedtest4 99999999999999999999999999999999999998 9999999999999999998
|
|
signedtest4 -99999999999999999999999999999999999998 -9999999999999999998
|
|
signedtest4 0 0
|
|
signedtest4 NULL NULL
|
|
SELECT "signedtest4", d1, d2 FROM cs2;
|
|
signedtest4 d1 d2
|
|
signedtest4 1.2500000000 1.2500000000
|
|
signedtest4 -1.2500000000 -1.2500000000
|
|
signedtest4 9999999999999999999999999999.9999999998 999999999.9999999998
|
|
signedtest4 -9999999999999999999999999999.9999999998 -999999999.9999999998
|
|
signedtest4 1.1234567891 1.1234567891
|
|
signedtest4 1.9999999999 1.9999999999
|
|
signedtest4 -1.1234567891 -1.1234567891
|
|
signedtest4 -1.9999999999 -1.9999999999
|
|
signedtest4 1.0000000009 1.0000000009
|
|
signedtest4 -1.0000000009 -1.0000000009
|
|
signedtest4 0.0000000000 0.0000000000
|
|
signedtest4 NULL NULL
|
|
SELECT "signedtest4", d1, d2 FROM cs3;
|
|
signedtest4 d1 d2
|
|
signedtest4 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
signedtest4 -0.12500000000000000000000000000000000000 -0.1250000000000000000
|
|
signedtest4 0.99999999999999999999999999999999999998 0.9999999999999999998
|
|
signedtest4 -0.99999999999999999999999999999999999998 -0.9999999999999999998
|
|
signedtest4 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
signedtest4 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
signedtest4 -0.12345678901234567890123456789012345678 -0.1234567890123456789
|
|
signedtest4 -0.19999999999999999999999999999999999999 -0.1999999999999999999
|
|
signedtest4 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
signedtest4 -0.00000000000000000000000000000000000009 -0.0000000000000000009
|
|
signedtest4 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
signedtest4 NULL NULL
|
|
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;
|
|
signedtest5 d1 d2
|
|
signedtest5 125 125
|
|
signedtest5 -125 -125
|
|
signedtest5 99999999999999999999999999999999999998 9999999999999999998
|
|
signedtest5 -99999999999999999999999999999999999998 -9999999999999999998
|
|
signedtest5 0 0
|
|
SELECT "signedtest5", d1, d2 FROM cs2;
|
|
signedtest5 d1 d2
|
|
signedtest5 1.2500000000 1.2500000000
|
|
signedtest5 -1.2500000000 -1.2500000000
|
|
signedtest5 9999999999999999999999999999.9999999998 999999999.9999999998
|
|
signedtest5 -9999999999999999999999999999.9999999998 -999999999.9999999998
|
|
signedtest5 1.1234567891 1.1234567891
|
|
signedtest5 1.9999999999 1.9999999999
|
|
signedtest5 -1.1234567891 -1.1234567891
|
|
signedtest5 -1.9999999999 -1.9999999999
|
|
signedtest5 1.0000000009 1.0000000009
|
|
signedtest5 -1.0000000009 -1.0000000009
|
|
signedtest5 0.0000000000 0.0000000000
|
|
SELECT "signedtest5", d1, d2 FROM cs3;
|
|
signedtest5 d1 d2
|
|
signedtest5 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
signedtest5 -0.12500000000000000000000000000000000000 -0.1250000000000000000
|
|
signedtest5 0.99999999999999999999999999999999999998 0.9999999999999999998
|
|
signedtest5 -0.99999999999999999999999999999999999998 -0.9999999999999999998
|
|
signedtest5 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
signedtest5 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
signedtest5 -0.12345678901234567890123456789012345678 -0.1234567890123456789
|
|
signedtest5 -0.19999999999999999999999999999999999999 -0.1999999999999999999
|
|
signedtest5 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
signedtest5 -0.00000000000000000000000000000000000009 -0.0000000000000000009
|
|
signedtest5 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
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;
|
|
signedtest6 d1 d2
|
|
signedtest6 125 125
|
|
SELECT "signedtest6", d1, d2 FROM cs2;
|
|
signedtest6 d1 d2
|
|
signedtest6 1.2500000000 1.2500000000
|
|
SELECT "signedtest6", d1, d2 FROM cs3;
|
|
signedtest6 d1 d2
|
|
signedtest6 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
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;
|
|
signedtest7 d1 d2
|
|
signedtest7 99999999999999999999999999999999999998 9999999999999999998
|
|
signedtest7 99999999999999999999999999999999999999 9999999999999999999
|
|
signedtest7 -99999999999999999999999999999999999998 -9999999999999999998
|
|
signedtest7 -99999999999999999999999999999999999999 -9999999999999999999
|
|
signedtest7 NULL NULL
|
|
SELECT "signedtest7", d1, d2 FROM cs2;
|
|
signedtest7 d1 d2
|
|
signedtest7 9999999999999999999999999999.9999999998 999999999.9999999998
|
|
signedtest7 9999999999999999999999999999.9999999999 999999999.9999999999
|
|
signedtest7 -9999999999999999999999999999.9999999998 -999999999.9999999998
|
|
signedtest7 -9999999999999999999999999999.9999999999 -999999999.9999999999
|
|
signedtest7 NULL NULL
|
|
SELECT "signedtest7", d1, d2 FROM cs3;
|
|
signedtest7 d1 d2
|
|
signedtest7 0.99999999999999999999999999999999999998 0.9999999999999999998
|
|
signedtest7 0.99999999999999999999999999999999999999 0.9999999999999999999
|
|
signedtest7 -0.99999999999999999999999999999999999998 -0.9999999999999999998
|
|
signedtest7 -0.99999999999999999999999999999999999999 -0.9999999999999999999
|
|
signedtest7 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
signedtest7 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
signedtest7 -0.12345678901234567890123456789012345678 -0.1234567890123456789
|
|
signedtest7 -0.19999999999999999999999999999999999999 -0.1999999999999999999
|
|
signedtest7 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
signedtest7 -0.00000000000000000000000000000000000009 -0.0000000000000000009
|
|
signedtest7 NULL NULL
|
|
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;
|
|
signedtest8 d1 d2
|
|
signedtest8 125 125
|
|
signedtest8 -125 -125
|
|
signedtest8 0 0
|
|
signedtest8 NULL NULL
|
|
SELECT "signedtest8", d1, d2 FROM cs2;
|
|
signedtest8 d1 d2
|
|
signedtest8 1.2500000000 1.2500000000
|
|
signedtest8 -1.2500000000 -1.2500000000
|
|
signedtest8 1.1234567891 1.1234567891
|
|
signedtest8 1.9999999999 1.9999999999
|
|
signedtest8 -1.1234567891 -1.1234567891
|
|
signedtest8 -1.9999999999 -1.9999999999
|
|
signedtest8 1.0000000009 1.0000000009
|
|
signedtest8 -1.0000000009 -1.0000000009
|
|
signedtest8 0.0000000000 0.0000000000
|
|
signedtest8 NULL NULL
|
|
SELECT "signedtest8", d1, d2 FROM cs3;
|
|
signedtest8 d1 d2
|
|
signedtest8 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
signedtest8 -0.12500000000000000000000000000000000000 -0.1250000000000000000
|
|
signedtest8 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
signedtest8 NULL NULL
|
|
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;
|
|
signedtest9 d1 d2
|
|
signedtest9 125 125
|
|
signedtest9 -125 -125
|
|
signedtest9 99999999999999999999999999999999999998 9999999999999999998
|
|
signedtest9 99999999999999999999999999999999999999 9999999999999999999
|
|
signedtest9 0 0
|
|
signedtest9 NULL NULL
|
|
SELECT "signedtest9", d1, d2 FROM cs2;
|
|
signedtest9 d1 d2
|
|
signedtest9 1.2500000000 1.2500000000
|
|
signedtest9 -1.2500000000 -1.2500000000
|
|
signedtest9 9999999999999999999999999999.9999999998 999999999.9999999998
|
|
signedtest9 9999999999999999999999999999.9999999999 999999999.9999999999
|
|
signedtest9 1.1234567891 1.1234567891
|
|
signedtest9 1.9999999999 1.9999999999
|
|
signedtest9 -1.1234567891 -1.1234567891
|
|
signedtest9 -1.9999999999 -1.9999999999
|
|
signedtest9 1.0000000009 1.0000000009
|
|
signedtest9 -1.0000000009 -1.0000000009
|
|
signedtest9 0.0000000000 0.0000000000
|
|
signedtest9 NULL NULL
|
|
SELECT "signedtest9", d1, d2 FROM cs3;
|
|
signedtest9 d1 d2
|
|
signedtest9 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
signedtest9 -0.12500000000000000000000000000000000000 -0.1250000000000000000
|
|
signedtest9 0.99999999999999999999999999999999999998 0.9999999999999999998
|
|
signedtest9 0.99999999999999999999999999999999999999 0.9999999999999999999
|
|
signedtest9 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
signedtest9 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
signedtest9 -0.00000000000000000000000000000000000009 -0.0000000000000000009
|
|
signedtest9 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
signedtest9 NULL NULL
|
|
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;
|
|
signedtest10 d1 d2
|
|
signedtest10 125 125
|
|
signedtest10 -125 -125
|
|
signedtest10 -99999999999999999999999999999999999998 -9999999999999999998
|
|
signedtest10 -99999999999999999999999999999999999999 -9999999999999999999
|
|
signedtest10 0 0
|
|
signedtest10 NULL NULL
|
|
SELECT "signedtest10", d1, d2 FROM cs2;
|
|
signedtest10 d1 d2
|
|
signedtest10 1.2500000000 1.2500000000
|
|
signedtest10 -1.2500000000 -1.2500000000
|
|
signedtest10 -9999999999999999999999999999.9999999998 -999999999.9999999998
|
|
signedtest10 -9999999999999999999999999999.9999999999 -999999999.9999999999
|
|
signedtest10 1.1234567891 1.1234567891
|
|
signedtest10 1.9999999999 1.9999999999
|
|
signedtest10 -1.1234567891 -1.1234567891
|
|
signedtest10 -1.9999999999 -1.9999999999
|
|
signedtest10 1.0000000009 1.0000000009
|
|
signedtest10 -1.0000000009 -1.0000000009
|
|
signedtest10 0.0000000000 0.0000000000
|
|
signedtest10 NULL NULL
|
|
SELECT "signedtest10", d1, d2 FROM cs3;
|
|
signedtest10 d1 d2
|
|
signedtest10 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
signedtest10 -0.12500000000000000000000000000000000000 -0.1250000000000000000
|
|
signedtest10 -0.99999999999999999999999999999999999998 -0.9999999999999999998
|
|
signedtest10 -0.99999999999999999999999999999999999999 -0.9999999999999999999
|
|
signedtest10 -0.12345678901234567890123456789012345678 -0.1234567890123456789
|
|
signedtest10 -0.19999999999999999999999999999999999999 -0.1999999999999999999
|
|
signedtest10 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
signedtest10 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
signedtest10 NULL NULL
|
|
CALL unsignedinsertproc();
|
|
DELETE FROM cs4;
|
|
DELETE FROM cs5;
|
|
DELETE FROM cs6;
|
|
SELECT "unsignedtest1", d1, d2 FROM cs4;
|
|
unsignedtest1 d1 d2
|
|
SELECT "unsignedtest1", d1, d2 FROM cs5;
|
|
unsignedtest1 d1 d2
|
|
SELECT "unsignedtest1", d1, d2 FROM cs6;
|
|
unsignedtest1 d1 d2
|
|
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;
|
|
unsignedtest2 d1 d2
|
|
unsignedtest2 125 125
|
|
unsignedtest2 0 0
|
|
unsignedtest2 NULL NULL
|
|
SELECT "unsignedtest2", d1, d2 FROM cs5;
|
|
unsignedtest2 d1 d2
|
|
unsignedtest2 1.2500000000 1.2500000000
|
|
unsignedtest2 1.1234567891 1.1234567891
|
|
unsignedtest2 1.9999999999 1.9999999999
|
|
unsignedtest2 1.0000000009 1.0000000009
|
|
unsignedtest2 0.0000000000 0.0000000000
|
|
unsignedtest2 NULL NULL
|
|
SELECT "unsignedtest2", d1, d2 FROM cs6;
|
|
unsignedtest2 d1 d2
|
|
unsignedtest2 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
unsignedtest2 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
unsignedtest2 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
unsignedtest2 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
unsignedtest2 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
unsignedtest2 NULL NULL
|
|
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;
|
|
unsignedtest3 d1 d2
|
|
unsignedtest3 125 125
|
|
unsignedtest3 0 0
|
|
unsignedtest3 NULL NULL
|
|
SELECT "unsignedtest3", d1, d2 FROM cs5;
|
|
unsignedtest3 d1 d2
|
|
unsignedtest3 1.2500000000 1.2500000000
|
|
unsignedtest3 1.1234567891 1.1234567891
|
|
unsignedtest3 1.9999999999 1.9999999999
|
|
unsignedtest3 1.0000000009 1.0000000009
|
|
unsignedtest3 0.0000000000 0.0000000000
|
|
unsignedtest3 NULL NULL
|
|
SELECT "unsignedtest3", d1, d2 FROM cs6;
|
|
unsignedtest3 d1 d2
|
|
unsignedtest3 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
unsignedtest3 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
unsignedtest3 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
unsignedtest3 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
unsignedtest3 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
unsignedtest3 NULL NULL
|
|
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;
|
|
unsignedtest4a d1 d2
|
|
unsignedtest4a 125 125
|
|
unsignedtest4a 0 0
|
|
unsignedtest4a NULL NULL
|
|
SELECT "unsignedtest4a", d1, d2 FROM cs5;
|
|
unsignedtest4a d1 d2
|
|
unsignedtest4a 1.2500000000 1.2500000000
|
|
unsignedtest4a 1.1234567891 1.1234567891
|
|
unsignedtest4a 1.9999999999 1.9999999999
|
|
unsignedtest4a 1.0000000009 1.0000000009
|
|
unsignedtest4a 0.0000000000 0.0000000000
|
|
unsignedtest4a NULL NULL
|
|
SELECT "unsignedtest4a", d1, d2 FROM cs6;
|
|
unsignedtest4a d1 d2
|
|
unsignedtest4a 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
unsignedtest4a 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
unsignedtest4a 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
unsignedtest4a 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
unsignedtest4a 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
unsignedtest4a NULL NULL
|
|
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;
|
|
unsignedtest4 d1 d2
|
|
unsignedtest4 125 125
|
|
unsignedtest4 99999999999999999999999999999999999998 9999999999999999998
|
|
unsignedtest4 0 0
|
|
unsignedtest4 NULL NULL
|
|
SELECT "unsignedtest4", d1, d2 FROM cs5;
|
|
unsignedtest4 d1 d2
|
|
unsignedtest4 1.2500000000 1.2500000000
|
|
unsignedtest4 9999999999999999999999999999.9999999998 999999999.9999999998
|
|
unsignedtest4 1.1234567891 1.1234567891
|
|
unsignedtest4 1.9999999999 1.9999999999
|
|
unsignedtest4 1.0000000009 1.0000000009
|
|
unsignedtest4 0.0000000000 0.0000000000
|
|
unsignedtest4 NULL NULL
|
|
SELECT "unsignedtest4", d1, d2 FROM cs6;
|
|
unsignedtest4 d1 d2
|
|
unsignedtest4 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
unsignedtest4 0.99999999999999999999999999999999999998 0.9999999999999999998
|
|
unsignedtest4 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
unsignedtest4 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
unsignedtest4 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
unsignedtest4 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
unsignedtest4 NULL NULL
|
|
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;
|
|
unsignedtest5 d1 d2
|
|
unsignedtest5 125 125
|
|
unsignedtest5 99999999999999999999999999999999999998 9999999999999999998
|
|
unsignedtest5 0 0
|
|
SELECT "unsignedtest5", d1, d2 FROM cs5;
|
|
unsignedtest5 d1 d2
|
|
unsignedtest5 1.2500000000 1.2500000000
|
|
unsignedtest5 9999999999999999999999999999.9999999998 999999999.9999999998
|
|
unsignedtest5 1.1234567891 1.1234567891
|
|
unsignedtest5 1.9999999999 1.9999999999
|
|
unsignedtest5 1.0000000009 1.0000000009
|
|
unsignedtest5 0.0000000000 0.0000000000
|
|
SELECT "unsignedtest5", d1, d2 FROM cs6;
|
|
unsignedtest5 d1 d2
|
|
unsignedtest5 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
unsignedtest5 0.99999999999999999999999999999999999998 0.9999999999999999998
|
|
unsignedtest5 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
unsignedtest5 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
unsignedtest5 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
unsignedtest5 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
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;
|
|
unsignedtest6 d1 d2
|
|
unsignedtest6 125 125
|
|
SELECT "unsignedtest6", d1, d2 FROM cs5;
|
|
unsignedtest6 d1 d2
|
|
unsignedtest6 1.2500000000 1.2500000000
|
|
SELECT "unsignedtest6", d1, d2 FROM cs6;
|
|
unsignedtest6 d1 d2
|
|
unsignedtest6 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
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;
|
|
unsignedtest7 d1 d2
|
|
unsignedtest7 99999999999999999999999999999999999998 9999999999999999998
|
|
unsignedtest7 99999999999999999999999999999999999999 9999999999999999999
|
|
unsignedtest7 NULL NULL
|
|
SELECT "unsignedtest7", d1, d2 FROM cs5;
|
|
unsignedtest7 d1 d2
|
|
unsignedtest7 9999999999999999999999999999.9999999998 999999999.9999999998
|
|
unsignedtest7 9999999999999999999999999999.9999999999 999999999.9999999999
|
|
unsignedtest7 NULL NULL
|
|
SELECT "unsignedtest7", d1, d2 FROM cs6;
|
|
unsignedtest7 d1 d2
|
|
unsignedtest7 0.99999999999999999999999999999999999998 0.9999999999999999998
|
|
unsignedtest7 0.99999999999999999999999999999999999999 0.9999999999999999999
|
|
unsignedtest7 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
unsignedtest7 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
unsignedtest7 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
unsignedtest7 NULL NULL
|
|
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;
|
|
unsignedtest8 d1 d2
|
|
unsignedtest8 125 125
|
|
unsignedtest8 0 0
|
|
unsignedtest8 NULL NULL
|
|
SELECT "unsignedtest8", d1, d2 FROM cs5;
|
|
unsignedtest8 d1 d2
|
|
unsignedtest8 1.2500000000 1.2500000000
|
|
unsignedtest8 1.1234567891 1.1234567891
|
|
unsignedtest8 1.9999999999 1.9999999999
|
|
unsignedtest8 1.0000000009 1.0000000009
|
|
unsignedtest8 0.0000000000 0.0000000000
|
|
unsignedtest8 NULL NULL
|
|
SELECT "unsignedtest8", d1, d2 FROM cs6;
|
|
unsignedtest8 d1 d2
|
|
unsignedtest8 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
unsignedtest8 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
unsignedtest8 NULL NULL
|
|
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;
|
|
unsignedtest9 d1 d2
|
|
unsignedtest9 125 125
|
|
unsignedtest9 99999999999999999999999999999999999998 9999999999999999998
|
|
unsignedtest9 99999999999999999999999999999999999999 9999999999999999999
|
|
unsignedtest9 0 0
|
|
unsignedtest9 NULL NULL
|
|
SELECT "unsignedtest9", d1, d2 FROM cs5;
|
|
unsignedtest9 d1 d2
|
|
unsignedtest9 1.2500000000 1.2500000000
|
|
unsignedtest9 9999999999999999999999999999.9999999998 999999999.9999999998
|
|
unsignedtest9 9999999999999999999999999999.9999999999 999999999.9999999999
|
|
unsignedtest9 1.1234567891 1.1234567891
|
|
unsignedtest9 1.9999999999 1.9999999999
|
|
unsignedtest9 1.0000000009 1.0000000009
|
|
unsignedtest9 0.0000000000 0.0000000000
|
|
unsignedtest9 NULL NULL
|
|
SELECT "unsignedtest9", d1, d2 FROM cs6;
|
|
unsignedtest9 d1 d2
|
|
unsignedtest9 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
unsignedtest9 0.99999999999999999999999999999999999998 0.9999999999999999998
|
|
unsignedtest9 0.99999999999999999999999999999999999999 0.9999999999999999999
|
|
unsignedtest9 0.12345678901234567890123456789012345678 0.1234567890123456789
|
|
unsignedtest9 0.19999999999999999999999999999999999999 0.1999999999999999999
|
|
unsignedtest9 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
unsignedtest9 NULL NULL
|
|
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;
|
|
unsignedtest10 d1 d2
|
|
unsignedtest10 125 125
|
|
unsignedtest10 0 0
|
|
unsignedtest10 NULL NULL
|
|
SELECT "unsignedtest10", d1, d2 FROM cs5;
|
|
unsignedtest10 d1 d2
|
|
unsignedtest10 1.2500000000 1.2500000000
|
|
unsignedtest10 1.1234567891 1.1234567891
|
|
unsignedtest10 1.9999999999 1.9999999999
|
|
unsignedtest10 1.0000000009 1.0000000009
|
|
unsignedtest10 0.0000000000 0.0000000000
|
|
unsignedtest10 NULL NULL
|
|
SELECT "unsignedtest10", d1, d2 FROM cs6;
|
|
unsignedtest10 d1 d2
|
|
unsignedtest10 0.12500000000000000000000000000000000000 0.1250000000000000000
|
|
unsignedtest10 0.00000000000000000000000000000000000009 0.0000000000000000009
|
|
unsignedtest10 0.00000000000000000000000000000000000000 0.0000000000000000000
|
|
unsignedtest10 NULL NULL
|
|
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;
|