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