From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 20:00:34 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- .../suite/engines/iuds/t/delete_decimal.test | 383 +++++++++++++++++++++ 1 file changed, 383 insertions(+) create mode 100644 mysql-test/suite/engines/iuds/t/delete_decimal.test (limited to 'mysql-test/suite/engines/iuds/t/delete_decimal.test') diff --git a/mysql-test/suite/engines/iuds/t/delete_decimal.test b/mysql-test/suite/engines/iuds/t/delete_decimal.test new file mode 100644 index 00000000..053fc781 --- /dev/null +++ b/mysql-test/suite/engines/iuds/t/delete_decimal.test @@ -0,0 +1,383 @@ +--disable_warnings +DROP TABLE IF EXISTS t1,t2,t3,t4; +--enable_warnings + +######## Running DELETE tests for DECIMAL ######## + +# Create tables +CREATE TABLE t1(c1 DECIMAL(10,5) UNSIGNED NOT NULL, c2 DECIMAL(10,5) SIGNED NULL, c3 DECIMAL, c4 INT, UNIQUE INDEX idx(c1,c4)); + +# Inserting data into table t1 + +# Trailing zeroes are not stripped, D=5 +INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2); + +# DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped; +INSERT IGNORE INTO t1 VALUES('1000000','10000000','1000000000.0001',3); + +# c1, c2, c3 will be rounded automatically +INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4); + +# Inserting maximum values +INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5); + +# Test insert leading zero, +/- signs, overflow handling +INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7); +INSERT IGNORE INTO t1 VALUES ("-.1","-.1","-.1",8); +INSERT IGNORE INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9); + +# Inserting in scientific notations +INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10); +#INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11); + +--sorted_result +SELECT * FROM t1; + +# Deletion starts here + +# Delete by order by limit +DELETE FROM t1 WHERE c2 > 100 ORDER BY c2 LIMIT 1; +--sorted_result +SELECT c1,c2 FROM t1; + +# Delete using range values +DELETE FROM t1 WHERE c3=1 AND c4=7; +--sorted_result +SELECT * FROM t1; +DELETE FROM t1 WHERE c3=9999999999 OR c2=1.0e-5; +--sorted_result +SELECT * FROM t1 WHERE c1=1.2e+2; +DELETE FROM t1 WHERE c4 IN (4,5,6); +--sorted_result +SELECT * FROM t1; + +TRUNCATE t1; +INSERT INTO t1 VALUES ('11111.11111','-11111.11111','1111111111',1),('22222.22222','-22222.22222','2222222222',2),('33333.33333','-33333.33333','3333333333',3),('44444.44444','-44444.44444','4444444444',4),('55555.55555','-55555.55555','5555555555',5),('66666.66666','-66666.66666','6666666666',6),('77777.77777','-77777.77777','7777777777',7),('88888.88888','-88888.88888','8888888888',8),('99999.99999','-99999.99999','9999999999',9); +--sorted_result +SELECT * FROM t1; + +SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1; +DELETE FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1; +--sorted_result +SELECT * FROM t1; + +# Multi table delete +CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx2(c1,c3)); +CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1,c3)); +CREATE TABLE t4(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx4(c1,c3)); +INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); +INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); +INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9); +--sorted_result +SELECT * FROM t2; +--sorted_result +SELECT * FROM t3; +--sorted_result +SELECT * FROM t4; +DELETE t1,t2,t3,t4 FROM t1,t2,t3,t4 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888'; +--sorted_result +SELECT * FROM t1; +--sorted_result +SELECT * FROM t1; +--sorted_result +SELECT * FROM t1; + +# Delete using various access methods +TRUNCATE t2; +TRUNCATE t3; +INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); +INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); + +# Delete using Const +# EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2; +DELETE FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2; +--sorted_result +SELECT * FROM t2; + +# Delete using range +# EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444'; +DELETE FROM t2 WHERE c1 BETWEEN '11111' AND '44444'; +--sorted_result +SELECT * FROM t2; +# EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999'); +DELETE FROM t2 WHERE c1 IN ('44444.44444','99999.99999'); +--sorted_result +SELECT * FROM t2; + +# Delete using eq_ref +DROP TABLE t2,t3; +CREATE TABLE t2(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT); +CREATE TABLE t3(c1 DECIMAL(10,5) NOT NULL, c2 DECIMAL, c3 INT, UNIQUE INDEX idx3(c1)); +INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); +INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3); +# EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3; +DELETE t2,t3 FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3; + +--sorted_result +SELECT * FROM t1; +--sorted_result +SELECT * FROM t2; +--sorted_result +SELECT * FROM t3; +--sorted_result +SELECT * FROM t4; +# Droping the tables +--disable_warnings +DROP TABLES IF EXISTS t1,t2,t3,t4; +--enable_warnings + +######## Running DELETE tests for FLOAT ######## + +# Create tables +CREATE TABLE t1(c1 FLOAT(10,5) UNSIGNED NOT NULL, c2 FLOAT(10,5) SIGNED NULL, c3 FLOAT, c4 INT, UNIQUE INDEX idx(c1,c4)); + +# Inserting data into table t1 + +# Trailing zeroes are not stripped, D=5 +INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2); + +# DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped; +INSERT IGNORE INTO t1 VALUES('1000000','10000000','1000000000.0001',3); + +# c1, c2, c3 will be rounded automatically +INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4); + +# Inserting maximum values +INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5); + +# Test insert leading zero, +/- signs, overflow handling +INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7); +INSERT IGNORE INTO t1 VALUES ("-.1","-.1","-.1",8); +INSERT IGNORE INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9); + +# Inserting in scientific notations +INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10); +#INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11); + +--sorted_result +SELECT * FROM t1; + +# Deletion starts here + +# Delete by order by limit +DELETE FROM t1 WHERE c2 > 100 ORDER BY c2 LIMIT 1; +--sorted_result +SELECT c1,c2 FROM t1; + +# Delete using range values +DELETE FROM t1 WHERE c3=1 AND c4=7; +--sorted_result +SELECT * FROM t1; +DELETE FROM t1 WHERE c3=9999999999 OR c2=1.0e-5; +--sorted_result +SELECT * FROM t1 WHERE c1=1.2e+2; +DELETE FROM t1 WHERE c4 IN (4,5,6); +--sorted_result +SELECT * FROM t1; + +TRUNCATE t1; +INSERT INTO t1 VALUES ('11111.11111','-11111.11111','1111111111',1),('22222.22222','-22222.22222','2222222222',2),('33333.33333','-33333.33333','3333333333',3),('44444.44444','-44444.44444','4444444444',4),('55555.55555','-55555.55555','5555555555',5),('66666.66666','-66666.66666','6666666666',6),('77777.77777','-77777.77777','7777777777',7),('88888.88888','-88888.88888','8888888888',8),('99999.99999','-99999.99999','9999999999',9); +--replace_result 88888.89063 88888.89062 +--sorted_result +SELECT * FROM t1; + +SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1; +DELETE FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1; +--replace_result 88888.89063 88888.89062 +--sorted_result +SELECT * FROM t1; + +# Multi table delete +CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx2(c1,c3)); +CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1,c3)); +CREATE TABLE t4(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx4(c1,c3)); +INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); +INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); +INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9); +--sorted_result +SELECT * FROM t2; +--sorted_result +SELECT * FROM t3; +--replace_result 88888.89063 88888.89062 +--sorted_result +SELECT * FROM t4; +DELETE t1,t2,t3,t4 FROM t1,t2,t3,t4 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888'; +--replace_result 88888.89063 88888.89062 +--sorted_result +SELECT * FROM t1; +--replace_result 88888.89063 88888.89062 +--sorted_result +SELECT * FROM t1; +--replace_result 88888.89063 88888.89062 +--sorted_result +SELECT * FROM t1; + +# Delete using various access methods +TRUNCATE t2; +TRUNCATE t3; +INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); +INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); + +# Delete using Const +# EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2; +DELETE FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2; +--sorted_result +SELECT * FROM t2; + +# Delete using range +# EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444'; +DELETE FROM t2 WHERE c1 BETWEEN '11111' AND '44444'; +--sorted_result +SELECT * FROM t2; +# EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999'); +DELETE FROM t2 WHERE c1 IN ('44444.44444','99999.99999'); +--sorted_result +SELECT * FROM t2; + +# Delete using eq_ref +DROP TABLE t2,t3; +CREATE TABLE t2(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT); +CREATE TABLE t3(c1 FLOAT(10,5) NOT NULL, c2 FLOAT, c3 INT, UNIQUE INDEX idx3(c1)); +INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); +INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3); +# EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3; +DELETE t2,t3 FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3; +--replace_result 88888.89063 88888.89062 +--sorted_result +SELECT * FROM t1; +--sorted_result +SELECT * FROM t2; +--sorted_result +SELECT * FROM t3; +--replace_result 88888.89063 88888.89062 +--sorted_result +SELECT * FROM t4; +# Droping the tables +--disable_warnings +DROP TABLES IF EXISTS t1,t2,t3,t4; +--enable_warnings + +######## Running DELETE tests for DOUBLE ######## + +# Create tables +CREATE TABLE t1(c1 DOUBLE(10,5) UNSIGNED NOT NULL, c2 DOUBLE(10,5) SIGNED NULL, c3 DOUBLE, c4 INT, UNIQUE INDEX idx(c1,c4)); + +# Inserting data into table t1 + +# Trailing zeroes are not stripped, D=5 +INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100119',1),('11111.00009','-9999.99999','9999999',2); + +# DECIMAL := DECIMAL(10,0); Decimal values in c3 will be stripped; +INSERT IGNORE INTO t1 VALUES('1000000','10000000','1000000000.0001',3); + +# c1, c2, c3 will be rounded automatically +INSERT INTO t1 values('100.000001','1000.999999','9999.999999',4); + +# Inserting maximum values +INSERT INTO t1 VALUES('99999.99999','-99999.99999','9999999999',5); + +# Test insert leading zero, +/- signs, overflow handling +INSERT INTO t1 VALUES ("0.0","0.0","0.0",6),("01.0","01.0","01.0",7); +INSERT IGNORE INTO t1 VALUES ("-.1","-.1","-.1",8); +INSERT IGNORE INTO t1 VALUES ("+111111111.11","+111111111.11","+111111111.11",9); + +# Inserting in scientific notations +INSERT INTO t1 VALUES('1.0e+4','1.0e-5','1.0e+9',10); +#INSERT INTO t1 VALUES('0.9999999999e+4','-.999999999e+4,'0.9999999999e+10',11); + +--sorted_result +SELECT * FROM t1; + +# Deletion starts here + +# Delete by order by limit +DELETE FROM t1 WHERE c2 > 100 ORDER BY c2 LIMIT 1; +--sorted_result +SELECT c1,c2 FROM t1; + +# Delete using range values +DELETE FROM t1 WHERE c3=1 AND c4=7; +--sorted_result +SELECT * FROM t1; +DELETE FROM t1 WHERE c3=9999999999 OR c2=1.0e-5; +--sorted_result +SELECT * FROM t1 WHERE c1=1.2e+2; +DELETE FROM t1 WHERE c4 IN (4,5,6); +--sorted_result +SELECT * FROM t1; + +TRUNCATE t1; +INSERT INTO t1 VALUES ('11111.11111','-11111.11111','1111111111',1),('22222.22222','-22222.22222','2222222222',2),('33333.33333','-33333.33333','3333333333',3),('44444.44444','-44444.44444','4444444444',4),('55555.55555','-55555.55555','5555555555',5),('66666.66666','-66666.66666','6666666666',6),('77777.77777','-77777.77777','7777777777',7),('88888.88888','-88888.88888','8888888888',8),('99999.99999','-99999.99999','9999999999',9); +--sorted_result +SELECT * FROM t1; + +SELECT c1 FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1; +DELETE FROM t1 WHERE c1>='22222.22222' AND c1<='55555.55555' ORDER BY c1; +--sorted_result +SELECT * FROM t1; + +# Multi table delete +CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx2(c1,c3)); +CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1,c3)); +CREATE TABLE t4(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx4(c1,c3)); +INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); +INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); +INSERT INTO t4 VALUES ('77777.77777','7777777777',7),('88888.88888','8888888888',8),('99999.99999','9999999999',9); +--sorted_result +SELECT * FROM t2; +--sorted_result +SELECT * FROM t3; +--sorted_result +SELECT * FROM t4; +DELETE t1,t2,t3,t4 FROM t1,t2,t3,t4 WHERE t1.c2 >'1111111111' AND t4.c2 < '8888888888'; +--sorted_result +SELECT * FROM t1; +--sorted_result +SELECT * FROM t1; +--sorted_result +SELECT * FROM t1; + +# Delete using various access methods +TRUNCATE t2; +TRUNCATE t3; +INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); +INSERT INTO t3 VALUES ('44444.44444','4444444444',4),('55555.55555','5555555555',5),('66666.66666','6666666666',6); + +# Delete using Const +# EXPLAIN SELECT * FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2; +DELETE FROM t2 WHERE t2.c1='22222.22222' AND t2.c3=2; +--sorted_result +SELECT * FROM t2; + +# Delete using range +# EXPLAIN SELECT * FROM t2 WHERE c1 BETWEEN '11111' AND '44444'; +DELETE FROM t2 WHERE c1 BETWEEN '11111' AND '44444'; +--sorted_result +SELECT * FROM t2; +# EXPLAIN SELECT * FROM t2 WHERE c1 IN ('44444.44444','99999.99999'); +DELETE FROM t2 WHERE c1 IN ('44444.44444','99999.99999'); +--sorted_result +SELECT * FROM t2; + +# Delete using eq_ref +DROP TABLE t2,t3; +CREATE TABLE t2(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT); +CREATE TABLE t3(c1 DOUBLE(10,5) NOT NULL, c2 DOUBLE, c3 INT, UNIQUE INDEX idx3(c1)); +INSERT INTO t2 VALUES ('11111.11111','1111111111',1),('22222.22222','2222222222',2),('33333.33333','3333333333',3); +INSERT INTO t3 VALUES ('11111.11111','4444444444',1),('55555.55555','5555555555',2),('66666.66666','6666666666',3); +# EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3; +DELETE t2,t3 FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3; + +--sorted_result +SELECT * FROM t1; +--sorted_result +SELECT * FROM t2; +--sorted_result +SELECT * FROM t3; +--sorted_result +SELECT * FROM t4; +# Droping the tables +--disable_warnings +DROP TABLES IF EXISTS t1,t2,t3,t4; +--enable_warnings + -- cgit v1.2.3