Debian Codegraph @

mysql-test/suite/engines/iuds/t/update_delete_calendar.test

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1,t2;
3 --enable_warnings
4
5 ######## Running UPDATE tests for DATE ########
6
7 # Create tables
8 CREATE TABLE t1(c1 DATE NOT NULL PRIMARY KEY, c2 DATE NULL, c3 INT, INDEX idx2(c2));
9 # Insert into table
10 INSERT INTO t1 VALUE('1998-01-01','10-01-01',1),('2001-01-01','70-01-01',2);
11 # Disabling warnings due to Bug#21276591
12 --disable_warnings
13 INSERT INTO t1 VALUE('2001-01-03','2001-01-04 09:34:00',3);
14 --enable_warnings
15 --sorted_result
16 SELECT * FROM t1;
17
18 # Update year,month,day,4 digit year date type to 2 digit and vice-versa
19 UPDATE t1 SET c1='69-02-02' WHERE c2='10-01-01';
20 SELECT year(c1),month(c1),day(c1),year(c2) FROM t1 WHERE c2='10-01-01';
21 UPDATE t1 SET c1='1969-02-02',c2='1910-02-02' WHERE c2='10-01-01';
22 SELECT * FROM t1 WHERE c1='1969-02-02' AND c2='1910-02-02';
23 UPDATE t1 SET c1='01-02-02',c2='2070-02-02' WHERE c2='70-01-01';
24 SELECT year(c1),c2 FROM t1 WHERE c1='01-02-02' AND c2='2070-02-02';
25
26 # Update with time values
27 UPDATE t1 SET c1='2001-01-03 08:30:00',c2='2001-01-05' WHERE c2='2001-01-04';
28 SELECT c1,hour(c1),minute(c1),second(c1) FROM t1 WHERE c2='2001-01-05';
29
30 # Update with function
31 INSERT INTO t1 VALUES('2009-02-25','09-02-27',4);
32 SET TIMESTAMP=1235553613; #'2009-02-25'
33 UPDATE t1 SET c2=NOW() WHERE c1 >=CURDATE();
34 UPDATE t1 SET c1=DATE_ADD(CURDATE(),INTERVAL '1 1:1:1' DAY_SECOND) WHERE c2=CURDATE();
35 --sorted_result
36 SELECT * FROM t1;
37
38 # Update with Null, null to valid date
39 INSERT INTO t1 VALUES('2001-01-06','2001-01-07',5),('2001-01-08',NULL,6);
40 UPDATE t1 SET c2=NULL WHERE c1='2001-01-06';
41 --sorted_result
42 SELECT * FROM t1 WHERE c2 IS NULL;
43 UPDATE t1 SET c2='2001-01-10' WHERE c2 IS NULL;
44 --sorted_result
45 SELECT * FROM t1 WHERE c2='2001-01-10';
46
47 # Update with invalid dates, doesn't convert to zero dates- throws error , ALLOW_INVALID_DATES allows
48 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);
49 --error ER_TRUNCATED_WRONG_VALUE
50 UPDATE t1 SET c1='10:45:15',c2='2009-01-32' WHERE c1='2001-01-11';
51 --error ER_TRUNCATED_WRONG_VALUE
52 UPDATE t1 SET c2='2009-02-30' WHERE c2='2001-01-12';
53 --error ER_TRUNCATED_WRONG_VALUE
54 UPDATE t1 SET c2='2009-04-31' WHERE c2='2001-01-13';
55 --error ER_TRUNCATED_WRONG_VALUE
56 UPDATE t1 SET c2='2009-06-31' WHERE c2='2001-01-14';
57 --error ER_TRUNCATED_WRONG_VALUE
58 UPDATE t1 SET c2='2009-09-31' WHERE c2='2001-01-15';
59 --error ER_WRONG_VALUE
60 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 */;
61 --error ER_TRUNCATED_WRONG_VALUE
62 UPDATE t1 SET c2='2009-11-31' WHERE c2='2001-01-16';
63 --error ER_WRONG_VALUE
64 SELECT * FROM t1 WHERE c2='0000-00-00';
65 SET SQL_MODE=ALLOW_INVALID_DATES;
66 --sorted_result
67 SELECT * FROM t1 WHERE c2='0000-00-00';
68 INSERT INTO t1 VALUES('2001-01-27','2001-01-28',19);
69 UPDATE t1 SET c1='2009-04-31',c2='2009-02-30' WHERE c3=19;
70 SELECT c1,c2,day(c1),day(c2) FROM t1 WHERE c3=19;
71 SET SQL_MODE=DEFAULT;
72
73 # Update with zero dates, zero dates to valid dates - Must throw errors for NO_ZERO_DATE sql mode
74 --error ER_TRUNCATED_WRONG_VALUE
75 INSERT INTO t1 VALUES('2001-01-17','0000-00-00',13);
76 INSERT INTO t1 VALUES('2001-01-18','2001-01-18',14);
77 SET SQL_MODE=TRADITIONAL;
78 --error ER_WRONG_VALUE
79 SELECT * FROM t1 WHERE c2='0000-00-00';
80 --error ER_TRUNCATED_WRONG_VALUE
81 UPDATE t1 SET c2='0000-00-00' WHERE c2='2001-01-18';
82 SET SQL_MODE=DEFAULT;
83 --error ER_TRUNCATED_WRONG_VALUE
84 UPDATE t1 SET c2='0000-00-00' WHERE c2='2001-01-18';
85 --error ER_TRUNCATED_WRONG_VALUE
86 UPDATE t1 SET c2='2001-01-18' WHERE c2='0000-00-00' AND c1='2001-01-17';
87 --error ER_WRONG_VALUE
88 SELECT * FROM t1 WHERE c1='0000-00-00' OR c2='0000-00-00';
89
90 # Update with valid dates with zero in month/day
91 --disable_query_log
92 SET @save_sql_mode = @@SQL_MODE;
93 SET SQL_MODE='ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
94 --enable_query_log
95
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
100 --disable_query_log
101 SET SQL_MODE= @save_sql_mode;
102 --enable_query_log
103
104 # Update with range values
105 INSERT INTO t1 VALUES('2001-01-21','2001-01-22',16);
106 UPDATE t1 SET c1='1000-01-01',c2='9999-12-31' WHERE c1='2001-01-21';
107 SELECT * FROM t1 WHERE c1='1000-01-01' AND c2='9999-12-31';
108
109 # Update ignore on bad null/duplicate key
110 INSERT INTO t1 VALUES('2001-01-23','2001-01-24',17),('2001-01-25','2001-01-26',18);
111 --error ER_DUP_ENTRY
112 UPDATE t1 SET c1='2001-01-23' WHERE c1='2001-01-25';
113 UPDATE IGNORE t1 SET c1='2001-01-23' WHERE c1='2001-01-25';
114 SELECT count(*) FROM t1 WHERE c1='2001-01-23';
115 --error ER_BAD_NULL_ERROR
116 UPDATE t1 SET c1=NULL WHERE c2='2001-01-24' /* updates to 0000-00-00 */;
117 UPDATE IGNORE t1 SET c1=NULL WHERE c2='2001-01-24';
118 --error ER_WRONG_VALUE
119 SELECT * FROM t1 WHERE c1='0000-00-00';
120
121 # Multi table update
122
123 TRUNCATE TABLE t1;
124 CREATE TABLE t2(c1 DATE NOT NULL PRIMARY KEY, c2 DATE, c3 INT);
125
126 # eq-ref join
127 INSERT INTO t1 VALUES('2001-01-02','2001-01-01',1),('2001-01-03','2001-01-02',2);
128 INSERT INTO t2 VALUES('2001-01-02','2001-01-03',1),('2001-01-04','2001-01-05',2);
129 --sorted_result
130 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
131 UPDATE t1,t2 SET t2.c1='2005-01-06',t1.c1='2005-01-06' WHERE t2.c1=t1.c1;
132 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
133
134 INSERT INTO t1 VALUES('2002-01-01','2002-01-02',3),('2002-01-04','2002-01-02',4);
135 INSERT INTO t2 VALUES('2002-01-02','2002-01-03',3),('2002-01-03','2002-01-04',4);
136 --sorted_result
137 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
138 UPDATE t1,t2 SET t2.c1='2002-01-06',t1.c2='2002-01-06' WHERE t2.c1=t1.c2;
139 --sorted_result
140 SELECT * FROM t1,t2 WHERE t2.c1='2002-01-06' AND t1.c2='2002-01-06';
141 --sorted_result
142 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
143 DELETE FROM t1 WHERE c2='2002-01-06' AND c3=3;
144 --sorted_result
145 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
146 DROP TABLE t2;
147
148 # Update with other access methods, ORDER BY LIMIT
149 TRUNCATE TABLE t1;
150 ALTER TABLE t1 CHANGE c1 c1 DATE NOT NULL, DROP PRIMARY KEY;
151 ALTER TABLE t1 CHANGE c1 c1 DATE NOT NULL, ADD KEY(c1);
152
153 # ref access
154 UPDATE t1 SET c1='2002-01-07' WHERE c2='2002-01-05';
155 SELECT * FROM t1 WHERE c1='2002-01-07';
156
157 # range access
158 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);
159 --sorted_result
160 SELECT * FROM t1;
161 UPDATE t1 SET c1='2003-01-01' WHERE c2 <> '2002-01-09' ORDER BY c2 LIMIT 2;
162 SELECT * FROM t1 WHERE c2 <> '2002-01-09' ORDER BY c2;
163 UPDATE t1 SET c1='2003-01-01' WHERE c2 >= '2002-01-13' ORDER BY c2 DESC LIMIT 2;
164 SELECT * FROM t1 WHERE c2 >= '2002-01-13' ORDER BY c2 DESC;
165 UPDATE t1 SET c1='2003-01-01' WHERE c2 <='2002-01-21' ORDER BY c2 LIMIT 2;
166 SELECT * FROM t1 WHERE c2 <='2002-01-21' ORDER BY c2;
167 UPDATE t1 SET c1='2003-01-01' WHERE c2 <=> '2002-01-23' ORDER BY c2 DESC LIMIT 2;
168 SELECT * FROM t1 WHERE c2 <=> '2002-01-23' ORDER BY c2 DESC;
169 UPDATE t1 SET c1='2003-01-01' WHERE c2 BETWEEN '2002-01-25' AND '2002-01-29' ORDER BY c2 LIMIT 2;
170 SELECT * FROM t1 WHERE c2 BETWEEN '2002-01-25' AND '2002-01-29' ORDER BY c2;
171 UPDATE t1 SET c1='2003-01-01' WHERE c2 IN('2002-01-29','2002-01-31') ORDER BY c2 DESC LIMIT 2;
172 SELECT * FROM t1 WHERE c2 IN('2002-01-29','2002-01-31') ORDER BY c2 DESC;
173 UPDATE t1 SET c1='2003-01-01' WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
174 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
175 UPDATE t1 SET c1='2003-01-01' WHERE c2>= '2002-02-06' AND c1 < '2002-02-09' ORDER BY c2 LIMIT 2;
176 SELECT * FROM t1 WHERE c2>= '2002-02-06' AND c1 < '2002-02-09' ORDER BY c2;
177 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);
178 # Select using various access methods
179 ## Full table scan
180 --sorted_result
181 SELECT * FROM t1;
182 ## Forward index scan, covering ##
183 --sorted_result
184 SELECT c1 FROM t1;
185 ## Backward index scan, covering ##
186 SELECT c1 FROM t1 ORDER BY c1 DESC;
187 # ref access
188 --sorted_result
189 SELECT * FROM t1 WHERE c1='2003-01-01';
190 ## Range access ##
191 SELECT * FROM t1 WHERE c1 <> '2003-01-01' ORDER BY c1,c2 LIMIT 2;
192 SELECT * FROM t1 WHERE c1 <> '2003-01-01' ORDER BY c1,c2 DESC;
193 SELECT * FROM t1 WHERE c1 > '2003-01-01' ORDER BY c1,c2 DESC LIMIT 2;
194 SELECT * FROM t1 WHERE c1 >= '2003-01-01' ORDER BY c1,c2;
195 SELECT * FROM t1 WHERE c1 < '2003-01-01' ORDER BY c1,c2 LIMIT 2;
196 SELECT * FROM t1 WHERE c1 <= '2003-01-01' ORDER BY c1,c2 DESC;
197 SELECT * FROM t1 WHERE c1 <=> '2003-01-01' ORDER BY c1,c2 DESC LIMIT 2;
198 SELECT * FROM t1 WHERE c1 <=> '2003-01-01' ORDER BY c1,c2;
199 SELECT * FROM t1 WHERE c1 BETWEEN '2002-03-01' AND '2003-01-01' ORDER BY c1,c2 LIMIT 2;
200 SELECT * FROM t1 WHERE c1 BETWEEN '2002-03-01' AND '2003-01-01' ORDER BY c1,c2 DESC;
201 SELECT * FROM t1 WHERE c1 IN('2003-01-01','2005-01-07') ORDER BY c1,c2 DESC LIMIT 2;
202 SELECT * FROM t1 WHERE c1 IN('2003-01-01','2005-01-07') ORDER BY c1,c2;
203 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
204 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
205 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
206 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1;
207 SELECT * FROM t1 WHERE c1>='2003-01-01' AND c1 <= '2005-01-07' ORDER BY c2,c1 DESC LIMIT 2;
208 SELECT * FROM t1 WHERE c1>='2003-01-01' AND c1 <= '2005-01-07' ORDER BY c2,c1;
209
210 # Index-merge access
211 SELECT * FROM t1 WHERE c1='2003-01-03' OR c2='2002-03-06';
212 UPDATE t1 SET c1='2006-01-01' WHERE c1='2003-01-03' OR c2='2002-03-06';
213 --sorted_result
214 SELECT * FROM t1 WHERE c1='2006-01-01' OR c2='2002-03-04';
215
216 # Testing delete using various access methods
217 TRUNCATE TABLE t1;
218 ALTER TABLE t1 CHANGE c1 c1 DATE NOT NULL, ADD PRIMARY KEY(c1);
219 # Disabling warnings due to #52283
220 --disable_warnings
221 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);
222 --enable_warnings
223 INSERT INTO t1 VALUES('1000-01-01','1000-01-01',1),('9999-12-31','9999-12-31',2);
224 SELECT * FROM t1 WHERE c1 <='1000-01-01';
225 DELETE FROM t1 WHERE c1 <='1000-01-01';
226 SELECT * FROM t1 WHERE c1 <='1000-01-01';
227 SELECT * FROM t1 WHERE c1 >='9999-12-31';
228 DELETE FROM t1 WHERE c1 >='9999-12-31';
229 SELECT * FROM t1 WHERE c1 >='9999-12-31';
230 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
231 DELETE FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC LIMIT 2;
232 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
233 SELECT * FROM t1 WHERE c1 <> CURDATE() ORDER BY c1;
234 DELETE FROM t1 WHERE c1 <> CURDATE() ORDER BY c1 LIMIT 2;
235 SELECT * FROM t1 WHERE c1 <> CURDATE() ORDER BY c1;
236 # Disabling warnings due to #52283
237 --disable_warnings
238 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);
239 --enable_warnings
240 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
241 DELETE FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
242 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
243 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
244 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
245 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
246 SELECT * FROM t1 WHERE c1 <=> ADDDATE(CURDATE(),'1 01:01:01') ORDER BY c1 DESC;
247 DELETE FROM t1 WHERE c1 <=> ADDDATE(CURDATE(),'1') ORDER BY c1 DESC;
248 SELECT * FROM t1 WHERE c1 <=> ADDDATE(CURDATE(),'1 01:01:01') ORDER BY c1 DESC;
249 INSERT INTO t1 VALUES('2001-01-01',NOW(),11);
250 INSERT INTO t1 VALUES(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),12);
251 # Disabling warnings due to #52283
252 --disable_warnings
253 INSERT INTO t1 VALUES(ADDTIME(NOW(),'4 01:01:01'),NULL,13),(ADDTIME(NOW(),'5 01:01:01'),NULL,14);
254 --enable_warnings
255 SELECT * FROM t1 WHERE c1 BETWEEN CURDATE() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
256 DELETE FROM t1 WHERE c1 BETWEEN CURDATE() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
257 SELECT * FROM t1 WHERE c1 BETWEEN CURDATE() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
258 --disable_warnings
259 SELECT * FROM t1 WHERE c1 IN(CURDATE(),ADDDATE(CURDATE(),'3 01:01:01')) ORDER BY c1 DESC;
260 --enable_warnings
261 DELETE FROM t1 WHERE c1 IN(CURDATE(),ADDDATE(CURDATE(),'3')) ORDER BY c1 DESC LIMIT 2;
262 --disable_warnings
263 SELECT * FROM t1 WHERE c1 IN(CURDATE(),ADDDATE(CURDATE(),'3 01:01:01')) ORDER BY c1 DESC;
264 --enable_warnings
265 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
266 DELETE FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
267 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
268 # Disabling warnings due to #52283
269 --disable_warnings
270 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);
271 --disable_warnings
272 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
273 DELETE FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
274 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
275 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 01:01:01') ORDER BY c1;
276 DELETE FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 01:01:01') ORDER BY c1 LIMIT 2;
277 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 01:01:01') ORDER BY c1;
278 # Disable warnings due to #52283
279 --disable_warnings
280 --sorted_result
281 SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
282 --enable_warnings
283 DELETE FROM t1 WHERE c1=ADDDATE(CURDATE(),'9') OR c2=ADDDATE(CURDATE(),'10');
284 # Disable warnings due to #43763
285 --disable_warnings
286 --sorted_result
287 SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
288 --enable_warnings
289 SELECT count(*) FROM t1;
290 TRUNCATE TABLE t1;
291 SELECT count(*) FROM t1;
292
293 # Multi-table delete
294 # eq-ref join
295 CREATE TABLE t2(c1 DATE NOT NULL PRIMARY KEY, c2 DATE, c3 INT);
296 INSERT INTO t1 VALUES('2001-01-02','2001-01-01',1),('2001-01-03','2001-01-02',2);
297 INSERT INTO t2 VALUES('2001-01-02','2001-01-03',1),('2001-01-04','2001-01-05',2);
298 --sorted_result
299 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
300 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c1;
301 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
302 TRUNCATE TABLE t1;
303 TRUNCATE TABLE t2;
304 INSERT INTO t1 VALUES('2001-01-01','2001-01-02',1),('2001-01-03','2001-01-02',2);
305 INSERT INTO t2 VALUES('2001-01-02','2001-01-03',1),('2001-01-04','2001-01-05',2);
306 --sorted_result
307 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
308 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
309 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
310 DROP TABLE t1,t2;
311
312 ######## Running UPDATE tests for DATETIME ########
313
314 # Create tables
315 CREATE TABLE t1(c1 DATETIME NOT NULL PRIMARY KEY, c2 DATETIME NULL, c3 INT, INDEX idx2(c2));
316
317 # Update date values to datetime field
318 INSERT INTO t1 VALUE('1998-01-01 09:23:45','10-01-01 14:12:33',1);
319 UPDATE t1 SET c1='2001-01-01' WHERE c2='10-01-01 14:12:33';
320 SELECT c1,hour(c1),minute(c1),second(c1) FROM t1 WHERE c2='10-01-01 14:12:33';
321
322 # Update with single digit hour, minute, second,fraction part to time
323 INSERT INTO t1 VALUES('2009-01-01 08:42:42','2009-01-02 23:59:59',2);
324 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';
325 SELECT c1,ADDTIME(c2,'1 1:1:1.000002') FROM t1 WHERE c3=2;
326
327 # Update with function
328 INSERT INTO t1 VALUES('2009-02-25 15:45','09-02-27 15:46',3);
329 SET TIMESTAMP=1235553613; #'2009-02-25'
330 UPDATE t1 SET c2=NOW() WHERE c1 >=CURDATE();
331 UPDATE t1 SET c1=DATE_ADD(CURDATE(),INTERVAL '1 1:1:1' DAY_SECOND) WHERE c2=CURRENT_TIMESTAMP();
332 --sorted_result
333 SELECT * FROM t1;
334
335 # Update with Null, null to valid date
336 INSERT INTO t1 VALUES('2001-01-06','2001-01-07 09:30:00',4),('2001-01-08',NULL,5);
337 --sorted_result
338 SELECT * FROM t1 WHERE c2 IS NULL;
339 UPDATE t1 SET c2=NULL WHERE c1='2001-01-06 00:00:00';
340 --sorted_result
341 SELECT * FROM t1 WHERE c2 IS NULL;
342 UPDATE t1 SET c2='2001-01-10 00:00:00' WHERE c2 IS NULL;
343 --sorted_result
344 SELECT * FROM t1 WHERE c2='2001-01-10';
345
346 # Update with invalid datetimes, converts to zero datetime
347 INSERT INTO t1 VALUES('2001-01-09','2001-01-10',6),('2001-01-11','2001-01-12',7);
348 --error ER_TRUNCATED_WRONG_VALUE
349 UPDATE t1 SET c1='2001-01-09 24:59:59',c2='2009-01-10 23:60:59' WHERE c1='2001-01-09';
350 --error ER_TRUNCATED_WRONG_VALUE
351 UPDATE t1 SET c2='2001-01-11 23:59:60' WHERE c1='2001-01-11';
352 --error ER_WRONG_VALUE
353 SELECT count(*) FROM t1 WHERE c1='2001-01-09 24:59:59' AND c2='2009-01-10 23:60:59';
354 --error ER_WRONG_VALUE
355 SELECT count(*) FROM t1 WHERE c2='2001-01-11 23:59:60' /* returns 0 */;
356 --error ER_WRONG_VALUE
357 SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
358
359 # Update with zero datetimes, zero datetimes to valid datetimes
360 --error ER_TRUNCATED_WRONG_VALUE
361 INSERT INTO t1 VALUES('2001-01-17','0000-00-00 00:00:00',8);
362 INSERT INTO t1 VALUES('2001-01-18','2001-01-18 07:35',9);
363 SET SQL_MODE=TRADITIONAL;
364 --error ER_WRONG_VALUE
365 SELECT * FROM t1 WHERE c2='0000-00-00 00:00:00';
366 --error ER_TRUNCATED_WRONG_VALUE
367 UPDATE t1 SET c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
368 SET SQL_MODE=DEFAULT;
369 --error ER_TRUNCATED_WRONG_VALUE
370 UPDATE t1 SET c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
371 --error ER_TRUNCATED_WRONG_VALUE
372 UPDATE t1 SET c2='2001-01-17 12:34:00' WHERE c2='0000-00-00 00:00:00' AND c1='2001-01-17';
373 SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=ALLOW_INVALID_DATES;
374 SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00' OR c2='0000-00-00 00:00:00';
375 SET @@SQL_MODE=@OLD_SQL_MODE;
376 --sorted_result
377 SELECT * FROM t1 WHERE c2='2001-01-17 12:34:00';
378
379 # Update with range values
380 INSERT INTO t1 VALUES('2001-01-21 05:43:43','2001-01-22 06:53:53',10);
381 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';
382 SELECT * FROM t1 WHERE c1='1000-01-01 00:00:00' AND c2='9999-12-31 23:59:59';
383
384 # Update ignore on bad null/duplicate key
385 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);
386 --error ER_DUP_ENTRY
387 UPDATE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
388 UPDATE IGNORE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
389 SELECT count(*) FROM t1 WHERE c1='2001-01-23 02:33';
390 --error ER_BAD_NULL_ERROR
391 UPDATE t1 SET c1=NULL WHERE c2='2001-01-24 03:33' /* updates to 0000-00-00 00:00:00 */;
392 UPDATE IGNORE t1 SET c1=NULL WHERE c2='2001-01-24 03:33';
393 SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=ALLOW_INVALID_DATES;
394 --sorted_result
395 SELECT * FROM t1 WHERE c1='0000-00-00 00:00:00';
396 SET @@SQL_MODE=@OLD_SQL_MODE;
397
398 # Multi table update
399
400 TRUNCATE TABLE t1;
401 CREATE TABLE t2(c1 DATETIME NOT NULL PRIMARY KEY, c2 DATETIME, c3 INT);
402
403 # eq-ref join
404 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);
405 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);
406 --sorted_result
407 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
408 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;
409 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
410
411 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);
412 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);
413 --sorted_result
414 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
415 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;
416 --sorted_result
417 SELECT * FROM t1,t2 WHERE t2.c1='2002-01-06 2:30:50' AND t1.c2='2002-01-06 2:30:50';
418 --sorted_result
419 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
420 DELETE FROM t1 WHERE c2='2002-01-06 2:30:50' AND c3=3;
421 --sorted_result
422 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
423 DROP TABLE t2;
424
425 # Update with other access methods, ORDER BY LIMIT
426 TRUNCATE TABLE t1;
427 ALTER TABLE t1 CHANGE c1 c1 DATETIME NOT NULL, DROP PRIMARY KEY;
428 ALTER TABLE t1 CHANGE c1 c1 DATETIME NOT NULL, ADD KEY(c1);
429
430 # ref access
431 UPDATE t1 SET c1='2002-01-07 7:30' WHERE c2='2002-01-05 5:30';
432 SELECT * FROM t1 WHERE c1='2002-01-07 7:30';
433
434 # range access, MRR
435 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);
436 --sorted_result
437 SELECT * FROM t1;
438 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2 LIMIT 2;
439 SELECT * FROM t1 WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2;
440 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 >= '20020109013010' ORDER BY c2 DESC LIMIT 2;
441 SELECT * FROM t1 WHERE c2 >= '20020109013010' ORDER BY c2 DESC;
442 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <='02-01-09 1:30:20' ORDER BY c2 LIMIT 2;
443 SELECT * FROM t1 WHERE c2 <='02-01-09 1:30:20' ORDER BY c2;
444 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <=> '020109013030' ORDER BY c2 DESC LIMIT 2;
445 SELECT * FROM t1 WHERE c2 <=> '020109013030' ORDER BY c2 DESC;
446 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;
447 SELECT * FROM t1 WHERE c2 BETWEEN '20020109 01:30:40' AND '20020109 01:30:50' ORDER BY c2;
448 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;
449 SELECT * FROM t1 WHERE c2 IN('2002-01-09 1:30:55','2002-01-09 2:00') ORDER BY c2 DESC;
450 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
451 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
452 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;
453 SELECT * FROM t1 WHERE c2>= '2002-02-09 2:00:5' AND c1 < '2002-02-09 2:00:15' ORDER BY c2;
454 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);
455 --error ER_TRUNCATED_WRONG_VALUE
456 INSERT INTO t1 VALUES('0000-00-00 00:00:01',NULL,5);
457 # Select using various access methods
458 ## Full table scan
459 --sorted_result
460 SELECT * FROM t1;
461 ## Forward index scan, covering ##
462 --sorted_result
463 SELECT c1 FROM t1;
464 ## Backward index scan, covering ##
465 SELECT c1 FROM t1 ORDER BY c1 DESC;
466 # ref access
467 --sorted_result
468 SELECT * FROM t1 WHERE c1='2003-01-01 1:2:3';
469 ## Range access ##
470 SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
471 SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
472 SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2 DESC LIMIT 2;
473 SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2;
474 SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
475 SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 DESC;
476 SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC LIMIT 2;
477 SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2;
478 SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
479 SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
480 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;
481 SELECT * FROM t1 WHERE c1 IN('2003-01-01 1:2:3','2005-01-03 00:00:01') ORDER BY c1,c2;
482 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
483 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
484 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
485 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1;
486 SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1 DESC LIMIT 2;
487 SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1;
488
489 # Index-merge access
490 SELECT * FROM t1 WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
491 UPDATE t1 SET c1='20060101000001' WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
492 --sorted_result
493 SELECT * FROM t1 WHERE c1='20060101000001' OR c2='020304010030';
494
495 # Testing delete using various access methods
496 TRUNCATE TABLE t1;
497 ALTER TABLE t1 CHANGE c1 c1 DATETIME NOT NULL, ADD PRIMARY KEY(c1);
498 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);
499 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);
500 SELECT * FROM t1 WHERE c1 <='1000-01-01 00:00:00';
501 DELETE FROM t1 WHERE c1 <='1000-01-01 00:00:00';
502 SELECT * FROM t1 WHERE c1 <='1000-01-01 00:00:00';
503 SELECT * FROM t1 WHERE c1 >='9999-12-31 23:59:59';
504 DELETE FROM t1 WHERE c1 >='9999-12-31 23:59:59';
505 SELECT * FROM t1 WHERE c1 >='9999-12-31 23:59:59';
506 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
507 DELETE FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC LIMIT 2;
508 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
509 SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
510 DELETE FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1 LIMIT 2;
511 SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
512 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);
513 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
514 DELETE FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
515 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
516 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
517 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
518 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
519 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
520 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
521 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
522 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);
523 SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
524 DELETE FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
525 SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
526 SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
527 DELETE FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC LIMIT 2;
528 SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
529 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
530 DELETE FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
531 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
532 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);
533 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
534 DELETE FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
535 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
536 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
537 DELETE FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1 LIMIT 2;
538 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
539
540 --sorted_result
541 SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
542 DELETE FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
543 --sorted_result
544 SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
545 SELECT count(*) FROM t1;
546 TRUNCATE TABLE t1;
547 SELECT count(*) FROM t1;
548
549 # Multi-table delete
550 # eq-ref join
551 CREATE TABLE t2(c1 DATETIME NOT NULL PRIMARY KEY, c2 DATETIME, c3 INT);
552 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);
553 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);
554 --sorted_result
555 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
556 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2;
557 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
558 TRUNCATE TABLE t1;
559 TRUNCATE TABLE t2;
560 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);
561 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);
562 --sorted_result
563 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
564 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
565 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
566 DROP TABLE t1,t2;
567
568 # More tests for update/delete........
569 CREATE TABLE mt1(id int(11) NOT NULL default '0', name varchar(10) default NULL, PRIMARY KEY (id));
570 INSERT INTO mt1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
571 CREATE TABLE mt2 (id int(11) NOT NULL default '0', name varchar(10) default NULL, PRIMARY KEY (id));
572 INSERT INTO mt2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
573 CREATE TABLE mt3 (id int(11) NOT NULL default '0', mydate datetime default NULL, PRIMARY KEY (id));
574 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');
575 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;
576 --sorted_result
577 select * from mt3;
578 DROP TABLE mt1,mt2,mt3;
579
580 ######## Running UPDATE tests for TIMESTAMP ########
581
582 # Create tables
583 CREATE TABLE t1(c1 TIMESTAMP NOT NULL PRIMARY KEY, c2 TIMESTAMP NULL, c3 INT, INDEX idx2(c2));
584
585 # Update date values to timestamp field
586 INSERT INTO t1 VALUE('1998-01-01 09:23:45','10-01-01 14:12:33',1);
587 UPDATE t1 SET c1='2001-01-01' WHERE c2='10-01-01 14:12:33';
588 SELECT c1,hour(c1),minute(c1),second(c1) FROM t1 WHERE c2='10-01-01 14:12:33';
589
590 # Update with single digit hour, minute, second,fraction part to time
591 INSERT INTO t1 VALUES('2009-01-01 08:42:42','2009-01-02 23:59:59',2);
592 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';
593 SELECT c1,ADDTIME(c2,'1 1:1:1.000002') FROM t1 WHERE c3=2;
594
595 # Update with function
596 INSERT INTO t1 VALUES('2009-02-25 15:45','09-02-27 15:46',3);
597 SET TIMESTAMP=1235553613; #'2009-02-25'
598 UPDATE t1 SET c2=NOW() WHERE c1 >=CURDATE() /* updates c1 with current timestamp */;
599 UPDATE t1 SET c1=DATE_ADD(CURDATE(),INTERVAL '1 1:1:1' DAY_SECOND) WHERE c2=CURRENT_TIMESTAMP();
600 --sorted_result
601 SELECT * FROM t1;
602
603 # Update with Null, null to valid date
604 INSERT INTO t1 VALUES('2001-01-06','2001-01-07 09:30:00',4),('2001-01-08',NULL,5);
605 UPDATE t1 SET c1='20010106010030',c2=NULL WHERE c1='2001-01-06 00:00:00';
606 --sorted_result
607 SELECT c1,c2 FROM t1 WHERE c2 IS NULL;
608 --sorted_result
609 SELECT * FROM t1;
610 UPDATE t1 SET c1=c1,c2='2001-01-10 00:00:00' WHERE c2 IS NULL;
611 --sorted_result
612 SELECT * FROM t1 WHERE c2='2001-01-10';
613
614 # Update with invalid datetimes, converts to zero datetime
615 INSERT INTO t1 VALUES('2001-01-09','2001-01-10',6),('2001-01-11','2001-01-12',7);
616 SELECT * FROM t1 ORDER BY c3;
617 --error ER_TRUNCATED_WRONG_VALUE
618 UPDATE t1 SET c1='2001-01-09 24:59:59',c2='2009-01-10 23:60:59' WHERE c1='2001-01-09';
619 --error ER_TRUNCATED_WRONG_VALUE
620 UPDATE t1 SET c1='2001-01-11 23:59:59',c2='2001-01-11 23:59:60' WHERE c1='2001-01-11';
621 SELECT * FROM t1 ORDER BY c3;
622
623 # Update with zero datetimes, zero datetimes to valid datetimes
624 --error ER_TRUNCATED_WRONG_VALUE
625 INSERT INTO t1 VALUES('2001-01-17','0000-00-00 00:00:00',8);
626 INSERT INTO t1 VALUES('2001-01-18','2001-01-18 07:35',9);
627 SET SQL_MODE=TRADITIONAL;
628 --sorted_result
629 SELECT * FROM t1;
630 --error ER_TRUNCATED_WRONG_VALUE
631 UPDATE t1 SET c1='1972-01-01 07:35',c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
632 SET SQL_MODE=DEFAULT;
633 --error ER_TRUNCATED_WRONG_VALUE
634 UPDATE t1 SET c1='1972-01-01 07:35',c2='0000-00-00 00:00:00' WHERE c2='2001-01-18 07:35';
635 --error ER_TRUNCATED_WRONG_VALUE
636 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';
637 --sorted_result
638 SELECT * FROM t1;
639 --sorted_result
640 SELECT * FROM t1 WHERE c1='2001-01-17 12:34:00' AND c2='2001-01-17 12:34:00';
641
642 # Update with range values
643 INSERT INTO t1 VALUES('2001-01-21 05:43:43','2001-01-22 06:53:53',10);
644 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';
645 SELECT * FROM t1 WHERE c1='1971-01-01 00:00:01' AND c2='2038-01-09 03:14:07';
646
647 # Update ignore on duplicate key
648 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);
649 --error ER_DUP_ENTRY
650 UPDATE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
651 UPDATE IGNORE t1 SET c1='2001-01-23 02:33' WHERE c1='2001-01-25 04:33';
652 SELECT count(*) FROM t1 WHERE c1='2001-01-23 02:33';
653 UPDATE t1 SET c1=CURRENT_TIMESTAMP WHERE c2='2001-01-24 03:33' /* updates to current timestamp */;
654 UPDATE IGNORE t1 SET c1=NULL WHERE c2='2001-01-26 05:33';
655 --sorted_result
656 SELECT * FROM t1 WHERE c1=CURRENT_TIMESTAMP();
657
658 # Multi table update
659
660 TRUNCATE TABLE t1;
661 CREATE TABLE t2(c1 TIMESTAMP NOT NULL PRIMARY KEY, c2 TIMESTAMP NULL, c3 INT);
662
663 # eq-ref join
664 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);
665 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);
666 --sorted_result
667 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
668 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;
669 SELECT * FROM t1,t2 WHERE t2.c1=t1.c1;
670
671 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);
672 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);
673 --sorted_result
674 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
675 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;
676 --sorted_result
677 SELECT * FROM t1,t2 WHERE t2.c1='2002-01-06 2:30:50' AND t1.c2='2002-01-06 2:30:50';
678 --sorted_result
679 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
680 DELETE FROM t1 WHERE c2='2002-01-06 2:30:50' AND c3=3;
681 --sorted_result
682 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
683 DROP TABLE t2;
684
685 # Update with other access methods, ORDER BY LIMIT
686 TRUNCATE TABLE t1;
687 ALTER TABLE t1 CHANGE c1 c1 TIMESTAMP NOT NULL, DROP PRIMARY KEY;
688 ALTER TABLE t1 CHANGE c1 c1 TIMESTAMP NOT NULL, ADD KEY(c1);
689
690 # ref access
691 UPDATE t1 SET c1='2002-01-07 7:30' WHERE c2='2002-01-05 5:30';
692 SELECT * FROM t1 WHERE c1='2002-01-07 7:30';
693
694 # range access, MRR
695 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);
696 --sorted_result
697 SELECT * FROM t1;
698 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2 LIMIT 2;
699 SELECT * FROM t1 WHERE c2 <> '2002-01-09 01:30:00' ORDER BY c2;
700 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 >= '20020109013010' ORDER BY c2 DESC LIMIT 2;
701 SELECT * FROM t1 WHERE c2 >= '20020109013010' ORDER BY c2 DESC;
702 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <='02-01-09 1:30:20' ORDER BY c2 LIMIT 2;
703 SELECT * FROM t1 WHERE c2 <='02-01-09 1:30:20' ORDER BY c2;
704 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 <=> '020109013030' ORDER BY c2 DESC LIMIT 2;
705 SELECT * FROM t1 WHERE c2 <=> '020109013030' ORDER BY c2 DESC;
706 --error ER_TRUNCATED_WRONG_VALUE
707 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;
708 SELECT * FROM t1 WHERE c2 BETWEEN '20020109 01:30:40' AND '20020109 01:30:50' ORDER BY c2;
709 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;
710 SELECT * FROM t1 WHERE c2 IN('2002-01-09 1:30:55','2002-01-09 2:00') ORDER BY c2 DESC;
711 UPDATE t1 SET c1='2003-01-01 1:2:3' WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
712 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
713 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;
714 SELECT * FROM t1 WHERE c2>= '2002-02-09 2:00:5' AND c1 < '2002-02-09 2:00:15' ORDER BY c2;
715 INSERT INTO t1 VALUES('20020301000001','20020302000001',1),('020303010030','020304010030',2),('050103000001','050104000001',3);
716 --error ER_TRUNCATED_WRONG_VALUE
717 INSERT INTO t1 VALUES('5-10-10 1:2:3','5-10-10 1:2:3',4);
718 --error ER_TRUNCATED_WRONG_VALUE
719 INSERT INTO t1 VALUES('0000-00-00 00:00:01',NULL,5);
720 # Select using various access methods
721 ## Full table scan
722 --sorted_result
723 SELECT * FROM t1;
724 ## Forward index scan, covering ##
725 --sorted_result
726 SELECT c1 FROM t1;
727 ## Backward index scan, covering ##
728 SELECT c1 FROM t1 ORDER BY c1 DESC;
729 # ref access
730 --sorted_result
731 SELECT * FROM t1 WHERE c1='2003-01-01 1:2:3';
732 ## Range access ##
733 SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
734 SELECT * FROM t1 WHERE c1 <> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
735 SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2 DESC LIMIT 2;
736 SELECT * FROM t1 WHERE c1 > '20030101010203' ORDER BY c1,c2;
737 SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
738 SELECT * FROM t1 WHERE c1 < '03-01-01 1:2:3' ORDER BY c1,c2 DESC;
739 SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2 DESC LIMIT 2;
740 SELECT * FROM t1 WHERE c1 <=> '2003-01-01 1:2:3' ORDER BY c1,c2;
741 SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 LIMIT 2;
742 SELECT * FROM t1 WHERE c1 BETWEEN '20020301' AND '2003-01-01 1:2:3' ORDER BY c1,c2 DESC;
743 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;
744 SELECT * FROM t1 WHERE c1 IN('2003-01-01 1:2:3','2005-01-03 00:00:01') ORDER BY c1,c2;
745 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
746 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 DESC;
747 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
748 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1;
749 SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1 DESC LIMIT 2;
750 SELECT * FROM t1 WHERE c1>='2003-01-01 01:02:03' AND c1 <= '20050104000001' ORDER BY c2,c1;
751
752 # Index-merge access
753 SELECT * FROM t1 WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
754 UPDATE t1 SET c1='20060101000001' WHERE c1='2003-01-03 1:2:3' OR c2='2002-03-02 00:00:01';
755 --sorted_result
756 SELECT * FROM t1 WHERE c1='20060101000001' OR c2='020304010030';
757
758 # Testing delete using various access methods
759 TRUNCATE TABLE t1;
760 ALTER TABLE t1 CHANGE c1 c1 TIMESTAMP NOT NULL, ADD PRIMARY KEY(c1);
761 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);
762 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);
763 SELECT * FROM t1 WHERE c1 <='1971-01-01 00:00:01';
764 DELETE FROM t1 WHERE c1 <='1971-01-01 00:00:01';
765 SELECT * FROM t1 WHERE c1 <='1971-01-01 00:00:01';
766 SELECT * FROM t1 WHERE c1 >='2038-01-09 03:14:07';
767 DELETE FROM t1 WHERE c1 >='2038-01-09 03:14:07';
768 SELECT * FROM t1 WHERE c1 >='2038-01-09 03:14:07';
769 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
770 DELETE FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC LIMIT 2;
771 SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
772 SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
773 DELETE FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1 LIMIT 2;
774 SELECT * FROM t1 WHERE c1 <> CURRENT_TIMESTAMP() ORDER BY c1;
775 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);
776 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
777 DELETE FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
778 SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
779 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
780 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
781 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
782 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
783 DELETE FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
784 SELECT * FROM t1 WHERE c1 <=> ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
785 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);
786 SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
787 DELETE FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
788 SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
789 SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
790 DELETE FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC LIMIT 2;
791 SELECT * FROM t1 WHERE c1 IN(NOW(),ADDTIME(NOW(),'3 01:01:01')) ORDER BY c1 DESC;
792 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
793 DELETE FROM t1 WHERE c2 IS NULL ORDER BY c2,c1 LIMIT 2;
794 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2,c1;
795 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);
796 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
797 DELETE FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC LIMIT 2;
798 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2,c1 DESC;
799 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
800 DELETE FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1 LIMIT 2;
801 SELECT * FROM t1 WHERE c1>=ADDTIME(NOW(),'5 01:01:01') AND c2 < ADDTIME(NOW(),'8 13:25') ORDER BY c1;
802
803 --sorted_result
804 SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
805 DELETE FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
806 --sorted_result
807 SELECT * FROM t1 WHERE c1=ADDTIME(NOW(),'9 01:01:01') OR c2=ADDTIME(NOW(),'10 01:01:01');
808 SELECT count(*) FROM t1;
809 TRUNCATE TABLE t1;
810 SELECT count(*) FROM t1;
811
812 # Multi-table delete
813 # eq-ref join
814 CREATE TABLE t2(c1 TIMESTAMP NOT NULL PRIMARY KEY, c2 TIMESTAMP NULL, c3 INT);
815 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);
816 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);
817 --sorted_result
818 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
819 DELETE t1,t2 FROM t1,t2 WHERE t2.c1=t1.c2;
820 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
821 TRUNCATE TABLE t1;
822 TRUNCATE TABLE t2;
823 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);
824 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);
825 --sorted_result
826 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
827 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
828 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
829 DROP TABLE t1,t2;
830