Debian Codegraph @

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

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