range.test 19.9 KB
Newer Older
1
#
2
# Problem with range optimizer
3 4
#

5
--disable_warnings
6
drop table if exists t1, t2;
7 8
--enable_warnings

9 10 11 12 13 14 15
CREATE TABLE t1 (
  event_date date DEFAULT '0000-00-00' NOT NULL,
  type int(11) DEFAULT '0' NOT NULL,
  event_id int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (event_date,type,event_id)
);

16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
INSERT INTO t1 VALUES ('1999-07-10',100100,24), ('1999-07-11',100100,25),
('1999-07-13',100600,0), ('1999-07-13',100600,4), ('1999-07-13',100600,26),
('1999-07-14',100600,10), ('1999-07-15',100600,16), ('1999-07-15',100800,45),
('1999-07-15',101000,47), ('1999-07-16',100800,46), ('1999-07-20',100600,5),
('1999-07-20',100600,27), ('1999-07-21',100600,11), ('1999-07-22',100600,17),
('1999-07-23',100100,39), ('1999-07-24',100100,39), ('1999-07-24',100500,40),
('1999-07-25',100100,39), ('1999-07-27',100600,1), ('1999-07-27',100600,6),
('1999-07-27',100600,28), ('1999-07-28',100600,12), ('1999-07-29',100500,41),
('1999-07-29',100600,18), ('1999-07-30',100500,41), ('1999-07-31',100500,41),
('1999-08-01',100700,34), ('1999-08-03',100600,7), ('1999-08-03',100600,29),
('1999-08-04',100600,13), ('1999-08-05',100500,42), ('1999-08-05',100600,19),
('1999-08-06',100500,42), ('1999-08-07',100500,42), ('1999-08-08',100500,42),
('1999-08-10',100600,2), ('1999-08-10',100600,9), ('1999-08-10',100600,30),
('1999-08-11',100600,14), ('1999-08-12',100600,20), ('1999-08-17',100500,8),
('1999-08-17',100600,31), ('1999-08-18',100600,15), ('1999-08-19',100600,22),
('1999-08-24',100600,3), ('1999-08-24',100600,32), ('1999-08-27',100500,43),
('1999-08-31',100600,33), ('1999-09-17',100100,37), ('1999-09-18',100100,37),
('1999-09-19',100100,37), ('2000-12-18',100700,38);
34 35 36 37 38 39 40 41 42 43 44 45 46 47

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;
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;
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;
drop table t1;

CREATE TABLE t1 (
  PAPER_ID smallint(6) DEFAULT '0' NOT NULL,
  YEAR smallint(6) DEFAULT '0' NOT NULL,
  ISSUE smallint(6) DEFAULT '0' NOT NULL,
  CLOSED tinyint(4) DEFAULT '0' NOT NULL,
  ISS_DATE date DEFAULT '0000-00-00' NOT NULL,
  PRIMARY KEY (PAPER_ID,YEAR,ISSUE)
);
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'), (1,1999,111,0,'1999-03-23'),
                      (1,1999,222,0,'1999-03-23'), (3,1999,33,0,'1999-07-12'),
                      (3,1999,32,0,'1999-07-12'), (3,1999,31,0,'1999-07-12'),
                      (3,1999,30,0,'1999-07-12'), (3,1999,29,0,'1999-07-12'),
                      (3,1999,28,0,'1999-07-12'), (1,1999,40,1,'1999-05-01'),
                      (1,1999,41,1,'1999-05-01'), (1,1999,42,1,'1999-05-01'),
                      (1,1999,46,1,'1999-05-01'), (1,1999,47,1,'1999-05-01'),
                      (1,1999,48,1,'1999-05-01'), (1,1999,49,1,'1999-05-01'),
                      (1,1999,50,0,'1999-05-01'), (1,1999,51,0,'1999-05-01'),
                      (1,1999,200,0,'1999-06-28'), (1,1999,52,0,'1999-06-28'),
                      (1,1999,53,0,'1999-06-28'), (1,1999,54,0,'1999-06-28'),
                      (1,1999,55,0,'1999-06-28'), (1,1999,56,0,'1999-07-01'),
                      (1,1999,57,0,'1999-07-01'), (1,1999,58,0,'1999-07-01'),
                      (1,1999,59,0,'1999-07-01'), (1,1999,60,0,'1999-07-01'),
                      (3,1999,35,0,'1999-07-12');
