Debian Codegraph @

mysql-test/suite/engines/iuds/r/delete_year.result

raw
1 DROP TABLE IF EXISTS t1,t2,t3,t4;
2 CREATE TABLE t1(c1 YEAR NOT NULL,c2 YEAR, PRIMARY KEY(c1));
3 CREATE TABLE t2(c1 YEAR NOT NULL, c2 YEAR, UNIQUE INDEX idx(c1,c2));
4 CREATE TABLE t3(c1 YEAR NOT NULL,c2 YEAR, PRIMARY KEY(c1));
5 CREATE TABLE t4(c1 YEAR, c2 YEAR, UNIQUE INDEX idx(c1,c2));
6 INSERT INTO t1 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
7 INSERT INTO t2 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
8 INSERT INTO t3 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
9 INSERT INTO t4 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
10 INSERT INTO t1 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
11 INSERT INTO t2 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
12 INSERT INTO t3 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
13 INSERT INTO t4 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
14 INSERT INTO t1 VALUES (04,04),(64,64),(69,69),(97,97);
15 INSERT INTO t2 VALUES (04,04),(64,64),(69,69),(97,97);
16 INSERT INTO t3 VALUES (04,04),(64,64),(69,69),(97,97);
17 INSERT INTO t4 VALUES (04,04),(64,64),(69,69),(97,97);
18 INSERT INTO t1 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
19 INSERT INTO t2 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
20 INSERT INTO t3 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
21 INSERT INTO t4 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
22 INSERT INTO t1 VALUES ('09',null),('61',null),('79',null),('96',null);
23 INSERT INTO t2 VALUES ('09',null),('61',null),('79',null),('96',null);
24 INSERT INTO t3 VALUES ('09',null),('61',null),('79',null),('96',null);
25 INSERT INTO t4 VALUES ('09',null),('61',null),('79',null),('96',null);
26 SELECT * FROM t1;
27 c1 c2
28 1901 1901
29 1902 1902
30 1970 1970
31 1971 1971
32 1975 1975
33 1979 NULL
34 1995 1995
35 1996 NULL
36 1997 1997
37 1998 1998
38 1999 1999
39 2000 2000
40 2001 2001
41 2004 2004
42 2005 2005
43 2009 NULL
44 2061 NULL
45 2064 2064
46 2065 2065
47 2069 2069
48 2154 2154
49 2155 2155
50 SELECT * FROM t2;
51 c1 c2
52 1901 1901
53 1902 1902
54 1970 1970
55 1971 1971
56 1975 1975
57 1979 NULL
58 1995 1995
59 1996 NULL
60 1997 1997
61 1998 1998
62 1999 1999
63 2000 2000
64 2001 2001
65 2004 2004
66 2005 2005
67 2009 NULL
68 2061 NULL
69 2064 2064
70 2065 2065
71 2069 2069
72 2154 2154
73 2155 2155
74 SELECT * FROM t3;
75 c1 c2
76 1901 1901
77 1902 1902
78 1970 1970
79 1971 1971
80 1975 1975
81 1979 NULL
82 1995 1995
83 1996 NULL
84 1997 1997
85 1998 1998
86 1999 1999
87 2000 2000
88 2001 2001
89 2004 2004
90 2005 2005
91 2009 NULL
92 2061 NULL
93 2064 2064
94 2065 2065
95 2069 2069
96 2154 2154
97 2155 2155
98 SELECT * FROM t4;
99 c1 c2
100 1901 1901
101 1902 1902
102 1970 1970
103 1971 1971
104 1975 1975
105 1979 NULL
106 1995 1995
107 1996 NULL
108 1997 1997
109 1998 1998
110 1999 1999
111 2000 2000
112 2001 2001
113 2004 2004
114 2005 2005
115 2009 NULL
116 2061 NULL
117 2064 2064
118 2065 2065
119 2069 2069
120 2154 2154
121 2155 2155
122 SELECT c1 FROM t1 WHERE c1='2005';
123 c1
124 2005
125 DELETE FROM t1 WHERE c1='2005';
126 SELECT c1 FROM t1;
127 c1
128 1901
129 1902
130 1970
131 1971
132 1975
133 1979
134 1995
135 1996
136 1997
137 1998
138 1999
139 2000
140 2001
141 2004
142 2009
143 2061
144 2064
145 2065
146 2069
147 2154
148 2155
149 SELECT c2 FROM t2 WHERE c2=null;
150 c2
151 DELETE FROM t2 WHERE c2=null LIMIT 2;
152 SELECT c2 FROM t2;
153 c2
154 1901
155 1902
156 1970
157 1971
158 1975
159 1995
160 1997
161 1998
162 1999
163 2000
164 2001
165 2004
166 2005
167 2064
168 2065
169 2069
170 2154
171 2155
172 NULL
173 NULL
174 NULL
175 NULL
176 SELECT c1 FROM t4 WHERE c1 < '55';
177 c1
178 1901
179 1902
180 1970
181 1971
182 1975
183 1979
184 1995
185 1996
186 1997
187 1998
188 1999
189 2000
190 2001
191 2004
192 2005
193 2009
194 DELETE FROM t4 WHERE c1 < '65' ORDER BY c1 LIMIT 5;
195 SELECT c1 FROM t4;
196 c1
197 1979
198 1995
199 1996
200 1997
201 1998
202 1999
203 2000
204 2001
205 2004
206 2005
207 2009
208 2061
209 2064
210 2065
211 2069
212 2154
213 2155
214 DELETE FROM t2 WHERE c1=2009 AND c2=null;
215 SELECT c2 FROM t2;
216 c2
217 1901
218 1902
219 1970
220 1971
221 1975
222 1995
223 1997
224 1998
225 1999
226 2000
227 2001
228 2004
229 2005
230 2064
231 2065
232 2069
233 2154
234 2155
235 NULL
236 NULL
237 NULL
238 NULL
239 DELETE FROM t1 WHERE c1=1979 OR c1=2154;
240 SELECT c2 FROM t1;
241 c2
242 1901
243 1902
244 1970
245 1971
246 1975
247 1995
248 1997
249 1998
250 1999
251 2000
252 2001
253 2004
254 2064
255 2065
256 2069
257 2155
258 NULL
259 NULL
260 NULL
261 DELETE FROM t4 WHERE c2 IN (01,54,65,69,null) LIMIT 2;
262 SELECT c1 FROM t4;
263 c1
264 1979
265 1995
266 1996
267 1997
268 1998
269 1999
270 2000
271 2001
272 2004
273 2005
274 2009
275 2061
276 2064
277 2065
278 2069
279 2154
280 2155
281 DELETE t3,t4 FROM t3,t4 WHERE t3.c1=61 AND t4.c2=4;
282 DELETE FROM t1 WHERE c1=2064 AND c2=2064;
283 SELECT * FROM t1;
284 c1 c2
285 1901 1901
286 1902 1902
287 1970 1970
288 1971 1971
289 1975 1975
290 1995 1995
291 1996 NULL
292 1997 1997
293 1998 1998
294 1999 1999
295 2000 2000
296 2001 2001
297 2004 2004
298 2009 NULL
299 2061 NULL
300 2065 2065
301 2069 2069
302 2155 2155
303 DELETE FROM t1 WHERE c1 BETWEEN 2000 AND 2010 LIMIT 2;
304 SELECT * FROM t1;
305 c1 c2
306 1901 1901
307 1902 1902
308 1970 1970
309 1971 1971
310 1975 1975
311 1995 1995
312 1996 NULL
313 1997 1997
314 1998 1998
315 1999 1999
316 2004 2004
317 2009 NULL
318 2061 NULL
319 2065 2065
320 2069 2069
321 2155 2155
322 DELETE FROM t1 WHERE c1 IN (2155,2009,1975) LIMIT 2;
323 SELECT * FROM t1;
324 c1 c2
325 1901 1901
326 1902 1902
327 1970 1970
328 1971 1971
329 1995 1995
330 1996 NULL
331 1997 1997
332 1998 1998
333 1999 1999
334 2004 2004
335 2061 NULL
336 2065 2065
337 2069 2069
338 2155 2155
339 DELETE t1,t2 FROM t1,t2 WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;
340 SELECT * FROM t1;
341 c1 c2
342 1996 NULL
343 2061 NULL
344 SELECT * FROM t2;
345 c1 c2
346 1975 1975
347 1979 NULL
348 1996 NULL
349 2000 2000
350 2001 2001
351 2005 2005
352 2009 NULL
353 2061 NULL
354 2064 2064
355 2154 2154
356 SELECT * FROM t3;
357 c1 c2
358 1901 1901
359 1902 1902
360 1970 1970
361 1971 1971
362 1975 1975
363 1979 NULL
364 1995 1995
365 1996 NULL
366 1997 1997
367 1998 1998
368 1999 1999
369 2000 2000
370 2001 2001
371 2004 2004
372 2005 2005
373 2009 NULL
374 2064 2064
375 2065 2065
376 2069 2069
377 2154 2154
378 2155 2155
379 SELECT * FROM t4;
380 c1 c2
381 1979 NULL
382 1995 1995
383 1996 NULL
384 1997 1997
385 1998 1998
386 1999 1999
387 2000 2000
388 2001 2001
389 2005 2005
390 2009 NULL
391 2061 NULL
392 2064 2064
393 2065 2065
394 2069 2069
395 2154 2154
396 2155 2155
397 DROP TABLE IF EXISTS t1,t2,t3,t4;