range.test

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

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;

analyze table t1,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 t2.uid > 0;
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 t2.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;

# 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;
select count(*) from t1 where x = 18446744073709551601;


create table t2 (x bigint not null);
insert into t2(x) values (cast(0xfffffffffffffff0+0 as signed));
insert into t2(x) values (cast(0xfffffffffffffff1+0 as signed));
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;
select count(*) from t2 where x = 18446744073709551601;
drop table t1,t2;

--disable_warnings
create table t1 (x bigint unsigned not null primary key) engine=innodb;
--enable_warnings
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;
select count(*) from t1 where x = 18446744073709551601;

drop table t1;

#
# Bug #11185 incorrect comparison of unsigned int to signed constant
#
create table t1 (a bigint unsigned);
create index t1i on t1(a);
insert into t1 select 18446744073709551615;
insert into t1 select 18446744073709551614;

explain select * from t1 where a <> -1;
select * from t1 where a <> -1;
explain select * from t1 where a > -1 or a < -1;
select * from t1 where a > -1 or a < -1;
explain select * from t1 where a > -1;
select * from t1 where a > -1;
explain select * from t1 where a < -1;
select * from t1 where a < -1;

drop table t1;

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

# Test for BUG#9348 "result for WHERE A AND (B OR C) differs from WHERE a AND (C OR B)"
--disable_warnings
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;
--enable_warnings

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;

# End of 4.1 tests

#
# Test for optimization request #10561: to use keys for
# NOT IN (c1,...,cn) and NOT BETWEEN c1 AND c2
#

CREATE TABLE t1 (
  id int(11) NOT NULL auto_increment,
  status varchar(20),
  PRIMARY KEY  (id),
  KEY (status)
);

INSERT INTO t1 VALUES
(1,'B'), (2,'B'), (3,'B'), (4,'B'), (5,'B'), (6,'B'),
(7,'B'), (8,'B'), (9,'B'), (10,'B'), (11,'B'), (12,'B'),
(13,'B'), (14,'B'), (15,'B'), (16,'B'), (17,'B'), (18,'B'),
(19,'B'), (20,'B'), (21,'B'), (22,'B'), (23,'B'), (24,'B'), 
(25,'A'), (26,'A'), (27,'A'), (28,'A'), (29,'A'), (30,'A'),
(31,'A'), (32,'A'), (33,'A'), (34,'A'), (35,'A'), (36,'A'),
(37,'A'), (38,'A'), (39,'A'), (40,'A'), (41,'A'), (42,'A'),
(43,'A'), (44,'A'), (45,'A'), (46,'A'), (47,'A'), (48,'A'),
(49,'A'), (50,'A'), (51,'A'), (52,'A'), (53,'C'), (54,'C'),
(55,'C'), (56,'C'), (57,'C'), (58,'C'), (59,'C'), (60,'C');

EXPLAIN SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B';
EXPLAIN SELECT * FROM t1 WHERE status NOT IN ('A','B');

SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B';
SELECT * FROM t1 WHERE status NOT IN ('A','B');

EXPLAIN SELECT status FROM t1 WHERE status <> 'A' AND status <> 'B';
EXPLAIN SELECT status FROM t1 WHERE status NOT IN ('A','B');

EXPLAIN SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B';

SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
SELECT * FROM t1 WHERE status < 'A' OR status > 'B';

DROP TABLE t1;

#
# Test for bug #10031: range to be used over a view
#

CREATE TABLE  t1 (a int, b int, primary key(a,b));

INSERT INTO  t1 VALUES
  (1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3),(4,1),(4,2),(4,3);

CREATE VIEW v1 as SELECT a,b FROM t1 WHERE b=3;

EXPLAIN SELECT a,b FROM t1 WHERE a < 2 and b=3;
EXPLAIN SELECT a,b FROM v1 WHERE a < 2 and b=3;

EXPLAIN SELECT a,b FROM t1 WHERE a < 2;
EXPLAIN SELECT a,b FROM v1 WHERE a < 2;

SELECT a,b FROM t1 WHERE a < 2 and b=3;
SELECT a,b FROM v1 WHERE a < 2 and b=3; 

DROP VIEW v1;
DROP TABLE t1;

#
# Bug #11853: DELETE statement with a NOT (LIKE/<=>) where condition
#             for an indexed attribute              
#             

CREATE TABLE t1 (name varchar(15) NOT NULL, KEY idx(name));
INSERT INTO t1 VALUES ('Betty'), ('Anna');

SELECT * FROM t1;
DELETE FROM t1 WHERE name NOT LIKE 'A%a';
SELECT * FROM t1;

DROP TABLE t1;

CREATE TABLE t1 (a int, KEY idx(a));
INSERT INTO t1 VALUES (NULL), (1), (2), (3);

SELECT * FROM t1;
DELETE FROM t1 WHERE NOT(a <=> 2);
SELECT * FROM t1;

DROP TABLE t1;

#
# BUG#13317: range optimization doesn't work for IN over VIEW.
#
create table t1 (a int, b int, primary key(a,b));
create view v1 as select a, b from t1;

INSERT INTO `t1` VALUES
(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)
,(13,2),(14,2),(15,3),(16,3),(17,3),(18,3),(19,3);

--replace_column 9 #
explain select * from t1 where a in (3,4)  and b in (1,2,3);
--replace_column 9 #
explain select * from v1 where a in (3,4)  and b in (1,2,3);
--replace_column 9 #
explain select * from t1 where a between 3 and 4 and b between 1 and 2;
--replace_column 9 #
explain select * from v1 where a between 3 and 4 and b between 1 and 2;
 
drop view v1;
drop table t1;

# BUG#13455: 
create table t3 (a int);
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (a varchar(10), filler char(200), key(a)) charset=binary;
insert into t1 values ('a','');
insert into t1 values ('a ','');
insert into t1 values ('a  ', '');
insert into t1 select concat('a', 1000 + A.a + 10 * (B.a + 10 * C.a)), ''
  from t3 A, t3 B, t3 C;

create table t2 (a varchar(10), filler char(200), key(a));
insert into t2 select * from t1;

--replace_column 9 #
explain select * from t1 where a between 'a' and 'a '; 
--replace_column 9 #
explain select * from t1 where a = 'a' or a='a ';

--replace_column 9 #
explain select * from t2 where a between 'a' and 'a '; 
--replace_column 9 #
explain select * from t2 where a = 'a' or a='a ';

update t1 set a='b' where a<>'a';
--replace_column 9 #
explain select * from t1 where a not between 'b' and 'b'; 
select a, hex(filler) from t1 where a not between 'b' and 'b'; 

drop table t1,t2,t3;