Debian Codegraph @

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

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