[tomato.git] / release / src / router / mysql / mysql-test / suite / engines / iuds / r / update_delete_calendar.result
| blob | e2dd83e03b472678deaa551dd2af6c318e2da42e |
1 DROP TABLE IF EXISTS t1,t2;
2 CREATE TABLE t1(c1 DATE NOT NULL PRIMARY KEY, c2 DATE NULL, c3 INT, INDEX idx2(c2));
3 INSERT INTO t1 VALUE('1998-01-01','10-01-01',1),('2001-01-01','70-01-01',2),('2001-01-03','2001-01-04 09:34:00',3);
4 Warnings:
5 Note 1265 Data truncated for column 'c2' at row 3
6 SELECT * FROM t1;
7 c1 c2 c3
8 1998-01-01 2010-01-01 1
9 2001-01-01 1970-01-01 2
10 2001-01-03 2001-01-04 3
11 UPDATE t1 SET c1='69-02-02' WHERE c2='10-01-01';
12 SELECT year(c1),month(c1),day(c1),year(c2) FROM t1 WHERE c2='10-01-01';
13 year(c1) month(c1) day(c1) year(c2)
14 2069 2 2 2010
15 UPDATE t1 SET c1='1969-02-02',c2='1910-02-02' WHERE c2='10-01-01';
16 SELECT * FROM t1 WHERE c1='1969-02-02' AND c2='1910-02-02';
17 c1 c2 c3
18 1969-02-02 1910-02-02 1
19 UPDATE t1 SET c1='01-02-02',c2='2070-02-02' WHERE c2='70-01-01';
20 SELECT year(c1),c2 FROM t1 WHERE c1='01-02-02' AND c2='2070-02-02';
21 year(c1) c2
22 2001 2070-02-02
23 UPDATE t1 SET c1='2001-01-03 08:30:00',c2='2001-01-05' WHERE c2='2001-01-04';
24 Warnings:
25 Note 1265 Data truncated for column 'c1' at row 1
26 SELECT c1,hour(c1),minute(c1),second(c1) FROM t1 WHERE c2='2001-01-05';
27 c1 hour(c1) minute(c1) second(c1)
28 2001-01-03 0 0 0
29 INSERT INTO t1 VALUES('2009-02-25','09-02-27',4);
30 SET TIMESTAMP=1235553613;
31 UPDATE t1 SET c2=NOW() WHERE c1 >=CURDATE();
32 Warnings:
33 Note 1265 Data truncated for column 'c2' at row 1
34 UPDATE t1 SET c1=DATE_ADD(CURDATE(),INTERVAL '1 1:1:1' DAY_SECOND) WHERE c2=CURDATE();
35 Warnings:
36 Note 1265 Data truncated for column 'c1' at row 1
37 SELECT * FROM t1;
38 c1 c2 c3
39 1969-02-02 1910-02-02 1
40 2001-01-03 2001-01-05 3
41 2001-02-02 2070-02-02 2
42 2009-02-26 2009-02-25 4
43 INSERT INTO t1 VALUES('2001-01-06','2001-01-07',5),('2001-01-08',NULL,6);
44 UPDATE t1 SET c2=NULL WHERE c1='2001-01-06';
45 SELECT * FROM t1 WHERE c2 IS NULL;
46 c1 c2 c3
47 2001-01-06 NULL 5
48 2001-01-08 NULL 6
49 UPDATE t1 SET c2='2001-01-10' WHERE c2 IS NULL;
50 SELECT * FROM t1 WHERE c2='2001-01-10';
51 c1 c2 c3
52 2001-01-06 2001-01-10 5
53 2001-01-08 2001-01-10 6
54 INSERT INTO t1 VALUES('2001-01-11','2001-01-11',7),('2001-01-12','2001-01-12',8),('2001-01-13','2001-01-13',9),('2001-01-14','2001-01-14',10),('2001-01-15','2001-01-15',11),('2001-01-16','2001-01-16',12);
55 UPDATE t1 SET c1='10:45:15',c2='2009-01-32' WHERE c1='2001-01-11';
56 Warnings:
57 Warning 1265 Data truncated for column 'c1' at row 1
58 Warning 1265 Data truncated for column 'c2' at row 1
59 UPDATE t1 SET c2='2009-02-30' WHERE c2='2001-01-12';
60 Warnings:
61 Warning 1265 Data truncated for column 'c2' at row 1
62 UPDATE t1 SET c2='2009-04-31' WHERE c2='2001-01-13';
63 Warnings:
64 Warning 1265 Data truncated for column 'c2' at row 1
65 UPDATE t1 SET c2='2009-06-31' WHERE c2='2001-01-14';
66 Warnings:
67 Warning 1265 Data truncated for column 'c2' at row 1
68 UPDATE t1 SET c2='2009-09-31' WHERE c2='2001-01-15';
69 Warnings:
70 Warning 1265 Data truncated for column 'c2' at row 1
71 SELECT count(*) FROM t1 WHERE c2='10:45:15' OR c2='2009-01-32' OR c2='2009-02-30' OR c2='2009-04-31' OR c2='2009-06-31' OR c2='2009-09-31' /* returns 0 */;
72 count(*)
73 0
74 Warnings:
75 Warning 1292 Incorrect date value: '10:45:15' for column 'c2' at row 1
76 Warning 1292 Incorrect date value: '2009-01-32' for column 'c2' at row 1
77 Warning 1292 Incorrect date value: '10:45:15' for column 'c2' at row 1
78 Warning 1292 Incorrect date value: '2009-01-32' for column 'c2' at row 1
79 Warning 1292 Incorrect date value: '10:45:15' for column 'c2' at row 1
80 Warning 1292 Incorrect date value: '2009-01-32' for column 'c2' at row 1
81 UPDATE t1 SET c2='2009-11-31' WHERE c2='2001-01-16';
82 Warnings:
83 Warning 1265 Data truncated for column 'c2' at row 1
84 SELECT * FROM t1 WHERE c2='0000-00-00';
85 c1 c2 c3
86 0000-00-00 0000-00-00 7
87 2001-01-12 0000-00-00 8
88 2001-01-13 0000-00-00 9
89 2001-01-14 0000-00-00 10
90 2001-01-15 0000-00-00 11
91 2001-01-16 0000-00-00 12
92 SET SQL_MODE=ALLOW_INVALID_DATES;
93 INSERT INTO t1 VALUES('2001-01-27','2001-01-28',19);
94 UPDATE t1 SET c1='2009-04-31',c2='2009-02-30' WHERE c3=19;
95 SELECT c1,c2,day(c1),day(c2) FROM t1 WHERE c3=19;
96 c1 c2 day(c1) day(c2)
97 2009-04-31 2009-02-30 31 30
98 SET SQL_MODE=DEFAULT;
99 INSERT INTO t1 VALUES('2001-01-17','0000-00-00',13),('2001-01-18','2001-01-18',14);
100 SET SQL_MODE=TRADITIONAL;
101 SELECT * FROM t1 WHERE c2='0000-00-00';
102 c1 c2 c3
103 0000-00-00 0000-00-00 7
104 2001-01-12 0000-00-00 8
105 2001-01-13 0000-00-00 9
106 2001-01-14 0000-00-00 10
107 2001-01-15 0000-00-00 11
108 2001-01-16 0000-00-00 12
109 2001-01-17 0000-00-00 13
110 Warnings:
111 Warning 1292 Incorrect date value: '0000-00-00' for column 'c2' at row 1
112 Warning 1292 Incorrect date value: '0000-00-00' for column 'c2' at row 1
113 UPDATE t1 SET c2='0000-00-00' WHERE c2='2001-01-18';
114 ERROR 22007: Incorrect date value: '0000-00-00' for column 'c2' at row 1
115 SET SQL_MODE=DEFAULT;
116 UPDATE t1 SET c2='0000-00-00' WHERE c2='2001-01-18';
117 UPDATE t1 SET c2='2001-01-18' WHERE c2='0000-00-00' AND c1='2001-01-17';
118 SELECT * FROM t1 WHERE c1='0000-00-00' OR c2='0000-00-00';
119 c1 c2 c3
120 0000-00-00 0000-00-00 7
121 2001-01-12 0000-00-00 8
122 2001-01-13 0000-00-00 9
123 2001-01-14 0000-00-00 10
124 2001-01-15 0000-00-00 11
125 2001-01-16 0000-00-00 12
126 2001-01-18 0000-00-00 14
127 INSERT INTO t1 VALUES('2001-01-19','2001-01-20',15);
128 UPDATE t1 SET c1='2010-00-01',c2='2010-10-00' WHERE c3=15;
129 SELECT *,month(c1),day(c2) from t1 WHERE c3=15;
130 c1 c2 c3 month(c1) day(c2)
131 2010-00-01 2010-10-00 15 0 0
132 INSERT INTO t1 VALUES('2001-01-21','2001-01-22',16);
133 UPDATE t1 SET c1='1000-01-01',c2='9999-12-31' WHERE c1='2001-01-21';
134 SELECT * FROM t1 WHERE c1='1000-01-01' AND c2='9999-12-31';
135 c1 c2 c3
136 1000-01-01 9999-12-31 16
137 INSERT INTO t1 VALUES('2001-01-23','2001-01-24',17),('2001-01-25','2001-01-26',18);
138 UPDATE t1 SET c1='2001-01-23' WHERE c1='2001-01-25';
139 ERROR 23000: Duplicate entry '2001-01-23' for key 'PRIMARY'
140 UPDATE IGNORE t1 SET c1='2001-01-23' WHERE c1='2001-01-25';
141 SELECT count(*) FROM t1 WHERE c1='2001-01-23';
142 count(*)
143 1
144 UPDATE t1 SET c1=NULL WHERE c2='2001-01-24' /* updates to 0000-00-00 */;
145 ERROR 23000: Duplicate entry '0000-00-00' for key 'PRIMARY'
146 UPDATE IGNORE t1 SET c1=NULL WHERE c2='2001-01-24';
147 Warnings:
148 Warning 1048 Column 'c1' cannot be null
149 SELECT * FROM t1 WHERE c1='0000-00-00';
150 c1 c2 c3
151 0000-00-00 0000-00-00 7
152 TRUNCATE TABLE t1;
153 CREATE TABLE t2(c1 DATE NOT NULL PRIMARY KEY, c2 DATE, c3 INT);
154 INSERT INTO t1 VALUES('2001-01-02','2001-01-01',1),('2001-01-03','2001-01-02',2);
155 INSERT INTO t2 VALUES('2001-01-02','2001-01-03',1),('2001-01-04','2001-01-05',2);
156 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
157 c1 c2 c3 c1 c2 c3
158 2001-01-02 2001-01-01 1 2001-01-02 2001-01-03 1
159 UPDATE t1,t2 SET t2.c1='2005-01-06',t1.c1='2005-01-06' WHERE t2.c1=t1.c1;
160 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
161 c1 c2 c3 c1 c2 c3
162 2005-01-06 2001-01-01 1 2005-01-06 2001-01-03 1
163 INSERT INTO t1 VALUES('2002-01-01','2002-01-02',3),('2002-01-04','2002-01-02',4);
164 INSERT INTO t2 VALUES('2002-01-02','2002-01-03',3),('2002-01-03','2002-01-04',4);
165 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
166 c1 c2 c3 c1 c2 c3
167 2002-01-01 2002-01-02 3 2002-01-02 2002-01-03 3
168 2002-01-04 2002-01-02 4 2002-01-02 2002-01-03 3
169 UPDATE t1,t2 SET t2.c1='2002-01-06',t1.c2='2002-01-06' WHERE t2.c1=t1.c2;
170 SELECT * FROM t1,t2 WHERE t2.c1='2002-01-06' AND t1.c2='2002-01-06';
171 c1 c2 c3 c1 c2 c3
172 2002-01-01 2002-01-06 3 2002-01-06 2002-01-03 3
173 2002-01-04 2002-01-06 4 2002-01-06 2002-01-03 3
174 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
175 c1 c2 c3 c1 c2 c3
176 2002-01-01 2002-01-06 3 2002-01-06 2002-01-03 3
177 2002-01-04 2002-01-06 4 2002-01-06 2002-01-03 3
178 DELETE FROM t1 WHERE c2='2002-01-06' AND c3=3;
179 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
180 c1 c2 c3 c1 c2 c3
181 2002-01-04 2002-01-06 4 2002-01-06 2002-01-03 3
182 DROP TABLE t2;
183 TRUNCATE TABLE t1;
184 ALTER TABLE t1 CHANGE c1 c1 DATE NOT NULL, DROP PRIMARY KEY;
185 ALTER TABLE t1 CHANGE c1 c1 DATE NOT NULL, ADD KEY(c1);
186 UPDATE t1 SET c1='2002-01-07' WHERE c2='2002-01-05';
187 SELECT * FROM t1 WHERE c1='2002-01-07';
188 c1 c2 c3
189 INSERT INTO t1 VALUES('2002-01-08','2002-01-09',4),('2002-01-10','2002-01-11',5),('2002-01-12','2002-01-13',6),('2002-01-14','2002-01-15',7),('2002-01-16','2002-01-17',8),('2002-01-18','2002-01-19',9),('2002-01-20','2002-01-21',10),('2002-01-22','2002-01-23',11),('2002-01-24','2002-01-25',12),('2002-01-26','2002-01-27',13),('2002-01-28','2002-01-29',14),('2002-01-30','2002-01-31',15),('2002-02-01','2002-02-02',16),('2002-02-03','2002-02-04',17),('2002-02-05','2002-02-06',18),('2002-02-07','2002-02-08',19),('2002-02-09','2002-02-10',20);
190 SELECT * FROM t1;
191 c1 c2 c3
192 2002-01-08 2002-01-09 4
193 2002-01-10 2002-01-11 5
194 2002-01-12 2002-01-13 6
195 2002-01-14 2002-01-15 7
196 2002-01-16 2002-01-17 8
197 2002-01-18 2002-01-19 9
198 2002-01-20 2002-01-21 10
199 2002-01-22 2002-01-23 11
200 2002-01-24 2002-01-25 12
201 2002-01-26 2002-01-27 13
202 2002-01-28 2002-01-29 14
203 2002-01-30 2002-01-31 15
204 2002-02-01 2002-02-02 16
205 2002-02-03 2002-02-04 17
206 2002-02-05 2002-02-06 18
207 2002-02-07 2002-02-08 19
208 2002-02-09 2002-02-10 20
209 UPDATE t1 SET c1='2003-01-01' WHERE c2 <> '2002-01-09' ORDER BY c2 LIMIT 2;
210 SELECT * FROM t1 WHERE c2 <> '2002-01-09' ORDER BY c2;
211 c1 c2 c3
212 2003-01-01 2002-01-11 5
213 2003-01-01 2002-01-13 6
214 2002-01-14 2002-01-15 7
215 2002-01-16 2002-01-17 8
216 2002-01-18 2002-01-19 9
217 2002-01-20 2002-01-21 10
218 2002-01-22 2002-01-23 11
219 2002-01-24 2002-01-25 12
220 2002-01-26 2002-01-27 13
221 2002-01-28 2002-01-29 14
222 2002-01-30 2002-01-31 15
223 2002-02-01 2002-02-02 16
224 2002-02-03 2002-02-04 17
225 2002-02-05 2002-02-06 18
226 2002-02-07 2002-02-08 19
227 2002-02-09 2002-02-10 20
228 UPDATE t1 SET c1='2003-01-01' WHERE c2 >= '2002-01-13' ORDER BY c2 DESC LIMIT 2;
229 SELECT * FROM t1 WHERE c2 >= '2002-01-13' ORDER BY c2 DESC;
230 c1 c2 c3
231 2003-01-01 2002-02-10 20
232 2003-01-01 2002-02-08 19
233 2002-02-05 2002-02-06 18
234 2002-02-03 2002-02-04 17
235 2002-02-01 2002-02-02 16
236 2002-01-30 2002-01-31 15
237 2002-01-28 2002-01-29 14
238 2002-01-26 2002-01-27 13
239 2002-01-24 2002-01-25 12
240 2002-01-22 2002-01-23 11
241 2002-01-20 2002-01-21 10
242 2002-01-18 2002-01-19 9
243 2002-01-16 2002-01-17 8
244 2002-01-14 2002-01-15 7
245 2003-01-01 2002-01-13 6
246 UPDATE t1 SET c1='2003-01-01' WHERE c2 <='2002-01-21' ORDER BY c2 LIMIT 2;
247 SELECT * FROM t1 WHERE c2 <='2002-01-21' ORDER BY c2;
248 c1 c2 c3
249 2003-01-01 2002-01-09 4
250 2003-01-01 2002-01-11 5
251 2003-01-01 2002-01-13 6
252 2002-01-14 2002-01-15 7
253 2002-01-16 2002-01-17 8
254 2002-01-18 2002-01-19 9
255 2002-01-20 2002-01-21 10
256 UPDATE t1 SET c1='2003-01-01' WHERE c2 <=> '2002-01-23' ORDER BY c2 DESC LIMIT 2;
257 SELECT * FROM t1 WHERE c2 <=> '2002-01-23' ORDER BY c2 DESC;
258 c1 c2 c3
259 2003-01-01 2002-01-23 11
260 UPDATE t1 SET c1='2003-01-01' WHERE c2 BETWEEN '2002-01-25' AND '2002-01-29' ORDER BY c2 LIMIT 2;
261 SELECT * FROM t1 WHERE c2 BETWEEN '2002-01-25' AND '2002-01-29' ORDER BY c2;
262 c1 c2 c3
263 2003-01-01 2002-01-25 12
264 2003-01-01 2002-01-27 13
265 2002-01-28 2002-01-29 14
266 UPDATE t1 SET c1='2003-01-01' WHERE c2 IN('2002-01-29','2002-01-31') ORDER BY c2 DESC LIMIT 2;
267 SELECT * FROM t1 WHERE c2 IN('2002-01-29','2002-01-31') ORDER BY c2 DESC;
268 c1 c2 c3
269 2003-01-01 2002-01-31 15
270 2003-01-01 2002-01-29 14
271 UPDATE t1 SET c1='2003-01-01' WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
272 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
273 c1 c2 c3
274 UPDATE t1 SET c1='2003-01-01' WHERE c2>= '2002-02-06' AND c1 < '2002-02-09' ORDER BY c2 LIMIT 2;
275 SELECT * FROM t1 WHERE c2>= '2002-02-06' AND c1 < '2002-02-09' ORDER BY c2;
276 c1 c2 c3
277 INSERT INTO t1 VALUES('2002-03-01','2002-03-01',1),('2002-03-03','2002-03-04',2),('2002-03-05','2002-03-06',3),('2005-01-01','2005-01-02',4),('2005-01-03','2005-01-04',5),('2005-01-05','2005-01-06',6),('2005-01-07','2005-01-08',7),('2005-01-09',NULL,8);
278 SELECT * FROM t1;
279 c1 c2 c3
280 2002-01-14 2002-01-15 7
281 2002-01-16 2002-01-17 8
282 2002-01-18 2002-01-19 9
283 2002-01-20 2002-01-21 10
284 2002-02-01 2002-02-02 16
285 2002-02-03 2002-02-04 17
286 2002-03-01 2002-03-01 1
287 2002-03-03 2002-03-04 2
288 2002-03-05 2002-03-06 3
289 2003-01-01 2002-01-09 4
290 2003-01-01 2002-01-11 5
291 2003-01-01 2002-01-13 6
292 2003-01-01 2002-01-23 11
293 2003-01-01 2002-01-25 12
294 2003-01-01 2002-01-27 13
295 2003-01-01 2002-01-29 14
296 2003-01-01 2002-01-31 15
297 2003-01-01 2002-02-06 18
298 2003-01-01 2002-02-08 19
299 2003-01-01 2002-02-10 20
300 2005-01-01 2005-01-02 4
301 2005-01-03 2005-01-04 5
302 2005-01-05 2005-01-06 6
303 2005-01-07 2005-01-08 7
304 2005-01-09 NULL 8
305 SELECT c1 FROM t1;
306 c1
307 2002-01-14
308 2002-01-16
309 2002-01-18
310 2002-01-20
311 2002-02-01
312 2002-02-03
313 2002-03-01
314 2002-03-03
315 2002-03-05
316 2003-01-01
317 2003-01-01
318 2003-01-01
319 2003-01-01
320 2003-01-01
321 2003-01-01
322 2003-01-01
323 2003-01-01
324 2003-01-01
325 2003-01-01
326 2003-01-01
327 2005-01-01
328 2005-01-03
329 2005-01-05
330 2005-01-07
331 2005-01-09
332 SELECT c1 FROM t1 ORDER BY c1 DESC;
333 c1
334 2005-01-09
335 2005-01-07
336 2005-01-05
337 2005-01-03
338 2005-01-01
339 2003-01-01
340 2003-01-01
341 2003-01-01
342 2003-01-01
343 2003-01-01
344 2003-01-01
345 2003-01-01
346 2003-01-01
347 2003-01-01
348 2003-01-01
349 2003-01-01
350 2002-03-05
351 2002-03-03
352 2002-03-01
353 2002-02-03
354 2002-02-01
355 2002-01-20
356 2002-01-18
357 2002-01-16
358 2002-01-14
359 SELECT * FROM t1 WHERE c1='2003-01-01';
360 c1 c2 c3
361 2003-01-01 2002-01-09 4
362 2003-01-01 2002-01-11 5
363 2003-01-01 2002-01-13 6
364 2003-01-01 2002-01-23 11
365 2003-01-01 2002-01-25 12
366 2003-01-01 2002-01-27 13
367 2003-01-01 2002-01-29 14
368 2003-01-01 2002-01-31 15
369 2003-01-01 2002-02-06 18
370 2003-01-01 2002-02-08 19
371 2003-01-01 2002-02-10 20
372 SELECT * FROM t1 WHERE c1 <> '2003-01-01' ORDER BY c1,c2 LIMIT 2;
373 c1 c2 c3
374 2002-01-14 2002-01-15 7
375 2002-01-16 2002-01-17 8
376 SELECT * FROM t1 WHERE c1 <> '2003-01-01' ORDER BY c1,c2 DESC;
377 c1 c2 c3
378 2002-01-14 2002-01-15 7
379 2002-01-16 2002-01-17 8
380 2002-01-18 2002-01-19 9
381 2002-01-20 2002-01-21 10
382 2002-02-01 2002-02-02 16
383 2002-02-03 2002-02-04 17
384 2002-03-01 2002-03-01 1
385 2002-03-03 2002-03-04 2
386 2002-03-05 2002-03-06 3
387 2005-01-01 2005-01-02 4
388 2005-01-03 2005-01-04 5
389 2005-01-05 2005-01-06 6
390 2005-01-07 2005-01-08 7
391 2005-01-09 NULL 8
392 SELECT * FROM t1 WHERE c1 > '2003-01-01' ORDER BY c1,c2 DESC LIMIT 2;
393 c1 c2 c3
394 2005-01-01 2005-01-02 4
395 2005-01-03 2005-01-04 5
396 SELECT * FROM t1 WHERE c1 >= '2003-01-01' ORDER BY c1,c2;
397 c1 c2 c3
398 2003-01-01 2002-01-09 4
399 2003-01-01 2002-01-11 5
400 2003-01-01 2002-01-13 6
401 2003-01-01 2002-01-23 11
402 2003-01-01 2002-01-25 12
403 2003-01-01 2002-01-27 13
404 2003-01-01 2002-01-29 14
405 2003-01-01 2002-01-31 15
406 2003-01-01 2002-02-06 18
407 2003-01-01 2002-02-08 19
408 2003-01-01 2002-02-10 20
409 2005-01-01 2005-01-02 4
410 2005-01-03 2005-01-04 5
411 2005-01-05 2005-01-06 6
412 2005-01-07 2005-01-08 7
413 2005-01-09 NULL 8
414 SELECT * FROM t1 WHERE c1 < '2003-01-01' ORDER BY c1,c2 LIMIT 2;
415 c1 c2 c3
416 2002-01-14 2002-01-15 7
417 2002-01-16 2002-01-17 8
418 SELECT * FROM t1 WHERE c1 <= '2003-01-01' ORDER BY c1,c2 DESC;
419 c1 c2 c3
420 2002-01-14 2002-01-15 7
421 2002-01-16 2002-01-17 8
422 2002-01-18 2002-01-19 9
423 2002-01-20 2002-01-21 10
424 2002-02-01 2002-02-02 16
425 2002-02-03 2002-02-04 17
426 2002-03-01 2002-03-01 1
427 2002-03-03 2002-03-04 2
428 2002-03-05 2002-03-06 3
429 2003-01-01 2002-02-10 20
430 2003-01-01 2002-02-08 19
431 2003-01-01 2002-02-06 18
432 2003-01-01 2002-01-31 15
433 2003-01-01 2002-01-29 14
434 2003-01-01 2002-01-27 13
435 2003-01-01 2002-01-25 12
436 2003-01-01 2002-01-23 11
437 2003-01-01 2002-01-13 6
438 2003-01-01 2002-01-11 5
439 2003-01-01 2002-01-09 4
440 SELECT * FROM t1 WHERE c1 <=> '2003-01-01' ORDER BY c1,c2 DESC LIMIT 2;
441 c1 c2 c3
442 2003-01-01 2002-02-10 20
443 2003-01-01 2002-02-08 19
444 SELECT * FROM t1 WHERE c1 <=> '2003-01-01' ORDER BY c1,c2;
445 c1 c2 c3
446 2003-01-01 2002-01-09 4
447 2003-01-01 2002-01-11 5
448 2003-01-01 2002-01-13 6
449 2003-01-01 2002-01-23 11
450 2003-01-01 2002-01-25 12
451 2003-01-01 2002-01-27 13
452 2003-01-01 2002-01-29 14
453 2003-01-01 2002-01-31 15
454 2003-01-01 2002-02-06 18
455 2003-01-01 2002-02-08 19
456 2003-01-01 2002-02-10 20
457 SELECT * FROM t1 WHERE c1 BETWEEN '2002-03-01' AND '2003-01-01' ORDER BY c1,c2 LIMIT 2;
458 c1 c2 c3
459 2002-03-01 2002-03-01 1
460 2002-03-03 2002-03-04 2
461 SELECT * FROM t1 WHERE c1 BETWEEN '2002-03-01' AND '2003-01-01' ORDER BY c1,c2 DESC;
462 c1 c2 c3
463 2002-03-01 2002-03-01 1
464 2002-03-03 2002-03-04 2
465 2002-03-05 2002-03-06 3
466 2003-01-01 2002-02-10 20
467 2003-01-01 2002-02-08 19
468 2003-01-01 2002-02-06 18
469 2003-01-01 2002-01-31 15
470 2003-01-01 2002-01-29 14
471 2003-01-01 2002-01-27 13
472 2003-01-01 2002-01-25 12
473 2003-01-01 2002-01-23 11
474 2003-01-01 2002-01-13 6
475 2003-01-01 2002-01-11 5
476 2003-01-01 2002-01-09 4
477 SELECT * FROM t1 WHERE c1 IN('2003-01-01','2005-01-07') ORDER BY c1,c2 DESC LIMIT 2;
478 c1 c2 c3
479 2003-01-01 2002-02-10 20
480 2003-01-01 2002-02-08 19
481 SELECT * FROM t1 WHERE c1 IN('2003-01-01','2005-01-07') ORDER BY c1,c2;
482 c1 c2 c3
483 2003-01-01 2002-01-09 4
484 2003-01-01 2002-01-11 5
485 2003-01-01 2002-01-13 6
486 2003-01-01 2002-01-23 11
487 2003-01-01 2002-01-25 12
488 2003-01-01 2002-01-27 13
489 2003-01-01 2002-01-29 14
490 2003-01-01 2002-01-31 15
491 2003-01-01 2002-02-06 18
492 2003-01-01 2002-02-08 19
493 2003-01-01 2002-02-10 20
494 2005-01-07 2005-01-08 7
495 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
496 c1 c2 c3
497 2005-01-09 NULL 8
498 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
499 c1 c2 c3
500 2005-01-09 NULL 8
501 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
502 c1 c2 c3
503 2003-01-01 2002-01-09 4
504 2003-01-01 2002-01-11 5
505 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1;
506 c1 c2 c3
507 2003-01-01 2002-01-09 4
508 2003-01-01 2002-01-11 5
509 2003-01-01 2002-01-13 6
510 2002-01-14 2002-01-15 7
511 2002-01-16 2002-01-17 8
512 2002-01-18 2002-01-19 9
513 2002-01-20 2002-01-21 10
514 2003-01-01 2002-01-23 11
515 2003-01-01 2002-01-25 12
516 2003-01-01 2002-01-27 13
517 2003-01-01 2002-01-29 14
518 2003-01-01 2002-01-31 15
519 2002-02-01 2002-02-02 16
520 2002-02-03 2002-02-04 17
521 2003-01-01 2002-02-06 18
522 2003-01-01 2002-02-08 19
523 2003-01-01 2002-02-10 20
524 2002-03-01 2002-03-01 1
525 2002-03-03 2002-03-04 2
526 2002-03-05 2002-03-06 3
527 2005-01-01 2005-01-02 4
528 2005-01-03 2005-01-04 5
529 2005-01-05 2005-01-06 6
530 2005-01-07 2005-01-08 7
531 SELECT * FROM t1 WHERE c1>='2003-01-01' AND c1 <= '2005-01-07' ORDER BY c2,c1 DESC LIMIT 2;
532 c1 c2 c3
533 2003-01-01 2002-01-09 4
534 2003-01-01 2002-01-11 5
535 SELECT * FROM t1 WHERE c1>='2003-01-01' AND c1 <= '2005-01-07' ORDER BY c2,c1;
536 c1 c2 c3
537 2003-01-01 2002-01-09 4
538 2003-01-01 2002-01-11 5
539 2003-01-01 2002-01-13 6
540 2003-01-01 2002-01-23 11
541 2003-01-01 2002-01-25 12
542 2003-01-01 2002-01-27 13
543 2003-01-01 2002-01-29 14
544 2003-01-01 2002-01-31 15
545 2003-01-01 2002-02-06 18
546 2003-01-01 2002-02-08 19
547 2003-01-01 2002-02-10 20
548 2005-01-01 2005-01-02 4
549 2005-01-03 2005-01-04 5
550 2005-01-05 2005-01-06 6
551 2005-01-07 2005-01-08 7
552 SELECT * FROM t1 WHERE c1='2003-01-03' OR c2='2002-03-06';
553 c1 c2 c3
554 2002-03-05 2002-03-06 3
555 UPDATE t1 SET c1='2006-01-01' WHERE c1='2003-01-03' OR c2='2002-03-06';
556 SELECT * FROM t1 WHERE c1='2006-01-01' OR c2='2002-03-04';
557 c1 c2 c3
558 2002-03-03 2002-03-04 2
559 2006-01-01 2002-03-06 3
560 TRUNCATE TABLE t1;
561 ALTER TABLE t1 CHANGE c1 c1 DATE NOT NULL, ADD PRIMARY KEY(c1);
562 INSERT INTO t1 VALUES(NOW(),NOW(),3),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),4),(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),5),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),6);
563 Warnings:
564 Note 1265 Data truncated for column 'c1' at row 1
565 Note 1265 Data truncated for column 'c2' at row 1
566 Note 1265 Data truncated for column 'c1' at row 2
567 Note 1265 Data truncated for column 'c2' at row 2
568 Note 1265 Data truncated for column 'c1' at row 3
569 Note 1265 Data truncated for column 'c2' at row 3
570 Note 1265 Data truncated for column 'c1' at row 4
571 Note 1265 Data truncated for column 'c2' at row 4
572 INSERT INTO t1 VALUES('1000-01-01','1000-01-01',1),('9999-12-31','9999-12-31',2);
573 SELECT * FROM t1 WHERE c1 <='1000-01-01';
574 c1 c2 c3
575 1000-01-01 1000-01-01 1
576 DELETE FROM t1 WHERE c1 <='1000-01-01';
577 SELECT * FROM t1 WHERE c1 <='1000-01-01';
578 c1 c2 c3
579 SELECT * FROM t1 WHERE c1 >='9999-12-31';
580 c1 c2 c3
581 9999-12-31 9999-12-31 2
582 DELETE FROM t1 WHERE c1 >='9999-12-31';
583 SELECT * FROM t1 WHERE c1 >='9999-12-31';
584 c1 c2 c3
585 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
586 c1 c2 c3
587 2009-02-28 2009-02-28 6
588 2009-02-27 2009-02-27 5
589 DELETE FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC LIMIT 2;
590 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
591 c1 c2 c3
592 SELECT * FROM t1 WHERE c1 <> CURDATE() ORDER BY c1;
593 c1 c2 c3
594 2009-02-26 2009-02-26 4
595 DELETE FROM t1 WHERE c1 <> CURDATE() ORDER BY c1 LIMIT 2;
596 SELECT * FROM t1 WHERE c1 <> CURDATE() ORDER BY c1;
597 c1 c2 c3
598 INSERT INTO t1 VALUES('2001-01-01',NOW(),7),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),8),(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),9),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),10);
599 Warnings:
600 Note 1265 Data truncated for column 'c2' at row 1
601 Note 1265 Data truncated for column 'c1' at row 2
602 Note 1265 Data truncated for column 'c2' at row 2
603 Note 1265 Data truncated for column 'c1' at row 3
604 Note 1265 Data truncated for column 'c2' at row 3
605 Note 1265 Data truncated for column 'c1' at row 4
606 Note 1265 Data truncated for column 'c2' at row 4
607 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
608 c1 c2 c3
609 2001-01-01 2009-02-25 7
610 2009-02-25 2009-02-25 3
611 2009-02-26 2009-02-26 8
612 2009-02-27 2009-02-27 9
613 DELETE FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
614 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
615 c1 c2 c3
616 2009-02-26 2009-02-26 8
617 2009-02-27 2009-02-27 9
618 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
619 c1 c2 c3
620 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
621 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
622 c1 c2 c3
623 SELECT * FROM t1 WHERE c1 <=> ADDDATE(CURDATE(),'1 01:01:01') ORDER BY c1 DESC;
624 c1 c2 c3
625 2009-02-26 2009-02-26 8
626 Warnings:
627 Warning 1292 Truncated incorrect INTEGER value: '1 01:01:01'
628 Warning 1292 Truncated incorrect INTEGER value: '1 01:01:01'
629 DELETE FROM t1 WHERE c1 <=> ADDDATE(CURDATE(),'1 01:01:01') ORDER BY c1 DESC;
630 Warnings:
631 Warning 1292 Truncated incorrect INTEGER value: '1 01:01:01'
632 SELECT * FROM t1 WHERE c1 <=> ADDDATE(CURDATE(),'1 01:01:01') ORDER BY c1 DESC;
633 c1 c2 c3
634 Warnings:
635 Warning 1292 Truncated incorrect INTEGER value: '1 01:01:01'
636 INSERT INTO t1 VALUES('2001-01-01',NOW(),11),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),12),(ADDTIME(NOW(),'4 01:01:01'),NULL,13),(ADDTIME(NOW(),'5 01:01:01'),NULL,14);
637 Warnings:
638 Note 1265 Data truncated for column 'c2' at row 1
639 Note 1265 Data truncated for column 'c1' at row 2
640 Note 1265 Data truncated for column 'c2' at row 2
641 Note 1265 Data truncated for column 'c1' at row 3
642 Note 1265 Data truncated for column 'c1' at row 4
643 SELECT * FROM t1 WHERE c1 BETWEEN CURDATE() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
644 c1 c2 c3
645 2009-02-26 2009-02-26 12
646 2009-02-27 2009-02-27 9
647 DELETE FROM t1 WHERE c1 BETWEEN CURDATE() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
648 SELECT * FROM t1 WHERE c1 BETWEEN CURDATE() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
649 c1 c2 c3
650 SELECT * FROM t1 WHERE c1 IN(CURDATE(),ADDDATE(CURDATE(),'3 01:01:01')) ORDER BY c1 DESC;
651 c1 c2 c3
652 2009-02-28 2009-02-28 10
653 Warnings:
654 Warning 1292 Truncated incorrect INTEGER value: '3 01:01:01'
655 DELETE FROM t1 WHERE c1 IN(CURDATE(),ADDDATE(CURDATE(),'3 01:01:01')) ORDER BY c1 DESC LIMIT 2;
656 Warnings:
657 Warning 1292 Truncated incorrect INTEGER value: '3 01:01:01'
658 SELECT * FROM t1 WHERE c1 IN(CURDATE(),ADDDATE(CURDATE(),'3 01:01:01')) ORDER BY c1 DESC;
659 c1 c2 c3
660 Warnings:
661 Warning 1292 Truncated incorrect INTEGER value: '3 01:01:01'
662 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
663 c1 c2 c3
664 2009-03-01 NULL 13
665 2009-03-02 NULL 14
666 DELETE FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
667 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
668 c1 c2 c3
669 INSERT INTO t1 VALUES(ADDTIME(NOW(),'6 01:01:01'),ADDTIME(NOW(),'6 01:01:01'),15),(ADDTIME(NOW(),'7 01:01:01'),NULL,16),(ADDTIME(NOW(),'8 01:01:01'),ADDTIME(NOW(),'8 01:01:01'),17),(ADDTIME(NOW(),'9 01:01:01'),ADDTIME(NOW(),'9 01:01:01'),18),(ADDTIME(NOW(),'10 01:01:01'),ADDTIME(NOW(),'10 01:01:01'),19);
670 Warnings:
671 Note 1265 Data truncated for column 'c1' at row 1
672 Note 1265 Data truncated for column 'c2' at row 1
673 Note 1265 Data truncated for column 'c1' at row 2
674 Note 1265 Data truncated for column 'c1' at row 3
675 Note 1265 Data truncated for column 'c2' at row 3
676 Note 1265 Data truncated for column 'c1' at row 4
677 Note 1265 Data truncated for column 'c2' at row 4
678 Note 1265 Data truncated for column 'c1' at row 5
679 Note 1265 Data truncated for column 'c2' at row 5
680 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
681 c1 c2 c3
682 2001-01-01 2009-02-25 11
683 2009-03-03 2009-03-03 15
684 2009-03-05 2009-03-05 17
685 2009-03-06 2009-03-06 18
686 2009-03-07 2009-03-07 19
687 DELETE FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
688 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
689 c1 c2 c3
690 2009-03-05 2009-03-05 17
691 2009-03-06 2009-03-06 18
692 2009-03-07 2009-03-07 19
693 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 01:01:01') ORDER BY c1;
694 c1 c2 c3
695 2009-03-05 2009-03-05 17
696 DELETE FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 01:01:01') ORDER BY c1 LIMIT 2;
697 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 01:01:01') ORDER BY c1;
698 c1 c2 c3
699 SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
700 c1 c2 c3
701 2009-03-06 2009-03-06 18
702 2009-03-07 2009-03-07 19
703 Warning 1292 Truncated incorrect INTEGER value: '10 01:01:01'
704 Warning 1292 Truncated incorrect INTEGER value: '9 01:01:01'
705 Warnings:
706 DELETE FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
707 Warnings:
708 Warning 1292 Truncated incorrect INTEGER value: '9 01:01:01'
709 Warning 1292 Truncated incorrect INTEGER value: '10 01:01:01'
710 SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
711 c1 c2 c3
712 SELECT count(*) FROM t1;
713 count(*)
714 1
715 TRUNCATE TABLE t1;
716 SELECT count(*) FROM t1;
717 count(*)
718 0
719 CREATE TABLE t2(c1 DATE NOT NULL PRIMARY KEY, c2 DATE, c3 INT);
720 INSERT INTO t1 VALUES('2001-01-02','2001-01-01',1),('2001-01-03','2001-01-02',2);
721 INSERT INTO t2 VALUES('2001-01-02','2001-01-03',1),('2001-01-04','2001-01-05',2);
722 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
723 c1 c2 c3 c1 c2 c3
724 2001-01-02 2001-01-01 1 2001-01-02 2001-01-03 1
725 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c1;
726 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
727 c1 c2 c3 c1 c2 c3
728 TRUNCATE TABLE t1;
729 TRUNCATE TABLE t2;
730 INSERT INTO t1 VALUES('2001-01-01','2001-01-02',1),('2001-01-03','2001-01-02',2);
731 INSERT INTO t2 VALUES('2001-01-02','2001-01-03',1),('2001-01-04','2001-01-05',2);
732 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
733 c1 c2 c3 c1 c2 c3
734 2001-01-01 2001-01-02 1 2001-01-02 2001-01-03 1
735 2001-01-03 2001-01-02 2 2001-01-02 2001-01-03 1
736 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
737 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
738 c1 c2 c3 c1 c2 c3
739 DROP TABLE t1,t2;
740 CREATE TABLE t1(c1 DATETIME NOT NULL PRIMARY KEY, c2 DATETIME NULL, c3 INT, INDEX idx2(c2));
741 INSERT INTO t1 VALUE('1998-01-01 09:23:45','10-01-01 14:12:33',1);
742 UPDATE t1 SET c1='2001-01-01' WHERE c2='10-01-01 14:12:33';
743 SELECT c1,hour(c1),minute(c1),second(c1) FROM t1 WHERE c2='10-01-01 14:12:33';
744 c1 hour(c1) minute(c1) second(c1)
745 2001-01-01 00:00:00 0 0 0
746 INSERT INTO t1 VALUES('2009-01-01 08:42:42','2009-01-02 23:59:59',2);
747 UPDATE t1 SET c1='2009-01-01 1:2:3',c2='2009-01-02 23:59:59.999999' WHERE c2='2009-01-02 23:59:59';
748 SELECT c1,ADDTIME(c2,'1 1:1:1.000002') FROM t1 WHERE c3=2;
749 c1 ADDTIME(c2,'1 1:1:1.000002')
750 2009-01-01 01:02:03 2009-01-04 01:01:00.000002
751 INSERT INTO t1 VALUES('2009-02-25 15:45','09-02-27 15:46',3);
752 SET TIMESTAMP=1235553613;
753 UPDATE t1 SET c2=NOW() WHERE c1 >=CURDATE();
754 UPDATE t1 SET c1=DATE_ADD(CURDATE(),INTERVAL '1 1:1:1' DAY_SECOND) WHERE c2=CURRENT_TIMESTAMP();
755 SELECT * FROM t1;
756 c1 c2 c3
757 2001-01-01 00:00:00 2010-01-01 14:12:33 1
758 2009-01-01 01:02:03 2009-01-02 23:59:59 2
759 2009-02-26 01:01:01 2009-02-25 12:20:13 3
760 INSERT INTO t1 VALUES('2001-01-06','2001-01-07 09:30:00',4),('2001-01-08',NULL,5);
761 SELECT * FROM t1 WHERE c2 IS NULL;
762 c1 c2 c3
763 2001-01-08 00:00:00 NULL 5
764 UPDATE t1 SET c2=NULL WHERE c1='2001-01-06 00:00:00';
765 SELECT * FROM t1 WHERE c2 IS NULL;
766 c1 c2 c3
767 2001-01-06 00:00:00 NULL 4
768 2001-01-08 00:00:00 NULL 5
769 UPDATE t1 SET c2='2001-01-10 00:00:00' WHERE c2 IS NULL;
770 SELECT * FROM t1 WHERE c2='2001-01-10';
771 c1 c2 c3
772 2001-01-06 00:00:00 2001-01-10 00:00:00 4
773 2001-01-08 00:00:00 2001-01-10 00:00:00 5
774 INSERT INTO t1 VALUES('2001-01-09','2001-01-10',6),('2001-01-11','2001-01-12',7);
775 UPDATE t1 SET c1='2001-01-09 24:59:59',c2='2009-01-10 23:60:59' WHERE c1='2001-01-09';
776 Warnings:
777 Warning 1264 Out of range value for column 'c1' at row 1
778 Warning 1264 Out of range value for column 'c2' at row 1
779 UPDATE t1 SET c2='2001-01-11 23:59:60' WHERE c1='2001-01-11';
780 Warnings:
781 Warning 1264 Out of range value for column 'c2' at row 1
782 SELECT count(*) FROM t1 WHERE c1='2001-01-09 24:59:59' AND c2='2009-01-10 23:60:59';
783 count(*)
784 1
785 Warnings:
786 Warning 1292 Incorrect datetime value: '2001-01-09 24:59:59' for column 'c1' at row 1
787 Warning 1292 Incorrect datetime value: '2009-01-10 23:60:59' for column 'c2' at row 1
788 Warning 1292 Incorrect datetime value: '2001-01-09 24:59:59' for column 'c1' at row 1
789 Warning 1292 Truncated incorrect datetime value: '2001-01-09 24:59:59'
790 Warning 1292 Truncated incorrect datetime value: '2009-01-10 23:60:59'
791 SELECT count(*) FROM t1 WHERE c2='2001-01-11 23:59:60' /* returns 0 */;
792 count(*)
793 0
794 SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
795 c1 c2 c3
796 0000-00-00 00:00:00 0000-00-00 00:00:00 6
797 2001-01-11 00:00:00 0000-00-00 00:00:00 7
798 INSERT INTO t1 VALUES('2001-01-17','0000-00-00 00:00:00',8),('2001-01-18','2001-01-18 07:35',9);
799 SET SQL_MODE=TRADITIONAL;
800 SELECT * FROM t1 WHERE c2='0000-00-00 00:00:00';
801 c1 c2 c3
802 0000-00-00 00:00:00 0000-00-00 00:00:00 6
803 2001-01-11 00:00:00 0000-00-00 00:00:00 7
804 2001-01-17 00:00:00 0000-00-00 00:00:00 8
805 Warnings:
806 Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
807 Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
808 UPDATE t1 SET c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
809 ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
810 SET SQL_MODE=DEFAULT;
811 UPDATE t1 SET c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
812 UPDATE t1 SET c2='2001-01-17 12:34:00' WHERE c2='0000-00-00 00:00:00' AND c1='2001-01-17';
813 SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
814 c1 c2 c3
815 0000-00-00 00:00:00 0000-00-00 00:00:00 6
816 2001-01-11 00:00:00 0000-00-00 00:00:00 7
817 2001-01-18 00:00:00 0000-00-00 00:00:00 9
818 SELECT * FROM t1 WHERE c2='2001-01-17 12:34:00';
819 c1 c2 c3
820 2001-01-17 00:00:00 2001-01-17 12:34:00 8
821 INSERT INTO t1 VALUES('2001-01-21 05:43:43','2001-01-22 06:53:53',10);
822 UPDATE t1 SET c1='1000-01-01 00:00:00',c2='9999-12-31 23:59:59' WHERE c1='2001-01-21 05:43:43';
823 SELECT * FROM t1 WHERE c1='1000-01-01 00:00:00' AND c2='9999-12-31 23:59:59';
824 c1 c2 c3
825 1000-01-01 00:00:00 9999-12-31 23:59:59 10
826 INSERT INTO t1 VALUES('2001-01-23 02:33','2001-01-24 03:33',11),('2001-01-25 04:33','2001-01-26 05:33',12);
827 UPDATE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
828 ERROR 23000: Duplicate entry '2001-01-23 02:33:00' for key 'PRIMARY'
829 UPDATE IGNORE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
830 SELECT count(*) FROM t1 WHERE c1='2001-01-23 02:33';
831 count(*)
832 1
833 UPDATE t1 SET c1=NULL WHERE c2='2001-01-24 03:33' /* updates to 0000-00-00 00:00:00 */;
834 ERROR 23000: Duplicate entry '0000-00-00 00:00:00' for key 'PRIMARY'
835 UPDATE IGNORE t1 SET c1=NULL WHERE c2='2001-01-24 03:33';
836 Warnings:
837 Warning 1048 Column 'c1' cannot be null
838 SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00';
839 c1 c2 c3
840 0000-00-00 00:00:00 0000-00-00 00:00:00 6
841 TRUNCATE TABLE t1;
842 CREATE TABLE t2(c1 DATETIME NOT NULL PRIMARY KEY, c2 DATETIME, c3 INT);
843 INSERT INTO t1 VALUES('2001-01-02 2:30:45','2001-01-01 1:30',1),('2001-01-03 3:30','2001-01-02 2:30:45',2);
844 INSERT INTO t2 VALUES('2001-01-02 2:30:45','2001-01-03 3:30',1),('2001-01-04 4:30','2001-01-05 5:30',2);
845 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
846 c1 c2 c3 c1 c2 c3
847 2001-01-02 02:30:45 2001-01-01 01:30:00 1 2001-01-02 02:30:45 2001-01-03 03:30:00 1
848 UPDATE t1,t2 SET t2.c1='2005-01-06 2:30:50',t1.c1='2005-01-06 2:30:50' WHERE t2.c1=t1.c1;
849 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
850 c1 c2 c3 c1 c2 c3
851 2005-01-06 02:30:50 2001-01-01 01:30:00 1 2005-01-06 02:30:50 2001-01-03 03:30:00 1
852 INSERT INTO t1 VALUES('2002-01-01 3:30','2002-01-02 2:30:45',3),('2002-01-04 4:30','2002-01-02 2:30:45',4);
853 INSERT INTO t2 VALUES('2002-01-02 2:30:45','2002-01-03 3:30',3),('2002-01-03 3:30','2002-01-04 4:30',4);
854 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
855 c1 c2 c3 c1 c2 c3
856 2002-01-01 03:30:00 2002-01-02 02:30:45 3 2002-01-02 02:30:45 2002-01-03 03:30:00 3
857 2002-01-04 04:30:00 2002-01-02 02:30:45 4 2002-01-02 02:30:45 2002-01-03 03:30:00 3
858 UPDATE t1,t2 SET t2.c1='2002-01-06 2:30:50',t1.c2='2002-01-06 2:30:50' WHERE t2.c1=t1.c2;
859 SELECT * FROM t1,t2 WHERE t2.c1='2002-01-06 2:30:50' AND t1.c2='2002-01-06 2:30:50';
860 c1 c2 c3 c1 c2 c3
861 2002-01-01 03:30:00 2002-01-06 02:30:50 3 2002-01-06 02:30:50 2002-01-03 03:30:00 3
862 2002-01-04 04:30:00 2002-01-06 02:30:50 4 2002-01-06 02:30:50 2002-01-03 03:30:00 3
863 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
864 c1 c2 c3 c1 c2 c3
865 2002-01-01 03:30:00 2002-01-06 02:30:50 3 2002-01-06 02:30:50 2002-01-03 03:30:00 3
866 2002-01-04 04:30:00 2002-01-06 02:30:50 4 2002-01-06 02:30:50 2002-01-03 03:30:00 3
867 DELETE FROM t1 WHERE c2='2002-01-06 2:30:50' AND c3=3;
868 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
869 c1 c2 c3 c1 c2 c3
870 2002-01-04 04:30:00 2002-01-06 02:30:50 4 2002-01-06 02:30:50 2002-01-03 03:30:00 3
871 DROP TABLE t2;
872 TRUNCATE TABLE t1;
873 ALTER TABLE t1 CHANGE c1 c1 DATETIME NOT NULL, DROP PRIMARY KEY;
874 ALTER TABLE t1 CHANGE c1 c1 DATETIME NOT NULL, ADD KEY(c1);
875 UPDATE t1 SET c1='2002-01-07 7:30' WHERE c2='2002-01-05 5:30';
876 SELECT * FROM t1 WHERE c1='2002-01-07 7:30';
877 c1 c2 c3
878 INSERT INTO t1 VALUES('2002-01-08 1:30','2002-01-09 1:30',4),('2002-01-08 1:30:5','2002-01-09 1:30:5',5),('2002-01-08 1:30:10','2002-01-09 1:30:10',6),('2002-01-08 1:30:15','2002-01-09 1:30:15',7),('2002-01-08 1:30:20','2002-01-09 1:30:20',8),('2002-01-08 1:30:25','2002-01-09 1:30:25',9),('2002-01-08 1:30:30','2002-01-09 1:30:30',10),('2002-01-08 1:30:35','2002-01-09 1:30:35',11),('2002-01-08 1:30:40','2002-01-09 1:30:40',12),('2002-01-08 1:30:45','2002-01-09 1:30:45',13),('2002-01-08 1:30:50','2002-01-09 1:30:50',14),('2002-01-08 1:30:55','2002-01-09 1:30:55',15),('2002-01-08 1:30:59','2002-01-09 1:30:59',16),('2002-01-08 2:00','2002-01-09 2:00',17),('2002-01-08 2:00:05','2002-01-09 2:00:05',18),('2002-01-08 2:00:10','2002-01-09 2:00:10',19),('2002-01-08 2:00:15','2002-01-09 2:00:15',20);
879 SELECT * FROM t1;
880 c1 c2 c3
881 2002-01-08 01:30:00 2002-01-09 01:30:00 4
882 2002-01-08 01:30:05 2002-01-09 01:30:05 5
883 2002-01-08 01:30:10 2002-01-09 01:30:10 6
884 2002-01-08 01:30:15 2002-01-09 01:30:15 7
885 2002-01-08 01:30:20 2002-01-09 01:30:20 8
886 2002-01-08 01:30:25 2002-01-09 01:30:25 9
887 2002-01-08 01:30:30 2002-01-09 01:30:30 10
888 2002-01-08 01:30:35 2002-01-09 01:30:35 11
889 2002-01-08 01:30:40 2002-01-09 01:30:40 12
890 2002-01-08 01:30:45 2002-01-09 01:30:45 13
891 2002-01-08 01:30:50 2002-01-09 01:30:50 14
892 2002-01-08 01:30:55 2002-01-09 01:30:55 15
893 2002-01-08 01:30:59 2002-01-09 01:30:59 16
894 2002-01-08 02:00:00 2002-01-09 02:00:00 17
895 2002-01-08 02:00:05 2002-01-09 02:00:05 18
896 2002-01-08 02:00:10 2002-01-09 02:00:10 19
897 2002-01-08 02:00:15 2002-01-09 02:00:15 20
898 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2 LIMIT 2;
899 SELECT * FROM t1 WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2;
900 c1 c2 c3
901 2003-01-01 01:02:03 2002-01-09 01:30:05 5
902 2003-01-01 01:02:03 2002-01-09 01:30:10 6
903 2002-01-08 01:30:15 2002-01-09 01:30:15 7
904 2002-01-08 01:30:20 2002-01-09 01:30:20 8
905 2002-01-08 01:30:25 2002-01-09 01:30:25 9
906 2002-01-08 01:30:30 2002-01-09 01:30:30 10
907 2002-01-08 01:30:35 2002-01-09 01:30:35 11
908 2002-01-08 01:30:40 2002-01-09 01:30:40 12
909 2002-01-08 01:30:45 2002-01-09 01:30:45 13
910 2002-01-08 01:30:50 2002-01-09 01:30:50 14
911 2002-01-08 01:30:55 2002-01-09 01:30:55 15
912 2002-01-08 01:30:59 2002-01-09 01:30:59 16
913 2002-01-08 02:00:00 2002-01-09 02:00:00 17
914 2002-01-08 02:00:05 2002-01-09 02:00:05 18
915 2002-01-08 02:00:10 2002-01-09 02:00:10 19
916 2002-01-08 02:00:15 2002-01-09 02:00:15 20
917 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 >= '20020109013010' ORDER BY c2 DESC LIMIT 2;
918 SELECT * FROM t1 WHERE c2 >= '20020109013010' ORDER BY c2 DESC;
919 c1 c2 c3
920 2003-01-01 01:02:03 2002-01-09 02:00:15 20
921 2003-01-01 01:02:03 2002-01-09 02:00:10 19
922 2002-01-08 02:00:05 2002-01-09 02:00:05 18
923 2002-01-08 02:00:00 2002-01-09 02:00:00 17
924 2002-01-08 01:30:59 2002-01-09 01:30:59 16
925 2002-01-08 01:30:55 2002-01-09 01:30:55 15
926 2002-01-08 01:30:50 2002-01-09 01:30:50 14
927 2002-01-08 01:30:45 2002-01-09 01:30:45 13
928 2002-01-08 01:30:40 2002-01-09 01:30:40 12
929 2002-01-08 01:30:35 2002-01-09 01:30:35 11
930 2002-01-08 01:30:30 2002-01-09 01:30:30 10
931 2002-01-08 01:30:25 2002-01-09 01:30:25 9
932 2002-01-08 01:30:20 2002-01-09 01:30:20 8
933 2002-01-08 01:30:15 2002-01-09 01:30:15 7
934 2003-01-01 01:02:03 2002-01-09 01:30:10 6
935 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <='02-01-09 1:30:20' ORDER BY c2 LIMIT 2;
936 SELECT * FROM t1 WHERE c2 <='02-01-09 1:30:20' ORDER BY c2;
937 c1 c2 c3
938 2003-01-01 01:02:03 2002-01-09 01:30:00 4
939 2003-01-01 01:02:03 2002-01-09 01:30:05 5
940 2003-01-01 01:02:03 2002-01-09 01:30:10 6
941 2002-01-08 01:30:15 2002-01-09 01:30:15 7
942 2002-01-08 01:30:20 2002-01-09 01:30:20 8
943 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <=> '020109013030' ORDER BY c2 DESC LIMIT 2;
944 SELECT * FROM t1 WHERE c2 <=> '020109013030' ORDER BY c2 DESC;
945 c1 c2 c3
946 2003-01-01 01:02:03 2002-01-09 01:30:30 10
947 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 BETWEEN '20020109 01:30:40' AND '20020109 01:30:50' ORDER BY c2 LIMIT 2;
948 Warnings:
949 Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
950 Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
951 Warning 1292 Incorrect datetime value: '20020109 01:30:50' for column 'c2' at row 1
952 SELECT * FROM t1 WHERE c2 BETWEEN '20020109 01:30:40' AND '20020109 01:30:50' ORDER BY c2;
953 c1 c2 c3
954 Warnings:
955 Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
956 Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
957 Warning 1292 Incorrect datetime value: '20020109 01:30:50' for column 'c2' at row 1
958 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 IN('2002-01-09 1:30:55','2002-01-09 2:00') ORDER BY c2 DESC LIMIT 2;
959 SELECT * FROM t1 WHERE c2 IN('2002-01-09 1:30:55','2002-01-09 2:00') ORDER BY c2 DESC;
960 c1 c2 c3
961 2003-01-01 01:02:03 2002-01-09 02:00:00 17
962 2003-01-01 01:02:03 2002-01-09 01:30:55 15
963 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
964 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
965 c1 c2 c3
966 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2>= '2002-02-09 2:00:5' AND c1 < '2002-02-09 2:00:15' ORDER BY c2 LIMIT 2;
967 SELECT * FROM t1 WHERE c2>= '2002-02-09 2:00:5' AND c1 < '2002-02-09 2:00:15' ORDER BY c2;
968 c1 c2 c3
969 INSERT INTO t1 VALUES('20020301000001','20020302000001',1),('020303010030','020304010030',2),('050103000001','050104000001',3),('5-10-10 1:2:3','5-10-10 1:2:3',4),('0000-00-00 00:00:01',NULL,5);
970 SELECT * FROM t1;
971 c1 c2 c3
972 0000-00-00 00:00:01 NULL 5
973 0005-10-10 01:02:03 0005-10-10 01:02:03 4
974 2002-01-08 01:30:15 2002-01-09 01:30:15 7
975 2002-01-08 01:30:20 2002-01-09 01:30:20 8
976 2002-01-08 01:30:25 2002-01-09 01:30:25 9
977 2002-01-08 01:30:35 2002-01-09 01:30:35 11
978 2002-01-08 01:30:40 2002-01-09 01:30:40 12
979 2002-01-08 01:30:45 2002-01-09 01:30:45 13
980 2002-01-08 01:30:50 2002-01-09 01:30:50 14
981 2002-01-08 01:30:59 2002-01-09 01:30:59 16
982 2002-01-08 02:00:05 2002-01-09 02:00:05 18
983 2002-03-01 00:00:01 2002-03-02 00:00:01 1
984 2002-03-03 01:00:30 2002-03-04 01:00:30 2
985 2003-01-01 01:02:03 2002-01-09 01:30:00 4
986 2003-01-01 01:02:03 2002-01-09 01:30:05 5
987 2003-01-01 01:02:03 2002-01-09 01:30:10 6
988 2003-01-01 01:02:03 2002-01-09 01:30:30 10
989 2003-01-01 01:02:03 2002-01-09 01:30:55 15
990 2003-01-01 01:02:03 2002-01-09 02:00:00 17
991 2003-01-01 01:02:03 2002-01-09 02:00:10 19
992 2003-01-01 01:02:03 2002-01-09 02:00:15 20
993 2005-01-03 00:00:01 2005-01-04 00:00:01 3
994 SELECT c1 FROM t1;
995 c1
996 0000-00-00 00:00:01
997 0005-10-10 01:02:03
998 2002-01-08 01:30:15
999 2002-01-08 01:30:20
1000 2002-01-08 01:30:25
1001 2002-01-08 01:30:35
1002 2002-01-08 01:30:40
1003 2002-01-08 01:30:45
1004 2002-01-08 01:30:50
1005 2002-01-08 01:30:59
1006 2002-01-08 02:00:05
1007 2002-03-01 00:00:01
1008 2002-03-03 01:00:30
1009 2003-01-01 01:02:03
1010 2003-01-01 01:02:03
1011 2003-01-01 01:02:03
1012 2003-01-01 01:02:03
1013 2003-01-01 01:02:03
1014 2003-01-01 01:02:03
1015 2003-01-01 01:02:03
1016 2003-01-01 01:02:03
1017 2005-01-03 00:00:01
1018 SELECT c1 FROM t1 ORDER BY c1 DESC;
1019 c1
1020 2005-01-03 00:00:01
1021 2003-01-01 01:02:03
1022 2003-01-01 01:02:03
1023 2003-01-01 01:02:03
1024 2003-01-01 01:02:03
1025 2003-01-01 01:02:03
1026 2003-01-01 01:02:03
1027 2003-01-01 01:02:03
1028 2003-01-01 01:02:03
1029 2002-03-03 01:00:30
1030 2002-03-01 00:00:01
1031 2002-01-08 02:00:05
1032 2002-01-08 01:30:59
1033 2002-01-08 01:30:50
1034 2002-01-08 01:30:45
1035 2002-01-08 01:30:40
1036 2002-01-08 01:30:35
1037 2002-01-08 01:30:25
1038 2002-01-08 01:30:20
1039 2002-01-08 01:30:15
1040 0005-10-10 01:02:03
1041 0000-00-00 00:00:01
1042 SELECT * FROM t1 WHERE c1='2003-01-01 1:2:3';
1043 c1 c2 c3
1044 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1045 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1046 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1047 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1048 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1049 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1050 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1051 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1052 SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1053 c1 c2 c3
1054 0000-00-00 00:00:01 NULL 5
1055 0005-10-10 01:02:03 0005-10-10 01:02:03 4
1056 SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
1057 c1 c2 c3
1058 0000-00-00 00:00:01 NULL 5
1059 0005-10-10 01:02:03 0005-10-10 01:02:03 4
1060 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1061 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1062 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1063 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1064 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1065 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1066 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1067 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1068 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1069 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1070 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1071 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1072 SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2 DESC LIMIT 2;
1073 c1 c2 c3
1074 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1075 SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2;
1076 c1 c2 c3
1077 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1078 SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1079 c1 c2 c3
1080 0000-00-00 00:00:01 NULL 5
1081 0005-10-10 01:02:03 0005-10-10 01:02:03 4
1082 SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 DESC;
1083 c1 c2 c3
1084 0000-00-00 00:00:01 NULL 5
1085 0005-10-10 01:02:03 0005-10-10 01:02:03 4
1086 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1087 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1088 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1089 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1090 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1091 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1092 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1093 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1094 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1095 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1096 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1097 SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC LIMIT 2;
1098 c1 c2 c3
1099 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1100 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1101 SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2;
1102 c1 c2 c3
1103 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1104 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1105 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1106 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1107 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1108 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1109 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1110 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1111 SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1112 c1 c2 c3
1113 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1114 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1115 SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
1116 c1 c2 c3
1117 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1118 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1119 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1120 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1121 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1122 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1123 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1124 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1125 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1126 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1127 SELECT * FROM t1 WHERE c1 IN('2003-01-01 1:2:3','2005-01-03 00:00:01') ORDER BY c1,c2 DESC LIMIT 2;
1128 c1 c2 c3
1129 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1130 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1131 SELECT * FROM t1 WHERE c1 IN('2003-01-01 1:2:3','2005-01-03 00:00:01') ORDER BY c1,c2;
1132 c1 c2 c3
1133 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1134 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1135 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1136 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1137 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1138 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1139 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1140 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1141 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1142 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
1143 c1 c2 c3
1144 0000-00-00 00:00:01 NULL 5
1145 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1146 c1 c2 c3
1147 0000-00-00 00:00:01 NULL 5
1148 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
1149 c1 c2 c3
1150 0005-10-10 01:02:03 0005-10-10 01:02:03 4
1151 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1152 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1;
1153 c1 c2 c3
1154 0005-10-10 01:02:03 0005-10-10 01:02:03 4
1155 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1156 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1157 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1158 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1159 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1160 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1161 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1162 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1163 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1164 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1165 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1166 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1167 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1168 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1169 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1170 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1171 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1172 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1173 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1174 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1175 SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1 DESC LIMIT 2;
1176 c1 c2 c3
1177 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1178 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1179 SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1;
1180 c1 c2 c3
1181 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1182 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1183 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1184 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1185 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1186 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1187 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1188 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1189 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1190 SELECT * FROM t1 WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
1191 c1 c2 c3
1192 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1193 UPDATE t1 SET c1='20060101000001' WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
1194 SELECT * FROM t1 WHERE c1='20060101000001' OR c2='020304010030';
1195 c1 c2 c3
1196 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1197 2006-01-01 00:00:01 2002-03-02 00:00:01 1
1198 TRUNCATE TABLE t1;
1199 ALTER TABLE t1 CHANGE c1 c1 DATETIME NOT NULL, ADD PRIMARY KEY(c1);
1200 INSERT INTO t1 VALUES(NOW(),NOW(),3),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),4),(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),5),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),6);
1201 INSERT INTO t1 VALUES('1000-01-01 00:00:00','1000-01-01 00:00:00',1),('9999-12-31 23:59:59','9999-12-31 23:59:59',2);
1202 SELECT * FROM t1 WHERE c1 <='1000-01-01 00:00:00';
1203 c1 c2 c3
1204 1000-01-01 00:00:00 1000-01-01 00:00:00 1
1205 DELETE FROM t1 WHERE c1 <='1000-01-01 00:00:00';
1206 SELECT * FROM t1 WHERE c1 <='1000-01-01 00:00:00';
1207 c1 c2 c3
1208 SELECT * FROM t1 WHERE c1 >='9999-12-31 23:59:59';
1209 c1 c2 c3
1210 9999-12-31 23:59:59 9999-12-31 23:59:59 2
1211 DELETE FROM t1 WHERE c1 >='9999-12-31 23:59:59';
1212 SELECT * FROM t1 WHERE c1 >='9999-12-31 23:59:59';
1213 c1 c2 c3
1214 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1215 c1 c2 c3
1216 2009-02-28 13:21:14 2009-02-28 13:21:14 6
1217 2009-02-27 13:21:14 2009-02-27 13:21:14 5
1218 DELETE FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC LIMIT 2;
1219 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1220 c1 c2 c3
1221 SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
1222 c1 c2 c3
1223 2009-02-26 13:21:14 2009-02-26 13:21:14 4
1224 DELETE FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1 LIMIT 2;
1225 SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
1226 c1 c2 c3
1227 INSERT INTO t1 VALUES('2001-01-01',NOW(),7),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),8),(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),9),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),10);
1228 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1229 c1 c2 c3
1230 2001-01-01 00:00:00 2009-02-25 12:20:13 7
1231 2009-02-25 12:20:13 2009-02-25 12:20:13 3
1232 2009-02-26 13:21:14 2009-02-26 13:21:14 8
1233 DELETE FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
1234 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1235 c1 c2 c3
1236 2009-02-26 13:21:14 2009-02-26 13:21:14 8
1237 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1238 c1 c2 c3
1239 2009-02-26 13:21:14 2009-02-26 13:21:14 8
1240 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1241 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1242 c1 c2 c3
1243 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1244 c1 c2 c3
1245 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1246 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1247 c1 c2 c3
1248 INSERT INTO t1 VALUES('2001-01-01',NOW(),11),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),12),(ADDTIME(NOW(),'4 01:01:01'),NULL,13),(ADDTIME(NOW(),'5 01:01:01'),NULL,14);
1249 SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1250 c1 c2 c3
1251 2009-02-26 13:21:14 2009-02-26 13:21:14 12
1252 2009-02-27 13:21:14 2009-02-27 13:21:14 9
1253 DELETE FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
1254 SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1255 c1 c2 c3
1256 SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
1257 c1 c2 c3
1258 2009-02-28 13:21:14 2009-02-28 13:21:14 10
1259 DELETE FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC LIMIT 2;
1260 SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
1261 c1 c2 c3
1262 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
1263 c1 c2 c3
1264 2009-03-01 13:21:14 NULL 13
1265 2009-03-02 13:21:14 NULL 14
1266 DELETE FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
1267 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
1268 c1 c2 c3
1269 INSERT INTO t1 VALUES(ADDTIME(NOW(),'6 01:01:01'),ADDTIME(NOW(),'6 01:01:01'),15),(ADDTIME(NOW(),'7 01:01:01'),NULL,16),(ADDTIME(NOW(),'8 01:01:01'),ADDTIME(NOW(),'8 01:01:01'),17),(ADDTIME(NOW(),'9 01:01:01'),ADDTIME(NOW(),'9 01:01:01'),18),(ADDTIME(NOW(),'10 01:01:01'),ADDTIME(NOW(),'10 01:01:01'),19);
1270 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1271 c1 c2 c3
1272 2001-01-01 00:00:00 2009-02-25 12:20:13 11
1273 2009-03-03 13:21:14 2009-03-03 13:21:14 15
1274 2009-03-05 13:21:14 2009-03-05 13:21:14 17
1275 2009-03-06 13:21:14 2009-03-06 13:21:14 18
1276 2009-03-07 13:21:14 2009-03-07 13:21:14 19
1277 DELETE FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
1278 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1279 c1 c2 c3
1280 2009-03-05 13:21:14 2009-03-05 13:21:14 17
1281 2009-03-06 13:21:14 2009-03-06 13:21:14 18
1282 2009-03-07 13:21:14 2009-03-07 13:21:14 19
1283 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
1284 c1 c2 c3
1285 2009-03-05 13:21:14 2009-03-05 13:21:14 17
1286 DELETE FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1 LIMIT 2;
1287 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
1288 c1 c2 c3
1289 SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1290 c1 c2 c3
1291 2009-03-06 13:21:14 2009-03-06 13:21:14 18
1292 2009-03-07 13:21:14 2009-03-07 13:21:14 19
1293 DELETE FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1294 SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1295 c1 c2 c3
1296 SELECT count(*) FROM t1;
1297 count(*)
1298 1
1299 TRUNCATE TABLE t1;
1300 SELECT count(*) FROM t1;
1301 count(*)
1302 0
1303 CREATE TABLE t2(c1 DATETIME NOT NULL PRIMARY KEY, c2 DATETIME, c3 INT);
1304 INSERT INTO t1 VALUES('2001-01-01 1:30','2001-01-02 2:30',1),('2001-01-03 3:30','2001-01-02 2:30',2);
1305 INSERT INTO t2 VALUES('2001-01-02 2:30','2001-01-03 3:30',1),('2001-01-04 4:30','2001-01-05 5:30',2);
1306 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1307 c1 c2 c3 c1 c2 c3
1308 2001-01-01 01:30:00 2001-01-02 02:30:00 1 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1309 2001-01-03 03:30:00 2001-01-02 02:30:00 2 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1310 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2;
1311 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1312 c1 c2 c3 c1 c2 c3
1313 TRUNCATE TABLE t1;
1314 TRUNCATE TABLE t2;
1315 INSERT INTO t1 VALUES('2001-01-01 1:30','2001-01-02 2:30',1),('2001-01-03 3:30','2001-01-02 2:30',2);
1316 INSERT INTO t2 VALUES('2001-01-02 2:30','2001-01-03 3:30',1),('2001-01-04 4:30','2001-01-05 5:30',2);
1317 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1318 c1 c2 c3 c1 c2 c3
1319 2001-01-01 01:30:00 2001-01-02 02:30:00 1 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1320 2001-01-03 03:30:00 2001-01-02 02:30:00 2 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1321 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
1322 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1323 c1 c2 c3 c1 c2 c3
1324 DROP TABLE t1,t2;
1325 CREATE TABLE mt1(id int(11) NOT NULL default '0', name varchar(10) default NULL, PRIMARY KEY (id));
1326 INSERT INTO mt1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
1327 CREATE TABLE mt2 (id int(11) NOT NULL default '0', name varchar(10) default NULL, PRIMARY KEY (id));
1328 INSERT INTO mt2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
1329 CREATE TABLE mt3 (id int(11) NOT NULL default '0', mydate datetime default NULL, PRIMARY KEY (id));
1330 INSERT INTO mt3 VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22 00:00:00'),(7,'2002-07-22 00:00:00');
1331 delete mt1,mt2,mt3 from mt1,mt2,mt3 where to_days(now())-to_days(mt3.mydate)>=30 and mt3.id=mt1.id and mt3.id=mt2.id;
1332 select * from mt3;
1333 id mydate
1334 1 2002-02-04 00:00:00
1335 5 2002-05-12 00:00:00
1336 6 2002-06-22 00:00:00
1337 7 2002-07-22 00:00:00
1338 DROP TABLE mt1,mt2,mt3;
1339 CREATE TABLE t1(c1 TIMESTAMP NOT NULL PRIMARY KEY, c2 TIMESTAMP NULL, c3 INT, INDEX idx2(c2));
1340 INSERT INTO t1 VALUE('1998-01-01 09:23:45','10-01-01 14:12:33',1);
1341 UPDATE t1 SET c1='2001-01-01' WHERE c2='10-01-01 14:12:33';
1342 SELECT c1,hour(c1),minute(c1),second(c1) FROM t1 WHERE c2='10-01-01 14:12:33';
1343 c1 hour(c1) minute(c1) second(c1)
1344 2001-01-01 00:00:00 0 0 0
1345 INSERT INTO t1 VALUES('2009-01-01 08:42:42','2009-01-02 23:59:59',2);
1346 UPDATE t1 SET c1='2009-01-01 1:2:3',c2='2009-01-02 23:59:59.999999' WHERE c2='2009-01-02 23:59:59';
1347 SELECT c1,ADDTIME(c2,'1 1:1:1.000002') FROM t1 WHERE c3=2;
1348 c1 ADDTIME(c2,'1 1:1:1.000002')
1349 2009-01-01 01:02:03 2009-01-04 01:01:00.000002
1350 INSERT INTO t1 VALUES('2009-02-25 15:45','09-02-27 15:46',3);
1351 SET TIMESTAMP=1235553613;
1352 UPDATE t1 SET c2=NOW() WHERE c1 >=CURDATE() /* updates c1 with current timestamp */;
1353 UPDATE t1 SET c1=DATE_ADD(CURDATE(),INTERVAL '1 1:1:1' DAY_SECOND) WHERE c2=CURRENT_TIMESTAMP();
1354 SELECT * FROM t1;
1355 c1 c2 c3
1356 2001-01-01 00:00:00 2010-01-01 14:12:33 1
1357 2009-01-01 01:02:03 2009-01-02 23:59:59 2
1358 2009-02-26 01:01:01 2009-02-25 12:20:13 3
1359 INSERT INTO t1 VALUES('2001-01-06','2001-01-07 09:30:00',4),('2001-01-08',NULL,5);
1360 UPDATE t1 SET c1='20010106010030',c2=NULL WHERE c1='2001-01-06 00:00:00';
1361 SELECT c1,c2 FROM t1 WHERE c2 IS NULL;
1362 c1 c2
1363 2001-01-06 01:00:30 NULL
1364 2001-01-08 00:00:00 NULL
1365 SELECT * FROM t1;
1366 c1 c2 c3
1367 2001-01-01 00:00:00 2010-01-01 14:12:33 1
1368 2001-01-06 01:00:30 NULL 4
1369 2001-01-08 00:00:00 NULL 5
1370 2009-01-01 01:02:03 2009-01-02 23:59:59 2
1371 2009-02-26 01:01:01 2009-02-25 12:20:13 3
1372 UPDATE t1 SET c1=c1,c2='2001-01-10 00:00:00' WHERE c2 IS NULL;
1373 SELECT * FROM t1 WHERE c2='2001-01-10';
1374 c1 c2 c3
1375 2001-01-06 01:00:30 2001-01-10 00:00:00 4
1376 2001-01-08 00:00:00 2001-01-10 00:00:00 5
1377 INSERT INTO t1 VALUES('2001-01-09','2001-01-10',6),('2001-01-11','2001-01-12',7);
1378 UPDATE t1 SET c1='2001-01-09 24:59:59',c2='2009-01-10 23:60:59' WHERE c1='2001-01-09';
1379 Warnings:
1380 Warning 1265 Data truncated for column 'c1' at row 1
1381 Warning 1265 Data truncated for column 'c2' at row 1
1382 UPDATE t1 SET c1='2001-01-11 23:59:59',c2='2001-01-11 23:59:60' WHERE c1='2001-01-11';
1383 Warnings:
1384 Warning 1265 Data truncated for column 'c2' at row 1
1385 SELECT count(*) FROM t1 WHERE c1='2001-01-09 24:59:59' AND c2='2009-01-10 23:60:59';
1386 count(*)
1387 1
1388 Warnings:
1389 Warning 1292 Incorrect datetime value: '2001-01-09 24:59:59' for column 'c1' at row 1
1390 Warning 1292 Incorrect datetime value: '2009-01-10 23:60:59' for column 'c2' at row 1
1391 Warning 1292 Incorrect datetime value: '2001-01-09 24:59:59' for column 'c1' at row 1
1392 Warning 1292 Truncated incorrect datetime value: '2001-01-09 24:59:59'
1393 Warning 1292 Truncated incorrect datetime value: '2009-01-10 23:60:59'
1394 SELECT count(*) FROM t1 WHERE c1='2001-01-11 23:59:59' AND c2='2001-01-11 23:59:60';
1395 count(*)
1396 1
1397 Warnings:
1398 Warning 1292 Incorrect datetime value: '2001-01-11 23:59:60' for column 'c2' at row 1
1399 Warning 1292 Truncated incorrect datetime value: '2001-01-11 23:59:60'
1400 SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
1401 c1 c2 c3
1402 0000-00-00 00:00:00 0000-00-00 00:00:00 6
1403 2001-01-11 23:59:59 0000-00-00 00:00:00 7
1404 INSERT INTO t1 VALUES('2001-01-17','0000-00-00 00:00:00',8),('2001-01-18','2001-01-18 07:35',9);
1405 SET SQL_MODE=TRADITIONAL;
1406 SELECT * FROM t1 WHERE c2='0000-00-00 00:00:00';
1407 c1 c2 c3
1408 0000-00-00 00:00:00 0000-00-00 00:00:00 6
1409 2001-01-11 23:59:59 0000-00-00 00:00:00 7
1410 2001-01-17 00:00:00 0000-00-00 00:00:00 8
1411 Warnings:
1412 Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
1413 Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
1414 UPDATE t1 SET c1='1972-01-01 07:35',c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
1415 ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c2' at row 1
1416 SET SQL_MODE=DEFAULT;
1417 UPDATE t1 SET c1='1972-01-01 07:35',c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
1418 UPDATE t1 SET c1='2001-01-17 12:34:00',c2='2001-01-17 12:34:00' WHERE c2='0000-00-00 00:00:00' AND c1='2001-01-17';
1419 SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
1420 c1 c2 c3
1421 0000-00-00 00:00:00 0000-00-00 00:00:00 6
1422 1972-01-01 07:35:00 0000-00-00 00:00:00 9
1423 2001-01-11 23:59:59 0000-00-00 00:00:00 7
1424 SELECT * FROM t1 WHERE c1='2001-01-17 12:34:00' AND c2='2001-01-17 12:34:00';
1425 c1 c2 c3
1426 2001-01-17 12:34:00 2001-01-17 12:34:00 8
1427 INSERT INTO t1 VALUES('2001-01-21 05:43:43','2001-01-22 06:53:53',10);
1428 UPDATE t1 SET c1='1971-01-01 00:00:01',c2='2038-01-09 03:14:07' WHERE c1='2001-01-21 05:43:43';
1429 SELECT * FROM t1 WHERE c1='1971-01-01 00:00:01' AND c2='2038-01-09 03:14:07';
1430 c1 c2 c3
1431 1971-01-01 00:00:01 2038-01-09 03:14:07 10
1432 INSERT INTO t1 VALUES('2001-01-23 02:33','2001-01-24 03:33',11),('2001-01-25 04:33','2001-01-26 05:33',12);
1433 UPDATE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
1434 ERROR 23000: Duplicate entry '2001-01-23 02:33:00' for key 'PRIMARY'
1435 UPDATE IGNORE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
1436 SELECT count(*) FROM t1 WHERE c1='2001-01-23 02:33';
1437 count(*)
1438 1
1439 UPDATE t1 SET c1=NULL WHERE c2='2001-01-24 03:33' /* updates to current timestamp */;
1440 UPDATE IGNORE t1 SET c1=NULL WHERE c2='2001-01-26 05:33';
1441 SELECT * FROM t1 WHERE c1=CURRENT_TIMESTAMP();
1442 c1 c2 c3
1443 2009-02-25 12:20:13 2001-01-24 03:33:00 11
1444 TRUNCATE TABLE t1;
1445 CREATE TABLE t2(c1 TIMESTAMP NOT NULL PRIMARY KEY, c2 TIMESTAMP, c3 INT);
1446 INSERT INTO t1 VALUES('2001-01-02 2:30:45','2001-01-01 1:30',1),('2001-01-03 3:30','2001-01-02 2:30:45',2);
1447 INSERT INTO t2 VALUES('2001-01-02 2:30:45','2001-01-03 3:30',1),('2001-01-04 4:30','2001-01-05 5:30',2);
1448 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
1449 c1 c2 c3 c1 c2 c3
1450 2001-01-02 02:30:45 2001-01-01 01:30:00 1 2001-01-02 02:30:45 2001-01-03 03:30:00 1
1451 UPDATE t1,t2 SET t2.c1='2005-01-06 2:30:50',t1.c1='2005-01-06 2:30:50' WHERE t2.c1=t1.c1;
1452 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
1453 c1 c2 c3 c1 c2 c3
1454 2005-01-06 02:30:50 2001-01-01 01:30:00 1 2005-01-06 02:30:50 2001-01-03 03:30:00 1
1455 INSERT INTO t1 VALUES('2002-01-01 3:30','2002-01-02 2:30:45',3),('2002-01-04 4:30','2002-01-02 2:30:45',4);
1456 INSERT INTO t2 VALUES('2002-01-02 2:30:45','2002-01-03 3:30',3),('2002-01-03 3:30','2002-01-04 4:30',4);
1457 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1458 c1 c2 c3 c1 c2 c3
1459 2002-01-01 03:30:00 2002-01-02 02:30:45 3 2002-01-02 02:30:45 2002-01-03 03:30:00 3
1460 2002-01-04 04:30:00 2002-01-02 02:30:45 4 2002-01-02 02:30:45 2002-01-03 03:30:00 3
1461 UPDATE t1,t2 SET t2.c1='2002-01-06 2:30:50',t1.c1=t1.c1,t1.c2='2002-01-06 2:30:50' WHERE t2.c1=t1.c2;
1462 SELECT * FROM t1,t2 WHERE t2.c1='2002-01-06 2:30:50' AND t1.c2='2002-01-06 2:30:50';
1463 c1 c2 c3 c1 c2 c3
1464 2002-01-01 03:30:00 2002-01-06 02:30:50 3 2002-01-06 02:30:50 2002-01-03 03:30:00 3
1465 2002-01-04 04:30:00 2002-01-06 02:30:50 4 2002-01-06 02:30:50 2002-01-03 03:30:00 3
1466 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1467 c1 c2 c3 c1 c2 c3
1468 2002-01-01 03:30:00 2002-01-06 02:30:50 3 2002-01-06 02:30:50 2002-01-03 03:30:00 3
1469 2002-01-04 04:30:00 2002-01-06 02:30:50 4 2002-01-06 02:30:50 2002-01-03 03:30:00 3
1470 DELETE FROM t1 WHERE c2='2002-01-06 2:30:50' AND c3=3;
1471 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1472 c1 c2 c3 c1 c2 c3
1473 2002-01-04 04:30:00 2002-01-06 02:30:50 4 2002-01-06 02:30:50 2002-01-03 03:30:00 3
1474 DROP TABLE t2;
1475 TRUNCATE TABLE t1;
1476 ALTER TABLE t1 CHANGE c1 c1 TIMESTAMP NOT NULL, DROP PRIMARY KEY;
1477 ALTER TABLE t1 CHANGE c1 c1 TIMESTAMP NOT NULL, ADD KEY(c1);
1478 UPDATE t1 SET c1='2002-01-07 7:30' WHERE c2='2002-01-05 5:30';
1479 SELECT * FROM t1 WHERE c1='2002-01-07 7:30';
1480 c1 c2 c3
1481 INSERT INTO t1 VALUES('2002-01-08 1:30','2002-01-09 1:30',4),('2002-01-08 1:30:5','2002-01-09 1:30:5',5),('2002-01-08 1:30:10','2002-01-09 1:30:10',6),('2002-01-08 1:30:15','2002-01-09 1:30:15',7),('2002-01-08 1:30:20','2002-01-09 1:30:20',8),('2002-01-08 1:30:25','2002-01-09 1:30:25',9),('2002-01-08 1:30:30','2002-01-09 1:30:30',10),('2002-01-08 1:30:35','2002-01-09 1:30:35',11),('2002-01-08 1:30:40','2002-01-09 1:30:40',12),('2002-01-08 1:30:45','2002-01-09 1:30:45',13),('2002-01-08 1:30:50','2002-01-09 1:30:50',14),('2002-01-08 1:30:55','2002-01-09 1:30:55',15),('2002-01-08 1:30:59','2002-01-09 1:30:59',16),('2002-01-08 2:00','2002-01-09 2:00',17),('2002-01-08 2:00:05','2002-01-09 2:00:05',18),('2002-01-08 2:00:10','2002-01-09 2:00:10',19),('2002-01-08 2:00:15','2002-01-09 2:00:15',20);
1482 SELECT * FROM t1;
1483 c1 c2 c3
1484 2002-01-08 01:30:00 2002-01-09 01:30:00 4
1485 2002-01-08 01:30:05 2002-01-09 01:30:05 5
1486 2002-01-08 01:30:10 2002-01-09 01:30:10 6
1487 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1488 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1489 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1490 2002-01-08 01:30:30 2002-01-09 01:30:30 10
1491 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1492 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1493 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1494 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1495 2002-01-08 01:30:55 2002-01-09 01:30:55 15
1496 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1497 2002-01-08 02:00:00 2002-01-09 02:00:00 17
1498 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1499 2002-01-08 02:00:10 2002-01-09 02:00:10 19
1500 2002-01-08 02:00:15 2002-01-09 02:00:15 20
1501 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2 LIMIT 2;
1502 SELECT * FROM t1 WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2;
1503 c1 c2 c3
1504 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1505 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1506 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1507 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1508 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1509 2002-01-08 01:30:30 2002-01-09 01:30:30 10
1510 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1511 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1512 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1513 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1514 2002-01-08 01:30:55 2002-01-09 01:30:55 15
1515 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1516 2002-01-08 02:00:00 2002-01-09 02:00:00 17
1517 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1518 2002-01-08 02:00:10 2002-01-09 02:00:10 19
1519 2002-01-08 02:00:15 2002-01-09 02:00:15 20
1520 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 >= '20020109013010' ORDER BY c2 DESC LIMIT 2;
1521 SELECT * FROM t1 WHERE c2 >= '20020109013010' ORDER BY c2 DESC;
1522 c1 c2 c3
1523 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1524 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1525 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1526 2002-01-08 02:00:00 2002-01-09 02:00:00 17
1527 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1528 2002-01-08 01:30:55 2002-01-09 01:30:55 15
1529 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1530 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1531 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1532 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1533 2002-01-08 01:30:30 2002-01-09 01:30:30 10
1534 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1535 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1536 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1537 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1538 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <='02-01-09 1:30:20' ORDER BY c2 LIMIT 2;
1539 SELECT * FROM t1 WHERE c2 <='02-01-09 1:30:20' ORDER BY c2;
1540 c1 c2 c3
1541 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1542 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1543 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1544 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1545 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1546 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <=> '020109013030' ORDER BY c2 DESC LIMIT 2;
1547 SELECT * FROM t1 WHERE c2 <=> '020109013030' ORDER BY c2 DESC;
1548 c1 c2 c3
1549 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1550 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 BETWEEN '20020109 01:30:40' AND '20020109 01:30:50' ORDER BY c2 LIMIT 2;
1551 Warnings:
1552 Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
1553 Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
1554 Warning 1292 Incorrect datetime value: '20020109 01:30:50' for column 'c2' at row 1
1555 SELECT * FROM t1 WHERE c2 BETWEEN '20020109 01:30:40' AND '20020109 01:30:50' ORDER BY c2;
1556 c1 c2 c3
1557 Warnings:
1558 Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
1559 Warning 1292 Incorrect datetime value: '20020109 01:30:40' for column 'c2' at row 1
1560 Warning 1292 Incorrect datetime value: '20020109 01:30:50' for column 'c2' at row 1
1561 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 IN('2002-01-09 1:30:55','2002-01-09 2:00') ORDER BY c2 DESC LIMIT 2;
1562 SELECT * FROM t1 WHERE c2 IN('2002-01-09 1:30:55','2002-01-09 2:00') ORDER BY c2 DESC;
1563 c1 c2 c3
1564 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1565 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1566 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
1567 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
1568 c1 c2 c3
1569 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2>= '2002-02-09 2:00:5' AND c1 < '2002-02-09 2:00:15' ORDER BY c2 LIMIT 2;
1570 SELECT * FROM t1 WHERE c2>= '2002-02-09 2:00:5' AND c1 < '2002-02-09 2:00:15' ORDER BY c2;
1571 c1 c2 c3
1572 INSERT INTO t1 VALUES('20020301000001','20020302000001',1),('020303010030','020304010030',2),('050103000001','050104000001',3),('5-10-10 1:2:3','5-10-10 1:2:3',4),('0000-00-00 00:00:01',NULL,5);
1573 Warnings:
1574 Warning 1264 Out of range value for column 'c1' at row 4
1575 Warning 1264 Out of range value for column 'c2' at row 4
1576 Warning 1265 Data truncated for column 'c1' at row 5
1577 SELECT * FROM t1;
1578 c1 c2 c3
1579 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1580 0000-00-00 00:00:00 NULL 5
1581 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1582 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1583 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1584 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1585 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1586 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1587 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1588 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1589 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1590 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1591 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1592 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1593 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1594 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1595 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1596 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1597 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1598 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1599 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1600 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1601 SELECT c1 FROM t1;
1602 c1
1603 0000-00-00 00:00:00
1604 0000-00-00 00:00:00
1605 2002-01-08 01:30:15
1606 2002-01-08 01:30:20
1607 2002-01-08 01:30:25
1608 2002-01-08 01:30:35
1609 2002-01-08 01:30:40
1610 2002-01-08 01:30:45
1611 2002-01-08 01:30:50
1612 2002-01-08 01:30:59
1613 2002-01-08 02:00:05
1614 2002-03-01 00:00:01
1615 2002-03-03 01:00:30
1616 2003-01-01 01:02:03
1617 2003-01-01 01:02:03
1618 2003-01-01 01:02:03
1619 2003-01-01 01:02:03
1620 2003-01-01 01:02:03
1621 2003-01-01 01:02:03
1622 2003-01-01 01:02:03
1623 2003-01-01 01:02:03
1624 2005-01-03 00:00:01
1625 SELECT c1 FROM t1 ORDER BY c1 DESC;
1626 c1
1627 2005-01-03 00:00:01
1628 2003-01-01 01:02:03
1629 2003-01-01 01:02:03
1630 2003-01-01 01:02:03
1631 2003-01-01 01:02:03
1632 2003-01-01 01:02:03
1633 2003-01-01 01:02:03
1634 2003-01-01 01:02:03
1635 2003-01-01 01:02:03
1636 2002-03-03 01:00:30
1637 2002-03-01 00:00:01
1638 2002-01-08 02:00:05
1639 2002-01-08 01:30:59
1640 2002-01-08 01:30:50
1641 2002-01-08 01:30:45
1642 2002-01-08 01:30:40
1643 2002-01-08 01:30:35
1644 2002-01-08 01:30:25
1645 2002-01-08 01:30:20
1646 2002-01-08 01:30:15
1647 0000-00-00 00:00:00
1648 0000-00-00 00:00:00
1649 SELECT * FROM t1 WHERE c1='2003-01-01 1:2:3';
1650 c1 c2 c3
1651 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1652 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1653 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1654 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1655 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1656 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1657 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1658 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1659 SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1660 c1 c2 c3
1661 0000-00-00 00:00:00 NULL 5
1662 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1663 SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
1664 c1 c2 c3
1665 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1666 0000-00-00 00:00:00 NULL 5
1667 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1668 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1669 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1670 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1671 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1672 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1673 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1674 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1675 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1676 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1677 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1678 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1679 SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2 DESC LIMIT 2;
1680 c1 c2 c3
1681 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1682 SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2;
1683 c1 c2 c3
1684 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1685 SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1686 c1 c2 c3
1687 0000-00-00 00:00:00 NULL 5
1688 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1689 SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 DESC;
1690 c1 c2 c3
1691 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1692 0000-00-00 00:00:00 NULL 5
1693 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1694 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1695 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1696 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1697 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1698 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1699 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1700 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1701 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1702 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1703 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1704 SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC LIMIT 2;
1705 c1 c2 c3
1706 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1707 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1708 SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2;
1709 c1 c2 c3
1710 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1711 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1712 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1713 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1714 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1715 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1716 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1717 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1718 SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
1719 c1 c2 c3
1720 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1721 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1722 SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
1723 c1 c2 c3
1724 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1725 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1726 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1727 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1728 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1729 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1730 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1731 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1732 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1733 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1734 SELECT * FROM t1 WHERE c1 IN('2003-01-01 1:2:3','2005-01-03 00:00:01') ORDER BY c1,c2 DESC LIMIT 2;
1735 c1 c2 c3
1736 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1737 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1738 SELECT * FROM t1 WHERE c1 IN('2003-01-01 1:2:3','2005-01-03 00:00:01') ORDER BY c1,c2;
1739 c1 c2 c3
1740 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1741 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1742 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1743 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1744 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1745 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1746 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1747 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1748 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1749 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
1750 c1 c2 c3
1751 0000-00-00 00:00:00 NULL 5
1752 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
1753 c1 c2 c3
1754 0000-00-00 00:00:00 NULL 5
1755 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
1756 c1 c2 c3
1757 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1758 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1759 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1;
1760 c1 c2 c3
1761 0000-00-00 00:00:00 0000-00-00 00:00:00 4
1762 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1763 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1764 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1765 2002-01-08 01:30:15 2002-01-09 01:30:15 7
1766 2002-01-08 01:30:20 2002-01-09 01:30:20 8
1767 2002-01-08 01:30:25 2002-01-09 01:30:25 9
1768 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1769 2002-01-08 01:30:35 2002-01-09 01:30:35 11
1770 2002-01-08 01:30:40 2002-01-09 01:30:40 12
1771 2002-01-08 01:30:45 2002-01-09 01:30:45 13
1772 2002-01-08 01:30:50 2002-01-09 01:30:50 14
1773 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1774 2002-01-08 01:30:59 2002-01-09 01:30:59 16
1775 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1776 2002-01-08 02:00:05 2002-01-09 02:00:05 18
1777 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1778 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1779 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1780 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1781 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1782 SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1 DESC LIMIT 2;
1783 c1 c2 c3
1784 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1785 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1786 SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1;
1787 c1 c2 c3
1788 2003-01-01 01:02:03 2002-01-09 01:30:00 4
1789 2003-01-01 01:02:03 2002-01-09 01:30:05 5
1790 2003-01-01 01:02:03 2002-01-09 01:30:10 6
1791 2003-01-01 01:02:03 2002-01-09 01:30:30 10
1792 2003-01-01 01:02:03 2002-01-09 01:30:55 15
1793 2003-01-01 01:02:03 2002-01-09 02:00:00 17
1794 2003-01-01 01:02:03 2002-01-09 02:00:10 19
1795 2003-01-01 01:02:03 2002-01-09 02:00:15 20
1796 2005-01-03 00:00:01 2005-01-04 00:00:01 3
1797 SELECT * FROM t1 WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
1798 c1 c2 c3
1799 2002-03-01 00:00:01 2002-03-02 00:00:01 1
1800 UPDATE t1 SET c1='20060101000001' WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
1801 SELECT * FROM t1 WHERE c1='20060101000001' OR c2='020304010030';
1802 c1 c2 c3
1803 2002-03-03 01:00:30 2002-03-04 01:00:30 2
1804 2006-01-01 00:00:01 2002-03-02 00:00:01 1
1805 TRUNCATE TABLE t1;
1806 ALTER TABLE t1 CHANGE c1 c1 TIMESTAMP NOT NULL, ADD PRIMARY KEY(c1);
1807 INSERT INTO t1 VALUES(NOW(),NOW(),3),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),4),(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),5),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),6);
1808 INSERT INTO t1 VALUES('1971-01-01 00:00:01','1971-01-01 00:00:01',1),('2038-01-09 03:14:07','2038-01-09 03:14:07',2);
1809 SELECT * FROM t1 WHERE c1 <='1971-01-01 00:00:01';
1810 c1 c2 c3
1811 1971-01-01 00:00:01 1971-01-01 00:00:01 1
1812 DELETE FROM t1 WHERE c1 <='1971-01-01 00:00:01';
1813 SELECT * FROM t1 WHERE c1 <='1971-01-01 00:00:01';
1814 c1 c2 c3
1815 SELECT * FROM t1 WHERE c1 >='2038-01-09 03:14:07';
1816 c1 c2 c3
1817 2038-01-09 03:14:07 2038-01-09 03:14:07 2
1818 DELETE FROM t1 WHERE c1 >='2038-01-09 03:14:07';
1819 SELECT * FROM t1 WHERE c1 >='2038-01-09 03:14:07';
1820 c1 c2 c3
1821 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1822 c1 c2 c3
1823 2009-02-28 13:21:14 2009-02-28 13:21:14 6
1824 2009-02-27 13:21:14 2009-02-27 13:21:14 5
1825 DELETE FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC LIMIT 2;
1826 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1827 c1 c2 c3
1828 SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
1829 c1 c2 c3
1830 2009-02-26 13:21:14 2009-02-26 13:21:14 4
1831 DELETE FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1 LIMIT 2;
1832 SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
1833 c1 c2 c3
1834 INSERT INTO t1 VALUES('2001-01-01',NOW(),7),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),8),(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),9),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),10);
1835 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1836 c1 c2 c3
1837 2001-01-01 00:00:00 2009-02-25 12:20:13 7
1838 2009-02-25 12:20:13 2009-02-25 12:20:13 3
1839 2009-02-26 13:21:14 2009-02-26 13:21:14 8
1840 DELETE FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
1841 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1842 c1 c2 c3
1843 2009-02-26 13:21:14 2009-02-26 13:21:14 8
1844 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1845 c1 c2 c3
1846 2009-02-26 13:21:14 2009-02-26 13:21:14 8
1847 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1848 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1849 c1 c2 c3
1850 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1851 c1 c2 c3
1852 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1853 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
1854 c1 c2 c3
1855 INSERT INTO t1 VALUES('2001-01-01',NOW(),11),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),12),(ADDTIME(NOW(),'4 01:01:01'),NULL,13),(ADDTIME(NOW(),'5 01:01:01'),NULL,14);
1856 SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1857 c1 c2 c3
1858 2009-02-26 13:21:14 2009-02-26 13:21:14 12
1859 2009-02-27 13:21:14 2009-02-27 13:21:14 9
1860 DELETE FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
1861 SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
1862 c1 c2 c3
1863 SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
1864 c1 c2 c3
1865 2009-02-28 13:21:14 2009-02-28 13:21:14 10
1866 DELETE FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC LIMIT 2;
1867 SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
1868 c1 c2 c3
1869 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
1870 c1 c2 c3
1871 2009-03-01 13:21:14 NULL 13
1872 2009-03-02 13:21:14 NULL 14
1873 DELETE FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
1874 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
1875 c1 c2 c3
1876 INSERT INTO t1 VALUES(ADDTIME(NOW(),'6 01:01:01'),ADDTIME(NOW(),'6 01:01:01'),15),(ADDTIME(NOW(),'7 01:01:01'),NULL,16),(ADDTIME(NOW(),'8 01:01:01'),ADDTIME(NOW(),'8 01:01:01'),17),(ADDTIME(NOW(),'9 01:01:01'),ADDTIME(NOW(),'9 01:01:01'),18),(ADDTIME(NOW(),'10 01:01:01'),ADDTIME(NOW(),'10 01:01:01'),19);
1877 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1878 c1 c2 c3
1879 2001-01-01 00:00:00 2009-02-25 12:20:13 11
1880 2009-03-03 13:21:14 2009-03-03 13:21:14 15
1881 2009-03-05 13:21:14 2009-03-05 13:21:14 17
1882 2009-03-06 13:21:14 2009-03-06 13:21:14 18
1883 2009-03-07 13:21:14 2009-03-07 13:21:14 19
1884 DELETE FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
1885 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
1886 c1 c2 c3
1887 2009-03-05 13:21:14 2009-03-05 13:21:14 17
1888 2009-03-06 13:21:14 2009-03-06 13:21:14 18
1889 2009-03-07 13:21:14 2009-03-07 13:21:14 19
1890 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
1891 c1 c2 c3
1892 2009-03-05 13:21:14 2009-03-05 13:21:14 17
1893 DELETE FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1 LIMIT 2;
1894 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
1895 c1 c2 c3
1896 SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1897 c1 c2 c3
1898 2009-03-06 13:21:14 2009-03-06 13:21:14 18
1899 2009-03-07 13:21:14 2009-03-07 13:21:14 19
1900 DELETE FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1901 SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
1902 c1 c2 c3
1903 SELECT count(*) FROM t1;
1904 count(*)
1905 1
1906 TRUNCATE TABLE t1;
1907 SELECT count(*) FROM t1;
1908 count(*)
1909 0
1910 CREATE TABLE t2(c1 TIMESTAMP NOT NULL PRIMARY KEY, c2 TIMESTAMP, c3 INT);
1911 INSERT INTO t1 VALUES('2001-01-01 1:30','2001-01-02 2:30',1),('2001-01-03 3:30','2001-01-02 2:30',2);
1912 INSERT INTO t2 VALUES('2001-01-02 2:30','2001-01-03 3:30',1),('2001-01-04 4:30','2001-01-05 5:30',2);
1913 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1914 c1 c2 c3 c1 c2 c3
1915 2001-01-01 01:30:00 2001-01-02 02:30:00 1 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1916 2001-01-03 03:30:00 2001-01-02 02:30:00 2 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1917 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2;
1918 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1919 c1 c2 c3 c1 c2 c3
1920 TRUNCATE TABLE t1;
1921 TRUNCATE TABLE t2;
1922 INSERT INTO t1 VALUES('2001-01-01 1:30','2001-01-02 2:30',1),('2001-01-03 3:30','2001-01-02 2:30',2);
1923 INSERT INTO t2 VALUES('2001-01-02 2:30','2001-01-03 3:30',1),('2001-01-04 4:30','2001-01-05 5:30',2);
1924 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1925 c1 c2 c3 c1 c2 c3
1926 2001-01-01 01:30:00 2001-01-02 02:30:00 1 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1927 2001-01-03 03:30:00 2001-01-02 02:30:00 2 2001-01-02 02:30:00 2001-01-03 03:30:00 1
1928 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
1929 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
1930 c1 c2 c3 c1 c2 c3
1931 DROP TABLE t1,t2;