63 64 65 66 67 68 69 70 71 72 73 74 75
select YEAR,ISSUE from t1 where PAPER_ID=3 and (YEAR>1999 or (YEAR=1999 and ISSUE>28))  order by YEAR,ISSUE;
check table t1;
repair table t1;
drop table t1;

CREATE TABLE t1 (
  id int(11) NOT NULL auto_increment,
  parent_id int(11) DEFAULT '0' NOT NULL,
  level tinyint(4) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY parent_id (parent_id),
  KEY level (level)
);
76 77 78 79 80 81
INSERT INTO t1 VALUES (1,0,0), (3,1,1), (4,1,1), (8,2,2), (9,2,2), (17,3,2),
(22,4,2), (24,4,2), (28,5,2), (29,5,2), (30,5,2), (31,6,2), (32,6,2), (33,6,2),
(203,7,2), (202,7,2), (20,3,2), (157,0,0), (193,5,2), (40,7,2), (2,1,1),
(15,2,2), (6,1,1), (34,6,2), (35,6,2), (16,3,2), (7,1,1), (36,7,2), (18,3,2),
(26,5,2), (27,5,2), (183,4,2), (38,7,2), (25,5,2), (37,7,2), (21,4,2),
(19,3,2), (5,1,1), (179,5,2);
82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113
SELECT * FROM t1 WHERE level = 1 AND parent_id = 1;
# The following select returned 0 rows in 3.23.8
SELECT * FROM t1 WHERE level = 1 AND parent_id = 1 order by id;
drop table t1;

#
# Testing of bug in range optimizer with many key parts and > and <
#

create table t1(
		Satellite		varchar(25)	not null,
		SensorMode		varchar(25)	not null,
		FullImageCornersUpperLeftLongitude	double	not null,
		FullImageCornersUpperRightLongitude	double	not null,
		FullImageCornersUpperRightLatitude	double	not null,
		FullImageCornersLowerRightLatitude	double	not null,
	        index two (Satellite, SensorMode, FullImageCornersUpperLeftLongitude, FullImageCornersUpperRightLongitude, FullImageCornersUpperRightLatitude, FullImageCornersLowerRightLatitude));

insert into t1 values("OV-3","PAN1",91,-92,40,50);
insert into t1 values("OV-4","PAN1",91,-92,40,50);

select * from t1 where t1.Satellite = "OV-3" and t1.SensorMode = "PAN1" and t1.FullImageCornersUpperLeftLongitude > -90.000000 and t1.FullImageCornersUpperRightLongitude < -82.000000;
drop table t1;

create table t1 ( aString char(100) not null default "", key aString (aString(10)) );
insert t1 (aString) values ( "believe in myself" ), ( "believe" ), ("baaa" ), ( "believe in love");
select * from t1 where aString < "believe in myself" order by aString;
select * from t1 where aString > "believe in love" order by aString;
alter table t1 drop key aString;
select * from t1 where aString < "believe in myself" order by aString;
select * from t1 where aString > "believe in love" order by aString;
drop table t1;
114 115 116 117 118 119 120

#
# Problem with binary strings
#

CREATE TABLE t1 (
  t1ID int(10) unsigned NOT NULL auto_increment,
121
  art binary(1) NOT NULL default '',
122 123 124 125 126 127 128 129 130
  KNR char(5) NOT NULL default '',
  RECHNR char(6) NOT NULL default '',
  POSNR char(2) NOT NULL default '',
  ARTNR char(10) NOT NULL default '',
  TEX char(70) NOT NULL default '',
  PRIMARY KEY  (t1ID),
  KEY IdxArt (art),
  KEY IdxKnr (KNR),
  KEY IdxArtnr (ARTNR)
unknown's avatar
unknown committed
131
) ENGINE=MyISAM;
132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173

