Debian Codegraph @

mysql-test/suite/engines/iuds/t/delete_year.test

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1,t2,t3,t4;
3 --enable_warnings
4 # Create tables
5 CREATE TABLE t1(c1 YEAR NOT NULL,c2 YEAR, PRIMARY KEY(c1));
6 CREATE TABLE t2(c1 YEAR NOT NULL, c2 YEAR, UNIQUE INDEX idx(c1,c2));
7 CREATE TABLE t3(c1 YEAR NOT NULL,c2 YEAR, PRIMARY KEY(c1));
8 CREATE TABLE t4(c1 YEAR, c2 YEAR, UNIQUE INDEX idx(c1,c2));
9 #Insert as 4-digit number format in the range '1901' to '2155'
10 INSERT INTO t1 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
11 INSERT INTO t2 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
12 INSERT INTO t3 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
13 INSERT INTO t4 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
14 #Insert as 4-digit string format in the range '1901' to '2155'
15 INSERT INTO t1 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
16 INSERT INTO t2 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
17 INSERT INTO t3 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
18 INSERT INTO t4 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
19 #Insert as 2-digit number format
20 INSERT INTO t1 VALUES (04,04),(64,64),(69,69),(97,97);
21 INSERT INTO t2 VALUES (04,04),(64,64),(69,69),(97,97);
22 INSERT INTO t3 VALUES (04,04),(64,64),(69,69),(97,97);
23 INSERT INTO t4 VALUES (04,04),(64,64),(69,69),(97,97);
24 #Insert as 2-digit string format
25 INSERT INTO t1 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
26 INSERT INTO t2 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
27 INSERT INTO t3 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
28 INSERT INTO t4 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
29 #Insert permissible NULLs
30 INSERT INTO t1 VALUES ('09',null),('61',null),('79',null),('96',null);
31 INSERT INTO t2 VALUES ('09',null),('61',null),('79',null),('96',null);
32 INSERT INTO t3 VALUES ('09',null),('61',null),('79',null),('96',null);
33 INSERT INTO t4 VALUES ('09',null),('61',null),('79',null),('96',null);
34 --sorted_result
35 SELECT * FROM t1;
36 --sorted_result
37 SELECT * FROM t2;
38 --sorted_result
39 SELECT * FROM t3;
40 --sorted_result
41 SELECT * FROM t4;
42 #Deleting the specified rows
43 --sorted_result
44 SELECT c1 FROM t1 WHERE c1='2005';
45 DELETE FROM t1 WHERE c1='2005';
46 --sorted_result
47 SELECT c1 FROM t1;
48
49 # Delete rows with null attributes
50 --sorted_result
51 SELECT c2 FROM t2 WHERE c2=null;
52 DELETE FROM t2 WHERE c2=null LIMIT 2;
53 --sorted_result
54 SELECT c2 FROM t2;
55
56 #Delete order by limit
57 --sorted_result
58 SELECT c1 FROM t4 WHERE c1 < '55';
59 DELETE FROM t4 WHERE c1 < '65' ORDER BY c1 LIMIT 5;
60 --sorted_result
61 SELECT c1 FROM t4;
62
63 #Delete by range values
64 DELETE FROM t2 WHERE c1=2009 AND c2=null;
65 --sorted_result
66 SELECT c2 FROM t2;
67 DELETE FROM t1 WHERE c1=1979 OR c1=2154;
68 --sorted_result
69 SELECT c2 FROM t1;
70 DELETE FROM t4 WHERE c2 IN (01,54,65,69,null) LIMIT 2;
71 --sorted_result
72 SELECT c1 FROM t4;
73
74 #Multi table delete
75 DELETE t3,t4 FROM t3,t4 WHERE t3.c1=61 AND t4.c2=4;
76
77 # Delete using various access methods
78
79 # Delete using Const
80 # EXPLAIN SELECT * FROM t1 WHERE c1=2064 AND c2=2064;
81 DELETE FROM t1 WHERE c1=2064 AND c2=2064;
82 --sorted_result
83 SELECT * FROM t1;
84
85 # Delete using range
86 # EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 2000 AND 2010;
87 DELETE FROM t1 WHERE c1 BETWEEN 2000 AND 2010 LIMIT 2;
88 --sorted_result
89 SELECT * FROM t1;
90 # EXPLAIN SELECT * FROM t1 WHERE c1 IN (2155,2009,1975);
91 DELETE FROM t1 WHERE c1 IN (2155,2009,1975) LIMIT 2;
92 --sorted_result
93 SELECT * FROM t1;
94
95 # Delete using eq_ref
96 # EXPLAIN SELECT * FROM t1,t2 WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;
97 DELETE t1,t2 FROM t1,t2 WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;
98
99 --sorted_result
100 SELECT * FROM t1;
101 --sorted_result
102 SELECT * FROM t2;
103 --sorted_result
104 SELECT * FROM t3;
105 --sorted_result
106 SELECT * FROM t4;
107
108 #Drop tables
109 DROP TABLE IF EXISTS t1,t2,t3,t4;
110