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