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