| 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 |
|