1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
|
--disable_warnings
DROP TABLE IF EXISTS t1,t2,t3,t4;
--enable_warnings
# Create tables
CREATE TABLE t1(c1 TIME NOT NULL, c2 TIME NULL, PRIMARY KEY(c1));
CREATE TABLE t2(c1 TIME NOT NULL, c2 TIME NULL, PRIMARY KEY(c1,c2));
CREATE TABLE t3(c1 TIME NOT NULL, c2 TIME NULL, UNIQUE INDEX idx(c1,c2));
CREATE TABLE t4(c1 TIME NOT NULL, c2 TIME NULL);
# As a string in 'D HH:MM:SS.fraction' format
INSERT INTO t1 VALUES ('00:00:00.00','00:00:00.00'),('01:01:01.01','01:01:01.00'),('838:59:59.00','838:59:59.00'),('00:00:01','00:00:01'),('02:02:02','02:02:02'),('838:59:58','838:59:58'),('-838:59:59','-838:59:59'),('00:03','00:03'),('03:03','03:03'),('838:58','838:58'),('00 00:00:04','00 00:00:04'),('04 04:04:04','04 04:04:04'),('34 22:59:57','34 22:59:57'),('00 00:04','00 00:04'),('05 05:05','05 05:05'),('34 22:56','34 22:56'),('05 05','05 05'),('06 06','06 06'),('34 22','34 22'),('07','07'),('59','59');
INSERT INTO t2 VALUES ('00:00:00.00','00:00:00.00'),('01:01:01.01','01:01:01.00'),('838:59:59.00','838:59:59.00'),('00:00:01','00:00:01'),('02:02:02','02:02:02'),('838:59:58','838:59:58'),('-838:59:59','-838:59:59'),('00:03','00:03'),('03:03','03:03'),('838:58','838:58'),('00 00:00:04','00 00:00:04'),('04 04:04:04','04 04:04:04'),('34 22:59:57','34 22:59:57'),('00 00:04','00 00:04'),('05 05:05','05 05:05'),('34 22:56','34 22:56'),('05 05','05 05'),('06 06','06 06'),('34 22','34 22'),('07','07'),('59','59');
INSERT INTO t3 VALUES ('00:00:00.00','00:00:00.00'),('01:01:01.01','01:01:01.00'),('838:59:59.00','838:59:59.00'),('00:00:01','00:00:01'),('02:02:02','02:02:02'),('838:59:58','838:59:58'),('-838:59:59','-838:59:59'),('00:03','00:03'),('03:03','03:03'),('838:58','838:58'),('00 00:00:04','00 00:00:04'),('04 04:04:04','04 04:04:04'),('34 22:59:57','34 22:59:57'),('00 00:04','00 00:04'),('05 05:05','05 05:05'),('34 22:56','34 22:56'),('05 05','05 05'),('06 06','06 06'),('34 22','34 22'),('07','07'),('59','59');
INSERT INTO t4 VALUES ('00:00:00.00','00:00:00.00'),('01:01:01.01','01:01:01.00'),('838:59:59.00','838:59:59.00'),('00:00:01','00:00:01'),('02:02:02','02:02:02'),('838:59:58','838:59:58'),('-838:59:59','-838:59:59'),('00:03','00:03'),('03:03','03:03'),('838:58','838:58'),('00 00:00:04','00 00:00:04'),('04 04:04:04','04 04:04:04'),('34 22:59:57','34 22:59:57'),('00 00:04','00 00:04'),('05 05:05','05 05:05'),('34 22:56','34 22:56'),('05 05','05 05'),('06 06','06 06'),('34 22','34 22'),('07','07'),('59','59');
#As a string with no delimiters in 'HHMMSS' format
INSERT INTO t1 VALUES('000008','000008'),('080808','080808'),('8385955','8385955'),('-8385955','-8385955'),('0009','0009'),('0909','0909'),('5454','5454'),('10','10'),('53','53');
INSERT INTO t2 VALUES('000008','000008'),('080808','080808'),('8385955','8385955'),('-8385955','-8385955'),('0009','0009'),('0909','0909'),('5454','5454'),('10','10'),('53','53');
INSERT INTO t3 VALUES('000008','000008'),('080808','080808'),('8385955','8385955'),('-8385955','-8385955'),('0009','0009'),('0909','0909'),('5454','5454'),('10','10'),('53','53');
INSERT INTO t4 VALUES('000008','000008'),('080808','080808'),('8385955','8385955'),('-8385955','-8385955'),('0009','0009'),('0909','0909'),('5454','5454'),('10','10'),('53','53');
#As a number in HHMMSS format
INSERT INTO t1 VALUES (000011.00,000011.00),(111111.11,111111.11),(8385952.00,8385952.00),(000012,000012),(121212,121212),(8385951,8385951),(1313,1313),(5151,5151),(14,14),(50,50);
INSERT INTO t2 VALUES (000011.00,000011.00),(111111.11,111111.11),(8385952.00,8385952.00),(000012,000012),(121212,121212),(8385951,8385951),(1313,1313),(5151,5151),(14,14),(50,50);
INSERT INTO t3 VALUES (000011.00,000011.00),(111111.11,111111.11),(8385952.00,8385952.00),(000012,000012),(121212,121212),(8385951,8385951),(1313,1313),(5151,5151),(14,14),(50,50);
INSERT INTO t4 VALUES (000011.00,000011.00),(111111.11,111111.11),(8385952.00,8385952.00),(000012,000012),(121212,121212),(8385951,8385951),(1313,1313),(5151,5151),(14,14),(50,50);
#As the result of a function that returns a value that is acceptable in a TIME context
SET TIMESTAMP=1233216687; # 2009-01-29 13:41:27
INSERT INTO t1 VALUES(CURRENT_TIME(),CURRENT_TIME());
INSERT INTO t2 VALUES(CURRENT_TIME(),CURRENT_TIME());
INSERT INTO t3 VALUES(CURRENT_TIME(),CURRENT_TIME());
INSERT INTO t4 VALUES(CURRENT_TIME(),CURRENT_TIME());
#Insert permissible NULLs
INSERT INTO t1 VALUES('123456',null);
#INSERT INTO t2 VALUES('123456',null);
INSERT INTO t3 VALUES('123456',null);
INSERT INTO t4 VALUES('123456',null);
--sorted_result
SELECT * FROM t1;
--sorted_result
SELECT * FROM t2;
--sorted_result
SELECT * FROM t3;
--sorted_result
SELECT * FROM t4;
#Updating the tables
--sorted_result
SELECT c1 FROM t1 WHERE c1='00:00:07';
UPDATE t1 SET c1='22:22:22' WHERE c1='00:00:07';
--sorted_result
SELECT c1 FROM t1;
--sorted_result
SELECT c1 FROM t2 WHERE c1='-838:59:59' AND c2='-838:59:59';
UPDATE t2 SET c1='22:22:22' WHERE c1='-838:59:59' AND c2='-838:59:59';
--sorted_result
SELECT c1 FROM t2;
# Update with NULL ( NULL to number & number to NULL)
--sorted_result
SELECT c2 FROM t3 WHERE c2=null;
UPDATE t3 SET c2='34 22:59:59' WHERE c2=null;
--sorted_result
SELECT c2 FROM t3;
--sorted_result
SELECT c2 FROM t4 WHERE c1='00:00:00';
UPDATE t4 SET c2=null WHERE c1='00:00:00';
--sorted_result
SELECT c2 FROM t4;
#Update order by limit
--sorted_result
SELECT c1 FROM t4 WHERE c1 < '000009';
UPDATE t4 SET c1='0101' WHERE c1 < '000009' ORDER BY c1 LIMIT 3;
--sorted_result
SELECT c1 FROM t4;
#Update negative value to unsigned column
--sorted_result
SELECT c1 FROM t1 WHERE c1='12:12:12';
UPDATE t1 SET c1='-12:12:12' WHERE c1='12:12:12';
--sorted_result
SELECT c1 FROM t1;
#Update with Arithmetic operations
--sorted_result
SELECT c1 FROM t1 WHERE c1=111127;
UPDATE t1 SET c1=c1+c2 WHERE c1=111127;
--sorted_result
SELECT c1 FROM t1;
--sorted_result
SELECT c1 FROM t2 WHERE c2='100:04:04';
UPDATE t2 SET c1=c1 - '010101' WHERE c2=1000404;
--sorted_result
SELECT c1 FROM t2;
--sorted_result
SELECT c2 FROM t3 WHERE c2=020202;
UPDATE t3 SET c2=c1 * 2 WHERE c2='020202';
--sorted_result
SELECT c1 FROM t3;
--sorted_result
SELECT c1 FROM t4 WHERE c1=121212;
UPDATE t4 SET c1=c1 / 2 WHERE c1='12:12:12';
--sorted_result
SELECT c1 FROM t4;
#Update range values
UPDATE t1 SET c2=1250000 WHERE c1='00:00:09' AND c1='01:01:01';
--sorted_result
SELECT c2 FROM t1;
UPDATE t2 SET c1=125959 WHERE c2=000400 OR c2= 000900;
--sorted_result
SELECT c1 FROM t2;
UPDATE t2 SET c2='1111' WHERE c1 IN ('100:04:04',005454,'2:2:2',111111);
--sorted_result
SELECT c2 FROM t2;
#Update outside range would be clipped to closest endpoints
UPDATE IGNORE t4 SET c2='-838:59:60' WHERE c1='100:04:04';
--sorted_result
SELECT c2 FROM t4;
UPDATE IGNORE t4 SET c2='838:59:60' WHERE c1='100:04:04';
--sorted_result
SELECT c2 FROM t4;
UPDATE t4 SET c2='00:00:00' WHERE c1='100:04:04';
--sorted_result
SELECT c2 FROM t4;
UPDATE IGNORE t4 SET c2='11:11:60' WHERE c1='100:04:04';
--sorted_result
SELECT c2 FROM t4;
UPDATE IGNORE t4 SET c2='11:60:11' WHERE c1='100:04:04';
--sorted_result
SELECT c2 FROM t4;
# Update ignore on bad null error
--sorted_result
SELECT c1 FROM t3 WHERE c2 < '000007';
SET SQL_MODE=STRICT_ALL_TABLES;
--error ER_BAD_NULL_ERROR
UPDATE t3 SET c1=NULL WHERE c2 < '000007';
UPDATE IGNORE t3 SET c1=NULL WHERE c2 < '000007';
--sorted_result
SELECT c1 from t3 WHERE c2 < '000007';
SET SQL_MODE=DEFAULT;
#Duplicate keys
--error ER_DUP_ENTRY
UPDATE t1 SET c1=8385958 WHERE c2='34 22:59:59';
#Update with invalid values
UPDATE IGNORE t1 SET c2='def' WHERE c1=59;
--sorted_result
SELECT c2 FROM t1;
--error ER_PARSE_ERROR
UPDATE t4 SET c1=11:11:11 WHERE c2=NULL;
--sorted_result
SELECT c1 FROM t4;
#Multi table update
UPDATE t1,t2,t3,t4 SET t1.c2=t2.c1+t2.c2,t3.c2=t4.c1*2 WHERE t1.c1='00:13:13' AND t2.c1=080808 AND t4.c1='00:04:00' AND t3.c2=020202;
# Update using various access methods
# Update using Const
# EXPLAIN SELECT * FROM t1 WHERE c1='00:09:09' AND c2='00:09:09';
UPDATE t1 SET t1.c1='00:12:12' WHERE c1='00:09:09' AND c2='00:09:09';
--sorted_result
SELECT * FROM t1;
# Update using range
# EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 080000 AND 100000;
UPDATE IGNORE t1 SET t1.c2='99999.99999' WHERE c1 BETWEEN 080000 AND 100000;
--sorted_result
SELECT * FROM t1;
# EXPLAIN SELECT * FROM t1 WHERE c1 IN (222222,8385955,1500000);
UPDATE IGNORE t1 SET c1=c1+1,c2=c2-1 WHERE c1 IN (222222,8385955,1500000) ORDER BY c1;
--sorted_result
SELECT * FROM t1;
# Update using eq_ref
# EXPLAIN SELECT * FROM t2,t3 WHERE t2.c1=t3.c1 AND t2.c3=t3.c3;
UPDATE t1,t2 SET t1.c2='22222.22222' WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;
--sorted_result
SELECT * FROM t1;
--sorted_result
SELECT * FROM t2;
--sorted_result
SELECT * FROM t3;
--sorted_result
SELECT * FROM t4;
#Drop tables;
DROP TABLE IF EXISTS t1,t2,t3,t4;
|