INSERT INTO t1 (art) VALUES ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j');
select count(*) from t1 where upper(art) = 'J';
select count(*) from t1 where art = 'J' or art = 'j';
select count(*) from t1 where art = 'j' or art = 'J';
174 175
select count(*) from t1 where art = 'j';
select count(*) from t1 where art = 'J';
176
drop table t1;
unknown's avatar
unknown committed
177 178 179 180 181 182 183 184 185 186 187 188 189 190 191
#
# BETWEEN problems
#
create table t1 (x int, y int, index(x), index(y));
insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9);
update t1 set y=x;
# between with only one end fixed
explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 7 and t1.y+0;
explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 7 and t2.x <= t1.y+0;
# between with both expressions on both ends
explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1;
explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1;
# equation propagation
explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y;
explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y;
192 193 194
# testing IN
explain select count(*) from t1 where x in (1);
explain select count(*) from t1 where x in (1,2);
unknown's avatar
unknown committed
195 196
drop table t1;

197 198 199
#
# bug #1172
#
200
CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1));
201 202 203 204 205 206
INSERT INTO t1 VALUES (0),(0),(1),(1);
CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya));
INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2);
explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
DROP TABLE t1,t2;

207 208 209 210 211 212 213 214 215
#
# bug #1724: use RANGE on more selective column instead of REF on less
# selective

CREATE TABLE t1 (
  a int(11) default NULL,
  b int(11) default NULL,
  KEY a (a),
  KEY b (b)
unknown's avatar
unknown committed
216
) ENGINE=MyISAM;
217 218 219 220 221 222 223 224


INSERT INTO t1 VALUES
(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,2),(10,2),
(13,2),(14,2),(15,2),(16,2),(17,3),(17,3),(16,3),(17,3),(19,3),(20,3),
(21,4),(22,5),(23,5),(24,5),(25,5),(26,5),(30,5),(31,5),(32,5),(33,5),
(33,5),(33,5),(33,5),(33,5),(34,5),(35,5);

unknown's avatar
unknown committed
225
# we expect that optimizer will choose index on A
226 227
EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
228 229
DROP TABLE t1;

230
#
231
# Test problem with range optimzer and sub ranges
232
#
233

234 235 236 237 238 239
CREATE TABLE t1 (a int, b int, c int, INDEX (c,a,b));
INSERT INTO t1 VALUES (1,0,0),(1,0,0),(1,0,0);
INSERT INTO t1 VALUES (0,1,0),(0,1,0),(0,1,0);
# -- First reports 3; second reports 6
SELECT COUNT(*) FROM t1 WHERE (c=0 and a=1) or (c=0 and b=1);
SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1);
240 241
DROP TABLE t1;

242 243 244 245 246 247 248 249 250 251 252 253 254 255
#
# Test problem with range optimization over overlapping ranges (#2448)
#

CREATE TABLE t1 ( a int not null, b int not null, INDEX ab(a,b) );
INSERT INTO t1 VALUES (47,1), (70,1), (15,1), (15, 4);
SELECT * FROM t1
WHERE
(
    ( b =1 AND a BETWEEN 14 AND 21 ) OR
    ( b =2 AND a BETWEEN 16 AND 18 ) OR
    ( b =3 AND a BETWEEN 15 AND 19 ) OR
    (a BETWEEN 19 AND 47)
);
unknown's avatar
unknown committed
256 257
DROP TABLE t1;

258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281
#
# Test of problem with IN on many different keyparts. (Bug #4157)
#

