Debian Codegraph @

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

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1,t2,t3,t4;
3 --enable_warnings
4 SET sql_mode='NO_ENGINE_SUBSTITUTION';
5 ######## Running INSERT tests for TIME ########
6
7 # Create tables
8 CREATE TABLE t1(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, PRIMARY KEY(c1), UNIQUE INDEX(c2));
9 CREATE TABLE t2(c1 TIME NOT NULL, c2 TIME NOT NULL, c3 DATE, PRIMARY KEY(c1,c2));
10 CREATE TABLE t3(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, UNIQUE INDEX idx(c1,c2));
11 CREATE TABLE t4(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE);
12 # Insert some rows with targeted values
13
14 # As a string in 'D HH:MM:SS.fraction' format
15 INSERT INTO t1 VALUES('12:34:56.78','12:34:56.78','2009-01-01'),('10:22:33','10:22:33','2009-01-02'),('1:23','1:23','2009-01-03'),('20 11:22:33','20 11:22:33','2009-01-04'),('34 9:23','34 9:23','2009-01-05'),('0 10','0 10','2009-01-06'),('45','45','2009-01-07'),('-838:59:59','-838:59:59','2009-01-08'),('0','0','2009-01-09'),('838:59:59','838:59:59','2009-01-10');
16 INSERT INTO t2 VALUES('12:34:56.78','12:34:56.78','2009-01-01'),('10:22:33','10:22:33','2009-01-02'),('1:23','1:23','2009-01-03'),('20 11:22:33','20 11:22:33','2009-01-04'),('34 9:23','34 9:23','2009-01-05'),('0 10','0 10','2009-01-06'),('45','45','2009-01-07'),('-838:59:59','-838:59:59','2009-01-08'),('0','0','2009-01-09'),('838:59:59','838:59:59','2009-01-10');
17 INSERT INTO t3 VALUES('12:34:56.78','12:34:56.78','2009-01-01'),('10:22:33','10:22:33','2009-01-02'),('1:23','1:23','2009-01-03'),('20 11:22:33','20 11:22:33','2009-01-04'),('34 9:23','34 9:23','2009-01-05'),('0 10','0 10','2009-01-06'),('45','45','2009-01-07'),('-838:59:59','-838:59:59','2009-01-08'),('0','0','2009-01-09'),('838:59:59','838:59:59','2009-01-10');
18 INSERT INTO t4 VALUES('12:34:56.78','12:34:56.78','2009-01-01'),('10:22:33','10:22:33','2009-01-02'),('1:23','1:23','2009-01-03'),('20 11:22:33','20 11:22:33','2009-01-04'),('34 9:23','34 9:23','2009-01-05'),('0 10','0 10','2009-01-06'),('45','45','2009-01-07'),('-838:59:59','-838:59:59','2009-01-08'),('0','0','2009-01-09'),('838:59:59','838:59:59','2009-01-10');
19
20 # As a string with no delimiters in 'HHMMSS' format
21 INSERT INTO t1 VALUES('101112','101112','2009-01-11');
22 INSERT INTO t2 VALUES('101112','101112','2009-01-11');
23 INSERT INTO t3 VALUES('101112','101112','2009-01-11');
24 INSERT INTO t4 VALUES('101112','101112','2009-01-11');
25
26 # As a number in HHMMSS format
27 INSERT INTO t1 VALUES(111112,111112,'2009-01-12'),(12,12,'2009-01-13'),(1234,1234,'2009-01-14'),(123458,123458,'2009-01-15'),(123556.99,123556.99,'2009-01-16');
28 INSERT INTO t2 VALUES(111112,111112,'2009-01-12'),(12,12,'2009-01-13'),(1234,1234,'2009-01-14'),(123458,123458,'2009-01-15'),(123556.99,123556.99,'2009-01-16');
29 INSERT INTO t3 VALUES(111112,111112,'2009-01-12'),(12,12,'2009-01-13'),(1234,1234,'2009-01-14'),(123458,123458,'2009-01-15'),(123556.99,123556.99,'2009-01-16');
30 INSERT INTO t4 VALUES(111112,111112,'2009-01-12'),(12,12,'2009-01-13'),(1234,1234,'2009-01-14'),(123458,123458,'2009-01-15'),(123556.99,123556.99,'2009-01-16');
31
32 # As the result of a function
33 SET TIMESTAMP=1233216687; # 2009-01-29 13:41:27
34 INSERT INTO t1 VALUES(CURRENT_TIME(),CURRENT_TIME(),'2009-01-17');
35 INSERT INTO t2 VALUES(CURRENT_TIME(),CURRENT_TIME(),'2009-01-17');
36 INSERT INTO t3 VALUES(CURRENT_TIME(),CURRENT_TIME(),'2009-01-17');
37 INSERT INTO t4 VALUES(CURRENT_TIME(),CURRENT_TIME(),'2009-01-17');
38
39 # Other valid formats (single digit, zero hrs,mins,secs.)
40 INSERT INTO t1 VALUES('8:3:2','8:3:2','2009-01-18'),('1112','1112','2009-01-19'),(11,11,'2009-01-20'),('00:12:30','00:12:30','2009-01-23'),('9:00:45','9:00:45','2009-01-24'),('9:36:00','9:36:00','2009-01-25');
41 INSERT INTO t2 VALUES('8:3:2','8:3:2','2009-01-18'),('1112','1112','2009-01-19'),(11,11,'2009-01-20'),('00:12:30','00:12:30','2009-01-23'),('9:00:45','9:00:45','2009-01-24'),('9:36:00','9:36:00','2009-01-25');
42 INSERT INTO t3 VALUES('8:3:2','8:3:2','2009-01-18'),('1112','1112','2009-01-19'),(11,11,'2009-01-20'),('00:12:30','00:12:30','2009-01-23'),('9:00:45','9:00:45','2009-01-24'),('9:36:00','9:36:00','2009-01-25');
43 INSERT INTO t4 VALUES('8:3:2','8:3:2','2009-01-18'),('1112','1112','2009-01-19'),(11,11,'2009-01-20'),('00:12:30','00:12:30','2009-01-23'),('9:00:45','9:00:45','2009-01-24'),('9:36:00','9:36:00','2009-01-25');
44 # Outside range would be clipped to closest end point
45 --error ER_DUP_ENTRY
46 INSERT INTO t1 VALUES('-850:00:00','-850:00:00','2009-01-21');
47 --error ER_DUP_ENTRY
48 INSERT INTO t1 VALUES('850:00:00','850:00:00','2009-01-21');
49 DELETE FROM t1 WHERE c1='-838:59:59' AND c2='-838:59:59';
50 DELETE FROM t1 WHERE c1='838:59:59' AND c2='838:59:59';
51 INSERT INTO t1 VALUES('-850:00:00','-850:00:00','2009-01-21'),('850:00:00','850:00:00','2009-01-21');
52
53 # Insert invalid values
54 INSERT INTO t4 VALUES('10.22.22','10.22.22','2009-01-26'),(1234567,1234567,'2009-01-27'),(123456789,123456789,'2009-01-28'),(123456789.10,123456789.10,'2009-01-29'),('10 22:22','10 22:22','2009-01-30'),('12.45a','12.45a','2009-01-31');
55
56 # Insert permissible NULLs
57 INSERT INTO t1 VALUES('8:29:45',NULL,'2009-02-01');
58 INSERT INTO t3 VALUES('8:29:45',NULL,'2009-02-01');
59 INSERT INTO t4 VALUES('8:29:45',NULL,'2009-02-01');
60
61 # Insert duplicate NULLs to unique column
62 INSERT INTO t1(c1,c2) VALUES('9:30',NULL);
63 DELETE FROM t1 WHERE c1='9:30' AND c2 IS NULL;
64
65 --sorted_result
66 SELECT * FROM t1;
67 --sorted_result
68 SELECT * FROM t2;
69 --sorted_result
70 SELECT * FROM t3;
71 --sorted_result
72 SELECT * FROM t4;
73
74 # Now select using various table access methods (full table scan, range scan, index scan etc.)
75 ## Full table scan ##
76 --sorted_result
77 SELECT * FROM t1;
78 --sorted_result
79 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1;
80 --sorted_result
81 SELECT * FROM t1 WHERE c3 = '2009-01-17';
82
83 ## Forward index scan, covering ##
84 --sorted_result
85 SELECT c1 FROM t1;
86
87 ## Backward index scan, covering ##
88 SELECT c1 FROM t1 ORDER BY c1 DESC;
89
90 ## Forward index scan, non-covering ##
91 SELECT * FROM t1 ORDER BY c1 LIMIT 2;
92
93 ## Backward index scan, non-covering ##
94 SELECT * FROM t1 ORDER BY c1 DESC LIMIT 2;
95
96 ## ref type access
97 SELECT * FROM t1 WHERE c1 = '838:59:59' ORDER BY c1;
98 SELECT * FROM t1 WHERE c1 = '838:59:59' ORDER BY c1 LIMIT 2;
99 SELECT * FROM t1 WHERE c1 = '838:59:59' ORDER BY c1 DESC;
100 SELECT * FROM t1 WHERE c1 = '838:59:59' ORDER BY c1 DESC LIMIT 2;
101
102 ## Range access, ordered ##
103 SELECT * FROM t1 WHERE c1 <> '838:59:59' ORDER BY c1;
104 SELECT * FROM t1 WHERE c1 <> '838:59:59' ORDER BY c1 LIMIT 2;
105 SELECT * FROM t1 WHERE c1 > '838:59:59' ORDER BY c1;
106 SELECT * FROM t1 WHERE c1 > '838:59:59' ORDER BY c1 LIMIT 2;
107 SELECT * FROM t1 WHERE c1 >= '838:59:59' ORDER BY c1;
108 SELECT * FROM t1 WHERE c1 >= '838:59:59' ORDER BY c1 LIMIT 2;
109 SELECT * FROM t1 WHERE c1 < '838:59:59' ORDER BY c1;
110 SELECT * FROM t1 WHERE c1 < '838:59:59' ORDER BY c1 LIMIT 2;
111 SELECT * FROM t1 WHERE c1 <= '838:59:59' ORDER BY c1;
112 SELECT * FROM t1 WHERE c1 <= '838:59:59' ORDER BY c1 LIMIT 2;
113 SELECT * FROM t1 WHERE c1 <=> '838:59:59' ORDER BY c1;
114 SELECT * FROM t1 WHERE c1 <=> '838:59:59' ORDER BY c1 LIMIT 2;
115 SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1;
116 SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1 LIMIT 2;
117 SELECT * FROM t1 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1;
118 SELECT * FROM t1 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1 LIMIT 2;
119 SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1;
120 SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1 LIMIT 2;
121 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1;
122 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1 LIMIT 2;
123 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1;
124 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1 LIMIT 2;
125
126 ## Range access, backwards scan ##
127 SELECT * FROM t1 WHERE c1 <> '838:59:59' ORDER BY c1 DESC;
128 SELECT * FROM t1 WHERE c1 <> '838:59:59' ORDER BY c1 DESC LIMIT 2;
129 SELECT * FROM t1 WHERE c1 > '838:59:59' ORDER BY c1 DESC;
130 SELECT * FROM t1 WHERE c1 > '838:59:59' ORDER BY c1 DESC LIMIT 2;
131 SELECT * FROM t1 WHERE c1 >= '838:59:59' ORDER BY c1 DESC;
132 SELECT * FROM t1 WHERE c1 >= '838:59:59' ORDER BY c1 DESC LIMIT 2;
133 SELECT * FROM t1 WHERE c1 < '838:59:59' ORDER BY c1 DESC;
134 SELECT * FROM t1 WHERE c1 < '838:59:59' ORDER BY c1 DESC LIMIT 2;
135 SELECT * FROM t1 WHERE c1 <= '838:59:59' ORDER BY c1 DESC;
136 SELECT * FROM t1 WHERE c1 <= '838:59:59' ORDER BY c1 DESC LIMIT 2;
137 SELECT * FROM t1 WHERE c1 <=> '838:59:59' ORDER BY c1 DESC;
138 SELECT * FROM t1 WHERE c1 <=> '838:59:59' ORDER BY c1 DESC LIMIT 2;
139 SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1 DESC;
140 SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1 DESC LIMIT 2;
141 SELECT * FROM t1 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1 DESC;
142 SELECT * FROM t1 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1 DESC LIMIT 2;
143 SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1 DESC;
144 SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1 DESC LIMIT 2;
145 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1 DESC;
146 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1 DESC LIMIT 2;
147 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1 DESC;
148 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1 DESC LIMIT 2;
149 ## Full table scan ##
150 --sorted_result
151 SELECT * FROM t1;
152 --sorted_result
153 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1;
154 --sorted_result
155 SELECT * FROM t1 WHERE c3 = '2009-01-16';
156
157 ## Forward index scan, covering ##
158 --sorted_result
159 SELECT c1 FROM t1;
160
161 ## Backward index scan, covering ##
162 SELECT c1 FROM t1 ORDER BY c1 DESC;
163
164 ## Forward index scan, non-covering ##
165 SELECT * FROM t1 ORDER BY c1 LIMIT 2;
166
167 ## Backward index scan, non-covering ##
168 SELECT * FROM t1 ORDER BY c1 DESC LIMIT 2;
169
170 ## ref type access
171 SELECT * FROM t1 WHERE c1 = '00:00:00' ORDER BY c1;
172 SELECT * FROM t1 WHERE c1 = '00:00:00' ORDER BY c1 LIMIT 2;
173 SELECT * FROM t1 WHERE c1 = '00:00:00' ORDER BY c1 DESC;
174 SELECT * FROM t1 WHERE c1 = '00:00:00' ORDER BY c1 DESC LIMIT 2;
175
176 ## Range access, ordered ##
177 SELECT * FROM t1 WHERE c1 <> '00:00:00' ORDER BY c1;
178 SELECT * FROM t1 WHERE c1 <> '00:00:00' ORDER BY c1 LIMIT 2;
179 SELECT * FROM t1 WHERE c1 > '00:00:00' ORDER BY c1;
180 SELECT * FROM t1 WHERE c1 > '00:00:00' ORDER BY c1 LIMIT 2;
181 SELECT * FROM t1 WHERE c1 >= '00:00:00' ORDER BY c1;
182 SELECT * FROM t1 WHERE c1 >= '00:00:00' ORDER BY c1 LIMIT 2;
183 SELECT * FROM t1 WHERE c1 < '00:00:00' ORDER BY c1;
184 SELECT * FROM t1 WHERE c1 < '00:00:00' ORDER BY c1 LIMIT 2;
185 SELECT * FROM t1 WHERE c1 <= '00:00:00' ORDER BY c1;
186 SELECT * FROM t1 WHERE c1 <= '00:00:00' ORDER BY c1 LIMIT 2;
187 SELECT * FROM t1 WHERE c1 <=> '00:00:00' ORDER BY c1;
188 SELECT * FROM t1 WHERE c1 <=> '00:00:00' ORDER BY c1 LIMIT 2;
189 SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1;
190 SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1 LIMIT 2;
191 SELECT * FROM t1 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1;
192 SELECT * FROM t1 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1 LIMIT 2;
193 SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1;
194 SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1 LIMIT 2;
195 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1;
196 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1 LIMIT 2;
197 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1;
198 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1 LIMIT 2;
199
200 ## Range access, backwards scan ##
201 SELECT * FROM t1 WHERE c1 <> '00:00:00' ORDER BY c1 DESC;
202 SELECT * FROM t1 WHERE c1 <> '00:00:00' ORDER BY c1 DESC LIMIT 2;
203 SELECT * FROM t1 WHERE c1 > '00:00:00' ORDER BY c1 DESC;
204 SELECT * FROM t1 WHERE c1 > '00:00:00' ORDER BY c1 DESC LIMIT 2;
205 SELECT * FROM t1 WHERE c1 >= '00:00:00' ORDER BY c1 DESC;
206 SELECT * FROM t1 WHERE c1 >= '00:00:00' ORDER BY c1 DESC LIMIT 2;
207 SELECT * FROM t1 WHERE c1 < '00:00:00' ORDER BY c1 DESC;
208 SELECT * FROM t1 WHERE c1 < '00:00:00' ORDER BY c1 DESC LIMIT 2;
209 SELECT * FROM t1 WHERE c1 <= '00:00:00' ORDER BY c1 DESC;
210 SELECT * FROM t1 WHERE c1 <= '00:00:00' ORDER BY c1 DESC LIMIT 2;
211 SELECT * FROM t1 WHERE c1 <=> '00:00:00' ORDER BY c1 DESC;
212 SELECT * FROM t1 WHERE c1 <=> '00:00:00' ORDER BY c1 DESC LIMIT 2;
213 SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1 DESC;
214 SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1 DESC LIMIT 2;
215 SELECT * FROM t1 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1 DESC;
216 SELECT * FROM t1 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1 DESC LIMIT 2;
217 SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1 DESC;
218 SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1 DESC LIMIT 2;
219 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1 DESC;
220 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1 DESC LIMIT 2;
221 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1 DESC;
222 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1 DESC LIMIT 2;
223 ## Full table scan ##
224 --sorted_result
225 SELECT * FROM t1;
226 --sorted_result
227 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t1;
228 --sorted_result
229 SELECT * FROM t1 WHERE c3 = '2009-01-15';
230
231 ## Forward index scan, covering ##
232 --sorted_result
233 SELECT c2 FROM t1;
234
235 ## Backward index scan, covering ##
236 SELECT c2 FROM t1 ORDER BY c2 DESC;
237
238 ## Forward index scan, non-covering ##
239 SELECT * FROM t1 ORDER BY c2 LIMIT 2;
240
241 ## Backward index scan, non-covering ##
242 SELECT * FROM t1 ORDER BY c2 DESC LIMIT 2;
243
244 ## ref type access
245 SELECT * FROM t1 WHERE c2 = '-838:59:59' ORDER BY c2;
246 SELECT * FROM t1 WHERE c2 = '-838:59:59' ORDER BY c2 LIMIT 2;
247 SELECT * FROM t1 WHERE c2 = '-838:59:59' ORDER BY c2 DESC;
248 SELECT * FROM t1 WHERE c2 = '-838:59:59' ORDER BY c2 DESC LIMIT 2;
249
250 ## Range access, ordered ##
251 SELECT * FROM t1 WHERE c2 <> '-838:59:59' ORDER BY c2;
252 SELECT * FROM t1 WHERE c2 <> '-838:59:59' ORDER BY c2 LIMIT 2;
253 SELECT * FROM t1 WHERE c2 > '-838:59:59' ORDER BY c2;
254 SELECT * FROM t1 WHERE c2 > '-838:59:59' ORDER BY c2 LIMIT 2;
255 SELECT * FROM t1 WHERE c2 >= '-838:59:59' ORDER BY c2;
256 SELECT * FROM t1 WHERE c2 >= '-838:59:59' ORDER BY c2 LIMIT 2;
257 SELECT * FROM t1 WHERE c2 < '-838:59:59' ORDER BY c2;
258 SELECT * FROM t1 WHERE c2 < '-838:59:59' ORDER BY c2 LIMIT 2;
259 SELECT * FROM t1 WHERE c2 <= '-838:59:59' ORDER BY c2;
260 SELECT * FROM t1 WHERE c2 <= '-838:59:59' ORDER BY c2 LIMIT 2;
261 SELECT * FROM t1 WHERE c2 <=> '-838:59:59' ORDER BY c2;
262 SELECT * FROM t1 WHERE c2 <=> '-838:59:59' ORDER BY c2 LIMIT 2;
263 SELECT * FROM t1 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c2;
264 SELECT * FROM t1 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c2 LIMIT 2;
265 SELECT * FROM t1 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c2;
266 SELECT * FROM t1 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c2 LIMIT 2;
267 SELECT * FROM t1 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c2;
268 SELECT * FROM t1 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c2 LIMIT 2;
269 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
270 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
271 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2;
272 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2 LIMIT 2;
273
274 ## Range access, backwards scan ##
275 SELECT * FROM t1 WHERE c2 <> '-838:59:59' ORDER BY c2 DESC;
276 SELECT * FROM t1 WHERE c2 <> '-838:59:59' ORDER BY c2 DESC LIMIT 2;
277 SELECT * FROM t1 WHERE c2 > '-838:59:59' ORDER BY c2 DESC;
278 SELECT * FROM t1 WHERE c2 > '-838:59:59' ORDER BY c2 DESC LIMIT 2;
279 SELECT * FROM t1 WHERE c2 >= '-838:59:59' ORDER BY c2 DESC;
280 SELECT * FROM t1 WHERE c2 >= '-838:59:59' ORDER BY c2 DESC LIMIT 2;
281 SELECT * FROM t1 WHERE c2 < '-838:59:59' ORDER BY c2 DESC;
282 SELECT * FROM t1 WHERE c2 < '-838:59:59' ORDER BY c2 DESC LIMIT 2;
283 SELECT * FROM t1 WHERE c2 <= '-838:59:59' ORDER BY c2 DESC;
284 SELECT * FROM t1 WHERE c2 <= '-838:59:59' ORDER BY c2 DESC LIMIT 2;
285 SELECT * FROM t1 WHERE c2 <=> '-838:59:59' ORDER BY c2 DESC;
286 SELECT * FROM t1 WHERE c2 <=> '-838:59:59' ORDER BY c2 DESC LIMIT 2;
287 SELECT * FROM t1 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c2 DESC;
288 SELECT * FROM t1 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c2 DESC LIMIT 2;
289 SELECT * FROM t1 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c2 DESC;
290 SELECT * FROM t1 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c2 DESC LIMIT 2;
291 SELECT * FROM t1 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c2 DESC;
292 SELECT * FROM t1 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c2 DESC LIMIT 2;
293 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2 DESC;
294 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2 DESC LIMIT 2;
295 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2 DESC;
296 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2 DESC LIMIT 2;
297 ## Full table scan ##
298 --sorted_result
299 SELECT * FROM t1;
300 --sorted_result
301 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t1;
302 --sorted_result
303 SELECT * FROM t1 WHERE c3 = '2009-01-11';
304
305 ## Forward index scan, covering ##
306 --sorted_result
307 SELECT c2 FROM t1;
308
309 ## Backward index scan, covering ##
310 SELECT c2 FROM t1 ORDER BY c2 DESC;
311
312 ## Forward index scan, non-covering ##
313 SELECT * FROM t1 ORDER BY c2 LIMIT 2;
314
315 ## Backward index scan, non-covering ##
316 SELECT * FROM t1 ORDER BY c2 DESC LIMIT 2;
317
318 ## ref type access
319 SELECT * FROM t1 WHERE c2 = NULL ORDER BY c2;
320 SELECT * FROM t1 WHERE c2 = NULL ORDER BY c2 LIMIT 2;
321 SELECT * FROM t1 WHERE c2 = NULL ORDER BY c2 DESC;
322 SELECT * FROM t1 WHERE c2 = NULL ORDER BY c2 DESC LIMIT 2;
323
324 ## Range access, ordered ##
325 SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2;
326 SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 LIMIT 2;
327 SELECT * FROM t1 WHERE c2 > NULL ORDER BY c2;
328 SELECT * FROM t1 WHERE c2 > NULL ORDER BY c2 LIMIT 2;
329 SELECT * FROM t1 WHERE c2 >= NULL ORDER BY c2;
330 SELECT * FROM t1 WHERE c2 >= NULL ORDER BY c2 LIMIT 2;
331 SELECT * FROM t1 WHERE c2 < NULL ORDER BY c2;
332 SELECT * FROM t1 WHERE c2 < NULL ORDER BY c2 LIMIT 2;
333 SELECT * FROM t1 WHERE c2 <= NULL ORDER BY c2;
334 SELECT * FROM t1 WHERE c2 <= NULL ORDER BY c2 LIMIT 2;
335 SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2;
336 SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2;
337 SELECT * FROM t1 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c2;
338 SELECT * FROM t1 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c2 LIMIT 2;
339 SELECT * FROM t1 WHERE c2 IN (NULL,'10:22:33') ORDER BY c2;
340 SELECT * FROM t1 WHERE c2 IN (NULL,'10:22:33') ORDER BY c2 LIMIT 2;
341 SELECT * FROM t1 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c2;
342 SELECT * FROM t1 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c2 LIMIT 2;
343 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2;
344 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
345 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2;
346 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2 LIMIT 2;
347
348 ## Range access, backwards scan ##
349 SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 DESC;
350 SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 DESC LIMIT 2;
351 SELECT * FROM t1 WHERE c2 > NULL ORDER BY c2 DESC;
352 SELECT * FROM t1 WHERE c2 > NULL ORDER BY c2 DESC LIMIT 2;
353 SELECT * FROM t1 WHERE c2 >= NULL ORDER BY c2 DESC;
354 SELECT * FROM t1 WHERE c2 >= NULL ORDER BY c2 DESC LIMIT 2;
355 SELECT * FROM t1 WHERE c2 < NULL ORDER BY c2 DESC;
356 SELECT * FROM t1 WHERE c2 < NULL ORDER BY c2 DESC LIMIT 2;
357 SELECT * FROM t1 WHERE c2 <= NULL ORDER BY c2 DESC;
358 SELECT * FROM t1 WHERE c2 <= NULL ORDER BY c2 DESC LIMIT 2;
359 SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 DESC;
360 SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 DESC LIMIT 2;
361 SELECT * FROM t1 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c2 DESC;
362 SELECT * FROM t1 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c2 DESC LIMIT 2;
363 SELECT * FROM t1 WHERE c2 IN (NULL,'10:22:33') ORDER BY c2 DESC;
364 SELECT * FROM t1 WHERE c2 IN (NULL,'10:22:33') ORDER BY c2 DESC LIMIT 2;
365 SELECT * FROM t1 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c2 DESC;
366 SELECT * FROM t1 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c2 DESC LIMIT 2;
367 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2 DESC;
368 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2 DESC LIMIT 2;
369 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2 DESC;
370 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2 DESC LIMIT 2;
371
372 # Index-merge access
373 --sorted_result
374 SELECT * FROM t1 WHERE c1='838:59:59' OR c2='-838:59:59';
375
376 ## Full table scan ##
377 --sorted_result
378 SELECT * FROM t2;
379 --sorted_result
380 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2;
381 --sorted_result
382 SELECT * FROM t2 WHERE c3 = '2009-01-17';
383
384 ## Forward index scan, covering ##
385 --sorted_result
386 SELECT c1,c2 FROM t2;
387
388 ## Backward index scan, covering ##
389 SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC;
390
391 ## Forward index scan, non-covering ##
392 SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2;
393
394 ## Backward index scan, non-covering ##
395 SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2;
396
397 ## ref type access
398 SELECT * FROM t2 WHERE c1 = '838:59:59' ORDER BY c1,c2;
399 SELECT * FROM t2 WHERE c1 = '838:59:59' ORDER BY c1,c2 LIMIT 2;
400 SELECT * FROM t2 WHERE c1 = '838:59:59' ORDER BY c1,c2 DESC;
401 SELECT * FROM t2 WHERE c1 = '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
402
403 ## Range access, ordered ##
404 SELECT * FROM t2 WHERE c1 <> '838:59:59' ORDER BY c1,c2;
405 SELECT * FROM t2 WHERE c1 <> '838:59:59' ORDER BY c1,c2 LIMIT 2;
406 SELECT * FROM t2 WHERE c1 > '838:59:59' ORDER BY c1,c2;
407 SELECT * FROM t2 WHERE c1 > '838:59:59' ORDER BY c1,c2 LIMIT 2;
408 SELECT * FROM t2 WHERE c1 >= '838:59:59' ORDER BY c1,c2;
409 SELECT * FROM t2 WHERE c1 >= '838:59:59' ORDER BY c1,c2 LIMIT 2;
410 SELECT * FROM t2 WHERE c1 < '838:59:59' ORDER BY c1,c2;
411 SELECT * FROM t2 WHERE c1 < '838:59:59' ORDER BY c1,c2 LIMIT 2;
412 SELECT * FROM t2 WHERE c1 <= '838:59:59' ORDER BY c1,c2;
413 SELECT * FROM t2 WHERE c1 <= '838:59:59' ORDER BY c1,c2 LIMIT 2;
414 SELECT * FROM t2 WHERE c1 <=> '838:59:59' ORDER BY c1,c2;
415 SELECT * FROM t2 WHERE c1 <=> '838:59:59' ORDER BY c1,c2 LIMIT 2;
416 SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2;
417 SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2 LIMIT 2;
418 SELECT * FROM t2 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2;
419 SELECT * FROM t2 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2 LIMIT 2;
420 SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1,c2;
421 SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1,c2 LIMIT 2;
422 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2;
423 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
424 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2;
425 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
426
427 ## Range access, backwards scan ##
428 SELECT * FROM t2 WHERE c1 <> '838:59:59' ORDER BY c1,c2 DESC;
429 SELECT * FROM t2 WHERE c1 <> '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
430 SELECT * FROM t2 WHERE c1 > '838:59:59' ORDER BY c1,c2 DESC;
431 SELECT * FROM t2 WHERE c1 > '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
432 SELECT * FROM t2 WHERE c1 >= '838:59:59' ORDER BY c1,c2 DESC;
433 SELECT * FROM t2 WHERE c1 >= '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
434 SELECT * FROM t2 WHERE c1 < '838:59:59' ORDER BY c1,c2 DESC;
435 SELECT * FROM t2 WHERE c1 < '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
436 SELECT * FROM t2 WHERE c1 <= '838:59:59' ORDER BY c1,c2 DESC;
437 SELECT * FROM t2 WHERE c1 <= '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
438 SELECT * FROM t2 WHERE c1 <=> '838:59:59' ORDER BY c1,c2 DESC;
439 SELECT * FROM t2 WHERE c1 <=> '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
440 SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2 DESC;
441 SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
442 SELECT * FROM t2 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2 DESC;
443 SELECT * FROM t2 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2 DESC LIMIT 2;
444 SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1,c2 DESC;
445 SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1,c2 DESC LIMIT 2;
446 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
447 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
448 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
449 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
450 ## Full table scan ##
451 --sorted_result
452 SELECT * FROM t2;
453 --sorted_result
454 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2;
455 --sorted_result
456 SELECT * FROM t2 WHERE c3 = '2009-01-16';
457
458 ## Forward index scan, covering ##
459 --sorted_result
460 SELECT c1,c2 FROM t2;
461
462 ## Backward index scan, covering ##
463 SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC;
464
465 ## Forward index scan, non-covering ##
466 SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2;
467
468 ## Backward index scan, non-covering ##
469 SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2;
470
471 ## ref type access
472 SELECT * FROM t2 WHERE c1 = '00:00:00' ORDER BY c1,c2;
473 SELECT * FROM t2 WHERE c1 = '00:00:00' ORDER BY c1,c2 LIMIT 2;
474 SELECT * FROM t2 WHERE c1 = '00:00:00' ORDER BY c1,c2 DESC;
475 SELECT * FROM t2 WHERE c1 = '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
476
477 ## Range access, ordered ##
478 SELECT * FROM t2 WHERE c1 <> '00:00:00' ORDER BY c1,c2;
479 SELECT * FROM t2 WHERE c1 <> '00:00:00' ORDER BY c1,c2 LIMIT 2;
480 SELECT * FROM t2 WHERE c1 > '00:00:00' ORDER BY c1,c2;
481 SELECT * FROM t2 WHERE c1 > '00:00:00' ORDER BY c1,c2 LIMIT 2;
482 SELECT * FROM t2 WHERE c1 >= '00:00:00' ORDER BY c1,c2;
483 SELECT * FROM t2 WHERE c1 >= '00:00:00' ORDER BY c1,c2 LIMIT 2;
484 SELECT * FROM t2 WHERE c1 < '00:00:00' ORDER BY c1,c2;
485 SELECT * FROM t2 WHERE c1 < '00:00:00' ORDER BY c1,c2 LIMIT 2;
486 SELECT * FROM t2 WHERE c1 <= '00:00:00' ORDER BY c1,c2;
487 SELECT * FROM t2 WHERE c1 <= '00:00:00' ORDER BY c1,c2 LIMIT 2;
488 SELECT * FROM t2 WHERE c1 <=> '00:00:00' ORDER BY c1,c2;
489 SELECT * FROM t2 WHERE c1 <=> '00:00:00' ORDER BY c1,c2 LIMIT 2;
490 SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2;
491 SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2 LIMIT 2;
492 SELECT * FROM t2 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2;
493 SELECT * FROM t2 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2 LIMIT 2;
494 SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1,c2;
495 SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1,c2 LIMIT 2;
496 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2;
497 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
498 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2;
499 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
500
501 ## Range access, backwards scan ##
502 SELECT * FROM t2 WHERE c1 <> '00:00:00' ORDER BY c1,c2 DESC;
503 SELECT * FROM t2 WHERE c1 <> '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
504 SELECT * FROM t2 WHERE c1 > '00:00:00' ORDER BY c1,c2 DESC;
505 SELECT * FROM t2 WHERE c1 > '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
506 SELECT * FROM t2 WHERE c1 >= '00:00:00' ORDER BY c1,c2 DESC;
507 SELECT * FROM t2 WHERE c1 >= '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
508 SELECT * FROM t2 WHERE c1 < '00:00:00' ORDER BY c1,c2 DESC;
509 SELECT * FROM t2 WHERE c1 < '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
510 SELECT * FROM t2 WHERE c1 <= '00:00:00' ORDER BY c1,c2 DESC;
511 SELECT * FROM t2 WHERE c1 <= '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
512 SELECT * FROM t2 WHERE c1 <=> '00:00:00' ORDER BY c1,c2 DESC;
513 SELECT * FROM t2 WHERE c1 <=> '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
514 SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2 DESC;
515 SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2 DESC LIMIT 2;
516 SELECT * FROM t2 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2 DESC;
517 SELECT * FROM t2 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2 DESC LIMIT 2;
518 SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1,c2 DESC;
519 SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1,c2 DESC LIMIT 2;
520 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
521 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
522 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
523 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
524 ## Full table scan ##
525 --sorted_result
526 SELECT * FROM t2;
527 --sorted_result
528 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2;
529 --sorted_result
530 SELECT * FROM t2 WHERE c3 = '2009-01-15';
531
532 ## Forward index scan, covering ##
533 --sorted_result
534 SELECT c1,c2 FROM t2;
535
536 ## Backward index scan, covering ##
537 SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC;
538
539 ## Forward index scan, non-covering ##
540 SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2;
541
542 ## Backward index scan, non-covering ##
543 SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2;
544
545 ## ref type access
546 SELECT * FROM t2 WHERE c2 = '-838:59:59' ORDER BY c1,c2;
547 SELECT * FROM t2 WHERE c2 = '-838:59:59' ORDER BY c1,c2 LIMIT 2;
548 SELECT * FROM t2 WHERE c2 = '-838:59:59' ORDER BY c1,c2 DESC;
549 SELECT * FROM t2 WHERE c2 = '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
550
551 ## Range access, ordered ##
552 SELECT * FROM t2 WHERE c2 <> '-838:59:59' ORDER BY c1,c2;
553 SELECT * FROM t2 WHERE c2 <> '-838:59:59' ORDER BY c1,c2 LIMIT 2;
554 SELECT * FROM t2 WHERE c2 > '-838:59:59' ORDER BY c1,c2;
555 SELECT * FROM t2 WHERE c2 > '-838:59:59' ORDER BY c1,c2 LIMIT 2;
556 SELECT * FROM t2 WHERE c2 >= '-838:59:59' ORDER BY c1,c2;
557 SELECT * FROM t2 WHERE c2 >= '-838:59:59' ORDER BY c1,c2 LIMIT 2;
558 SELECT * FROM t2 WHERE c2 < '-838:59:59' ORDER BY c1,c2;
559 SELECT * FROM t2 WHERE c2 < '-838:59:59' ORDER BY c1,c2 LIMIT 2;
560 SELECT * FROM t2 WHERE c2 <= '-838:59:59' ORDER BY c1,c2;
561 SELECT * FROM t2 WHERE c2 <= '-838:59:59' ORDER BY c1,c2 LIMIT 2;
562 SELECT * FROM t2 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2;
563 SELECT * FROM t2 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2 LIMIT 2;
564 SELECT * FROM t2 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2;
565 SELECT * FROM t2 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2 LIMIT 2;
566 SELECT * FROM t2 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2;
567 SELECT * FROM t2 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2 LIMIT 2;
568 SELECT * FROM t2 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c1,c2;
569 SELECT * FROM t2 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c1,c2 LIMIT 2;
570 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2;
571 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
572 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2;
573 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
574
575 ## Range access, backwards scan ##
576 SELECT * FROM t2 WHERE c2 <> '-838:59:59' ORDER BY c1,c2 DESC;
577 SELECT * FROM t2 WHERE c2 <> '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
578 SELECT * FROM t2 WHERE c2 > '-838:59:59' ORDER BY c1,c2 DESC;
579 SELECT * FROM t2 WHERE c2 > '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
580 SELECT * FROM t2 WHERE c2 >= '-838:59:59' ORDER BY c1,c2 DESC;
581 SELECT * FROM t2 WHERE c2 >= '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
582 SELECT * FROM t2 WHERE c2 < '-838:59:59' ORDER BY c1,c2 DESC;
583 SELECT * FROM t2 WHERE c2 < '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
584 SELECT * FROM t2 WHERE c2 <= '-838:59:59' ORDER BY c1,c2 DESC;
585 SELECT * FROM t2 WHERE c2 <= '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
586 SELECT * FROM t2 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2 DESC;
587 SELECT * FROM t2 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
588 SELECT * FROM t2 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2 DESC;
589 SELECT * FROM t2 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2 DESC LIMIT 2;
590 SELECT * FROM t2 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2 DESC;
591 SELECT * FROM t2 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2 DESC LIMIT 2;
592 SELECT * FROM t2 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c1,c2 DESC;
593 SELECT * FROM t2 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c1,c2 DESC LIMIT 2;
594 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
595 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
596 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
597 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
598 ## Full table scan ##
599 --sorted_result
600 SELECT * FROM t2;
601 --sorted_result
602 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2;
603 --sorted_result
604 SELECT * FROM t2 WHERE c3 = '2009-01-11';
605
606 ## Forward index scan, covering ##
607 --sorted_result
608 SELECT c1,c2 FROM t2;
609
610 ## Backward index scan, covering ##
611 SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC;
612
613 ## Forward index scan, non-covering ##
614 SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2;
615
616 ## Backward index scan, non-covering ##
617 SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2;
618
619 ## ref type access
620 SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2;
621 SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2;
622 SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC;
623 SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2;
624
625 ## Range access, ordered ##
626 SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2;
627 SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2;
628 SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2;
629 SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2;
630 SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2;
631 SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2;
632 SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2;
633 SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2;
634 SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2;
635 SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2;
636 SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2;
637 SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
638 SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c1,c2;
639 SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c1,c2 LIMIT 2;
640 SELECT * FROM t2 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2;
641 SELECT * FROM t2 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2 LIMIT 2;
642 SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c1,c2;
643 SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c1,c2 LIMIT 2;
644 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2;
645 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
646 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2;
647 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
648
649 ## Range access, backwards scan ##
650 SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC;
651 SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2;
652 SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC;
653 SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2;
654 SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC;
655 SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2;
656 SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC;
657 SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2;
658 SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC;
659 SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2;
660 SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC;
661 SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2;
662 SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c1,c2 DESC;
663 SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c1,c2 DESC LIMIT 2;
664 SELECT * FROM t2 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2 DESC;
665 SELECT * FROM t2 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2 DESC LIMIT 2;
666 SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c1,c2 DESC;
667 SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c1,c2 DESC LIMIT 2;
668 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
669 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
670 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
671 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
672 ## Full table scan ##
673 --sorted_result
674 SELECT * FROM t3;
675 --sorted_result
676 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3;
677 --sorted_result
678 SELECT * FROM t3 WHERE c3 = '2009-01-17';
679
680 ## Forward index scan, covering ##
681 --sorted_result
682 SELECT c1,c2 FROM t3;
683
684 ## Backward index scan, covering ##
685 SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC;
686
687 ## Forward index scan, non-covering ##
688 SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2;
689
690 ## Backward index scan, non-covering ##
691 SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2;
692
693 ## ref type access
694 SELECT * FROM t3 WHERE c1 = '838:59:59' ORDER BY c1,c2;
695 SELECT * FROM t3 WHERE c1 = '838:59:59' ORDER BY c1,c2 LIMIT 2;
696 SELECT * FROM t3 WHERE c1 = '838:59:59' ORDER BY c1,c2 DESC;
697 SELECT * FROM t3 WHERE c1 = '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
698
699 ## Range access, ordered ##
700 SELECT * FROM t3 WHERE c1 <> '838:59:59' ORDER BY c1,c2;
701 SELECT * FROM t3 WHERE c1 <> '838:59:59' ORDER BY c1,c2 LIMIT 2;
702 SELECT * FROM t3 WHERE c1 > '838:59:59' ORDER BY c1,c2;
703 SELECT * FROM t3 WHERE c1 > '838:59:59' ORDER BY c1,c2 LIMIT 2;
704 SELECT * FROM t3 WHERE c1 >= '838:59:59' ORDER BY c1,c2;
705 SELECT * FROM t3 WHERE c1 >= '838:59:59' ORDER BY c1,c2 LIMIT 2;
706 SELECT * FROM t3 WHERE c1 < '838:59:59' ORDER BY c1,c2;
707 SELECT * FROM t3 WHERE c1 < '838:59:59' ORDER BY c1,c2 LIMIT 2;
708 SELECT * FROM t3 WHERE c1 <= '838:59:59' ORDER BY c1,c2;
709 SELECT * FROM t3 WHERE c1 <= '838:59:59' ORDER BY c1,c2 LIMIT 2;
710 SELECT * FROM t3 WHERE c1 <=> '838:59:59' ORDER BY c1,c2;
711 SELECT * FROM t3 WHERE c1 <=> '838:59:59' ORDER BY c1,c2 LIMIT 2;
712 SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2;
713 SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2 LIMIT 2;
714 SELECT * FROM t3 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2;
715 SELECT * FROM t3 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2 LIMIT 2;
716 SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1,c2;
717 SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1,c2 LIMIT 2;
718 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2;
719 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
720 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2;
721 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
722
723 ## Range access, backwards scan ##
724 SELECT * FROM t3 WHERE c1 <> '838:59:59' ORDER BY c1,c2 DESC;
725 SELECT * FROM t3 WHERE c1 <> '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
726 SELECT * FROM t3 WHERE c1 > '838:59:59' ORDER BY c1,c2 DESC;
727 SELECT * FROM t3 WHERE c1 > '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
728 SELECT * FROM t3 WHERE c1 >= '838:59:59' ORDER BY c1,c2 DESC;
729 SELECT * FROM t3 WHERE c1 >= '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
730 SELECT * FROM t3 WHERE c1 < '838:59:59' ORDER BY c1,c2 DESC;
731 SELECT * FROM t3 WHERE c1 < '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
732 SELECT * FROM t3 WHERE c1 <= '838:59:59' ORDER BY c1,c2 DESC;
733 SELECT * FROM t3 WHERE c1 <= '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
734 SELECT * FROM t3 WHERE c1 <=> '838:59:59' ORDER BY c1,c2 DESC;
735 SELECT * FROM t3 WHERE c1 <=> '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
736 SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2 DESC;
737 SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
738 SELECT * FROM t3 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2 DESC;
739 SELECT * FROM t3 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2 DESC LIMIT 2;
740 SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1,c2 DESC;
741 SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1,c2 DESC LIMIT 2;
742 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
743 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
744 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
745 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
746 ## Full table scan ##
747 --sorted_result
748 SELECT * FROM t3;
749 --sorted_result
750 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3;
751 --sorted_result
752 SELECT * FROM t3 WHERE c3 = '2009-01-16';
753
754 ## Forward index scan, covering ##
755 --sorted_result
756 SELECT c1,c2 FROM t3;
757
758 ## Backward index scan, covering ##
759 SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC;
760
761 ## Forward index scan, non-covering ##
762 SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2;
763
764 ## Backward index scan, non-covering ##
765 SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2;
766
767 ## ref type access
768 SELECT * FROM t3 WHERE c1 = '00:00:00' ORDER BY c1,c2;
769 SELECT * FROM t3 WHERE c1 = '00:00:00' ORDER BY c1,c2 LIMIT 2;
770 SELECT * FROM t3 WHERE c1 = '00:00:00' ORDER BY c1,c2 DESC;
771 SELECT * FROM t3 WHERE c1 = '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
772
773 ## Range access, ordered ##
774 SELECT * FROM t3 WHERE c1 <> '00:00:00' ORDER BY c1,c2;
775 SELECT * FROM t3 WHERE c1 <> '00:00:00' ORDER BY c1,c2 LIMIT 2;
776 SELECT * FROM t3 WHERE c1 > '00:00:00' ORDER BY c1,c2;
777 SELECT * FROM t3 WHERE c1 > '00:00:00' ORDER BY c1,c2 LIMIT 2;
778 SELECT * FROM t3 WHERE c1 >= '00:00:00' ORDER BY c1,c2;
779 SELECT * FROM t3 WHERE c1 >= '00:00:00' ORDER BY c1,c2 LIMIT 2;
780 SELECT * FROM t3 WHERE c1 < '00:00:00' ORDER BY c1,c2;
781 SELECT * FROM t3 WHERE c1 < '00:00:00' ORDER BY c1,c2 LIMIT 2;
782 SELECT * FROM t3 WHERE c1 <= '00:00:00' ORDER BY c1,c2;
783 SELECT * FROM t3 WHERE c1 <= '00:00:00' ORDER BY c1,c2 LIMIT 2;
784 SELECT * FROM t3 WHERE c1 <=> '00:00:00' ORDER BY c1,c2;
785 SELECT * FROM t3 WHERE c1 <=> '00:00:00' ORDER BY c1,c2 LIMIT 2;
786 SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2;
787 SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2 LIMIT 2;
788 SELECT * FROM t3 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2;
789 SELECT * FROM t3 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2 LIMIT 2;
790 SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1,c2;
791 SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1,c2 LIMIT 2;
792 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2;
793 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
794 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2;
795 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
796
797 ## Range access, backwards scan ##
798 SELECT * FROM t3 WHERE c1 <> '00:00:00' ORDER BY c1,c2 DESC;
799 SELECT * FROM t3 WHERE c1 <> '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
800 SELECT * FROM t3 WHERE c1 > '00:00:00' ORDER BY c1,c2 DESC;
801 SELECT * FROM t3 WHERE c1 > '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
802 SELECT * FROM t3 WHERE c1 >= '00:00:00' ORDER BY c1,c2 DESC;
803 SELECT * FROM t3 WHERE c1 >= '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
804 SELECT * FROM t3 WHERE c1 < '00:00:00' ORDER BY c1,c2 DESC;
805 SELECT * FROM t3 WHERE c1 < '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
806 SELECT * FROM t3 WHERE c1 <= '00:00:00' ORDER BY c1,c2 DESC;
807 SELECT * FROM t3 WHERE c1 <= '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
808 SELECT * FROM t3 WHERE c1 <=> '00:00:00' ORDER BY c1,c2 DESC;
809 SELECT * FROM t3 WHERE c1 <=> '00:00:00' ORDER BY c1,c2 DESC LIMIT 2;
810 SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2 DESC;
811 SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2 DESC LIMIT 2;
812 SELECT * FROM t3 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2 DESC;
813 SELECT * FROM t3 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2 DESC LIMIT 2;
814 SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1,c2 DESC;
815 SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1,c2 DESC LIMIT 2;
816 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
817 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
818 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
819 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
820 ## Full table scan ##
821 --sorted_result
822 SELECT * FROM t3;
823 --sorted_result
824 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3;
825 --sorted_result
826 SELECT * FROM t3 WHERE c3 = '2009-01-15';
827
828 ## Forward index scan, covering ##
829 --sorted_result
830 SELECT c1,c2 FROM t3;
831
832 ## Backward index scan, covering ##
833 SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC;
834
835 ## Forward index scan, non-covering ##
836 SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2;
837
838 ## Backward index scan, non-covering ##
839 SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2;
840
841 ## ref type access
842 SELECT * FROM t3 WHERE c2 = '-838:59:59' ORDER BY c1,c2;
843 SELECT * FROM t3 WHERE c2 = '-838:59:59' ORDER BY c1,c2 LIMIT 2;
844 SELECT * FROM t3 WHERE c2 = '-838:59:59' ORDER BY c1,c2 DESC;
845 SELECT * FROM t3 WHERE c2 = '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
846
847 ## Range access, ordered ##
848 SELECT * FROM t3 WHERE c2 <> '-838:59:59' ORDER BY c1,c2;
849 SELECT * FROM t3 WHERE c2 <> '-838:59:59' ORDER BY c1,c2 LIMIT 2;
850 SELECT * FROM t3 WHERE c2 > '-838:59:59' ORDER BY c1,c2;
851 SELECT * FROM t3 WHERE c2 > '-838:59:59' ORDER BY c1,c2 LIMIT 2;
852 SELECT * FROM t3 WHERE c2 >= '-838:59:59' ORDER BY c1,c2;
853 SELECT * FROM t3 WHERE c2 >= '-838:59:59' ORDER BY c1,c2 LIMIT 2;
854 SELECT * FROM t3 WHERE c2 < '-838:59:59' ORDER BY c1,c2;
855 SELECT * FROM t3 WHERE c2 < '-838:59:59' ORDER BY c1,c2 LIMIT 2;
856 SELECT * FROM t3 WHERE c2 <= '-838:59:59' ORDER BY c1,c2;
857 SELECT * FROM t3 WHERE c2 <= '-838:59:59' ORDER BY c1,c2 LIMIT 2;
858 SELECT * FROM t3 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2;
859 SELECT * FROM t3 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2 LIMIT 2;
860 SELECT * FROM t3 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2;
861 SELECT * FROM t3 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2 LIMIT 2;
862 SELECT * FROM t3 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2;
863 SELECT * FROM t3 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2 LIMIT 2;
864 SELECT * FROM t3 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c1,c2;
865 SELECT * FROM t3 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c1,c2 LIMIT 2;
866 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2;
867 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
868 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2;
869 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
870
871 ## Range access, backwards scan ##
872 SELECT * FROM t3 WHERE c2 <> '-838:59:59' ORDER BY c1,c2 DESC;
873 SELECT * FROM t3 WHERE c2 <> '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
874 SELECT * FROM t3 WHERE c2 > '-838:59:59' ORDER BY c1,c2 DESC;
875 SELECT * FROM t3 WHERE c2 > '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
876 SELECT * FROM t3 WHERE c2 >= '-838:59:59' ORDER BY c1,c2 DESC;
877 SELECT * FROM t3 WHERE c2 >= '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
878 SELECT * FROM t3 WHERE c2 < '-838:59:59' ORDER BY c1,c2 DESC;
879 SELECT * FROM t3 WHERE c2 < '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
880 SELECT * FROM t3 WHERE c2 <= '-838:59:59' ORDER BY c1,c2 DESC;
881 SELECT * FROM t3 WHERE c2 <= '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
882 SELECT * FROM t3 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2 DESC;
883 SELECT * FROM t3 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2;
884 SELECT * FROM t3 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2 DESC;
885 SELECT * FROM t3 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2 DESC LIMIT 2;
886 SELECT * FROM t3 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2 DESC;
887 SELECT * FROM t3 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2 DESC LIMIT 2;
888 SELECT * FROM t3 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c1,c2 DESC;
889 SELECT * FROM t3 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c1,c2 DESC LIMIT 2;
890 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
891 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
892 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
893 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
894 ## Full table scan ##
895 --sorted_result
896 SELECT * FROM t3;
897 --sorted_result
898 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3;
899 --sorted_result
900 SELECT * FROM t3 WHERE c3 = '2009-01-11';
901
902 ## Forward index scan, covering ##
903 --sorted_result
904 SELECT c1,c2 FROM t3;
905
906 ## Backward index scan, covering ##
907 SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC;
908
909 ## Forward index scan, non-covering ##
910 SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2;
911
912 ## Backward index scan, non-covering ##
913 SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2;
914
915 ## ref type access
916 SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2;
917 SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2;
918 SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC;
919 SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2;
920
921 ## Range access, ordered ##
922 SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2;
923 SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2;
924 SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2;
925 SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2;
926 SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2;
927 SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2;
928 SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2;
929 SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2;
930 SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2;
931 SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2;
932 SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2;
933 SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
934 SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c1,c2;
935 SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c1,c2 LIMIT 2;
936 SELECT * FROM t3 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2;
937 SELECT * FROM t3 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2 LIMIT 2;
938 SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c1,c2;
939 SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c1,c2 LIMIT 2;
940 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2;
941 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
942 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2;
943 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
944
945 ## Range access, backwards scan ##
946 SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC;
947 SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2;
948 SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC;
949 SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2;
950 SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC;
951 SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2;
952 SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC;
953 SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2;
954 SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC;
955 SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2;
956 SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC;
957 SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2;
958 SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c1,c2 DESC;
959 SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c1,c2 DESC LIMIT 2;
960 SELECT * FROM t3 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2 DESC;
961 SELECT * FROM t3 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2 DESC LIMIT 2;
962 SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c1,c2 DESC;
963 SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c1,c2 DESC LIMIT 2;
964 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
965 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
966 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
967 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
968
969 # Using index for group-by
970 --sorted_result
971 SELECT c1 FROM t3 GROUP BY c1;
972 --sorted_result
973 SELECT DISTINCT c1 FROM t3;
974 --sorted_result
975 SELECT c1,MIN(c2) FROM t3 GROUP BY c1;
976
977 # Some more testing.........
978
979 # These must return normal result
980 SELECT CAST(235959.123456 AS TIME);
981 SELECT CAST(0.235959123456e+6 AS TIME);
982 SELECT CAST(235959123456e-6 AS TIME);
983 # These must cut fraction part and produce warning
984 SELECT CAST(235959.1234567 AS TIME);
985 SELECT CAST(0.2359591234567e6 AS TIME);
986 # This must return NULL and produce warning
987 --replace_result 2.359591234567e+029 2.359591234567e+29
988 SELECT CAST(0.2359591234567e+30 AS TIME);
989
990 # Compare time values as strings
991 select cast('100:55:50' as time) < cast('24:00:00' as time);
992 select cast('100:55:50' as time) < cast('024:00:00' as time);
993 select cast('300:55:50' as time) < cast('240:00:00' as time);
994 select cast('100:55:50' as time) > cast('24:00:00' as time);
995 select cast('100:55:50' as time) > cast('024:00:00' as time);
996 select cast('300:55:50' as time) > cast('240:00:00' as time);
997 create table t5 (f1 time);
998 insert into t5 values ('24:00:00');
999 select cast('24:00:00' as time) = (select f1 from t5);
1000 drop table t5;
1001 # Test Error conditions- PK constraint violation, Unique constraint violation
1002
1003 # Insert duplicate value to pk column
1004 --error ER_DUP_ENTRY
1005 INSERT INTO t1(c1,c2) VALUES('10:22:33','10:22:34') /* throws error as row exists with c1='10:22:33' */;
1006
1007 # Insert duplicate value to clustered pk, throws error
1008 --error ER_DUP_ENTRY
1009 INSERT INTO t2(c1,c2) VALUES('12:34:56.78','12:34:56.78') /* throws error as row exists with c1='12:34:56.78',c2='12:34:56.78' */;
1010
1011 # Insert duplicate value to unique column, throws error
1012 --error ER_DUP_ENTRY
1013 INSERT INTO t1(c1,c2) VALUES('10:22:34','34 9:23') /* throws error as row exists with c2='34 9:23' */;
1014
1015 # Insert duplicate value to clustered unique column, throws error
1016 --error ER_DUP_ENTRY
1017 INSERT INTO t3(c1,c2) VALUES('34 9:23','34 9:23') /* throws error as row exists with c1='34 9:23',c2='34 9:23' */;
1018
1019 # Test 'INSERT IGNORE' with the same rows that reported constraint violation above
1020 # Ignore pk constraint
1021 INSERT IGNORE INTO t1(c1,c2) VALUES('10:22:33','10:22:34') /* doesn't throw error */;
1022 INSERT IGNORE INTO t2(c1,c2) VALUES('12:34:56.78','12:34:56.78') /*doesn't throw error */;
1023 # Ignore unique constraint
1024 INSERT IGNORE INTO t1(c1,c2) VALUES('10:22:34','34 9:23') /*doesn't throw error */;
1025 INSERT IGNORE INTO t3(c1,c2) VALUES('34 9:23','34 9:23') /*doesn't throw error */;
1026 # Select
1027
1028 # Test 'INSERT ON DUPLICATE KEY UPDATE' with PK one column/multi-column
1029 SELECT * FROM t1 WHERE c1='10:23:33' /* no rows */;
1030 INSERT INTO t1(c1) VALUES('10:22:33') ON DUPLICATE KEY UPDATE c1='10:23:33';
1031 SELECT * FROM t1 WHERE c1='10:23:33' /* 1 row */;
1032 SELECT * FROM t2 WHERE c1='12:34:56.79' AND c2='12:34:57.78' /* no rows */;
1033 INSERT INTO t2(c1,c2) VALUES('12:34:56.78','12:34:56.78') ON DUPLICATE KEY UPDATE c1='12:34:56.79',c2='12:34:57.78';
1034 SELECT * FROM t2 WHERE c1='12:34:56.79' AND c2='12:34:57.78' /* 1 row */;
1035 # Test 'INSERT ON DUPLICATE KEY UPDATE' with unique one column/multi-column
1036 SELECT * FROM t1 WHERE c1='10:22:35' AND c2='33 9:23' /* no rows */;
1037 INSERT INTO t1(c1,c2) VALUES('10:22:34','34 9:23') ON DUPLICATE KEY UPDATE c1='10:22:35',c2='33 9:23';
1038 SELECT * FROM t1 WHERE c1='10:22:35' AND c2='33 9:23' /* 1 row */;
1039 SELECT * FROM t3 WHERE c1='32 9:23' AND c2='33 9:23' /* no rows */;
1040 INSERT INTO t3(c1,c2) VALUES('34 9:23','34 9:23') ON DUPLICATE KEY UPDATE c1='32 9:23',c2='33 9:23';
1041 SELECT * FROM t3 WHERE c1='32 9:23' AND c2='33 9:23' /* 1 row */;
1042
1043 # Test 'INSERT SET'
1044 INSERT INTO t1 SET c1='07:23:55',c2='13 06:23:55';
1045 INSERT INTO t2 SET c1='07:23:55',c2='13 06:23:55';
1046 INSERT INTO t3 SET c1='07:23:55';
1047 INSERT INTO t4 SET c2='07:23:55';
1048 SELECT * FROM t1 WHERE c1='07:23:55' AND c2='13 06:23:55' /* Returns 1 row with values for other column as NULL */;
1049 SELECT * FROM t2 WHERE c1='07:23:55' AND c2='13 06:23:55' /* Returns 1 row with values for other column as NULL */;
1050 SELECT * FROM t3 WHERE c1='07:23:55' /* Returns 1 row with values for other column as NULL */;
1051 SELECT * FROM t4 WHERE c2='07:23:55' /* Returns 1 row with values for other column as NULL */;
1052
1053 # Test 'INSERT INTO SELECT FROM'
1054 CREATE TABLE t5(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, INDEX idx(c1,c2));
1055 INSERT INTO t5 SELECT * FROM t4;
1056 SELECT * FROM t5;
1057 TRUNCATE TABLE t5;
1058 INSERT INTO t5 SELECT * FROM t4 WHERE c1 >='12colon34colon56';
1059 SELECT * FROM t5;
1060 TRUNCATE TABLE t5;
1061 DROP TABLE t5;
1062 DROP TABLE t1,t2,t3,t4;
1063 SET sql_mode=default;