MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
range.inc
1 #
2 # Problem with range optimizer
3 #
4 
5 --disable_warnings
6 drop table if exists t1, t2, t3;
7 --enable_warnings
8 
9 CREATE TABLE t1 (
10  event_date date DEFAULT '0000-00-00' NOT NULL,
11  type int(11) DEFAULT '0' NOT NULL,
12  event_id int(11) DEFAULT '0' NOT NULL,
13  PRIMARY KEY (event_date,type,event_id)
14 );
15 
16 INSERT INTO t1 VALUES ('1999-07-10',100100,24), ('1999-07-11',100100,25),
17 ('1999-07-13',100600,0), ('1999-07-13',100600,4), ('1999-07-13',100600,26),
18 ('1999-07-14',100600,10), ('1999-07-15',100600,16), ('1999-07-15',100800,45),
19 ('1999-07-15',101000,47), ('1999-07-16',100800,46), ('1999-07-20',100600,5),
20 ('1999-07-20',100600,27), ('1999-07-21',100600,11), ('1999-07-22',100600,17),
21 ('1999-07-23',100100,39), ('1999-07-24',100100,39), ('1999-07-24',100500,40),
22 ('1999-07-25',100100,39), ('1999-07-27',100600,1), ('1999-07-27',100600,6),
23 ('1999-07-27',100600,28), ('1999-07-28',100600,12), ('1999-07-29',100500,41),
24 ('1999-07-29',100600,18), ('1999-07-30',100500,41), ('1999-07-31',100500,41),
25 ('1999-08-01',100700,34), ('1999-08-03',100600,7), ('1999-08-03',100600,29),
26 ('1999-08-04',100600,13), ('1999-08-05',100500,42), ('1999-08-05',100600,19),
27 ('1999-08-06',100500,42), ('1999-08-07',100500,42), ('1999-08-08',100500,42),
28 ('1999-08-10',100600,2), ('1999-08-10',100600,9), ('1999-08-10',100600,30),
29 ('1999-08-11',100600,14), ('1999-08-12',100600,20), ('1999-08-17',100500,8),
30 ('1999-08-17',100600,31), ('1999-08-18',100600,15), ('1999-08-19',100600,22),
31 ('1999-08-24',100600,3), ('1999-08-24',100600,32), ('1999-08-27',100500,43),
32 ('1999-08-31',100600,33), ('1999-09-17',100100,37), ('1999-09-18',100100,37),
33 ('1999-09-19',100100,37), ('2000-12-18',100700,38);
34 
35 select event_date,type,event_id from t1 WHERE event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date;
36 explain select event_date,type,event_id from t1 WHERE type = 100601 and event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date;
37 select event_date,type,event_id from t1 WHERE event_date >= "1999-07-01" AND event_date <= "1999-07-15" AND (type=100600 OR type=100100) or event_date >= "1999-07-01" AND event_date <= "1999-07-15" AND type=100099;
38 drop table t1;
39 
40 CREATE TABLE t1 (
41  PAPER_ID smallint(6) DEFAULT '0' NOT NULL,
42  YEAR smallint(6) DEFAULT '0' NOT NULL,
43  ISSUE smallint(6) DEFAULT '0' NOT NULL,
44  CLOSED tinyint(4) DEFAULT '0' NOT NULL,
45  ISS_DATE date DEFAULT '0000-00-00' NOT NULL,
46  PRIMARY KEY (PAPER_ID,YEAR,ISSUE)
47 );
48 INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'), (1,1999,111,0,'1999-03-23'),
49  (1,1999,222,0,'1999-03-23'), (3,1999,33,0,'1999-07-12'),
50  (3,1999,32,0,'1999-07-12'), (3,1999,31,0,'1999-07-12'),
51  (3,1999,30,0,'1999-07-12'), (3,1999,29,0,'1999-07-12'),
52  (3,1999,28,0,'1999-07-12'), (1,1999,40,1,'1999-05-01'),
53  (1,1999,41,1,'1999-05-01'), (1,1999,42,1,'1999-05-01'),
54  (1,1999,46,1,'1999-05-01'), (1,1999,47,1,'1999-05-01'),
55  (1,1999,48,1,'1999-05-01'), (1,1999,49,1,'1999-05-01'),
56  (1,1999,50,0,'1999-05-01'), (1,1999,51,0,'1999-05-01'),
57  (1,1999,200,0,'1999-06-28'), (1,1999,52,0,'1999-06-28'),
58  (1,1999,53,0,'1999-06-28'), (1,1999,54,0,'1999-06-28'),
59  (1,1999,55,0,'1999-06-28'), (1,1999,56,0,'1999-07-01'),
60  (1,1999,57,0,'1999-07-01'), (1,1999,58,0,'1999-07-01'),
61  (1,1999,59,0,'1999-07-01'), (1,1999,60,0,'1999-07-01'),
62  (3,1999,35,0,'1999-07-12');
63 select YEAR,ISSUE from t1 where PAPER_ID=3 and (YEAR>1999 or (YEAR=1999 and ISSUE>28)) order by YEAR,ISSUE;
64 check table t1;
65 repair table t1;
66 drop table t1;
67 
68 CREATE TABLE t1 (
69  id int(11) NOT NULL auto_increment,
70  parent_id int(11) DEFAULT '0' NOT NULL,
71  level tinyint(4) DEFAULT '0' NOT NULL,
72  PRIMARY KEY (id),
73  KEY parent_id (parent_id),
74  KEY level (level)
75 );
76 INSERT INTO t1 VALUES (1,0,0), (3,1,1), (4,1,1), (8,2,2), (9,2,2), (17,3,2),
77 (22,4,2), (24,4,2), (28,5,2), (29,5,2), (30,5,2), (31,6,2), (32,6,2), (33,6,2),
78 (203,7,2), (202,7,2), (20,3,2), (157,0,0), (193,5,2), (40,7,2), (2,1,1),
79 (15,2,2), (6,1,1), (34,6,2), (35,6,2), (16,3,2), (7,1,1), (36,7,2), (18,3,2),
80 (26,5,2), (27,5,2), (183,4,2), (38,7,2), (25,5,2), (37,7,2), (21,4,2),
81 (19,3,2), (5,1,1), (179,5,2);
82 SELECT * FROM t1 WHERE level = 1 AND parent_id = 1;
83 # The following select returned 0 rows in 3.23.8
84 SELECT * FROM t1 WHERE level = 1 AND parent_id = 1 order by id;
85 drop table t1;
86 
87 #
88 # Testing of bug in range optimizer with many key parts and > and <
89 #
90 
91 create table t1(
92  Satellite varchar(25) not null,
93  SensorMode varchar(25) not null,
94  FullImageCornersUpperLeftLongitude double not null,
95  FullImageCornersUpperRightLongitude double not null,
96  FullImageCornersUpperRightLatitude double not null,
97  FullImageCornersLowerRightLatitude double not null,
98  index two (Satellite, SensorMode, FullImageCornersUpperLeftLongitude, FullImageCornersUpperRightLongitude, FullImageCornersUpperRightLatitude, FullImageCornersLowerRightLatitude));
99 
100 insert into t1 values("OV-3","PAN1",91,-92,40,50);
101 insert into t1 values("OV-4","PAN1",91,-92,40,50);
102 
103 select * from t1 where t1.Satellite = "OV-3" and t1.SensorMode = "PAN1" and t1.FullImageCornersUpperLeftLongitude > -90.000000 and t1.FullImageCornersUpperRightLongitude < -82.000000;
104 drop table t1;
105 
106 create table t1 ( aString char(100) not null default "", key aString (aString(10)) );
107 insert t1 (aString) values ( "believe in myself" ), ( "believe" ), ("baaa" ), ( "believe in love");
108 select * from t1 where aString < "believe in myself" order by aString;
109 select * from t1 where aString > "believe in love" order by aString;
110 alter table t1 drop key aString;
111 select * from t1 where aString < "believe in myself" order by aString;
112 select * from t1 where aString > "believe in love" order by aString;
113 drop table t1;
114 
115 #
116 # Problem with binary strings
117 #
118 
119 CREATE TABLE t1 (
120  t1ID int(10) unsigned NOT NULL auto_increment,
121  art binary(1) NOT NULL default '',
122  KNR char(5) NOT NULL default '',
123  RECHNR char(6) NOT NULL default '',
124  POSNR char(2) NOT NULL default '',
125  ARTNR char(10) NOT NULL default '',
126  TEX char(70) NOT NULL default '',
127  PRIMARY KEY (t1ID),
128  KEY IdxArt (art),
129  KEY IdxKnr (KNR),
130  KEY IdxArtnr (ARTNR)
131 ) ENGINE=MyISAM;
132 
133 INSERT INTO t1 (art) VALUES ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
134 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
135 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
136 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
137 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
138 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
139 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
140 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
141 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
142 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
143 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
144 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
145 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
146 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
147 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
148 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
149 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
150 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
151 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
152 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
153 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
154 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
155 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
156 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
157 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
158 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
159 ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
160 ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
161 ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
162 ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
163 ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
164 ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
165 ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
166 ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
167 ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
168 ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
169 ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
170 ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j');
171 select count(*) from t1 where upper(art) = 'J';
172 select count(*) from t1 where art = 'J' or art = 'j';
173 select count(*) from t1 where art = 'j' or art = 'J';
174 select count(*) from t1 where art = 'j';
175 select count(*) from t1 where art = 'J';
176 drop table t1;
177 #
178 # BETWEEN problems
179 #
180 create table t1 (x int, y int, index(x), index(y));
181 insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9);
182 update t1 set y=x;
183 # between with only one end fixed
184 explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0;
185 explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0;
186 # between with both expressions on both ends
187 explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1;
188 explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1;
189 # equation propagation
190 explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y;
191 explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y;
192 # testing IN
193 explain select count(*) from t1 where x in (1);
194 explain select count(*) from t1 where x in (1,2);
195 drop table t1;
196 
197 #
198 # bug #1172: "Force index" option caused server crash
199 #
200 CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1));
201 INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(1),(1);
202 CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya));
203 INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2);
204 explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
205 explain select * from t1 force index(i1), t2 force index(j1) where
206  (t1.key1 <t2.keya + 1) and t2.keya=3;
207 DROP TABLE t1,t2;
208 
209 #
210 # bug #1724: use RANGE on more selective column instead of REF on less
211 # selective
212 
213 CREATE TABLE t1 (
214  a int(11) default NULL,
215  b int(11) default NULL,
216  KEY a (a),
217  KEY b (b)
218 ) ENGINE=MyISAM;
219 
220 
221 INSERT INTO t1 VALUES
222 (1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,2),(10,2),
223 (13,2),(14,2),(15,2),(16,2),(17,3),(17,3),(16,3),(17,3),(19,3),(20,3),
224 (21,4),(22,5),(23,5),(24,5),(25,5),(26,5),(30,5),(31,5),(32,5),(33,5),
225 (33,5),(33,5),(33,5),(33,5),(34,5),(35,5);
226 
227 # we expect that optimizer will choose index on A
228 EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
229 SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
230 DROP TABLE t1;
231 
232 #
233 # Test problem with range optimzer and sub ranges
234 #
235 
236 CREATE TABLE t1 (a int, b int, c int, INDEX (c,a,b));
237 INSERT INTO t1 VALUES (1,0,0),(1,0,0),(1,0,0);
238 INSERT INTO t1 VALUES (0,1,0),(0,1,0),(0,1,0);
239 # -- First reports 3; second reports 6
240 SELECT COUNT(*) FROM t1 WHERE (c=0 and a=1) or (c=0 and b=1);
241 SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1);
242 DROP TABLE t1;
243 
244 #
245 # Test problem with range optimization over overlapping ranges (#2448)
246 #
247 
248 CREATE TABLE t1 ( a int not null, b int not null, INDEX ab(a,b) );
249 INSERT INTO t1 VALUES (47,1), (70,1), (15,1), (15, 4);
250 SELECT * FROM t1
251 WHERE
252 (
253  ( b =1 AND a BETWEEN 14 AND 21 ) OR
254  ( b =2 AND a BETWEEN 16 AND 18 ) OR
255  ( b =3 AND a BETWEEN 15 AND 19 ) OR
256  (a BETWEEN 19 AND 47)
257 );
258 DROP TABLE t1;
259 
260 #
261 # Test of problem with IN on many different keyparts. (Bug #4157)
262 #
263 
264 CREATE TABLE t1 (
265 id int( 11 ) unsigned NOT NULL AUTO_INCREMENT ,
266 line int( 5 ) unsigned NOT NULL default '0',
267 columnid int( 3 ) unsigned NOT NULL default '0',
268 owner int( 3 ) unsigned NOT NULL default '0',
269 ordinal int( 3 ) unsigned NOT NULL default '0',
270 showid smallint( 6 ) unsigned NOT NULL default '1',
271 tableid int( 1 ) unsigned NOT NULL default '1',
272 content int( 5 ) unsigned NOT NULL default '188',
273 PRIMARY KEY ( owner, id ) ,
274 KEY menu( owner, showid, columnid ) ,
275 KEY `COLUMN` ( owner, columnid, line ) ,
276 KEY `LINES` ( owner, tableid, content, id ) ,
277 KEY recount( owner, line )
278 ) ENGINE = MYISAM;
279 
280 INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5);
281 
282 SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30;
283 drop table t1;
284 
285 #
286 # test for a bug with in() and unique key
287 #
288 
289 create table t1 (id int(10) primary key);
290 insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
291 
292 select id from t1 where id in (2,5,9) ;
293 select id from t1 where id=2 or id=5 or id=9 ;
294 drop table t1;
295 create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2));
296 insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"),
297  (3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"),
298  (6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"),
299  (9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"),
300  (12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"),
301  (15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"),
302  (18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa");
303 select a.id1, b.idnull from t1 as a, t1 as b where a.id2=1 and a.id1=1 and b.id1=a.idnull order by b.id2 desc limit 1;
304 drop table t1;
305 
306 
307 #
308 # Problem with optimizing !=
309 #
310 
311 create table t1 (
312  id int not null auto_increment,
313  name char(1) not null,
314  uid int not null,
315  primary key (id),
316  index uid_index (uid));
317 
318 create table t2 (
319  id int not null auto_increment,
320  name char(1) not null,
321  uid int not null,
322  primary key (id),
323  index uid_index (uid));
324 
325 insert into t1(id, uid, name) values(1, 0, ' ');
326 insert into t1(uid, name) values(0, ' ');
327 
328 insert into t2(uid, name) select uid, name from t1;
329 insert into t1(uid, name) select uid, name from t2;
330 insert into t2(uid, name) select uid, name from t1;
331 insert into t1(uid, name) select uid, name from t2;
332 insert into t2(uid, name) select uid, name from t1;
333 insert into t1(uid, name) select uid, name from t2;
334 insert into t2(uid, name) select uid, name from t1;
335 insert into t1(uid, name) select uid, name from t2;
336 insert into t2(uid, name) select uid, name from t1;
337 insert into t1(uid, name) select uid, name from t2;
338 insert into t2(uid, name) select uid, name from t1;
339 insert into t2(uid, name) select uid, name from t1;
340 insert into t2(uid, name) select uid, name from t1;
341 insert into t2(uid, name) select uid, name from t1;
342 insert into t1(uid, name) select uid, name from t2;
343 
344 delete from t2;
345 insert into t2(uid, name) values
346  (1, CHAR(64+1)),
347  (2, CHAR(64+2)),
348  (3, CHAR(64+3)),
349  (4, CHAR(64+4)),
350  (5, CHAR(64+5)),
351  (6, CHAR(64+6)),
352  (7, CHAR(64+7)),
353  (8, CHAR(64+8)),
354  (9, CHAR(64+9)),
355  (10, CHAR(64+10)),
356  (11, CHAR(64+11)),
357  (12, CHAR(64+12)),
358  (13, CHAR(64+13)),
359  (14, CHAR(64+14)),
360  (15, CHAR(64+15)),
361  (16, CHAR(64+16)),
362  (17, CHAR(64+17)),
363  (18, CHAR(64+18)),
364  (19, CHAR(64+19)),
365  (20, CHAR(64+20)),
366  (21, CHAR(64+21)),
367  (22, CHAR(64+22)),
368  (23, CHAR(64+23)),
369  (24, CHAR(64+24)),
370  (25, CHAR(64+25)),
371  (26, CHAR(64+26));
372 
373 insert into t1(uid, name) select uid, name from t2 order by uid;
374 
375 delete from t2;
376 insert into t2(id, uid, name) select id, uid, name from t1;
377 
378 select count(*) from t1;
379 select count(*) from t2;
380 
381 analyze table t1,t2;
382 
383 explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
384 explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0;
385 explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0;
386 explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0;
387 
388 select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
389 select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0;
390 
391 drop table t1,t2;
392 
393 # Fix for bug#4488
394 #
395 create table t1 (x bigint unsigned not null);
396 insert into t1(x) values (0xfffffffffffffff0);
397 insert into t1(x) values (0xfffffffffffffff1);
398 select * from t1;
399 select count(*) from t1 where x>0;
400 select count(*) from t1 where x=0;
401 select count(*) from t1 where x<0;
402 select count(*) from t1 where x < -16;
403 select count(*) from t1 where x = -16;
404 select count(*) from t1 where x > -16;
405 select count(*) from t1 where x = 18446744073709551601;
406 
407 
408 create table t2 (x bigint not null);
409 insert into t2(x) values (-16);
410 insert into t2(x) values (-15);
411 select * from t2;
412 select count(*) from t2 where x>0;
413 select count(*) from t2 where x=0;
414 select count(*) from t2 where x<0;
415 select count(*) from t2 where x < -16;
416 select count(*) from t2 where x = -16;
417 select count(*) from t2 where x > -16;
418 select count(*) from t2 where x = 18446744073709551601;
419 drop table t1,t2;
420 
421 --disable_warnings
422 create table t1 (x bigint unsigned not null primary key) engine=innodb;
423 --enable_warnings
424 insert into t1(x) values (0xfffffffffffffff0);
425 insert into t1(x) values (0xfffffffffffffff1);
426 select * from t1;
427 select count(*) from t1 where x>0;
428 select count(*) from t1 where x=0;
429 select count(*) from t1 where x<0;
430 select count(*) from t1 where x < -16;
431 select count(*) from t1 where x = -16;
432 select count(*) from t1 where x > -16;
433 select count(*) from t1 where x = 18446744073709551601;
434 
435 drop table t1;
436 
437 #
438 # Bug #11185 incorrect comparison of unsigned int to signed constant
439 #
440 create table t1 (a bigint unsigned);
441 create index t1i on t1(a);
442 insert into t1 select 18446744073709551615;
443 insert into t1 select 18446744073709551614;
444 
445 explain select * from t1 where a <> -1;
446 select * from t1 where a <> -1;
447 explain select * from t1 where a > -1 or a < -1;
448 select * from t1 where a > -1 or a < -1;
449 explain select * from t1 where a > -1;
450 select * from t1 where a > -1;
451 explain select * from t1 where a < -1;
452 select * from t1 where a < -1;
453 
454 drop table t1;
455 
456 #
457 # Bug #6045: Binary Comparison regression in MySQL 4.1
458 # Binary searches didn't use a case insensitive index.
459 #
460 set names latin1;
461 create table t1 (a char(10), b text, key (a)) character set latin1;
462 INSERT INTO t1 (a) VALUES
463 ('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb');
464 # all these three can be optimized
465 explain select * from t1 where a='aaa';
466 explain select * from t1 where a=binary 'aaa';
467 explain select * from t1 where a='aaa' collate latin1_bin;
468 # this one cannot:
469 explain select * from t1 where a='aaa' collate latin1_german1_ci;
470 drop table t1;
471 
472 # Test for BUG#9348 "result for WHERE A AND (B OR C) differs from WHERE a AND (C OR B)"
473 --disable_warnings
474 CREATE TABLE t1 (
475  `CLIENT` char(3) character set latin1 collate latin1_bin NOT NULL default '000',
476  `ARG1` char(3) character set latin1 collate latin1_bin NOT NULL default '',
477  `ARG2` char(3) character set latin1 collate latin1_bin NOT NULL default '',
478  `FUNCTION` varchar(10) character set latin1 collate latin1_bin NOT NULL default '',
479  `FUNCTINT` int(11) NOT NULL default '0',
480  KEY `VERI_CLNT~2` (`ARG1`)
481 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
482 --enable_warnings
483 
484 INSERT INTO t1 VALUES ('000',' 0',' 0','Text 001',0), ('000',' 0',' 1','Text 002',0),
485  ('000',' 1',' 2','Text 003',0), ('000',' 2',' 3','Text 004',0),
486  ('001',' 3',' 0','Text 017',0);
487 
488 SELECT count(*) FROM t1 WHERE CLIENT='000' AND (ARG1 != ' 1' OR ARG1 != ' 2');
489 
490 SELECT count(*) FROM t1 WHERE CLIENT='000' AND (ARG1 != ' 2' OR ARG1 != ' 1');
491 drop table t1;
492 
493 # BUG#16168: Wrong range optimizer results, "Use_count: Wrong count ..."
494 # warnings in server stderr.
495 create table t1 (a int);
496 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
497 
498 CREATE TABLE t2 (
499  pk1 int(11) NOT NULL,
500  pk2 int(11) NOT NULL,
501  pk3 int(11) NOT NULL,
502  pk4 int(11) NOT NULL,
503  filler char(82),
504  PRIMARY KEY (pk1,pk2,pk3,pk4)
505 ) DEFAULT CHARSET=latin1;
506 
507 insert into t2 select 1, A.a+10*B.a, 432, 44, 'fillerZ' from t1 A, t1 B;
508 INSERT INTO t2 VALUES (2621, 2635, 0, 0,'filler'), (2621, 2635, 1, 0,'filler'),
509  (2621, 2635, 10, 0,'filler'), (2621, 2635, 11, 0,'filler'),
510  (2621, 2635, 14, 0,'filler'), (2621, 2635, 1000015, 0,'filler');
511 
512 SELECT * FROM t2
513 WHERE ((((pk4 =0) AND (pk1 =2621) AND (pk2 =2635)))
514 OR ((pk4 =1) AND (((pk1 IN ( 7, 2, 1 ))) OR (pk1 =522)) AND ((pk2 IN ( 0, 2635))))
515 ) AND (pk3 >=1000000);
516 drop table t1, t2;
517 
518 #
519 # Bug #20732: Partial index and long sjis search with '>' fails sometimes
520 #
521 
522 create table t1(a char(2), key(a(1)));
523 insert into t1 values ('x'), ('xx');
524 explain select a from t1 where a > 'x';
525 select a from t1 where a > 'x';
526 drop table t1;
527 
528 #
529 # Bug #24776: assertion abort for 'range checked for each record'
530 #
531 
532 CREATE TABLE t1 (
533  OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
534  OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid',
535  OXLEFT int NOT NULL DEFAULT '0',
536  OXRIGHT int NOT NULL DEFAULT '0',
537  OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
538  PRIMARY KEY (OXID),
539  KEY OXNID (OXID),
540  KEY OXLEFT (OXLEFT),
541  KEY OXRIGHT (OXRIGHT),
542  KEY OXROOTID (OXROOTID)
543 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
544 
545 INSERT INTO t1 VALUES
546 ('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),
547 ('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3,
548  'd8c4177d09f8b11f5.52725521'),
549 ('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5,
550  'd8c4177d09f8b11f5.52725521'),
551 ('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7,
552  'd8c4177d09f8b11f5.52725521'),
553 ('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9,
554  'd8c4177d09f8b11f5.52725521'),
555 ('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11,
556  'd8c4177d09f8b11f5.52725521');
557 
558 EXPLAIN
559 SELECT s.oxid FROM t1 v, t1 s
560  WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
561  v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
562  s.oxleft > v.oxleft AND s.oxleft < v.oxright;
563 
564 SELECT s.oxid FROM t1 v, t1 s
565  WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
566  v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
567  s.oxleft > v.oxleft AND s.oxleft < v.oxright;
568 
569 DROP TABLE t1;
570 
571 # BUG#26624 high mem usage (crash) in range optimizer (depends on order of fields in where)
572 create table t1 (
573  c1 char(10), c2 char(10), c3 char(10), c4 char(10),
574  c5 char(10), c6 char(10), c7 char(10), c8 char(10),
575  c9 char(10), c10 char(10), c11 char(10), c12 char(10),
576  c13 char(10), c14 char(10), c15 char(10), c16 char(10),
577  index(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,c13,c14,c15,c16)
578 );
579 insert into t1 (c1) values ('1'),('1'),('1'),('1');
580 
581 # This must run without crash and fast:
582 select * from t1 where
583  c1 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
584  "abcdefg1", "123456781", "qwertyui1", "asddfg1",
585  "abcdefg2", "123456782", "qwertyui2", "asddfg2",
586  "abcdefg3", "123456783", "qwertyui3", "asddfg3",
587  "abcdefg4", "123456784", "qwertyui4", "asddfg4",
588  "abcdefg5", "123456785", "qwertyui5", "asddfg5",
589  "abcdefg6", "123456786", "qwertyui6", "asddfg6",
590  "abcdefg7", "123456787", "qwertyui7", "asddfg7",
591  "abcdefg8", "123456788", "qwertyui8", "asddfg8",
592  "abcdefg9", "123456789", "qwertyui9", "asddfg9",
593  "abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
594  "abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
595  "abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
596  and c2 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
597  "abcdefg1", "123456781", "qwertyui1", "asddfg1",
598  "abcdefg2", "123456782", "qwertyui2", "asddfg2",
599  "abcdefg3", "123456783", "qwertyui3", "asddfg3",
600  "abcdefg4", "123456784", "qwertyui4", "asddfg4",
601  "abcdefg5", "123456785", "qwertyui5", "asddfg5",
602  "abcdefg6", "123456786", "qwertyui6", "asddfg6",
603  "abcdefg7", "123456787", "qwertyui7", "asddfg7",
604  "abcdefg8", "123456788", "qwertyui8", "asddfg8",
605  "abcdefg9", "123456789", "qwertyui9", "asddfg9",
606  "abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
607  "abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
608  "abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
609  and c3 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
610  "abcdefg1", "123456781", "qwertyui1", "asddfg1",
611  "abcdefg2", "123456782", "qwertyui2", "asddfg2",
612  "abcdefg3", "123456783", "qwertyui3", "asddfg3",
613  "abcdefg4", "123456784", "qwertyui4", "asddfg4",
614  "abcdefg5", "123456785", "qwertyui5", "asddfg5",
615  "abcdefg6", "123456786", "qwertyui6", "asddfg6",
616  "abcdefg7", "123456787", "qwertyui7", "asddfg7",
617  "abcdefg8", "123456788", "qwertyui8", "asddfg8",
618  "abcdefg9", "123456789", "qwertyui9", "asddfg9",
619  "abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
620  "abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
621  "abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
622  and c4 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
623  "abcdefg1", "123456781", "qwertyui1", "asddfg1",
624  "abcdefg2", "123456782", "qwertyui2", "asddfg2",
625  "abcdefg3", "123456783", "qwertyui3", "asddfg3",
626  "abcdefg4", "123456784", "qwertyui4", "asddfg4",
627  "abcdefg5", "123456785", "qwertyui5", "asddfg5",
628  "abcdefg6", "123456786", "qwertyui6", "asddfg6",
629  "abcdefg7", "123456787", "qwertyui7", "asddfg7",
630  "abcdefg8", "123456788", "qwertyui8", "asddfg8",
631  "abcdefg9", "123456789", "qwertyui9", "asddfg9",
632  "abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
633  "abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
634  "abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
635  and c5 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
636  "abcdefg1", "123456781", "qwertyui1", "asddfg1",
637  "abcdefg2", "123456782", "qwertyui2", "asddfg2",
638  "abcdefg3", "123456783", "qwertyui3", "asddfg3",
639  "abcdefg4", "123456784", "qwertyui4", "asddfg4",
640  "abcdefg5", "123456785", "qwertyui5", "asddfg5",
641  "abcdefg6", "123456786", "qwertyui6", "asddfg6",
642  "abcdefg7", "123456787", "qwertyui7", "asddfg7",
643  "abcdefg8", "123456788", "qwertyui8", "asddfg8",
644  "abcdefg9", "123456789", "qwertyui9", "asddfg9",
645  "abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
646  "abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
647  "abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
648  and c6 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
649  "abcdefg1", "123456781", "qwertyui1", "asddfg1",
650  "abcdefg2", "123456782", "qwertyui2", "asddfg2",
651  "abcdefg3", "123456783", "qwertyui3", "asddfg3",
652  "abcdefg4", "123456784", "qwertyui4", "asddfg4",
653  "abcdefg5", "123456785", "qwertyui5", "asddfg5",
654  "abcdefg6", "123456786", "qwertyui6", "asddfg6",
655  "abcdefg7", "123456787", "qwertyui7", "asddfg7",
656  "abcdefg8", "123456788", "qwertyui8", "asddfg8",
657  "abcdefg9", "123456789", "qwertyui9", "asddfg9",
658  "abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
659  "abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
660  "abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
661  and c7 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
662  "abcdefg1", "123456781", "qwertyui1", "asddfg1",
663  "abcdefg2", "123456782", "qwertyui2", "asddfg2",
664  "abcdefg3", "123456783", "qwertyui3", "asddfg3",
665  "abcdefg4", "123456784", "qwertyui4", "asddfg4",
666  "abcdefg5", "123456785", "qwertyui5", "asddfg5",
667  "abcdefg6", "123456786", "qwertyui6", "asddfg6",
668  "abcdefg7", "123456787", "qwertyui7", "asddfg7",
669  "abcdefg8", "123456788", "qwertyui8", "asddfg8",
670  "abcdefg9", "123456789", "qwertyui9", "asddfg9",
671  "abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
672  "abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
673  "abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
674  and c8 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
675  "abcdefg1", "123456781", "qwertyui1", "asddfg1",
676  "abcdefg2", "123456782", "qwertyui2", "asddfg2",
677  "abcdefg3", "123456783", "qwertyui3", "asddfg3",
678  "abcdefg4", "123456784", "qwertyui4", "asddfg4",
679  "abcdefg5", "123456785", "qwertyui5", "asddfg5",
680  "abcdefg6", "123456786", "qwertyui6", "asddfg6",
681  "abcdefg7", "123456787", "qwertyui7", "asddfg7",
682  "abcdefg8", "123456788", "qwertyui8", "asddfg8",
683  "abcdefg9", "123456789", "qwertyui9", "asddfg9",
684  "abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
685  "abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
686  "abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
687  and c9 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
688  "abcdefg1", "123456781", "qwertyui1", "asddfg1",
689  "abcdefg2", "123456782", "qwertyui2", "asddfg2",
690  "abcdefg3", "123456783", "qwertyui3", "asddfg3",
691  "abcdefg4", "123456784", "qwertyui4", "asddfg4",
692  "abcdefg5", "123456785", "qwertyui5", "asddfg5",
693  "abcdefg6", "123456786", "qwertyui6", "asddfg6",
694  "abcdefg7", "123456787", "qwertyui7", "asddfg7",
695  "abcdefg8", "123456788", "qwertyui8", "asddfg8",
696  "abcdefg9", "123456789", "qwertyui9", "asddfg9",
697  "abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
698  "abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
699  "abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
700  and c10 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
701  "abcdefg1", "123456781", "qwertyui1", "asddfg1",
702  "abcdefg2", "123456782", "qwertyui2", "asddfg2",
703  "abcdefg3", "123456783", "qwertyui3", "asddfg3",
704  "abcdefg4", "123456784", "qwertyui4", "asddfg4",
705  "abcdefg5", "123456785", "qwertyui5", "asddfg5",
706  "abcdefg6", "123456786", "qwertyui6", "asddfg6",
707  "abcdefg7", "123456787", "qwertyui7", "asddfg7",
708  "abcdefg8", "123456788", "qwertyui8", "asddfg8",
709  "abcdefg9", "123456789", "qwertyui9", "asddfg9",
710  "abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
711  "abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
712  "abcdefgC", "12345678C", "qwertyuiC", "asddfgC");
713 drop table t1;
714 --echo End of 4.1 tests
715 
716 #
717 # Test for optimization request #10561: to use keys for
718 # NOT IN (c1,...,cn) and NOT BETWEEN c1 AND c2
719 #
720 
721 CREATE TABLE t1 (
722  id int(11) NOT NULL auto_increment,
723  status varchar(20),
724  PRIMARY KEY (id),
725  KEY (status)
726 );
727 
728 INSERT INTO t1 VALUES
729 (1,'B'), (2,'B'), (3,'B'), (4,'B'), (5,'B'), (6,'B'),
730 (7,'B'), (8,'B'), (9,'B'), (10,'B'), (11,'B'), (12,'B'),
731 (13,'B'), (14,'B'), (15,'B'), (16,'B'), (17,'B'), (18,'B'),
732 (19,'B'), (20,'B'), (21,'B'), (22,'B'), (23,'B'), (24,'B'),
733 (25,'A'), (26,'A'), (27,'A'), (28,'A'), (29,'A'), (30,'A'),
734 (31,'A'), (32,'A'), (33,'A'), (34,'A'), (35,'A'), (36,'A'),
735 (37,'A'), (38,'A'), (39,'A'), (40,'A'), (41,'A'), (42,'A'),
736 (43,'A'), (44,'A'), (45,'A'), (46,'A'), (47,'A'), (48,'A'),
737 (49,'A'), (50,'A'), (51,'A'), (52,'A'), (53,'C'), (54,'C'),
738 (55,'C'), (56,'C'), (57,'C'), (58,'C'), (59,'C'), (60,'C');
739 
740 EXPLAIN SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B';
741 EXPLAIN SELECT * FROM t1 WHERE status NOT IN ('A','B');
742 
743 SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B';
744 SELECT * FROM t1 WHERE status NOT IN ('A','B');
745 
746 EXPLAIN SELECT status FROM t1 WHERE status <> 'A' AND status <> 'B';
747 EXPLAIN SELECT status FROM t1 WHERE status NOT IN ('A','B');
748 
749 EXPLAIN SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
750 EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
751 
752 SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
753 SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
754 
755 DROP TABLE t1;
756 
757 #
758 # Test for bug #10031: range to be used over a view
759 #
760 
761 CREATE TABLE t1 (a int, b int, primary key(a,b));
762 
763 INSERT INTO t1 VALUES
764  (1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3),(4,1),(4,2),(4,3);
765 
766 CREATE VIEW v1 as SELECT a,b FROM t1 WHERE b=3;
767 
768 EXPLAIN SELECT a,b FROM t1 WHERE a < 2 and b=3;
769 EXPLAIN SELECT a,b FROM v1 WHERE a < 2 and b=3;
770 
771 EXPLAIN SELECT a,b FROM t1 WHERE a < 2;
772 EXPLAIN SELECT a,b FROM v1 WHERE a < 2;
773 
774 SELECT a,b FROM t1 WHERE a < 2 and b=3;
775 SELECT a,b FROM v1 WHERE a < 2 and b=3;
776 
777 DROP VIEW v1;
778 DROP TABLE t1;
779 
780 #
781 # Bug #11853: DELETE statement with a NOT (LIKE/<=>) where condition
782 # for an indexed attribute
783 #
784 
785 CREATE TABLE t1 (name varchar(15) NOT NULL, KEY idx(name));
786 INSERT INTO t1 VALUES ('Betty'), ('Anna');
787 
788 SELECT * FROM t1;
789 DELETE FROM t1 WHERE name NOT LIKE 'A%a';
790 SELECT * FROM t1;
791 
792 DROP TABLE t1;
793 
794 CREATE TABLE t1 (a int, KEY idx(a));
795 INSERT INTO t1 VALUES (NULL), (1), (2), (3);
796 
797 SELECT * FROM t1;
798 DELETE FROM t1 WHERE NOT(a <=> 2);
799 SELECT * FROM t1;
800 
801 DROP TABLE t1;
802 
803 #
804 # BUG#13317: range optimization doesn't work for IN over VIEW.
805 #
806 create table t1 (a int, b int, primary key(a,b));
807 create view v1 as select a, b from t1;
808 
809 INSERT INTO `t1` VALUES
810 (0,0),(1,0),(2,0),(3,0),(4,0),(5,1),(6,1),(7,1),(8,1),(9,1),(10,2),(11,2),(12,2)
811 ,(13,2),(14,2),(15,3),(16,3),(17,3),(18,3),(19,3);
812 
813 --replace_column 9 #
814 explain select * from t1 where a in (3,4) and b in (1,2,3);
815 --replace_column 9 #
816 explain select * from v1 where a in (3,4) and b in (1,2,3);
817 --replace_column 9 #
818 explain select * from t1 where a between 3 and 4 and b between 1 and 2;
819 --replace_column 9 #
820 explain select * from v1 where a between 3 and 4 and b between 1 and 2;
821 
822 drop view v1;
823 drop table t1;
824 
825 # BUG#13455:
826 create table t3 (a int);
827 insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
828 
829 create table t1 (a varchar(10), filler char(200), key(a)) charset=binary;
830 insert into t1 values ('a','');
831 insert into t1 values ('a ','');
832 insert into t1 values ('a ', '');
833 insert into t1 select concat('a', 1000 + A.a + 10 * (B.a + 10 * C.a)), ''
834  from t3 A, t3 B, t3 C;
835 
836 create table t2 (a varchar(10), filler char(200), key(a));
837 insert into t2 select * from t1;
838 
839 --replace_column 9 #
840 explain select * from t1 where a between 'a' and 'a ';
841 --replace_column 9 #
842 explain select * from t1 where a = 'a' or a='a ';
843 
844 --replace_column 9 #
845 explain select * from t2 where a between 'a' and 'a ';
846 --replace_column 9 #
847 explain select * from t2 where a = 'a' or a='a ';
848 
849 update t1 set a='b' where a<>'a';
850 --replace_column 9 #
851 explain select * from t1 where a not between 'b' and 'b';
852 select a, hex(filler) from t1 where a not between 'b' and 'b';
853 
854 drop table t1,t2,t3;
855 
856 #
857 # BUG#21282
858 #
859 create table t1 (a int);
860 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
861 create table t2 (a int, key(a));
862 insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C;
863 
864 set @a="select * from t2 force index (a) where a NOT IN(0";
865 select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z;
866 set @a=concat(@a, ')');
867 
868 insert into t2 values (11),(13),(15);
869 
870 set @b= concat("explain ", @a);
871 
872 prepare stmt1 from @b;
873 execute stmt1;
874 
875 prepare stmt1 from @a;
876 execute stmt1;
877 
878 drop table t1, t2;
879 
880 #
881 # Bug #18165: range access for BETWEEN with a constant for the first argument
882 #
883 
884 CREATE TABLE t1 (
885  id int NOT NULL DEFAULT '0',
886  b int NOT NULL DEFAULT '0',
887  c int NOT NULL DEFAULT '0',
888  INDEX idx1(b,c), INDEX idx2(c));
889 
890 INSERT INTO t1(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
891 
892 INSERT INTO t1(b,c) VALUES (3,4), (3,4);
893 
894 SELECT * FROM t1 WHERE b<=3 AND 3<=c;
895 SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
896 
897 EXPLAIN SELECT * FROM t1 WHERE b<=3 AND 3<=c;
898 EXPLAIN SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
899 
900 SELECT * FROM t1 WHERE 0 < b OR 0 > c;
901 SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
902 
903 EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c;
904 EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
905 
906 DROP TABLE t1;
907 
908 #
909 # Bug #16249: different results for a range with an without index
910 # when a range condition use an invalid datetime constant
911 #
912 
913 CREATE TABLE t1 (
914  item char(20) NOT NULL default '',
915  started datetime NOT NULL default '0000-00-00 00:00:00',
916  price decimal(16,3) NOT NULL default '0.000',
917  PRIMARY KEY (item,started)
918 ) ENGINE=MyISAM;
919 
920 INSERT INTO t1 VALUES
921 ('A1','2005-11-01 08:00:00',1000),
922 ('A1','2005-11-15 00:00:00',2000),
923 ('A1','2005-12-12 08:00:00',3000),
924 ('A2','2005-12-01 08:00:00',1000);
925 
926 EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
927 SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
928 SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
929 
930 DROP INDEX `PRIMARY` ON t1;
931 
932 EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
933 SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
934 SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
935 
936 DROP TABLE t1;
937 
938 --echo
939 --echo BUG#32198 "Comparison of DATE with DATETIME still not using indexes correctly"
940 --echo
941 CREATE TABLE t1 (
942  id int(11) NOT NULL auto_increment,
943  dateval date default NULL,
944  PRIMARY KEY (id),
945  KEY dateval (dateval)
946 ) AUTO_INCREMENT=173;
947 
948 INSERT INTO t1 VALUES
949 (1,'2007-01-01'),(2,'2007-01-02'),(3,'2007-01-03'),(4,'2007-01-04'),
950 (5,'2007-01-05'),(6,'2007-01-06'),(7,'2007-01-07'),(8,'2007-01-08'),
951 (9,'2007-01-09'),(10,'2007-01-10'),(11,'2007-01-11');
952 
953 --echo This must use range access:
954 explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <= '2007-01-02 23:59:59';
955 
956 drop table t1;
957 
958 #
959 # Bug #33833: different or-ed predicates were erroneously merged into one that
960 # resulted in ref access instead of range access and a wrong result set
961 #
962 
963 CREATE TABLE t1 (
964  a varchar(32), index (a)
965 ) DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
966 
967 INSERT INTO t1 VALUES
968  ('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A');
969 
970 SELECT a FROM t1 WHERE a='b' OR a='B';
971 EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B';
972 
973 DROP TABLE t1;
974 
975 #
976 # Bug #34731: highest possible value for INT erroneously filtered by WHERE
977 #
978 
979 # test UNSIGNED. only occurs when indexed.
980 CREATE TABLE t1 (f1 TINYINT(11) UNSIGNED NOT NULL, PRIMARY KEY (f1));
981 
982 INSERT INTO t1 VALUES (127),(254),(0),(1),(255);
983 
984 # test upper bound
985 # count 5
986 SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256;
987 SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256.0;
988 # count 4
989 SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 255;
990 
991 # show we don't fiddle with lower bound on UNSIGNED
992 # count 0
993 SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < -1;
994 # count 5
995 SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -1;
996 
997 DROP TABLE t1;
998 
999 
1000 # test signed. only occurs when index.
1001 CREATE TABLE t1 ( f1 TINYINT(11) NOT NULL, PRIMARY KEY (f1));
1002 
1003 INSERT INTO t1 VALUES (127),(126),(0),(-128),(-127);
1004 
1005 # test upper bound
1006 # count 5
1007 SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128;
1008 SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128.0;
1009 # count 4
1010 SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 127;
1011 
1012 # test lower bound
1013 # count 5
1014 SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129;
1015 SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129.0;
1016 # count 4
1017 SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -128;
1018 
1019 DROP TABLE t1;
1020 
1021 # End of 5.0 tests
1022 
1023 # BUG#22393 fix: Adjust 'ref' estimate if we have 'range' estimate for
1024 # a smaller scan interval
1025 create table t1 (a int);
1026 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1027 
1028 create table t2 (a int, b int, filler char(100));
1029 insert into t2 select A.a + 10 * (B.a + 10 * C.a), 10, 'filler' from t1 A,
1030 t1 B, t1 C where A.a < 5;
1031 
1032 insert into t2 select 1000, b, 'filler' from t2;
1033 alter table t2 add index (a,b);
1034 # t2 values
1035 # ( 1 , 10, 'filler')
1036 # ( 2 , 10, 'filler')
1037 # ( 3 , 10, 'filler')
1038 # (... , 10, 'filler')
1039 # ...
1040 # (1000, 10, 'filler') - 500 times
1041 
1042 # 500 rows, 1 row
1043 
1044 select 'In following EXPLAIN the access method should be ref or range, #rows~=500 (and not 2)' Z;
1045 explain select * from t2 where a=1000 and b<11;
1046 
1047 drop table t1, t2;
1048 
1049 #
1050 # Bug#42846: wrong result returned for range scan when using covering index
1051 #
1052 CREATE TABLE t1( a INT, b INT, KEY( a, b ) );
1053 
1054 CREATE TABLE t2( a INT, b INT, KEY( a, b ) );
1055 
1056 CREATE TABLE t3( a INT, b INT, KEY( a, b ) );
1057 
1058 INSERT INTO t1( a, b )
1059 VALUES (0, 1), (1, 2), (1, 4), (2, 3), (5, 0), (9, 7);
1060 
1061 INSERT INTO t2( a, b )
1062 VALUES ( 1, 1), ( 2, 1), ( 3, 1), ( 4, 1), ( 5, 1),
1063  ( 6, 1), ( 7, 1), ( 8, 1), ( 9, 1), (10, 1),
1064  (11, 1), (12, 1), (13, 1), (14, 1), (15, 1),
1065  (16, 1), (17, 1), (18, 1), (19, 1), (20, 1);
1066 
1067 INSERT INTO t2 SELECT a, 2 FROM t2 WHERE b = 1;
1068 INSERT INTO t2 SELECT a, 3 FROM t2 WHERE b = 1;
1069 
1070 # To make range scan compelling to the optimizer
1071 INSERT INTO t2 SELECT -1, -1 FROM t2;
1072 INSERT INTO t2 SELECT -1, -1 FROM t2;
1073 INSERT INTO t2 SELECT -1, -1 FROM t2;
1074 
1075 INSERT INTO t3
1076 VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0),
1077  (6, 0), (7, 0), (8, 0), (9, 0), (10, 0);
1078 
1079 # To make range scan compelling to the optimizer
1080 INSERT INTO t3 SELECT * FROM t3 WHERE a = 10;
1081 INSERT INTO t3 SELECT * FROM t3 WHERE a = 10;
1082 
1083 
1084 #
1085 # Problem#1 Test queries. Will give missing results unless Problem#1 is fixed.
1086 # With one exception, they are independent of Problem#2.
1087 #
1088 SELECT * FROM t1 WHERE
1089 3 <= a AND a < 5 OR
1090 5 < a AND b = 3 OR
1091 3 <= a;
1092 
1093 EXPLAIN
1094 SELECT * FROM t1 WHERE
1095 3 <= a AND a < 5 OR
1096 5 < a AND b = 3 OR
1097 3 <= a;
1098 
1099 # Query below: Tests both Problem#1 and Problem#2 (EXPLAIN differs as well)
1100 SELECT * FROM t1 WHERE
1101 3 <= a AND a < 5 OR
1102 5 <= a AND b = 3 OR
1103 3 <= a;
1104 
1105 EXPLAIN
1106 SELECT * FROM t1 WHERE
1107 3 <= a AND a < 5 OR
1108 5 <= a AND b = 3 OR
1109 3 <= a;
1110 
1111 SELECT * FROM t1 WHERE
1112 3 <= a AND a <= 5 OR
1113 5 <= a AND b = 3 OR
1114 3 <= a;
1115 
1116 EXPLAIN
1117 SELECT * FROM t1 WHERE
1118 3 <= a AND a <= 5 OR
1119 5 <= a AND b = 3 OR
1120 3 <= a;
1121 
1122 SELECT * FROM t1 WHERE
1123 3 <= a AND a <= 5 OR
1124 3 <= a;
1125 
1126 EXPLAIN
1127 SELECT * FROM t1 WHERE
1128 3 <= a AND a <= 5 OR
1129 3 <= a;
1130 
1131 #
1132 # Problem#2 Test queries.
1133 # These queries will give missing results if Problem#1 is fixed.
1134 # But Problem#1 also hides this bug.
1135 #
1136 SELECT * FROM t2 WHERE
1137 5 <= a AND a < 10 AND b = 1 OR
1138 15 <= a AND a < 20 AND b = 3
1139 OR
1140 1 <= a AND b = 1;
1141 
1142 EXPLAIN
1143 SELECT * FROM t2 WHERE
1144 5 <= a AND a < 10 AND b = 1 OR
1145 15 <= a AND a < 20 AND b = 3
1146 OR
1147 1 <= a AND b = 1;
1148 
1149 SELECT * FROM t2 WHERE
1150 5 <= a AND a < 10 AND b = 2 OR
1151 15 <= a AND a < 20 AND b = 3
1152 OR
1153 1 <= a AND b = 1;
1154 
1155 EXPLAIN
1156 SELECT * FROM t2 WHERE
1157 5 <= a AND a < 10 AND b = 2 OR
1158 15 <= a AND a < 20 AND b = 3
1159 OR
1160 1 <= a AND b = 1;
1161 
1162 SELECT * FROM t3 WHERE
1163 5 <= a AND a < 10 AND b = 3 OR
1164 a < 5 OR
1165 a < 10;
1166 
1167 EXPLAIN
1168 SELECT * FROM t3 WHERE
1169 5 <= a AND a < 10 AND b = 3 OR
1170 a < 5 OR
1171 a < 10;
1172 
1173 DROP TABLE t1, t2, t3;
1174 
1175 --echo #
1176 --echo # Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN
1177 --echo #
1178 
1179 CREATE TABLE t1(a INT, KEY(a));
1180 INSERT INTO t1 VALUES (1), (NULL);
1181 SELECT * FROM t1 WHERE a <> NULL and (a <> NULL or a <= NULL);
1182 DROP TABLE t1;
1183 
1184 --echo #
1185 --echo # Bug#47925: regression of range optimizer and date comparison in 5.1.39!
1186 --echo #
1187 CREATE TABLE t1 ( a DATE, KEY ( a ) );
1188 CREATE TABLE t2 ( a DATETIME, KEY ( a ) );
1189 
1190 --echo # Make optimizer choose range scan
1191 INSERT INTO t1 VALUES ('2009-09-22'), ('2009-09-22'), ('2009-09-22');
1192 INSERT INTO t1 VALUES ('2009-09-23'), ('2009-09-23'), ('2009-09-23');
1193 
1194 INSERT INTO t2 VALUES ('2009-09-22 12:00:00'), ('2009-09-22 12:00:00'),
1195  ('2009-09-22 12:00:00');
1196 INSERT INTO t2 VALUES ('2009-09-23 12:00:00'), ('2009-09-23 12:00:00'),
1197  ('2009-09-23 12:00:00');
1198 
1199 --echo # DATE vs DATE
1200 --replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X
1201 EXPLAIN
1202 SELECT * FROM t1 WHERE a >= '2009/09/23';
1203 SELECT * FROM t1 WHERE a >= '2009/09/23';
1204 SELECT * FROM t1 WHERE a >= '20090923';
1205 SELECT * FROM t1 WHERE a >= 20090923;
1206 SELECT * FROM t1 WHERE a >= '2009-9-23';
1207 SELECT * FROM t1 WHERE a >= '2009.09.23';
1208 SELECT * FROM t1 WHERE a >= '2009:09:23';
1209 
1210 --echo # DATE vs DATETIME
1211 --replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X
1212 EXPLAIN
1213 SELECT * FROM t2 WHERE a >= '2009/09/23';
1214 SELECT * FROM t2 WHERE a >= '2009/09/23';
1215 SELECT * FROM t2 WHERE a >= '2009/09/23';
1216 SELECT * FROM t2 WHERE a >= '20090923';
1217 SELECT * FROM t2 WHERE a >= 20090923;
1218 SELECT * FROM t2 WHERE a >= '2009-9-23';
1219 SELECT * FROM t2 WHERE a >= '2009.09.23';
1220 SELECT * FROM t2 WHERE a >= '2009:09:23';
1221 
1222 --echo # DATETIME vs DATETIME
1223 --replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X
1224 EXPLAIN
1225 SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00';
1226 SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00';
1227 SELECT * FROM t2 WHERE a >= '20090923120000';
1228 SELECT * FROM t2 WHERE a >= 20090923120000;
1229 SELECT * FROM t2 WHERE a >= '2009-9-23 12:00:00';
1230 SELECT * FROM t2 WHERE a >= '2009.09.23 12:00:00';
1231 SELECT * FROM t2 WHERE a >= '2009:09:23 12:00:00';
1232 
1233 --echo # DATETIME vs DATE
1234 --replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X
1235 EXPLAIN
1236 SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
1237 SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
1238 SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
1239 SELECT * FROM t1 WHERE a >= '20090923000000';
1240 SELECT * FROM t1 WHERE a >= 20090923000000;
1241 SELECT * FROM t1 WHERE a >= '2009-9-23 00:00:00';
1242 SELECT * FROM t1 WHERE a >= '2009.09.23 00:00:00';
1243 SELECT * FROM t1 WHERE a >= '2009:09:23 00:00:00';
1244 
1245 --echo # Test of the new get_date_from_str implementation
1246 --echo # Behavior differs slightly between the trunk and mysql-pe.
1247 --echo # The former may give errors for the truncated values, while the latter
1248 --echo # gives warnings. The purpose of this test is not to interfere, and only
1249 --echo # preserve existing behavior.
1250 SELECT str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND
1251  str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20';
1252 
1253 SELECT str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND
1254  str_to_date('2007-20-00', '%Y-%m-%d') <= '';
1255 
1256 SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20';
1257 SELECT str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND '';
1258 
1259 SELECT str_to_date('', '%Y-%m-%d');
1260 
1261 DROP TABLE t1, t2;
1262 
1263 --echo #
1264 --echo # Bug#48459: valgrind errors with query using 'Range checked for each
1265 --echo # record'
1266 --echo #
1267 CREATE TABLE t1 (
1268  a INT,
1269  b CHAR(2),
1270  c INT,
1271  d INT,
1272  KEY ( c ),
1273  KEY ( d, a, b ( 2 ) ),
1274  KEY ( b ( 1 ) )
1275 );
1276 
1277 INSERT INTO t1 VALUES ( NULL, 'a', 1, 2 ), ( NULL, 'a', 1, 2 ),
1278  ( 1, 'a', 1, 2 ), ( 1, 'a', 1, 2 );
1279 
1280 CREATE TABLE t2 (
1281  a INT,
1282  c INT,
1283  e INT,
1284  KEY ( e )
1285 );
1286 
1287 INSERT INTO t2 VALUES ( 1, 1, NULL ), ( 1, 1, NULL );
1288 
1289 --echo # Should not give Valgrind warnings
1290 SELECT 1
1291 FROM t1, t2
1292 WHERE t1.d <> '1' AND t1.b > '1'
1293 AND t1.a = t2.a AND t1.c = t2.c;
1294 
1295 DROP TABLE t1, t2;
1296 
1297 --echo #
1298 --echo # Bug #48665: sql-bench's insert test fails due to wrong result
1299 --echo #
1300 
1301 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a));
1302 
1303 INSERT INTO t1 VALUES (0,0), (1,1);
1304 
1305 --replace_column 1 @ 2 @ 3 @ 5 @ 6 @ 7 @ 8 @ 9 @ 10 @
1306 EXPLAIN
1307 SELECT * FROM t1 FORCE INDEX (PRIMARY)
1308  WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10);
1309 
1310 --echo # Should return 2 rows
1311 SELECT * FROM t1 FORCE INDEX (PRIMARY)
1312  WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10);
1313 
1314 DROP TABLE t1;
1315 
1316 --echo #
1317 --echo # Bug #54802: 'NOT BETWEEN' evaluation is incorrect
1318 --echo #
1319 
1320 CREATE TABLE t1 (c_key INT, c_notkey INT, KEY(c_key));
1321 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
1322 
1323 EXPLAIN SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key;
1324 SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key;
1325 
1326 DROP TABLE t1;
1327 
1328 --echo #
1329 --echo # Bug #57030: 'BETWEEN' evaluation is incorrect
1330 --echo #
1331 
1332 # Test some BETWEEN predicates which does *not* follow the
1333 # 'normal' pattern of <field> BETWEEN <low const> AND <high const>
1334 
1335 CREATE TABLE t1(pk INT PRIMARY KEY, i4 INT);
1336 CREATE UNIQUE INDEX i4_uq ON t1(i4);
1337 
1338 INSERT INTO t1 VALUES (1,10), (2,20), (3,30);
1339 
1340 EXPLAIN
1341 SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10;
1342 SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10;
1343 
1344 EXPLAIN
1345 SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4;
1346 SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4;
1347 
1348 EXPLAIN
1349 SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4;
1350 SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4;
1351 
1352 EXPLAIN
1353 SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10;
1354 SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10;
1355 
1356 EXPLAIN
1357 SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10;
1358 SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10;
1359 
1360 EXPLAIN
1361 SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11;
1362 SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11;
1363 
1364 EXPLAIN
1365 SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0;
1366 SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0;
1367 
1368 EXPLAIN
1369 SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0;
1370 SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0;
1371 
1372 EXPLAIN
1373 SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999;
1374 SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999;
1375 
1376 EXPLAIN
1377 SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30;
1378 SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30;
1379 
1380 EXPLAIN
1381 SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20';
1382 SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20';
1383 
1384 #Should detect the EQ_REF 't2.pk=t1.i4'
1385 EXPLAIN
1386 SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4;
1387 SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4;
1388 
1389 EXPLAIN
1390 SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
1391 SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
1392 
1393 DROP TABLE t1;
1394 
1395 --echo #
1396 --echo # BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND
1397 --echo # WITH/WITHOUT INDEX RANGE SCAN
1398 --echo #
1399 
1400 create table t1 (id int unsigned not null auto_increment primary key);
1401 insert into t1 values (null);
1402 insert into t1 select null from t1;
1403 insert into t1 select null from t1;
1404 insert into t1 select null from t1;
1405 insert into t1 select null from t1;
1406 insert into t1 select null from t1;
1407 insert into t1 select null from t1;
1408 insert into t1 select null from t1;
1409 insert into t1 select null from t1;
1410 create table t2 (
1411  id int unsigned not null auto_increment,
1412  val decimal(5,3) not null,
1413  primary key (id,val),
1414  unique key (val,id),
1415  unique key (id));
1416 --disable_warnings
1417 insert into t2 select null,id*0.0009 from t1;
1418 --enable_warnings
1419 
1420 select count(val) from t2 ignore index (val) where val > 0.1155;
1421 select count(val) from t2 force index (val) where val > 0.1155;
1422 
1423 drop table t2, t1;
1424 
1425 --echo #
1426 --echo # BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG
1427 --echo # RESULTS WITH DECIMAL CONVERSION
1428 --echo #
1429 
1430 create table t1 (a int,b int,c int,primary key (a,c));
1431 insert into t1 values (1,1,2),(1,1,3),(1,1,4);
1432 # show that the integer 3 is bigger than the decimal 2.9,
1433 # which should also apply to comparing "c" with 2.9
1434 # when c is 3.
1435 select convert(3, signed integer) > 2.9;
1436 select * from t1 force index (primary) where a=1 and c>= 2.9;
1437 select * from t1 ignore index (primary) where a=1 and c>= 2.9;
1438 select * from t1 force index (primary) where a=1 and c> 2.9;
1439 select * from t1 ignore index (primary) where a=1 and c> 2.9;
1440 drop table t1;
1441 
1442 --echo #
1443 --echo # BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG
1444 --echo # RESULT AFTER MYSQL 5.1.
1445 --echo #
1446 
1447 CREATE TABLE t1(
1448  F1 CHAR(5) NOT NULL,
1449  F2 CHAR(5) NOT NULL,
1450  F3 CHAR(5) NOT NULL,
1451  PRIMARY KEY(F1),
1452  INDEX IDX_F2(F2)
1453 );
1454 
1455 INSERT INTO t1 VALUES
1456 ('A','A','A'),('AA','AA','AA'),('AAA','AAA','AAA'),
1457 ('AAAA','AAAA','AAAA'),('AAAAA','AAAAA','AAAAA');
1458 
1459 SELECT * FROM t1 WHERE F1 = 'A ';
1460 SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 = 'A ';
1461 SELECT * FROM t1 WHERE F1 >= 'A ';
1462 SELECT * FROM t1 WHERE F1 > 'A ';
1463 SELECT * FROM t1 WHERE F1 BETWEEN 'A ' AND 'AAAAA';
1464 SELECT * FROM t1 WHERE F2 BETWEEN 'A ' AND 'AAAAA';
1465 SELECT * FROM t1 WHERE F3 BETWEEN 'A ' AND 'AAAAA';
1466 SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A ' AND
1467 'AAAAA';
1468 
1469 DROP TABLE t1;
1470 
1471 --echo End of 5.1 tests
1472 
1473 #
1474 # BUG#32262 fix: crash with decimal column...
1475 #
1476 
1477 CREATE TABLE t1 (c1 DECIMAL(10,0),INDEX(c1));
1478 INSERT INTO t1 VALUES (1),(2),(3);
1479 SELECT c1 FROM t1 WHERE c1 >= 'A' GROUP BY 1;
1480 DROP TABLE t1;
1481 
1482 #
1483 # BUG#32229: Range optimizer crashes for 'range checked for each record' query
1484 #
1485 create table t1 (a int,b int,key (b),key (a),key (b,a));
1486 insert into t1(a,b) values (1,2),(3,4),(5,6),(7,8);
1487 create table t2 (c int);
1488 insert into t2(c) values (1),(5),(6),(7),(8);
1489 select 1 from (select c from t1,t2 where b >= 1 and a <=> c group by 1 limit 1) as d1;
1490 drop table t1, t2;
1491 
1492 --echo #
1493 --echo # Bug #26106: Wrong plan may be chosen when there are several possible
1494 --echo # range and ref accesses
1495 --echo #
1496 --echo # Note: The fix for this bug has been reverted. The code will no longer
1497 --echo # select the optimal plan for the two following test queries. This is
1498 --echo # not due to a bug but due to minor differences in range estimates
1499 --echo # produced by the storage engine.
1500 
1501 CREATE TABLE t1(
1502  a INT,
1503  b INT,
1504  KEY k ( a ),
1505  KEY l ( a, b )
1506 );
1507 
1508 INSERT INTO t1(a) VALUES (1);
1509 INSERT INTO t1
1510 VALUES (2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3);
1511 INSERT INTO t1 SELECT 3, 4 FROM t1 WHERE a = 2 AND b = 3;
1512 INSERT INTO t1 SELECT 4, 1 FROM t1 WHERE a = 2 AND b = 3;
1513 ANALYZE TABLE t1;
1514 INSERT INTO t1 VALUES (1, 2);
1515 INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 2
1516 INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 4
1517 INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 8
1518 INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 16
1519 INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 32
1520 INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 64
1521 INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 128
1522 --echo # This must use range over index l, not k.
1523 --echo # Update: Due to patch being reverted and minor differences in
1524 --echo # range estimates k is selected.
1525 EXPLAIN SELECT * FROM t1 WHERE a = 1 AND b >= 2;
1526 
1527 CREATE TABLE t2(
1528  a INT,
1529  b INT,
1530  c INT,
1531  KEY k ( a ),
1532  KEY l ( a, b ),
1533  KEY m ( b ),
1534  KEY n ( a, c )
1535 );
1536 
1537 INSERT INTO t2(a) VALUES (1);
1538 INSERT INTO t2
1539 VALUES (2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3),
1540  (2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3);
1541 INSERT INTO t2 SELECT 3, 4, 4 FROM t2 WHERE a = 2 AND b = 3;
1542 INSERT INTO t2 SELECT 4, 1, 1 FROM t2 WHERE a = 2 AND b = 3;
1543 ANALYZE TABLE t2;
1544 INSERT INTO t2 VALUES (1, 2, 2);
1545 INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 2
1546 INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 4
1547 INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 8
1548 INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 16
1549 INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 32
1550 INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 64
1551 INSERT INTO t2 VALUES (1, 1, 2);
1552 
1553 --echo # This must use range over index l, not n.
1554 --echo # Update: Due to patch being reverted and minor differences in
1555 --echo # range estimates k is selected.
1556 EXPLAIN SELECT * FROM t2 WHERE a = 1 AND b >= 2 AND c >= 2;
1557 
1558 DROP TABLE t1, t2;
1559 
1560 --echo #
1561 --echo # BUG#11765831: 'RANGE ACCESS' MAY INCORRECTLY FILTER
1562 --echo # AWAY QUALIFYING ROWS
1563 --echo #
1564 
1565 CREATE TABLE t10(
1566  K INT NOT NULL AUTO_INCREMENT,
1567  I INT, J INT,
1568  PRIMARY KEY(K),
1569  KEY(I,J)
1570 );
1571 INSERT INTO t10(I,J) VALUES (6,1),(6,2),(6,3),(6,4),(6,5),
1572  (6,6),(6,7),(6,8),(6,9),(6,0);
1573 
1574 CREATE TABLE t100 LIKE t10;
1575 INSERT INTO t100(I,J) SELECT X.I, X.K+(10*Y.K) FROM t10 AS X,t10 AS Y;
1576 
1577 # Insert offending value:
1578 INSERT INTO t100(I,J) VALUES(8,26);
1579 
1580 let $query= SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5);
1581 
1582 #Verify that 'range' access will be used
1583 --echo
1584 --eval EXPLAIN $query
1585 
1586 # Only row 101,8,26 should be returned
1587 --echo
1588 --eval $query
1589 
1590 DROP TABLE t10,t100;
1591 
1592 --echo #
1593 --echo # BUG#12698916 - JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR
1594 --echo # AFTER FLUSH TABLES [-INT VS NULL]
1595 --echo #
1596 CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB STATS_PERSISTENT=0;
1597 
1598 INSERT INTO t1 VALUES (-100,1),(1,6);
1599 
1600 CREATE TABLE t2 (
1601  col_int_key INT,
1602  col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT",
1603  pk INT NOT NULL,
1604  PRIMARY KEY (pk),
1605  KEY (col_int_key)
1606 ) ENGINE=InnoDB STATS_PERSISTENT=0;
1607 
1608 INSERT INTO t2 VALUES
1609 (1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8);
1610 
1611 let $query=SELECT t1.*,t2.* FROM t1 straight_join t2
1612 ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
1613 
1614 eval EXPLAIN $query;
1615 eval $query;
1616 --echo # need FLUSH so that InnoDB statistics change and thus plan changes
1617 FLUSH TABLES;
1618 eval EXPLAIN $query;
1619 eval $query;
1620 
1621 DROP TABLE t1,t2;
1622 
1623 --echo #
1624 --echo # Bug#12694872 -
1625 --echo # VALGRIND: 18,816 BYTES IN 196 BLOCKS ARE DEFINITELY LOST IN UNIQUE::GET
1626 --echo #
1627 
1628 CREATE TABLE t1 (
1629  pk INTEGER AUTO_INCREMENT,
1630  col_int_nokey INTEGER NOT NULL,
1631  col_int_key INTEGER NOT NULL,
1632 
1633  col_date_key DATE NOT NULL,
1634 
1635  col_varchar_key VARCHAR(1) NOT NULL,
1636  col_varchar_nokey VARCHAR(1) NOT NULL,
1637 
1638  PRIMARY KEY (pk),
1639  KEY (col_int_key),
1640  KEY (col_date_key),
1641  KEY (col_varchar_key, col_int_key)
1642 );
1643 
1644 INSERT INTO t1 (
1645  col_int_key,
1646  col_int_nokey,
1647  col_date_key,
1648  col_varchar_key,
1649  col_varchar_nokey
1650 ) VALUES
1651 (0, 4, '2011-08-25', 'j', 'j'),
1652 (8, 6, '2004-09-18', 'v', 'v'),
1653 (1, 3, '2009-12-01', 'c', 'c'),
1654 (8, 5, '2004-12-17', 'm', 'm'),
1655 (9, 3, '2000-03-14', 'd', 'd'),
1656 (6, 2, '2006-05-25', 'y', 'y'),
1657 (1, 9, '2008-01-23', 't', 't'),
1658 (6, 3, '2007-06-18', 'd', 'd'),
1659 (2, 8, '2002-10-13', 's', 's'),
1660 (4, 1, '1900-01-01', 'r', 'r'),
1661 (8, 8, '1959-04-25', 'm', 'm'),
1662 (4, 8, '2006-03-09', 'b', 'b'),
1663 (4, 5, '2001-06-05', 'x', 'x'),
1664 (7, 7, '2006-05-28', 'g', 'g'),
1665 (4, 5, '2001-04-19', 'p', 'p'),
1666 (1, 1, '1900-01-01', 'q', 'q'),
1667 (9, 6, '2004-08-20', 'w', 'w'),
1668 (4, 2, '2004-10-10', 'd', 'd'),
1669 (8, 9, '2000-04-02', 'e', 'e')
1670 ;
1671 
1672 let $query=
1673 SELECT table2.col_date_key AS field1,
1674  CONCAT ( table2.col_varchar_nokey, table1.col_varchar_nokey ) AS field2
1675 FROM ( t1 AS table1 INNER JOIN t1 AS table2
1676  ON (( table2.pk <> table1.pk ) AND
1677  ( table2.pk >= table1.col_int_nokey ) ) )
1678 WHERE ( table1.pk > 226 AND
1679  table1.pk < ( 226 + 102 ) OR
1680  ( table1.col_int_key > 226 AND
1681  table1.col_int_key < ( 226 + 36 ) OR
1682  ( table1.col_varchar_key <= 'h' OR
1683  table1.col_int_key > 226 AND
1684  table1.col_int_key < ( 226 + 227 ) )
1685  )
1686  )
1687 ;
1688 
1689 ALTER TABLE t1 DISABLE KEYS;
1690 sorted_result;
1691 eval $query;
1692 ALTER TABLE t1 ENABLE KEYS;
1693 
1694 eval CREATE TABLE t2 $query;
1695 
1696 sorted_result;
1697 eval SELECT * FROM t2
1698  WHERE (field1, field2) IN ($query);
1699 
1700 DROP TABLE t1, t2;
1701 
1702 --echo #
1703 --echo # BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET ==
1704 --echo # SAVE_READ_SET
1705 --echo #
1706 CREATE TABLE t1 (
1707  a INT,
1708  b INT,
1709  c INT,
1710  PRIMARY KEY (c,a), KEY (a),KEY (a)
1711 ) ENGINE=INNODB PARTITION BY KEY () PARTITIONS 2;
1712 INSERT INTO t1 VALUES (1,5,1),(2,4,1),(3,3,1),(4,2,1),(5,1,1);
1713 UPDATE t1 SET b = 0, c=1 WHERE a <=>0;
1714 SELECT * FROM t1;
1715 DROP TABLE t1;
1716 
1717 --echo #
1718 --echo # BUG#13256446 - ASSERTION QUICK->HEAD->READ_SET ==
1719 --echo # SAVE_READ_SET' FAILED IN OPT_RANGE.CC:1606
1720 --echo #
1721 CREATE TABLE t1 (
1722  f1 INT AUTO_INCREMENT,
1723  f2 INT,
1724  f3 INT,
1725  f4 INT,
1726  PRIMARY KEY (f1),KEY(f2)
1727 ) ENGINE=INNODB;
1728 CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f2=103;
1729 INSERT INTO t1 VALUES (154,0,NULL,0),(0,NULL,9,0),
1730  (NULL,102,NULL,3),(0,3,NULL,0), (9,0,NULL,0),(0,9,NULL,157);
1731 SELECT * FROM v2;
1732 UPDATE v2 SET f4=0, f2=NULL, f1=NULL WHERE f1 > 16 ORDER BY f1;
1733 SELECT * FROM v2;
1734 DROP TABLE t1;
1735 DROP VIEW v2;
1736 
1737 CREATE TABLE t1 (
1738  f1 INT AUTO_INCREMENT,
1739  f2 INT,
1740  f3 INT,
1741  f4 INT,
1742  PRIMARY KEY (f1),KEY(f2)
1743 ) ENGINE=INNODB;
1744 INSERT INTO t1 VALUES(1,NULL,NULL,0), (2,2,0,3), (9,0,107,18),
1745  (10,0,0,0), (231,0,0,0), (232,0,8,0), (234,0,0,NULL), (235,8,0,3);
1746 CREATE ALGORITHM=MERGE VIEW v3 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f1<=85 ;
1747 SELECT * FROM v3;
1748 UPDATE v3 SET f3=0, f4=4 WHERE f2=68 ORDER BY f1;
1749 SELECT * FROM v3;
1750 DROP TABLE t1;
1751 DROP VIEW v3;
1752 
1753 --echo #
1754 --echo # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE
1755 --echo #
1756 
1757 CREATE TABLE t1 (pk INT PRIMARY KEY);
1758 INSERT INTO t1 VALUES (1),(3),(5);
1759 SELECT * FROM t1 WHERE pk <> 3 OR pk < 4;
1760 DROP TABLE t1;
1761 
1762 --echo #
1763 --echo # BUG#13803810: TOO FEW ROWS RETURNED FOR RANGE ACCESS IN
1764 --echo # VARCHAR INDEX USING DATETIME VALUE
1765 --echo
1766 CREATE TABLE t1 (a DATETIME);
1767 INSERT INTO t1 VALUES ('2001-01-01 00:00:00');
1768 INSERT INTO t1 VALUES ('2001-01-01 11:22:33');
1769 
1770 CREATE TABLE t2 (b VARCHAR(64), KEY (b));
1771 INSERT INTO t2 VALUES ('2001-01-01');
1772 INSERT INTO t2 VALUES ('2001.01.01');
1773 INSERT INTO t2 VALUES ('2001#01#01');
1774 INSERT INTO t2 VALUES ('2001-01-01 00:00:00');
1775 INSERT INTO t2 VALUES ('2001-01-01 11:22:33');
1776 
1777 --echo
1778 --echo # range/ref access cannot be used for this query
1779 --echo
1780 EXPLAIN SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE);
1781 SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE);
1782 
1783 let $query_ab=SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b;
1784 let $query_ba=SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b;
1785 
1786 --echo
1787 --echo # range/ref access cannot be used for any of the queries below.
1788 --echo # See BUG#13814468 about 'Range checked for each record'
1789 --echo
1790 eval EXPLAIN $query_ab;
1791 eval $query_ab;
1792 --echo
1793 eval EXPLAIN $query_ba;
1794 eval $query_ba;
1795 
1796 --echo
1797 DROP TABLE t1,t2;
1798