Debian Codegraph @

mysql-test/suite/engines/iuds/t/insert_year.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
6 ######## Running INSERT tests for YEAR ########
7
8 # Create tables
9 CREATE TABLE t1(c1 YEAR NOT NULL, c2 YEAR NULL, c3 DATE, c4 DATETIME, PRIMARY KEY(c1), UNIQUE INDEX(c2));
10 CREATE TABLE t2(c1 YEAR NOT NULL, c2 YEAR NOT NULL, c3 DATE, c4 DATETIME, PRIMARY KEY(c1,c2));
11 CREATE TABLE t3(c1 YEAR NOT NULL, c2 YEAR NULL, c3 DATE, c4 DATETIME, UNIQUE INDEX idx(c1,c2));
12 CREATE TABLE t4(c1 YEAR NOT NULL, c2 YEAR NULL, c3 DATE, c4 DATETIME);
13 # Insert some rows with targeted values
14
15 # As a four-digit string in the range '1901' to '2155'
16 INSERT INTO t1 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45');
17 INSERT INTO t2 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45');
18 INSERT INTO t3 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45');
19 INSERT INTO t4 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-25','98.12.25 11:30:45');
20
21 # As a four-digit number in the range 1901 to 2155
22 INSERT INTO t4 VALUES(1901,1901,'98-12-24','98.12.24 11:30:45'),(1999,1999,'98-12-23','98.12.23 11:30:45'),(2000,2000,'98-12-22','98.12.22 11:30:45'),(2001,2001,'98-12-21','98.12.21 11:30:45'),(2099,2099,'98-12-20','98.12.20 11:30:45'),(2100,2100,'98-12-19','98.12.19 11:30:45'),(2155,2155,'98-12-18','98.12.18 11:30:45');
23
24 # As a two-digit string in the range '00' to '99'
25 INSERT INTO t1 VALUES('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45');
26 INSERT INTO t2 VALUES('00','10','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','98','98-12-10','98.12.10 11:30:45');
27 INSERT INTO t3 VALUES('00','10','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','98','98-12-10','98.12.10 11:30:45');
28 INSERT INTO t4 VALUES('00','00','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','99','98-12-10','98.12.10 11:30:45');
29
30 # As a two-digit number in the range 1 to 99
31 INSERT INTO t4 VALUES(1,1,'98-12-9','98.12.9 11:30:45'),(20,20,'98-12-8','98.12.8 11:30:45'),(40,40,'98-12-7','98.12.7 11:30:45'),(60,60,'98-12-6','98.12.6 11:30:45'),(69,69,'98-12-5','98.12.5 11:30:45'),(70,70,'98-12-4','98.12.4 11:30:45'),(90,90,'98-12-3','98.12.3 11:30:45'),(99,99,'98-12-2','98.12.2 11:30:45');
32
33 # As the result of a function
34 SET TIMESTAMP=1233216687; # 2009-01-29 13:41:27
35 INSERT INTO t1 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE);
36 INSERT INTO t2 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE);
37 INSERT INTO t3 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE);
38 INSERT INTO t4 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE);
39
40 # Insert other valid year values one digit strings, '0', '000'
41 INSERT INTO t1(c1) VALUES('5');
42 INSERT INTO t2(c1) VALUES('5');
43 INSERT INTO t3(c1) VALUES('5');
44 INSERT INTO t4(c1,c2) VALUES('0','000');
45
46 # Insert invalid year values, outside range, would be converted to zero value
47 INSERT INTO t2 VALUES(1900,2156,'08-01-06','08/01/07');
48 INSERT INTO t3 VALUES('1900','2156','08-01-08','08/1/9');
49 INSERT INTO t4 VALUES('1900','2156','08-01-08','08/1/9'),(1900,2156,'08-01-08','08/1/9'),(0,00,'08-01-08','08/1/9'),(000,000,'08-01-08','08/1/9'),('-20','100','08-01-08','08/1/9'),(-20,100,'08-01-08','08/1/9');
50
51 # Insert empty string '', would be converted to zero
52 INSERT INTO t1 VALUES('','','08-01-04','08/01/05') /* Inserts zero dates for '' strings */;
53
54 # Insert non-date value(absurd values), would be converted to zero
55 INSERT INTO t4 VALUES('abcd','abcd','08-01-10','08/01/11'),(1234,1234,'08-01-12','08/01/13') /* Inserts zero dates for absurd dates */;
56
57 # Insert duplicates for parts of the clustered key/unique index
58 INSERT INTO t2 VALUES('20','30','98-12-16','98.12.16 11:30:45'),('40','20','98-12-15','98.12.15 11:30:45');
59 INSERT INTO t3 VALUES('00','20','98-12-17','98.12.17 11:30:45'),('20','40','98-12-10','98.12.10 11:30:45');
60
61 # Insert permissible NULLs
62 INSERT INTO t1 VALUES(80,NULL,'08-01-02','08/01/03');
63 INSERT INTO t3 VALUES(80,NULL,'08-01-02','08/01/03');
64 INSERT INTO t4 VALUES(80,NULL,'08-01-02','08/01/03');
65
66 # Insert duplicate NULLs to unique column
67 INSERT INTO t1(c1,c2) VALUES(1992,NULL);
68
69 --sorted_result
70 SELECT * FROM t1;
71 --sorted_result
72 SELECT * FROM t2;
73 --sorted_result
74 SELECT * FROM t3;
75 --sorted_result
76 SELECT * FROM t4;
77
78 # Now select using various table access methods (full table scan, range scan, index scan etc.)
79 ## Full table scan ##
80 --sorted_result
81 SELECT * FROM t2;
82 --sorted_result
83 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2;
84 --sorted_result
85 SELECT * FROM t2 WHERE c3 = '1998-12-11';
86
87 ## Forward index scan, covering ##
88 --sorted_result
89 SELECT c1,c2 FROM t2;
90
91 ## Backward index scan, covering ##
92 SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC;
93
94 ## Forward index scan, non-covering ##
95 SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2;
96
97 ## Backward index scan, non-covering ##
98 SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2;
99
100 ## ref type access
101 SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2;
102 SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 LIMIT 2;
103 SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 DESC;
104 SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 DESC LIMIT 2;
105
106 ## Range access, ordered ##
107 SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2;
108 SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 LIMIT 2;
109 SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2;
110 SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 LIMIT 2;
111 SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2;
112 SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 LIMIT 2;
113 SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2;
114 SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 LIMIT 2;
115 SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2;
116 SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 LIMIT 2;
117 SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2;
118 SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 LIMIT 2;
119 SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2;
120 SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 LIMIT 2;
121 SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2;
122 SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 LIMIT 2;
123 SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2;
124 SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2;
125 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2;
126 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
127 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2;
128 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
129
130 ## Range access, backwards scan ##
131 SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 DESC;
132 SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 DESC LIMIT 2;
133 SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 DESC;
134 SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 DESC LIMIT 2;
135 SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 DESC;
136 SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 DESC LIMIT 2;
137 SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 DESC;
138 SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 DESC LIMIT 2;
139 SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 DESC;
140 SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 DESC LIMIT 2;
141 SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 DESC;
142 SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 DESC LIMIT 2;
143 SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 DESC;
144 SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 DESC LIMIT 2;
145 SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 DESC;
146 SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 DESC LIMIT 2;
147 SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC;
148 SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2;
149 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
150 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
151 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
152 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
153 ## Full table scan ##
154 --sorted_result
155 SELECT * FROM t2;
156 --sorted_result
157 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2;
158 --sorted_result
159 SELECT * FROM t2 WHERE c3 = '1998-12-11';
160
161 ## Forward index scan, covering ##
162 --sorted_result
163 SELECT c1,c2 FROM t2;
164
165 ## Backward index scan, covering ##
166 SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC;
167
168 ## Forward index scan, non-covering ##
169 SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2;
170
171 ## Backward index scan, non-covering ##
172 SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2;
173
174 ## ref type access
175 SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2;
176 SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 LIMIT 2;
177 SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 DESC;
178 SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 DESC LIMIT 2;
179
180 ## Range access, ordered ##
181 SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2;
182 SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 LIMIT 2;
183 SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2;
184 SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 LIMIT 2;
185 SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2;
186 SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 LIMIT 2;
187 SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2;
188 SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 LIMIT 2;
189 SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2;
190 SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 LIMIT 2;
191 SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2;
192 SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 LIMIT 2;
193 SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2;
194 SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 LIMIT 2;
195 SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2;
196 SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 LIMIT 2;
197 SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2;
198 SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2;
199 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2;
200 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
201 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2;
202 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
203
204 ## Range access, backwards scan ##
205 SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 DESC;
206 SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 DESC LIMIT 2;
207 SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 DESC;
208 SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 DESC LIMIT 2;
209 SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 DESC;
210 SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 DESC LIMIT 2;
211 SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 DESC;
212 SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 DESC LIMIT 2;
213 SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 DESC;
214 SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 DESC LIMIT 2;
215 SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 DESC;
216 SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 DESC LIMIT 2;
217 SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 DESC;
218 SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 DESC LIMIT 2;
219 SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 DESC;
220 SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 DESC LIMIT 2;
221 SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC;
222 SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2;
223 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
224 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
225 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
226 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
227 ## Full table scan ##
228 --sorted_result
229 SELECT * FROM t2;
230 --sorted_result
231 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2;
232 --sorted_result
233 SELECT * FROM t2 WHERE c3 = '1998-12-11';
234
235 ## Forward index scan, covering ##
236 --sorted_result
237 SELECT c1,c2 FROM t2;
238
239 ## Backward index scan, covering ##
240 SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC;
241
242 ## Forward index scan, non-covering ##
243 SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2;
244
245 ## Backward index scan, non-covering ##
246 SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2;
247
248 ## ref type access
249 SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2;
250 SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 LIMIT 2;
251 SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 DESC;
252 SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 DESC LIMIT 2;
253
254 ## Range access, ordered ##
255 SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2;
256 SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 LIMIT 2;
257 SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2;
258 SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 LIMIT 2;
259 SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2;
260 SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 LIMIT 2;
261 SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2;
262 SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 LIMIT 2;
263 SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2;
264 SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 LIMIT 2;
265 SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2;
266 SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 LIMIT 2;
267 SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2;
268 SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 LIMIT 2;
269 SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2;
270 SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 LIMIT 2;
271 SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2;
272 SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 LIMIT 2;
273 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2;
274 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
275 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2;
276 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
277
278 ## Range access, backwards scan ##
279 SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 DESC;
280 SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 DESC LIMIT 2;
281 SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 DESC;
282 SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 DESC LIMIT 2;
283 SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 DESC;
284 SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 DESC LIMIT 2;
285 SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 DESC;
286 SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 DESC LIMIT 2;
287 SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 DESC;
288 SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 DESC LIMIT 2;
289 SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 DESC;
290 SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 DESC LIMIT 2;
291 SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 DESC;
292 SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 DESC LIMIT 2;
293 SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 DESC;
294 SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 DESC LIMIT 2;
295 SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC;
296 SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC LIMIT 2;
297 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
298 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
299 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
300 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
301 ## Full table scan ##
302 --sorted_result
303 SELECT * FROM t2;
304 --sorted_result
305 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2;
306 --sorted_result
307 SELECT * FROM t2 WHERE c3 = '1998-12-11';
308
309 ## Forward index scan, covering ##
310 --sorted_result
311 SELECT c1,c2 FROM t2;
312
313 ## Backward index scan, covering ##
314 SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC;
315
316 ## Forward index scan, non-covering ##
317 SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2;
318
319 ## Backward index scan, non-covering ##
320 SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2;
321
322 ## ref type access
323 SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2;
324 SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2;
325 SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC;
326 SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2;
327
328 ## Range access, ordered ##
329 SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2;
330 SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2;
331 SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2;
332 SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2;
333 SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2;
334 SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2;
335 SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2;
336 SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2;
337 SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2;
338 SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2;
339 SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2;
340 SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
341 SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2;
342 SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 LIMIT 2;
343 SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2;
344 SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 LIMIT 2;
345 SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2;
346 SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 LIMIT 2;
347 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2;
348 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
349 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2;
350 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
351
352 ## Range access, backwards scan ##
353 SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC;
354 SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2;
355 SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC;
356 SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2;
357 SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC;
358 SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2;
359 SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC;
360 SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2;
361 SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC;
362 SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2;
363 SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC;
364 SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2;
365 SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC;
366 SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC LIMIT 2;
367 SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC;
368 SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC LIMIT 2;
369 SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC;
370 SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC LIMIT 2;
371 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
372 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
373 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
374 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
375
376 ## Full table scan ##
377 --sorted_result
378 SELECT * FROM t3;
379 --sorted_result
380 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3;
381 --sorted_result
382 SELECT * FROM t3 WHERE c3 = '1998-12-11';
383
384 ## Forward index scan, covering ##
385 --sorted_result
386 SELECT c1,c2 FROM t3;
387
388 ## Backward index scan, covering ##
389 SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC;
390
391 ## Forward index scan, non-covering ##
392 SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2;
393
394 ## Backward index scan, non-covering ##
395 SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2;
396
397 ## ref type access
398 SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2;
399 SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 LIMIT 2;
400 SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 DESC;
401 SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 DESC LIMIT 2;
402
403 ## Range access, ordered ##
404 SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2;
405 SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 LIMIT 2;
406 SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2;
407 SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 LIMIT 2;
408 SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2;
409 SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 LIMIT 2;
410 SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2;
411 SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 LIMIT 2;
412 SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2;
413 SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 LIMIT 2;
414 SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2;
415 SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 LIMIT 2;
416 SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2;
417 SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 LIMIT 2;
418 SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2;
419 SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 LIMIT 2;
420 SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2;
421 SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2;
422 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2;
423 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
424 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2;
425 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
426
427 ## Range access, backwards scan ##
428 SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 DESC;
429 SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 DESC LIMIT 2;
430 SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 DESC;
431 SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 DESC LIMIT 2;
432 SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 DESC;
433 SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 DESC LIMIT 2;
434 SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 DESC;
435 SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 DESC LIMIT 2;
436 SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 DESC;
437 SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 DESC LIMIT 2;
438 SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 DESC;
439 SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 DESC LIMIT 2;
440 SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 DESC;
441 SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 DESC LIMIT 2;
442 SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 DESC;
443 SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 DESC LIMIT 2;
444 SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC;
445 SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2;
446 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
447 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
448 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
449 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
450 ## Full table scan ##
451 --sorted_result
452 SELECT * FROM t3;
453 --sorted_result
454 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3;
455 --sorted_result
456 SELECT * FROM t3 WHERE c3 = '1998-12-11';
457
458 ## Forward index scan, covering ##
459 --sorted_result
460 SELECT c1,c2 FROM t3;
461
462 ## Backward index scan, covering ##
463 SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC;
464
465 ## Forward index scan, non-covering ##
466 SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2;
467
468 ## Backward index scan, non-covering ##
469 SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2;
470
471 ## ref type access
472 SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2;
473 SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 LIMIT 2;
474 SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 DESC;
475 SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 DESC LIMIT 2;
476
477 ## Range access, ordered ##
478 SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2;
479 SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 LIMIT 2;
480 SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2;
481 SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 LIMIT 2;
482 SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2;
483 SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 LIMIT 2;
484 SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2;
485 SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 LIMIT 2;
486 SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2;
487 SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 LIMIT 2;
488 SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2;
489 SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 LIMIT 2;
490 SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2;
491 SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 LIMIT 2;
492 SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2;
493 SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 LIMIT 2;
494 SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2;
495 SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2;
496 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2;
497 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
498 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2;
499 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
500
501 ## Range access, backwards scan ##
502 SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 DESC;
503 SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 DESC LIMIT 2;
504 SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 DESC;
505 SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 DESC LIMIT 2;
506 SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 DESC;
507 SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 DESC LIMIT 2;
508 SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 DESC;
509 SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 DESC LIMIT 2;
510 SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 DESC;
511 SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 DESC LIMIT 2;
512 SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 DESC;
513 SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 DESC LIMIT 2;
514 SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 DESC;
515 SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 DESC LIMIT 2;
516 SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 DESC;
517 SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 DESC LIMIT 2;
518 SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC;
519 SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2;
520 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
521 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
522 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
523 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
524 ## Full table scan ##
525 --sorted_result
526 SELECT * FROM t3;
527 --sorted_result
528 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3;
529 --sorted_result
530 SELECT * FROM t3 WHERE c3 = '1998-12-11';
531
532 ## Forward index scan, covering ##
533 --sorted_result
534 SELECT c1,c2 FROM t3;
535
536 ## Backward index scan, covering ##
537 SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC;
538
539 ## Forward index scan, non-covering ##
540 SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2;
541
542 ## Backward index scan, non-covering ##
543 SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2;
544
545 ## ref type access
546 SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2;
547 SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 LIMIT 2;
548 SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 DESC;
549 SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 DESC LIMIT 2;
550
551 ## Range access, ordered ##
552 SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2;
553 SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 LIMIT 2;
554 SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2;
555 SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 LIMIT 2;
556 SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2;
557 SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 LIMIT 2;
558 SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2;
559 SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 LIMIT 2;
560 SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2;
561 SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 LIMIT 2;
562 SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2;
563 SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 LIMIT 2;
564 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2;
565 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 LIMIT 2;
566 SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2;
567 SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 LIMIT 2;
568 SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2;
569 SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 LIMIT 2;
570 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2;
571 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
572 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2;
573 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
574
575 ## Range access, backwards scan ##
576 SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 DESC;
577 SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 DESC LIMIT 2;
578 SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 DESC;
579 SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 DESC LIMIT 2;
580 SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 DESC;
581 SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 DESC LIMIT 2;
582 SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 DESC;
583 SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 DESC LIMIT 2;
584 SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 DESC;
585 SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 DESC LIMIT 2;
586 SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 DESC;
587 SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 DESC LIMIT 2;
588 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 DESC;
589 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 DESC LIMIT 2;
590 SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 DESC;
591 SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 DESC LIMIT 2;
592 SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC;
593 SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC LIMIT 2;
594 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
595 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
596 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
597 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
598 ## Full table scan ##
599 --sorted_result
600 SELECT * FROM t3;
601 --sorted_result
602 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3;
603 --sorted_result
604 SELECT * FROM t3 WHERE c3 = '1998-12-11';
605
606 ## Forward index scan, covering ##
607 --sorted_result
608 SELECT c1,c2 FROM t3;
609
610 ## Backward index scan, covering ##
611 SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC;
612
613 ## Forward index scan, non-covering ##
614 SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2;
615
616 ## Backward index scan, non-covering ##
617 SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2;
618
619 ## ref type access
620 SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2;
621 SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2;
622 SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC;
623 SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2;
624
625 ## Range access, ordered ##
626 SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2;
627 SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2;
628 SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2;
629 SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2;
630 SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2;
631 SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2;
632 SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2;
633 SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2;
634 SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2;
635 SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2;
636 SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2;
637 SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
638 SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2;
639 SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 LIMIT 2;
640 SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2;
641 SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 LIMIT 2;
642 SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2;
643 SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 LIMIT 2;
644 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2;
645 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
646 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2;
647 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
648
649 ## Range access, backwards scan ##
650 SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC;
651 SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2;
652 SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC;
653 SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2;
654 SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC;
655 SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2;
656 SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC;
657 SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2;
658 SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC;
659 SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2;
660 SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC;
661 SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2;
662 SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC;
663 SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC LIMIT 2;
664 SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC;
665 SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC LIMIT 2;
666 SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC;
667 SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC LIMIT 2;
668 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
669 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
670 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
671 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
672
673 # Using index for group-by
674 --sorted_result
675 SELECT c1 FROM t3 GROUP BY c1;
676 --sorted_result
677 SELECT DISTINCT c1 FROM t3;
678 --sorted_result
679 SELECT c1,MIN(c2) FROM t3 GROUP BY c1;
680
681 # Using Index-merge
682 --sorted_result
683 SELECT * FROM t1 WHERE c1='1901' OR c2='2155';
684 # Test Error conditions- PK constraint violation, Unique constraint violation
685
686 # Insert duplicate value to pk column
687 --error ER_DUP_ENTRY
688 INSERT INTO t1(c1,c2) VALUES(01,'99');
689
690 # Insert duplicate value to clustered pk, throws error
691 --error ER_DUP_ENTRY
692 INSERT INTO t2(c1,c2) VALUES(99,99);
693
694 # Insert duplicate value to unique column, throws error
695 --error ER_DUP_ENTRY
696 INSERT INTO t1(c1,c2) VALUES('2098',69);
697
698 # Insert duplicate value to clustered unique column, throws error
699 --error ER_DUP_ENTRY
700 INSERT INTO t3(c1,c2) VALUES(00,00);
701
702 # Test 'INSERT IGNORE'
703 # Ignore pk constraint
704 INSERT IGNORE INTO t1(c1,c2) VALUES(01,'99');
705 INSERT IGNORE INTO t2(c1,c2) VALUES('1999','1999');
706 # Ignore unique constraint
707 INSERT IGNORE INTO t1(c1,c2) VALUES('2098','69');
708 INSERT IGNORE INTO t3(c1,c2) VALUES(00,00);
709 SELECT * FROM t1 WHERE c1='01' /* Returns 1 row */;
710 SELECT * FROM t2 WHERE c1='1999' AND c2='1999' /* Returns 1 row */;
711 SELECT * FROM t1 WHERE c2='69' /* Returns 1 row */;
712 SELECT * FROM t3 WHERE c1=0 AND c2=0 /* Returns 1 row */;
713
714 # Test 'INSERT ON DUPLICATE KEY UPDATE' with PK one column/multi-column
715 SELECT * FROM t1 WHERE c1='02' /* Returns no rows */;
716 INSERT INTO t1(c1) VALUES('00') ON DUPLICATE KEY UPDATE c1='02';
717 SELECT * FROM t1 WHERE c1='2002' /* Returns 1 row */;
718 SELECT * FROM t2 WHERE c1=69 AND c2=70 /* Returns no rows */;
719 INSERT INTO t2 VALUES(99,99,'98-12-20','98.12.20 11:30:45') ON DUPLICATE KEY UPDATE c1=69,c2=70;
720 SELECT * FROM t2 WHERE c1=2069 AND c2=1970 /* Returns 1 row */;
721 # Test 'INSERT ON DUPLICATE KEY UPDATE' with unique one column/multi-column
722 SELECT * FROM t1 WHERE c1=54 AND c2=53 /* Returns no rows */;
723 INSERT INTO t1 VALUES(69,69,'98-12-18','98.12.18 11:30:45') ON DUPLICATE KEY UPDATE c1=54,c2=53;
724 SELECT * FROM t1 WHERE c1=54 AND c2=53 /* Returns 1 row */;
725 SELECT * FROM t3 WHERE c1=6 AND c2=1970 /* Returns no rows */;
726 INSERT INTO t3(c1,c2) VALUES('69','69') ON DUPLICATE KEY UPDATE c1=06,c2=70;
727 SELECT * FROM t3 WHERE c1=2006 AND c2=1970 /* Returns 1 row */;
728
729 # Test 'INSERT INTO SELECT FROM'
730 CREATE TABLE t5(c1 YEAR NOT NULL, c2 YEAR NULL, c3 DATE, c4 DATETIME, INDEX idx(c1,c2));
731 INSERT INTO t5 SELECT * FROM t1;
732 --sorted_result
733 SELECT * FROM t5;
734 TRUNCATE TABLE t5;
735 INSERT INTO t5 SELECT * FROM t2 WHERE c1 >=1970 AND c1 < 1999 AND c2 <> '1990';
736 --sorted_result
737 SELECT * FROM t5;
738 TRUNCATE TABLE t5;
739 INSERT INTO t5 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND '2155';
740 --sorted_result
741 SELECT * FROM t5;
742 TRUNCATE TABLE t5;
743 DROP TABLE t5;
744
745 # Test 'INSERT SET'
746 INSERT INTO t1 SET c1='00';
747 INSERT INTO t2 SET c1=69;
748 INSERT INTO t3 SET c1=70;
749 INSERT INTO t4 SET c2=99;
750 --sorted_result
751 SELECT * FROM t1 WHERE c1=0;
752 --sorted_result
753 SELECT * FROM t2 WHERE c1=69;
754 --sorted_result
755 SELECT * FROM t3 WHERE c1=70;
756 --sorted_result
757 SELECT * FROM t4 WHERE c2=99;
758 DROP TABLE t1,t2,t3,t4;
759
760 ######## Running INSERT tests for YEAR ########
761
762 # Create tables
763 CREATE TABLE t1(c1 YEAR NOT NULL, c2 YEAR NULL, c3 DATE, c4 DATETIME, PRIMARY KEY(c1), UNIQUE INDEX(c2));
764 CREATE TABLE t2(c1 YEAR NOT NULL, c2 YEAR NOT NULL, c3 DATE, c4 DATETIME, PRIMARY KEY(c1,c2));
765 CREATE TABLE t3(c1 YEAR NOT NULL, c2 YEAR NULL, c3 DATE, c4 DATETIME, UNIQUE INDEX idx(c1,c2));
766 CREATE TABLE t4(c1 YEAR NOT NULL, c2 YEAR NULL, c3 DATE, c4 DATETIME);
767 # Insert some rows with targeted values
768
769 # As a four-digit string in the range '1901' to '2155'
770 INSERT INTO t1 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45');
771 INSERT INTO t2 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45');
772 INSERT INTO t3 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45');
773 INSERT INTO t4 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-25','98.12.25 11:30:45');
774
775 # As a four-digit number in the range 1901 to 2155
776 INSERT INTO t4 VALUES(1901,1901,'98-12-24','98.12.24 11:30:45'),(1999,1999,'98-12-23','98.12.23 11:30:45'),(2000,2000,'98-12-22','98.12.22 11:30:45'),(2001,2001,'98-12-21','98.12.21 11:30:45'),(2099,2099,'98-12-20','98.12.20 11:30:45'),(2100,2100,'98-12-19','98.12.19 11:30:45'),(2155,2155,'98-12-18','98.12.18 11:30:45');
777
778 # As a two-digit string in the range '00' to '99'
779 INSERT INTO t1 VALUES('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45');
780 INSERT INTO t2 VALUES('00','10','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','98','98-12-10','98.12.10 11:30:45');
781 INSERT INTO t3 VALUES('00','10','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','98','98-12-10','98.12.10 11:30:45');
782 INSERT INTO t4 VALUES('00','00','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','99','98-12-10','98.12.10 11:30:45');
783
784 # As a two-digit number in the range 1 to 99
785 INSERT INTO t4 VALUES(1,1,'98-12-9','98.12.9 11:30:45'),(20,20,'98-12-8','98.12.8 11:30:45'),(40,40,'98-12-7','98.12.7 11:30:45'),(60,60,'98-12-6','98.12.6 11:30:45'),(69,69,'98-12-5','98.12.5 11:30:45'),(70,70,'98-12-4','98.12.4 11:30:45'),(90,90,'98-12-3','98.12.3 11:30:45'),(99,99,'98-12-2','98.12.2 11:30:45');
786
787 # As the result of a function
788 SET TIMESTAMP=1233216687; # 2009-01-29 13:41:27
789 INSERT INTO t1 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE);
790 INSERT INTO t2 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE);
791 INSERT INTO t3 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE);
792 INSERT INTO t4 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE);
793
794 # Insert other valid year values one digit strings, '0', '000'
795 INSERT INTO t1(c1) VALUES('5');
796 INSERT INTO t2(c1) VALUES('5');
797 INSERT INTO t3(c1) VALUES('5');
798 INSERT INTO t4(c1,c2) VALUES('0','000');
799
800 # Insert invalid year values, outside range, would be converted to zero value
801 INSERT INTO t2 VALUES(1900,2156,'08-01-06','08/01/07');
802 INSERT INTO t3 VALUES('1900','2156','08-01-08','08/1/9');
803 INSERT INTO t4 VALUES('1900','2156','08-01-08','08/1/9'),(1900,2156,'08-01-08','08/1/9'),(0,00,'08-01-08','08/1/9'),(000,000,'08-01-08','08/1/9'),('-20','100','08-01-08','08/1/9'),(-20,100,'08-01-08','08/1/9');
804
805 # Insert empty string '', would be converted to zero
806 INSERT INTO t1 VALUES('','','08-01-04','08/01/05') /* Inserts zero dates for '' strings */;
807
808 # Insert non-date value(absurd values), would be converted to zero
809 INSERT INTO t4 VALUES('abcd','abcd','08-01-10','08/01/11'),(1234,1234,'08-01-12','08/01/13') /* Inserts zero dates for absurd dates */;
810
811 # Insert duplicates for parts of the clustered key/unique index
812 INSERT INTO t2 VALUES('20','30','98-12-16','98.12.16 11:30:45'),('40','20','98-12-15','98.12.15 11:30:45');
813 INSERT INTO t3 VALUES('00','20','98-12-17','98.12.17 11:30:45'),('20','40','98-12-10','98.12.10 11:30:45');
814
815 # Insert permissible NULLs
816 INSERT INTO t1 VALUES(80,NULL,'08-01-02','08/01/03');
817 INSERT INTO t3 VALUES(80,NULL,'08-01-02','08/01/03');
818 INSERT INTO t4 VALUES(80,NULL,'08-01-02','08/01/03');
819
820 # Insert duplicate NULLs to unique column
821 INSERT INTO t1(c1,c2) VALUES(1992,NULL);
822
823 --sorted_result
824 SELECT * FROM t1;
825 --sorted_result
826 SELECT * FROM t2;
827 --sorted_result
828 SELECT * FROM t3;
829 --sorted_result
830 SELECT * FROM t4;
831
832 # Now select using various table access methods (full table scan, range scan, index scan etc.)
833 ## Full table scan ##
834 --sorted_result
835 SELECT * FROM t2;
836 --sorted_result
837 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2;
838 --sorted_result
839 SELECT * FROM t2 WHERE c3 = '1998-12-11';
840
841 ## Forward index scan, covering ##
842 --sorted_result
843 SELECT c1,c2 FROM t2;
844
845 ## Backward index scan, covering ##
846 SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC;
847
848 ## Forward index scan, non-covering ##
849 SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2;
850
851 ## Backward index scan, non-covering ##
852 SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2;
853
854 ## ref type access
855 SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2;
856 SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 LIMIT 2;
857 SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 DESC;
858 SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 DESC LIMIT 2;
859
860 ## Range access, ordered ##
861 SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2;
862 SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 LIMIT 2;
863 SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2;
864 SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 LIMIT 2;
865 SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2;
866 SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 LIMIT 2;
867 SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2;
868 SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 LIMIT 2;
869 SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2;
870 SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 LIMIT 2;
871 SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2;
872 SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 LIMIT 2;
873 SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2;
874 SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 LIMIT 2;
875 SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2;
876 SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 LIMIT 2;
877 SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2;
878 SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2;
879 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2;
880 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
881 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2;
882 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
883
884 ## Range access, backwards scan ##
885 SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 DESC;
886 SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 DESC LIMIT 2;
887 SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 DESC;
888 SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 DESC LIMIT 2;
889 SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 DESC;
890 SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 DESC LIMIT 2;
891 SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 DESC;
892 SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 DESC LIMIT 2;
893 SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 DESC;
894 SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 DESC LIMIT 2;
895 SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 DESC;
896 SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 DESC LIMIT 2;
897 SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 DESC;
898 SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 DESC LIMIT 2;
899 SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 DESC;
900 SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 DESC LIMIT 2;
901 SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC;
902 SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2;
903 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
904 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
905 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
906 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
907 ## Full table scan ##
908 --sorted_result
909 SELECT * FROM t2;
910 --sorted_result
911 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2;
912 --sorted_result
913 SELECT * FROM t2 WHERE c3 = '1998-12-11';
914
915 ## Forward index scan, covering ##
916 --sorted_result
917 SELECT c1,c2 FROM t2;
918
919 ## Backward index scan, covering ##
920 SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC;
921
922 ## Forward index scan, non-covering ##
923 SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2;
924
925 ## Backward index scan, non-covering ##
926 SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2;
927
928 ## ref type access
929 SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2;
930 SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 LIMIT 2;
931 SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 DESC;
932 SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 DESC LIMIT 2;
933
934 ## Range access, ordered ##
935 SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2;
936 SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 LIMIT 2;
937 SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 LIMIT 2;
938 SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 LIMIT 2;
939 SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 LIMIT 2;
940 SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 LIMIT 2;
941 SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2;
942 SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 LIMIT 2;
943 SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2;
944 SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 LIMIT 2;
945 SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2;
946 SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 LIMIT 2;
947 SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2;
948 SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2;
949 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2;
950 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
951 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2;
952 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
953
954 ## Range access, backwards scan ##
955 SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 DESC;
956 SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 DESC LIMIT 2;
957 SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 DESC LIMIT 2;
958 SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 DESC LIMIT 2;
959 SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 DESC LIMIT 2;
960 SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 DESC LIMIT 2;
961 SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 DESC;
962 SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 DESC LIMIT 2;
963 SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 DESC;
964 SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 DESC LIMIT 2;
965 SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 DESC;
966 SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 DESC LIMIT 2;
967 SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC;
968 SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2;
969 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
970 SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
971 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
972 SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
973 ## Full table scan ##
974 --sorted_result
975 SELECT * FROM t2;
976 --sorted_result
977 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2;
978 --sorted_result
979 SELECT * FROM t2 WHERE c3 = '1998-12-11';
980
981 ## Forward index scan, covering ##
982 --sorted_result
983 SELECT c1,c2 FROM t2;
984
985 ## Backward index scan, covering ##
986 SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC;
987
988 ## Forward index scan, non-covering ##
989 SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2;
990
991 ## Backward index scan, non-covering ##
992 SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2;
993
994 ## ref type access
995 SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2;
996 SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 LIMIT 2;
997 SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 DESC;
998 SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 DESC LIMIT 2;
999
1000 ## Range access, ordered ##
1001 SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2;
1002 SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 LIMIT 2;
1003 SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2;
1004 SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 LIMIT 2;
1005 SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2;
1006 SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 LIMIT 2;
1007 SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2;
1008 SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 LIMIT 2;
1009 SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2;
1010 SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 LIMIT 2;
1011 SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2;
1012 SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 LIMIT 2;
1013 SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2;
1014 SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 LIMIT 2;
1015 SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2;
1016 SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 LIMIT 2;
1017 SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2;
1018 SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 LIMIT 2;
1019 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2;
1020 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
1021 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2;
1022 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
1023
1024 ## Range access, backwards scan ##
1025 SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 DESC;
1026 SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 DESC LIMIT 2;
1027 SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 DESC;
1028 SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 DESC LIMIT 2;
1029 SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 DESC;
1030 SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 DESC LIMIT 2;
1031 SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 DESC;
1032 SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 DESC LIMIT 2;
1033 SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 DESC;
1034 SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 DESC LIMIT 2;
1035 SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 DESC;
1036 SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 DESC LIMIT 2;
1037 SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 DESC;
1038 SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 DESC LIMIT 2;
1039 SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 DESC;
1040 SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 DESC LIMIT 2;
1041 SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC;
1042 SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC LIMIT 2;
1043 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
1044 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
1045 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
1046 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
1047 ## Full table scan ##
1048 --sorted_result
1049 SELECT * FROM t2;
1050 --sorted_result
1051 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2;
1052 --sorted_result
1053 SELECT * FROM t2 WHERE c3 = '1998-12-11';
1054
1055 ## Forward index scan, covering ##
1056 --sorted_result
1057 SELECT c1,c2 FROM t2;
1058
1059 ## Backward index scan, covering ##
1060 SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC;
1061
1062 ## Forward index scan, non-covering ##
1063 SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2;
1064
1065 ## Backward index scan, non-covering ##
1066 SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2;
1067
1068 ## ref type access
1069 SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2;
1070 SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2;
1071 SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC;
1072 SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2;
1073
1074 ## Range access, ordered ##
1075 SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2;
1076 SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2;
1077 SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2;
1078 SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2;
1079 SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2;
1080 SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2;
1081 SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2;
1082 SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2;
1083 SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2;
1084 SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2;
1085 SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2;
1086 SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
1087 SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2;
1088 SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 LIMIT 2;
1089 SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2;
1090 SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 LIMIT 2;
1091 SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2;
1092 SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 LIMIT 2;
1093 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2;
1094 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
1095 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2;
1096 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
1097
1098 ## Range access, backwards scan ##
1099 SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC;
1100 SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2;
1101 SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC;
1102 SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2;
1103 SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC;
1104 SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2;
1105 SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC;
1106 SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2;
1107 SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC;
1108 SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2;
1109 SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC;
1110 SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2;
1111 SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC;
1112 SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC LIMIT 2;
1113 SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC;
1114 SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC LIMIT 2;
1115 SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC;
1116 SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC LIMIT 2;
1117 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
1118 SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
1119 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
1120 SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
1121
1122 ## Full table scan ##
1123 --sorted_result
1124 SELECT * FROM t3;
1125 --sorted_result
1126 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3;
1127 --sorted_result
1128 SELECT * FROM t3 WHERE c3 = '1998-12-11';
1129
1130 ## Forward index scan, covering ##
1131 --sorted_result
1132 SELECT c1,c2 FROM t3;
1133
1134 ## Backward index scan, covering ##
1135 SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC;
1136
1137 ## Forward index scan, non-covering ##
1138 SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2;
1139
1140 ## Backward index scan, non-covering ##
1141 SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2;
1142
1143 ## ref type access
1144 SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2;
1145 SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 LIMIT 2;
1146 SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 DESC;
1147 SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 DESC LIMIT 2;
1148
1149 ## Range access, ordered ##
1150 SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2;
1151 SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 LIMIT 2;
1152 SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2;
1153 SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 LIMIT 2;
1154 SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2;
1155 SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 LIMIT 2;
1156 SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2;
1157 SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 LIMIT 2;
1158 SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2;
1159 SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 LIMIT 2;
1160 SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2;
1161 SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 LIMIT 2;
1162 SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2;
1163 SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 LIMIT 2;
1164 SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2;
1165 SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 LIMIT 2;
1166 SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2;
1167 SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2;
1168 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2;
1169 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
1170 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2;
1171 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
1172
1173 ## Range access, backwards scan ##
1174 SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 DESC;
1175 SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 DESC LIMIT 2;
1176 SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 DESC;
1177 SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 DESC LIMIT 2;
1178 SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 DESC;
1179 SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 DESC LIMIT 2;
1180 SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 DESC;
1181 SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 DESC LIMIT 2;
1182 SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 DESC;
1183 SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 DESC LIMIT 2;
1184 SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 DESC;
1185 SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 DESC LIMIT 2;
1186 SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 DESC;
1187 SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 DESC LIMIT 2;
1188 SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 DESC;
1189 SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 DESC LIMIT 2;
1190 SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC;
1191 SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2;
1192 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
1193 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
1194 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
1195 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
1196 ## Full table scan ##
1197 --sorted_result
1198 SELECT * FROM t3;
1199 --sorted_result
1200 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3;
1201 --sorted_result
1202 SELECT * FROM t3 WHERE c3 = '1998-12-11';
1203
1204 ## Forward index scan, covering ##
1205 --sorted_result
1206 SELECT c1,c2 FROM t3;
1207
1208 ## Backward index scan, covering ##
1209 SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC;
1210
1211 ## Forward index scan, non-covering ##
1212 SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2;
1213
1214 ## Backward index scan, non-covering ##
1215 SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2;
1216
1217 ## ref type access
1218 SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2;
1219 SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 LIMIT 2;
1220 SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 DESC;
1221 SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 DESC LIMIT 2;
1222
1223 ## Range access, ordered ##
1224 SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2;
1225 SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 LIMIT 2;
1226 SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2;
1227 SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 LIMIT 2;
1228 SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2;
1229 SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 LIMIT 2;
1230 SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2;
1231 SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 LIMIT 2;
1232 SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2;
1233 SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 LIMIT 2;
1234 SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2;
1235 SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 LIMIT 2;
1236 SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2;
1237 SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 LIMIT 2;
1238 SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2;
1239 SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 LIMIT 2;
1240 SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2;
1241 SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2;
1242 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2;
1243 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2;
1244 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2;
1245 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
1246
1247 ## Range access, backwards scan ##
1248 SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 DESC;
1249 SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 DESC LIMIT 2;
1250 SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 DESC;
1251 SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 DESC LIMIT 2;
1252 SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 DESC;
1253 SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 DESC LIMIT 2;
1254 SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 DESC;
1255 SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 DESC LIMIT 2;
1256 SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 DESC;
1257 SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 DESC LIMIT 2;
1258 SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 DESC;
1259 SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 DESC LIMIT 2;
1260 SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 DESC;
1261 SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 DESC LIMIT 2;
1262 SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 DESC;
1263 SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 DESC LIMIT 2;
1264 SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC;
1265 SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2;
1266 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC;
1267 SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
1268 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC;
1269 SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
1270 ## Full table scan ##
1271 --sorted_result
1272 SELECT * FROM t3;
1273 --sorted_result
1274 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3;
1275 --sorted_result
1276 SELECT * FROM t3 WHERE c3 = '1998-12-11';
1277
1278 ## Forward index scan, covering ##
1279 --sorted_result
1280 SELECT c1,c2 FROM t3;
1281
1282 ## Backward index scan, covering ##
1283 SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC;
1284
1285 ## Forward index scan, non-covering ##
1286 SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2;
1287
1288 ## Backward index scan, non-covering ##
1289 SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2;
1290
1291 ## ref type access
1292 SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2;
1293 SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 LIMIT 2;
1294 SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 DESC;
1295 SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 DESC LIMIT 2;
1296
1297 ## Range access, ordered ##
1298 SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2;
1299 SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 LIMIT 2;
1300 SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2;
1301 SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 LIMIT 2;
1302 SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2;
1303 SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 LIMIT 2;
1304 SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2;
1305 SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 LIMIT 2;
1306 SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2;
1307 SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 LIMIT 2;
1308 SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2;
1309 SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 LIMIT 2;
1310 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2;
1311 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 LIMIT 2;
1312 SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2;
1313 SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 LIMIT 2;
1314 SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2;
1315 SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 LIMIT 2;
1316 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2;
1317 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
1318 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2;
1319 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
1320
1321 ## Range access, backwards scan ##
1322 SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 DESC;
1323 SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 DESC LIMIT 2;
1324 SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 DESC;
1325 SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 DESC LIMIT 2;
1326 SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 DESC;
1327 SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 DESC LIMIT 2;
1328 SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 DESC;
1329 SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 DESC LIMIT 2;
1330 SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 DESC;
1331 SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 DESC LIMIT 2;
1332 SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 DESC;
1333 SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 DESC LIMIT 2;
1334 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 DESC;
1335 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 DESC LIMIT 2;
1336 SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 DESC;
1337 SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 DESC LIMIT 2;
1338 SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC;
1339 SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC LIMIT 2;
1340 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
1341 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
1342 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
1343 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
1344 ## Full table scan ##
1345 --sorted_result
1346 SELECT * FROM t3;
1347 --sorted_result
1348 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3;
1349 --sorted_result
1350 SELECT * FROM t3 WHERE c3 = '1998-12-11';
1351
1352 ## Forward index scan, covering ##
1353 --sorted_result
1354 SELECT c1,c2 FROM t3;
1355
1356 ## Backward index scan, covering ##
1357 SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC;
1358
1359 ## Forward index scan, non-covering ##
1360 SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2;
1361
1362 ## Backward index scan, non-covering ##
1363 SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2;
1364
1365 ## ref type access
1366 SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2;
1367 SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2;
1368 SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC;
1369 SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2;
1370
1371 ## Range access, ordered ##
1372 SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2;
1373 SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2;
1374 SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2;
1375 SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2;
1376 SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2;
1377 SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2;
1378 SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2;
1379 SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2;
1380 SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2;
1381 SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2;
1382 SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2;
1383 SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
1384 SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2;
1385 SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 LIMIT 2;
1386 SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2;
1387 SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 LIMIT 2;
1388 SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2;
1389 SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 LIMIT 2;
1390 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2;
1391 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2;
1392 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2;
1393 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2;
1394
1395 ## Range access, backwards scan ##
1396 SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC;
1397 SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2;
1398 SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC;
1399 SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2;
1400 SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC;
1401 SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2;
1402 SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC;
1403 SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2;
1404 SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC;
1405 SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2;
1406 SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC;
1407 SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2;
1408 SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC;
1409 SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC LIMIT 2;
1410 SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC;
1411 SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC LIMIT 2;
1412 SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC;
1413 SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC LIMIT 2;
1414 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC;
1415 SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2;
1416 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC;
1417 SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2;
1418
1419 # Using index for group-by
1420 --sorted_result
1421 SELECT c1 FROM t3 GROUP BY c1;
1422 --sorted_result
1423 SELECT DISTINCT c1 FROM t3;
1424 --sorted_result
1425 SELECT c1,MIN(c2) FROM t3 GROUP BY c1;
1426
1427 # Using Index-merge
1428 --sorted_result
1429 SELECT * FROM t1 WHERE c1='1901' OR c2='2155';
1430 # Test Error conditions- PK constraint violation, Unique constraint violation
1431
1432 # Insert duplicate value to pk column
1433 --error ER_DUP_ENTRY
1434 INSERT INTO t1(c1,c2) VALUES(01,'99');
1435
1436 # Insert duplicate value to clustered pk, throws error
1437 --error ER_DUP_ENTRY
1438 INSERT INTO t2(c1,c2) VALUES(99,99);
1439
1440 # Insert duplicate value to unique column, throws error
1441 --error ER_DUP_ENTRY
1442 INSERT INTO t1(c1,c2) VALUES('2098',69);
1443
1444 # Insert duplicate value to clustered unique column, throws error
1445 --error ER_DUP_ENTRY
1446 INSERT INTO t3(c1,c2) VALUES(00,00);
1447
1448 # Test 'INSERT IGNORE'
1449 # Ignore pk constraint
1450 INSERT IGNORE INTO t1(c1,c2) VALUES(01,'99');
1451 INSERT IGNORE INTO t2(c1,c2) VALUES('1999','1999');
1452 # Ignore unique constraint
1453 INSERT IGNORE INTO t1(c1,c2) VALUES('2098','69');
1454 INSERT IGNORE INTO t3(c1,c2) VALUES(00,00);
1455 SELECT * FROM t1 WHERE c1='01' /* Returns 1 row */;
1456 SELECT * FROM t2 WHERE c1='1999' AND c2='1999' /* Returns 1 row */;
1457 SELECT * FROM t1 WHERE c2='69' /* Returns 1 row */;
1458 SELECT * FROM t3 WHERE c1=0 AND c2=0 /* Returns 1 row */;
1459
1460 # Test 'INSERT ON DUPLICATE KEY UPDATE' with PK one column/multi-column
1461 SELECT * FROM t1 WHERE c1='02' /* Returns no rows */;
1462 INSERT INTO t1(c1) VALUES('00') ON DUPLICATE KEY UPDATE c1='02';
1463 SELECT * FROM t1 WHERE c1='2002' /* Returns 1 row */;
1464 SELECT * FROM t2 WHERE c1=69 AND c2=70 /* Returns no rows */;
1465 INSERT INTO t2 VALUES(99,99,'98-12-20','98.12.20 11:30:45') ON DUPLICATE KEY UPDATE c1=69,c2=70;
1466 SELECT * FROM t2 WHERE c1=2069 AND c2=1970 /* Returns 1 row */;
1467 # Test 'INSERT ON DUPLICATE KEY UPDATE' with unique one column/multi-column
1468 SELECT * FROM t1 WHERE c1=54 AND c2=53 /* Returns no rows */;
1469 INSERT INTO t1 VALUES(69,69,'98-12-18','98.12.18 11:30:45') ON DUPLICATE KEY UPDATE c1=54,c2=53;
1470 SELECT * FROM t1 WHERE c1=54 AND c2=53 /* Returns 1 row */;
1471 SELECT * FROM t3 WHERE c1=6 AND c2=1970 /* Returns no rows */;
1472 INSERT INTO t3(c1,c2) VALUES('69','69') ON DUPLICATE KEY UPDATE c1=06,c2=70;
1473 SELECT * FROM t3 WHERE c1=2006 AND c2=1970 /* Returns 1 row */;
1474
1475 # Test 'INSERT INTO SELECT FROM'
1476 CREATE TABLE t5(c1 YEAR NOT NULL, c2 YEAR NULL, c3 DATE, c4 DATETIME, INDEX idx(c1,c2));
1477 INSERT INTO t5 SELECT * FROM t1;
1478 --sorted_result
1479 SELECT * FROM t5;
1480 TRUNCATE TABLE t5;
1481 INSERT INTO t5 SELECT * FROM t2 WHERE c1 >=1970 AND c1 < 1999 AND c2 <> '1990';
1482 --sorted_result
1483 SELECT * FROM t5;
1484 TRUNCATE TABLE t5;
1485 INSERT INTO t5 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND '2155';
1486 --sorted_result
1487 SELECT * FROM t5;
1488 TRUNCATE TABLE t5;
1489 DROP TABLE t5;
1490
1491 # Test 'INSERT SET'
1492 INSERT INTO t1 SET c1='00';
1493 INSERT INTO t2 SET c1=69;
1494 INSERT INTO t3 SET c1=70;
1495 INSERT INTO t4 SET c2=99;
1496 --sorted_result
1497 SELECT * FROM t1 WHERE c1=0;
1498 --sorted_result
1499 SELECT * FROM t2 WHERE c1=69;
1500 --sorted_result
1501 SELECT * FROM t3 WHERE c1=70;
1502 --sorted_result
1503 SELECT * FROM t4 WHERE c2=99;
1504 DROP TABLE t1,t2,t3,t4;
1505 SET sql_mode=default;