range.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

  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;

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 (-16);
insert into t2(x) values (-15);
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;

# BUG#16168: Wrong range optimizer results, "Use_count: Wrong count ..."
#            warnings in server stderr.
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE t2 (
  pk1 int(11) NOT NULL,
  pk2 int(11) NOT NULL,
  pk3 int(11) NOT NULL,
  pk4 int(11) NOT NULL,
  filler char(82),
  PRIMARY KEY (pk1,pk2,pk3,pk4)
) DEFAULT CHARSET=latin1;

insert into t2 select 1, A.a+10*B.a, 432, 44, 'fillerZ' from t1 A, t1 B;
INSERT INTO t2 VALUES (2621, 2635, 0, 0,'filler'), (2621, 2635, 1, 0,'filler'),
  (2621, 2635, 10, 0,'filler'), (2621, 2635, 11, 0,'filler'),
  (2621, 2635, 14, 0,'filler'), (2621, 2635, 1000015, 0,'filler');

SELECT * FROM t2
WHERE ((((pk4 =0) AND (pk1 =2621) AND (pk2 =2635)))
OR ((pk4 =1) AND (((pk1 IN ( 7, 2, 1 ))) OR (pk1 =522)) AND ((pk2 IN ( 0, 2635))))
) AND (pk3 >=1000000);
drop table t1, t2;

#
# Bug #20732: Partial index and long sjis search with '>' fails sometimes
#

create table t1(a char(2), key(a(1)));
insert into t1 values ('x'), ('xx');
explain select a from t1 where a > 'x';
select a from t1 where a > 'x';
drop table t1;

#
# Bug #24776: assertion abort for 'range checked for each record' 
#

CREATE TABLE t1 (
  OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
  OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid',
  OXLEFT int NOT NULL DEFAULT '0',
  OXRIGHT int NOT NULL DEFAULT '0',
  OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
  PRIMARY KEY  (OXID),
  KEY OXNID (OXID),
  KEY OXLEFT (OXLEFT),
  KEY OXRIGHT (OXRIGHT),
  KEY OXROOTID (OXROOTID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

INSERT INTO t1 VALUES
('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),
('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3,
 'd8c4177d09f8b11f5.52725521'),
('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5,
 'd8c4177d09f8b11f5.52725521'),
('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7,
 'd8c4177d09f8b11f5.52725521'),
('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9,
 'd8c4177d09f8b11f5.52725521'),
('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11,
 'd8c4177d09f8b11f5.52725521');

EXPLAIN
SELECT s.oxid FROM t1 v, t1 s 
  WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
        v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
        s.oxleft > v.oxleft AND s.oxleft < v.oxright;

SELECT s.oxid FROM t1 v, t1 s 
  WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
        v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
        s.oxleft > v.oxleft AND s.oxleft < v.oxright;

DROP TABLE t1;

# BUG#26624 high mem usage (crash) in range optimizer (depends on order of fields in where)
create table t1 (
  c1  char(10), c2  char(10), c3  char(10), c4  char(10),
  c5  char(10), c6  char(10), c7  char(10), c8  char(10),
  c9  char(10), c10 char(10), c11 char(10), c12 char(10),
  c13 char(10), c14 char(10), c15 char(10), c16 char(10),
  index(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,c13,c14,c15,c16)
);
insert into t1 (c1) values ('1'),('1'),('1'),('1');

# This must run without crash and fast:
select * from t1 where
     c1 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 
            "abcdefg1", "123456781", "qwertyui1", "asddfg1", 
            "abcdefg2", "123456782", "qwertyui2", "asddfg2", 
            "abcdefg3", "123456783", "qwertyui3", "asddfg3", 
            "abcdefg4", "123456784", "qwertyui4", "asddfg4",
            "abcdefg5", "123456785", "qwertyui5", "asddfg5",
            "abcdefg6", "123456786", "qwertyui6", "asddfg6",
            "abcdefg7", "123456787", "qwertyui7", "asddfg7",
            "abcdefg8", "123456788", "qwertyui8", "asddfg8",
            "abcdefg9", "123456789", "qwertyui9", "asddfg9",
            "abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
            "abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
            "abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
 and c2 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 
            "abcdefg1", "123456781", "qwertyui1", "asddfg1", 
            "abcdefg2", "123456782", "qwertyui2", "asddfg2", 
            "abcdefg3", "123456783", "qwertyui3", "asddfg3", 
            "abcdefg4", "123456784", "qwertyui4", "asddfg4", 
            "abcdefg5", "123456785", "qwertyui5", "asddfg5",
            "abcdefg6", "123456786", "qwertyui6", "asddfg6",
            "abcdefg7", "123456787", "qwertyui7", "asddfg7",
            "abcdefg8", "123456788", "qwertyui8", "asddfg8",
            "abcdefg9", "123456789", "qwertyui9", "asddfg9",
            "abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
            "abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
            "abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
 and c3 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 
            "abcdefg1", "123456781", "qwertyui1", "asddfg1", 
            "abcdefg2", "123456782", "qwertyui2", "asddfg2", 
            "abcdefg3", "123456783", "qwertyui3", "asddfg3", 
            "abcdefg4", "123456784", "qwertyui4", "asddfg4", 
            "abcdefg5", "123456785", "qwertyui5", "asddfg5",
            "abcdefg6", "123456786", "qwertyui6", "asddfg6",
            "abcdefg7", "123456787", "qwertyui7", "asddfg7",
            "abcdefg8", "123456788", "qwertyui8", "asddfg8",
            "abcdefg9", "123456789", "qwertyui9", "asddfg9",
            "abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
            "abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
            "abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
 and c4 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh", 
            "abcdefg1", "123456781", "qwertyui1", "asddfg1", 
            "abcdefg2", "123456782", "qwertyui2", "asddfg2", 
            "abcdefg3", "123456783", "qwertyui3", "asddfg3", 
            "abcdefg4", "123456784", "qwertyui4", "asddfg4",