90 lines
4.8 KiB
Plaintext
90 lines
4.8 KiB
Plaintext
# TestBigIntPK
|
|
drop table if exists t;
|
|
create table t(a bigint unsigned primary key, b int, c int, index idx(a, b));
|
|
insert into t values(1, 1, 1), (9223372036854775807, 2, 2);
|
|
select * from t use index(idx) order by a;
|
|
|
|
# TestCorColToRanges
|
|
set sql_mode='STRICT_TRANS_TABLES';
|
|
drop table if exists t;
|
|
create table t(a int primary key, b int, c int, index idx(b));
|
|
insert into t values(1, 1, 1), (2, 2 ,2), (3, 3, 3), (4, 4, 4), (5, 5, 5), (6, 6, 6), (7, 7, 7), (8, 8, 8), (9, 9, 9);
|
|
analyze table t;
|
|
select t.c in (select count(*) from t s ignore index(idx), t t1 where s.a = t.a and s.a = t1.a) from t order by 1 desc;
|
|
select t.c in (select count(*) from t s use index(idx), t t1 where s.b = t.a and s.a = t1.a) from t order by 1 desc;
|
|
select t.c in (select count(*) from t s use index(idx), t t1 where s.b = t.a and s.c = t1.a) from t order by 1 desc;
|
|
set sql_mode=default;
|
|
|
|
# TestUniqueKeyNullValueSelect
|
|
drop table if exists t;
|
|
create table t (id int default null, c varchar(20), unique id (id));
|
|
insert t (c) values ('a'), ('b'), ('c');
|
|
--sorted_result
|
|
select * from t where id is null;
|
|
drop table t;
|
|
create table t (id int default null, b int default 1, c varchar(20), unique id_c(id, b));
|
|
insert t (c) values ('a'), ('b'), ('c');
|
|
--sorted_result
|
|
select * from t where id is null and b = 1;
|
|
drop table t;
|
|
create table t (id int default null, c varchar(20), key id (id));
|
|
insert t (c) values ('a'), ('b'), ('c');
|
|
--sorted_result
|
|
select * from t where id is null;
|
|
|
|
# TestIssue10178
|
|
drop table if exists t;
|
|
create table t(a bigint unsigned primary key);
|
|
insert into t values(9223372036854775807), (18446744073709551615);
|
|
select max(a) from t;
|
|
select * from t where a > 9223372036854775807;
|
|
select * from t where a < 9223372036854775808;
|
|
|
|
# TestPartitionTableIndexLookUpReader
|
|
drop table if exists t;
|
|
create table t (a int, b int, key(a))
|
|
partition by range (a) (
|
|
partition p1 values less than (10),
|
|
partition p2 values less than (20),
|
|
partition p3 values less than (30),
|
|
partition p4 values less than (40));
|
|
insert into t values (1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (31, 31), (32, 32);
|
|
set tidb_partition_prune_mode='dynamic';
|
|
analyze table t;
|
|
--sorted_result
|
|
select * from t where a>=1 and a<=1;
|
|
--sorted_result
|
|
select * from t where a>=1 and a<=2;
|
|
--sorted_result
|
|
select * from t where a>=1 and a<12;
|
|
--sorted_result
|
|
select * from t where a>=1 and a<15;
|
|
--sorted_result
|
|
select * from t where a>15 and a<32;
|
|
--sorted_result
|
|
select * from t where a>30;
|
|
select * from t where a>=1 and a<15 order by a;
|
|
select * from t where a>=1 and a<15 order by a limit 1;
|
|
select * from t where a>=1 and a<15 order by a limit 3;
|
|
select * from t where a between 1 and 15 order by a limit 3;
|
|
select * from t where a between 1 and 15 order by a limit 3 offset 1;
|
|
set tidb_partition_prune_mode=default;
|
|
|
|
# TestIndexLookUpGetResultChunk
|
|
drop table if exists tbl;
|
|
create table tbl(a int, b int, c int, key idx_a(a));
|
|
insert into tbl values (0,0,0), (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5), (6,6,6), (7,7,7), (8,8,8), (9,9,9), (10,10,10), (11,11,11), (12,12,12), (13,13,13), (14,14,14), (15,15,15), (16,16,16), (17,17,17), (18,18,18), (19,19,19), (20,20,20), (21,21,21), (22,22,22), (23,23,23), (24,24,24), (25,25,25), (26,26,26), (27,27,27), (28,28,28), (29,29,29), (30,30,30), (31,31,31), (32,32,32), (33,33,33), (34,34,34), (35,35,35), (36,36,36), (37,37,37), (38,38,38), (39,39,39), (40,40,40), (41,41,41), (42,42,42), (43,43,43), (44,44,44), (45,45,45), (46,46,46), (47,47,47), (48,48,48), (49,49,49), (50,50,50), (51,51,51), (52,52,52), (53,53,53), (54,54,54), (55,55,55), (56,56,56), (57,57,57), (58,58,58), (59,59,59), (60,60,60), (61,61,61), (62,62,62), (63,63,63), (64,64,64), (65,65,65), (66,66,66), (67,67,67), (68,68,68), (69,69,69), (70,70,70), (71,71,71), (72,72,72), (73,73,73), (74,74,74), (75,75,75), (76,76,76), (77,77,77), (78,78,78), (79,79,79), (80,80,80), (81,81,81), (82,82,82), (83,83,83), (84,84,84), (85,85,85), (86,86,86), (87,87,87), (88,88,88), (89,89,89), (90,90,90), (91,91,91), (92,92,92), (93,93,93), (94,94,94), (95,95,95), (96,96,96), (97,97,97), (98,98,98), (99,99,99), (100,100,100);
|
|
select * from tbl use index(idx_a) where a > 99 order by a asc limit 1;
|
|
select * from tbl use index(idx_a) where a > 10 order by a asc limit 4,1;
|
|
|
|
# TestIndexLookUpWithSelectForUpdateOnPartitionTable
|
|
drop table if exists t;
|
|
create table t(a int, b int, index k(b)) PARTITION BY HASH(a) partitions 4;
|
|
insert into t(a, b) values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
|
|
explain format='plan_tree' select b from t use index(k) where b > 2 order by b limit 1 for update;
|
|
select b from t use index(k) where b > 2 order by b limit 1 for update;
|
|
analyze table t;
|
|
explain format='plan_tree' select b from t use index(k) where b > 2 order by b limit 1 for update;
|
|
select b from t use index(k) where b > 2 order by b limit 1 for update;
|
|
|