CREATE TABLE t1 (
id int( 11 ) unsigned NOT NULL AUTO_INCREMENT ,
line int( 5 ) unsigned NOT NULL default '0',
columnid int( 3 ) unsigned NOT NULL default '0',
owner int( 3 ) unsigned NOT NULL default '0',
ordinal int( 3 ) unsigned NOT NULL default '0',
showid smallint( 6 ) unsigned NOT NULL default '1',
tableid int( 1 ) unsigned NOT NULL default '1',
content int( 5 ) unsigned NOT NULL default '188',
PRIMARY KEY ( owner, id ) ,
KEY menu( owner, showid, columnid ) ,
KEY `COLUMN` ( owner, columnid, line ) ,
KEY `LINES` ( owner, tableid, content, id ) ,
KEY recount( owner, line ) 
) ENGINE = MYISAM;

INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5);

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;
drop table t1;
unknown's avatar
unknown committed
282 283

#
unknown's avatar
unknown committed
284
# test for a bug with in() and unique key
unknown's avatar
unknown committed
285
#
unknown's avatar
unknown committed
286 287 288 289 290 291 292

create  table t1 (id int(10) primary key);
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);

select id from t1 where id in (2,5,9) ;
select id from t1 where id=2 or id=5 or id=9 ;
drop table t1;
293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385
create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2));
insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"),
                      (3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"),
                      (6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"),
                      (9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"),
                      (12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"),
                      (15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"),
                      (18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa");
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;
drop table t1;


#
# Problem with optimizing !=
#

create table t1 (
  id int not null auto_increment,
  name char(1) not null,
  uid int not null,
  primary key (id),
  index uid_index (uid));
  
create table t2 (
  id int not null auto_increment,
  name char(1) not null,
  uid int not null,
  primary key (id),
  index uid_index (uid));
  
insert into t1(id, uid, name) values(1, 0, ' ');
insert into t1(uid, name) values(0, ' ');

insert into t2(uid, name) select uid, name from t1;
insert into t1(uid, name) select uid, name from t2;
insert into t2(uid, name) select uid, name from t1;
insert into t1(uid, name) select uid, name from t2;
insert into t2(uid, name) select uid, name from t1;
insert into t1(uid, name) select uid, name from t2;
insert into t2(uid, name) select uid, name from t1;
insert into t1(uid, name) select uid, name from t2;
insert into t2(uid, name) select uid, name from t1;
insert into t1(uid, name) select uid, name from t2;
insert into t2(uid, name) select uid, name from t1;
insert into t2(uid, name) select uid, name from t1;
insert into t2(uid, name) select uid, name from t1;
insert into t2(uid, name) select uid, name from t1;
insert into t1(uid, name) select uid, name from t2;

delete from t2;
insert into t2(uid, name) values 
  (1, CHAR(64+1)),
  (2, CHAR(64+2)),
  (3, CHAR(64+3)),
  (4, CHAR(64+4)),
  (5, CHAR(64+5)),
  (6, CHAR(64+6)),
  (7, CHAR(64+7)),
  (8, CHAR(64+8)),
  (9, CHAR(64+9)),
  (10, CHAR(64+10)),
  (11, CHAR(64+11)),
  (12, CHAR(64+12)),
  (13, CHAR(64+13)),
  (14, CHAR(64+14)),
  (15, CHAR(64+15)),
  (16, CHAR(64+16)),
  (17, CHAR(64+17)),
  (18, CHAR(64+18)),
  (19, CHAR(64+19)),
  (20, CHAR(64+20)),
  (21, CHAR(64+21)),
  (22, CHAR(64+22)),
  (23, CHAR(64+23)),
  (24, CHAR(64+24)),
  (25, CHAR(64+25)),
  (26, CHAR(64+26));

insert into t1(uid, name) select uid, name from t2;

delete from t2;
insert into t2(id, uid, name) select id, uid, name from t1;

select count(*) from t1;  
select count(*) from t2;

explain select * from t1, t2  where t1.uid=t2.uid AND t1.uid > 0;
explain select * from t1, t2  where t1.uid=t2.uid AND t1.uid != 0;

select * from t1, t2  where t1.uid=t2.uid AND t1.uid > 0;
select * from t1, t2  where t1.uid=t2.uid AND t1.uid != 0;

drop table t1,t2;
386 387 388 389 390 391 392 393 394 395 396 397 398

# Fix for bug#4488 
#
create table t1 (x bigint unsigned not null);
insert into t1(x) values (0xfffffffffffffff0);
insert into t1(x) values (0xfffffffffffffff1);
select * from t1;
select count(*) from t1 where x>0;
select count(*) from t1 where x=0;
select count(*) from t1 where x<0;
select count(*) from t1 where x < -16;
select count(*) from t1 where x = -16;
select count(*) from t1 where x > -16;
399 400
select count(*) from t1 where x = 18446744073709551601;

401 402 403 404 405 406 407 408 409 410 411

create table t2 (x bigint not null);
insert into t2(x) values (0xfffffffffffffff0);
insert into t2(x) values (0xfffffffffffffff1);
select * from t2;
select count(*) from t2 where x>0;
select count(*) from t2 where x=0;
select count(*) from t2 where x<0;
select count(*) from t2 where x < -16;
select count(*) from t2 where x = -16;
select count(*) from t2 where x > -16;
412
select count(*) from t2 where x = 18446744073709551601;
413 414

drop table t1;
415
--disable_warnings
416
create table t1 (x bigint unsigned not null primary key) engine=innodb;
417
--enable_warnings
418 419 420 421 422 423 424 425
insert into t1(x) values (0xfffffffffffffff0);
insert into t1(x) values (0xfffffffffffffff1);
select * from t1;
select count(*) from t1 where x>0;
select count(*) from t1 where x=0;
select count(*) from t1 where x<0;
select count(*) from t1 where x < -16;
select count(*) from t1 where x = -16;
unknown's avatar
unknown committed
426 427 428
# The following query returns wrong value because the range optimizer can't
# handle search on a signed value for an unsigned parameter. This will be fixed in
# 5.0
429
select count(*) from t1 where x > -16;
430
select count(*) from t1 where x = 18446744073709551601;
431 432 433

drop table t1;

434 435 436 437 438 439 440 441 442 443 444 445 446 447
#
# Bug #6045: Binary Comparison regression in MySQL 4.1
# Binary searches didn't use a case insensitive index.
#
set names latin1;
create table t1 (a char(10), b text, key (a)) character set latin1;
INSERT INTO t1 (a) VALUES
('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb');
# all these three can be optimized
explain select * from t1 where a='aaa';
explain select * from t1 where a=binary 'aaa';
explain select * from t1 where a='aaa' collate latin1_bin;
# this one cannot:
explain select * from t1 where a='aaa' collate latin1_german1_ci;
448 449 450
drop table t1;

# Test for BUG#9348 "result for WHERE A AND (B OR C) differs from WHERE a AND (C OR B)"
451
--disable_warnings
452 453 454 455 456 457 458 459
CREATE TABLE t1 (
  `CLIENT` char(3) character set latin1 collate latin1_bin NOT NULL default '000',
  `ARG1` char(3) character set latin1 collate latin1_bin NOT NULL default '',
  `ARG2` char(3) character set latin1 collate latin1_bin NOT NULL default '',
  `FUNCTION` varchar(10) character set latin1 collate latin1_bin NOT NULL default '',
  `FUNCTINT` int(11) NOT NULL default '0',
  KEY `VERI_CLNT~2` (`ARG1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
460
--enable_warnings
461 462 463 464 465 466 467 468 469 470

INSERT INTO t1 VALUES ('000',' 0',' 0','Text 001',0), ('000',' 0',' 1','Text 002',0),
  ('000',' 1',' 2','Text 003',0), ('000',' 2',' 3','Text 004',0),
  ('001',' 3',' 0','Text 017',0);

SELECT count(*) FROM t1 WHERE CLIENT='000' AND (ARG1 != ' 1' OR ARG1 != ' 2');

SELECT count(*) FROM t1 WHERE CLIENT='000' AND (ARG1 != ' 2' OR ARG1 != ' 1');
drop table t1;