Debian Codegraph @

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

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