355 lines
15 KiB
Plaintext
355 lines
15 KiB
Plaintext
# TestPointGet
|
|
drop table if exists point, tab3;
|
|
create table point (id int primary key, c int, d varchar(10), unique c_d (c, d));
|
|
insert point values (1, 1, 'a');
|
|
insert point values (2, 2, 'b');
|
|
select * from point where id = 1 and c = 0;
|
|
select * from point where id < 0 and c = 1 and d = 'b';
|
|
select id as ident from point where id = 1;
|
|
CREATE TABLE tab3(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT);
|
|
CREATE UNIQUE INDEX idx_tab3_0 ON tab3 (col4);
|
|
INSERT INTO tab3 VALUES(0,854,111.96,'mguub',711,966.36,'snwlo');
|
|
SELECT ALL * FROM tab3 WHERE col4 = 85;
|
|
drop table if exists t;
|
|
create table t(a bigint primary key, b bigint, c bigint);
|
|
insert into t values(1, NULL, NULL), (2, NULL, 2), (3, 3, NULL), (4, 4, 4), (5, 6, 7);
|
|
select * from t where a = 1;
|
|
select * from t where a = 2;
|
|
select * from t where a = 3;
|
|
select * from t where a = 4;
|
|
select a, a, b, a, b, c, b, c, c from t where a = 5;
|
|
select b, b from t where a = 1;
|
|
|
|
# TestPointGetOverflow
|
|
drop table if exists t0, PK_S_MULTI_31_1;
|
|
CREATE TABLE t0(c1 BOOL UNIQUE);
|
|
INSERT INTO t0(c1) VALUES (-128);
|
|
INSERT INTO t0(c1) VALUES (127);
|
|
SELECT t0.c1 FROM t0 WHERE t0.c1=-129;
|
|
SELECT t0.c1 FROM t0 WHERE t0.c1=-128;
|
|
SELECT t0.c1 FROM t0 WHERE t0.c1=128;
|
|
SELECT t0.c1 FROM t0 WHERE t0.c1=127;
|
|
CREATE TABLE `PK_S_MULTI_31_1` (`COL1` tinyint(11) NOT NULL, `COL2` tinyint(11) NOT NULL, `COL3` tinyint(11) DEFAULT NULL, PRIMARY KEY (`COL1`,`COL2`) CLUSTERED);
|
|
select * from PK_S_MULTI_31_1 where col2 = -129 and col1 = 1;
|
|
insert into PK_S_MULTI_31_1 select 1, 1, 1;
|
|
select * from PK_S_MULTI_31_1 where (col1, col2) in ((1, -129),(1, 1));
|
|
|
|
# TestPointGetDataTooLong
|
|
# Close issue #22839
|
|
drop table if exists PK_1389;
|
|
CREATE TABLE `PK_1389` ( `COL1` bit(1) NOT NULL, `COL2` varchar(20) DEFAULT NULL, `COL3` datetime DEFAULT NULL, `COL4` bigint(20) DEFAULT NULL, `COL5` float DEFAULT NULL, PRIMARY KEY (`COL1`));
|
|
insert into PK_1389 values(0, "皟钹糁泅埞礰喾皑杏灚暋蛨歜檈瓗跾咸滐梀揉", "7701-12-27 23:58:43", 4806951672419474695, -1.55652e38);
|
|
select count(1) from PK_1389 where col1 = 0x30;
|
|
select count(1) from PK_1389 where col1 in ( 0x30);
|
|
drop table if exists PK_1389;
|
|
|
|
# TestIssue25489
|
|
set @@tidb_partition_prune_mode = 'dynamic';
|
|
drop table if exists UK_RP16939;
|
|
CREATE TABLE UK_RP16939 (
|
|
COL1 tinyint(16) DEFAULT '108' COMMENT 'NUMERIC UNIQUE INDEX',
|
|
COL2 varchar(20) DEFAULT NULL,
|
|
COL4 datetime DEFAULT NULL,
|
|
COL3 bigint(20) DEFAULT NULL,
|
|
COL5 float DEFAULT NULL,
|
|
UNIQUE KEY UK_COL1 (COL1)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
|
|
PARTITION BY RANGE ( COL1+13 ) (
|
|
PARTITION P0 VALUES LESS THAN (-44),
|
|
PARTITION P1 VALUES LESS THAN (-23),
|
|
PARTITION P2 VALUES LESS THAN (-22),
|
|
PARTITION P3 VALUES LESS THAN (63),
|
|
PARTITION P4 VALUES LESS THAN (75),
|
|
PARTITION P5 VALUES LESS THAN (90),
|
|
PARTITION PMX VALUES LESS THAN (MAXVALUE)
|
|
) ;
|
|
explain format='plan_tree' select col1, col2 from UK_RP16939 where col1 in (116, 48, -30);
|
|
select col1, col2 from UK_RP16939 where col1 in (116, 48, -30);
|
|
drop table if exists UK_RP16939;
|
|
CREATE TABLE UK_RP16939 (
|
|
COL1 tinyint(16) DEFAULT '108' COMMENT 'NUMERIC UNIQUE INDEX',
|
|
COL2 varchar(20) DEFAULT NULL,
|
|
COL4 datetime DEFAULT NULL,
|
|
COL3 bigint(20) DEFAULT NULL,
|
|
COL5 float DEFAULT NULL,
|
|
UNIQUE KEY UK_COL1 (COL1)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
|
|
PARTITION BY LIST ( COL1+13 ) (
|
|
PARTITION P0 VALUES IN (-44, -23),
|
|
PARTITION P1 VALUES IN (-22, 63),
|
|
PARTITION P2 VALUES IN (75, 90)
|
|
) ;
|
|
explain format='plan_tree' select col1, col2 from UK_RP16939 where col1 in (116, 48, -30);
|
|
select col1, col2 from UK_RP16939 where col1 in (116, 48, -30);
|
|
drop table if exists UK_RP16939;
|
|
set @@tidb_partition_prune_mode = DEFAULT;
|
|
|
|
# TestDistinctPlan
|
|
# issue #25320
|
|
drop table if exists test_distinct;
|
|
CREATE TABLE test_distinct (
|
|
id bigint(18) NOT NULL COMMENT '主键',
|
|
b bigint(18) NOT NULL COMMENT '用户ID',
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
insert into test_distinct values (123456789101112131,223456789101112131),(123456789101112132,223456789101112131);
|
|
select distinct b from test_distinct where id in (123456789101112131,123456789101112132);
|
|
|
|
# TestPointGetBinaryPK
|
|
drop table if exists t;
|
|
create table t(a binary(2) primary key, b binary(2));
|
|
insert into t values("a", "b");
|
|
set @@sql_mode="";
|
|
explain format='plan_tree' select * from t where a = "a";
|
|
select * from t where a = "a";
|
|
explain format='plan_tree' select * from t where a = "a ";
|
|
select * from t where a = "a ";
|
|
explain format='plan_tree' select * from t where a = "a ";
|
|
select * from t where a = "a ";
|
|
explain format='plan_tree' select hex(a), hex(b) from t where a = "a\0";
|
|
select hex(a), hex(b) from t where a = "a\0";
|
|
insert into t values("a ", "b ");
|
|
explain format='plan_tree' select * from t where a = "a";
|
|
select * from t where a = "a";
|
|
explain format='plan_tree' select * from t where a = "a ";
|
|
select * from t where a = "a ";
|
|
explain format='plan_tree' select * from t where a = "a ";
|
|
select * from t where a = "a ";
|
|
explain format='plan_tree' select * from t where a = "a";
|
|
select * from t where a = "a";
|
|
explain format='plan_tree' select * from t where a = "a ";
|
|
select * from t where a = "a ";
|
|
explain format='plan_tree' select * from t where a = "a ";
|
|
select * from t where a = "a ";
|
|
set @@sql_mode=DEFAULT;
|
|
|
|
# TestPointGetAliasTableBinaryPK
|
|
drop table if exists t;
|
|
create table t(a binary(2) primary key, b binary(2));
|
|
insert into t values("a", "b");
|
|
set @@sql_mode="";
|
|
explain format='plan_tree' select * from t tmp where a = "a";
|
|
select * from t tmp where a = "a";
|
|
explain format='plan_tree' select * from t tmp where a = "a ";
|
|
select * from t tmp where a = "a ";
|
|
explain format='plan_tree' select * from t tmp where a = "a ";
|
|
select * from t tmp where a = "a ";
|
|
explain format='plan_tree' select hex(a), hex(b) from t tmp where a = "a\0";
|
|
select hex(a), hex(b) from t tmp where a = "a\0";
|
|
insert into t values("a ", "b ");
|
|
explain format='plan_tree' select * from t tmp where a = "a";
|
|
select * from t tmp where a = "a";
|
|
explain format='plan_tree' select * from t tmp where a = "a ";
|
|
select * from t tmp where a = "a ";
|
|
explain format='plan_tree' select * from t tmp where a = "a ";
|
|
select * from t tmp where a = "a ";
|
|
explain format='plan_tree' select * from t tmp where a = "a";
|
|
select * from t tmp where a = "a";
|
|
explain format='plan_tree' select * from t tmp where a = "a ";
|
|
select * from t tmp where a = "a ";
|
|
explain format='plan_tree' select * from t tmp where a = "a ";
|
|
select * from t tmp where a = "a ";
|
|
set @@sql_mode=DEFAULT;
|
|
|
|
# TestIndexLookupBinary
|
|
drop table if exists t;
|
|
create table t(a binary(2), b binary(2), index idx_1(a));
|
|
insert into t values("a", "b");
|
|
set @@sql_mode="";
|
|
explain format='plan_tree' select * from t where a = "a";
|
|
select * from t where a = "a";
|
|
explain format='plan_tree' select * from t where a = "a ";
|
|
select * from t where a = "a ";
|
|
explain format='plan_tree' select * from t where a = "a ";
|
|
select * from t where a = "a ";
|
|
explain format='plan_tree' select hex(a), hex(b) from t where a = "a\0";
|
|
select hex(a), hex(b) from t where a = "a\0";
|
|
set @@sql_mode="";
|
|
explain format='plan_tree' select * from t tmp where a = "a";
|
|
select * from t tmp where a = "a";
|
|
explain format='plan_tree' select * from t tmp where a = "a ";
|
|
select * from t tmp where a = "a ";
|
|
explain format='plan_tree' select * from t tmp where a = "a ";
|
|
select * from t tmp where a = "a ";
|
|
explain format='plan_tree' select hex(a), hex(b) from t tmp where a = "a\0";
|
|
select hex(a), hex(b) from t tmp where a = "a\0";
|
|
insert into t values("a ", "b ");
|
|
explain format='plan_tree' select * from t where a = "a";
|
|
select * from t where a = "a";
|
|
explain format='plan_tree' select * from t where a = "a ";
|
|
select * from t where a = "a ";
|
|
explain format='plan_tree' select * from t where a = "a ";
|
|
select * from t where a = "a ";
|
|
explain format='plan_tree' select * from t where a = "a";
|
|
select * from t where a = "a";
|
|
explain format='plan_tree' select * from t where a = "a ";
|
|
select * from t where a = "a ";
|
|
explain format='plan_tree' select * from t where a = "a ";
|
|
select * from t where a = "a ";
|
|
set sql_mode=default;
|
|
|
|
# TestOverflowOrTruncated
|
|
drop table if exists t6;
|
|
create table t6 (id bigint, a bigint, primary key(id), unique key(a));
|
|
insert into t6 values(9223372036854775807, 9223372036854775807);
|
|
insert into t6 values(1, 1);
|
|
select * from t6 where a = 9223372036854775808;
|
|
select * from t6 where a = '1.123';
|
|
select * from t6 where id = 9223372036854775808;
|
|
select * from t6 where id = '1.123';
|
|
|
|
# TestIssue10448
|
|
drop table if exists t;
|
|
create table t(pk int1 primary key);
|
|
insert into t values(125);
|
|
explain format='plan_tree' select * from t where pk = 9223372036854775807;
|
|
explain format='plan_tree' select * from t where pk = 18446744073709551616;
|
|
explain format='plan_tree' select * from t where pk = 9223372036854775808;
|
|
explain format='plan_tree' select * from t where pk = 18446744073709551615;
|
|
explain format='plan_tree' select * from t where pk = 128;
|
|
drop table if exists t;
|
|
create table t(pk int8 primary key);
|
|
insert into t values(9223372036854775807);
|
|
select * from t where pk = 9223372036854775807;
|
|
explain format='plan_tree' select * from t where pk = 9223372036854775807;
|
|
explain format='plan_tree' select * from t where pk = 18446744073709551616;
|
|
explain format='plan_tree' select * from t where pk = 9223372036854775808;
|
|
explain format='plan_tree' select * from t where pk = 18446744073709551615;
|
|
drop table if exists t;
|
|
create table t(pk int1 unsigned primary key);
|
|
insert into t values(255);
|
|
select * from t where pk = 255;
|
|
explain format='plan_tree' select * from t where pk = 256;
|
|
explain format='plan_tree' select * from t where pk = 9223372036854775807;
|
|
explain format='plan_tree' select * from t where pk = 18446744073709551616;
|
|
explain format='plan_tree' select * from t where pk = 9223372036854775808;
|
|
explain format='plan_tree' select * from t where pk = 18446744073709551615;
|
|
drop table if exists t;
|
|
create table t(pk int8 unsigned primary key);
|
|
insert into t value(18446744073709551615);
|
|
explain format='plan_tree' select * from t where pk = 18446744073709551615;
|
|
select * from t where pk = 18446744073709551615;
|
|
explain format='plan_tree' select * from t where pk = 9223372036854775807;
|
|
explain format='plan_tree' select * from t where pk = 18446744073709551616;
|
|
explain format='plan_tree' select * from t where pk = 9223372036854775808;
|
|
|
|
# TestIssue10677
|
|
drop table if exists t;
|
|
create table t(pk int1 primary key);
|
|
insert into t values(1);
|
|
explain format='plan_tree' select * from t where pk = 1.1;
|
|
select * from t where pk = 1.1;
|
|
explain format='plan_tree' select * from t where pk = '1.1';
|
|
select * from t where pk = '1.1';
|
|
explain format='plan_tree' select * from t where pk = 1;
|
|
select * from t where pk = 1;
|
|
explain format='plan_tree' select * from t where pk = '1';
|
|
select * from t where pk = '1';
|
|
explain format='plan_tree' select * from t where pk = '1.0';
|
|
select * from t where pk = '1.0';
|
|
|
|
# TestPointGetByRowID
|
|
drop table if exists t;
|
|
create table t (a varchar(20), b int);
|
|
insert into t values("aaa", 12);
|
|
explain format = 'plan_tree' select * from t where t._tidb_rowid = 1;
|
|
select * from t where t._tidb_rowid = 1;
|
|
|
|
# TestPointGetBinaryLiteralString
|
|
drop table if exists t;
|
|
create table t (a varchar(255) charset gbk primary key /*T![clustered_index] clustered */, b int);
|
|
insert into t values ('你好', 1);
|
|
explain select * from t where a = 0xC4E3BAC3;
|
|
select * from t where a = 0xC4E3BAC3;
|
|
|
|
# TestClusterIndexPointGet
|
|
set tidb_enable_clustered_index = ON;
|
|
drop table if exists pgt;
|
|
create table pgt (a varchar(64), b varchar(64), uk int, v int, primary key(a, b), unique key uuk(uk));
|
|
insert pgt values ('a', 'a1', 1, 11), ('b', 'b1', 2, 22), ('c', 'c1', 3, 33);
|
|
select * from pgt where (a, b) in (('a', 'a1'), ('c', 'c1'));
|
|
select * from pgt where a = 'b' and b = 'b1';
|
|
select * from pgt where uk = 1;
|
|
select * from pgt where uk in (1, 2, 3);
|
|
admin check table pgt;
|
|
drop table if exists snp;
|
|
create table snp(id1 int, id2 int, v int, primary key(id1, id2));
|
|
insert snp values (1, 1, 1), (2, 2, 2), (2, 3, 3);
|
|
explain format = 'plan_tree' select * from snp where id1 = 1;
|
|
explain format = 'plan_tree' select * from snp where id1 in (1, 100);
|
|
select * from snp where id1 = 2;
|
|
set tidb_enable_clustered_index = DEFAULT;
|
|
|
|
# TestClusterIndexCBOPointGet
|
|
set tidb_enable_clustered_index = ON;
|
|
drop table if exists t1, t2;
|
|
create table t1 (a int, b decimal(10,0), c int, primary key(a,b));
|
|
create table t2 (a varchar(20), b int, primary key(a), unique key(b));
|
|
insert into t1 values(1,1,1),(2,2,2),(3,3,3);
|
|
insert into t2 values('111',1),('222',2),('333',3);
|
|
analyze table t1 all columns;
|
|
analyze table t2 all columns;
|
|
explain format = 'plan_tree' select * from t1 where a = 1 and b = 1 and c = 1;
|
|
--sorted_result
|
|
select * from t1 where a = 1 and b = 1 and c = 1;
|
|
explain format = 'plan_tree' select * from t2 where t2.a = '111' and t2.b = 1;
|
|
--sorted_result
|
|
select * from t2 where t2.a = '111' and t2.b = 1;
|
|
explain format = 'plan_tree' select * from t1 join t2 on t1.a = t2.b where t1.a = 1;
|
|
--sorted_result
|
|
select * from t1 join t2 on t1.a = t2.b where t1.a = 1;
|
|
explain format = 'plan_tree' select * from t1 where (a,b) in ((1,1),(2,2)) and c = 2;
|
|
--sorted_result
|
|
select * from t1 where (a,b) in ((1,1),(2,2)) and c = 2;
|
|
explain format = 'plan_tree' select * from t2 where a in ('111','222') and b = 2;
|
|
--sorted_result
|
|
select * from t2 where a in ('111','222') and b = 2;
|
|
explain format = 'plan_tree' select * from t2 where a in ('111','222') union all select a,c from t1 where (a,b) in ((1,1),(2,2));
|
|
--sorted_result
|
|
select * from t2 where a in ('111','222') union all select a,c from t1 where (a,b) in ((1,1),(2,2));
|
|
set tidb_enable_clustered_index = DEFAULT;
|
|
|
|
# TestPointGetIssue25167
|
|
drop table if exists t;
|
|
create table t (a int primary key);
|
|
set @a=(select current_timestamp(3));
|
|
select sleep(0.05);
|
|
insert into t values (1);
|
|
select * from t as of timestamp @a where a = 1;
|
|
drop table if exists t;
|
|
|
|
# TestPointGetIssue40194
|
|
drop table if exists t1;
|
|
create table t1(id int primary key, v int);
|
|
insert into t1 values(1, 10);
|
|
prepare s from 'select * from t1 where id=1';
|
|
set @@tidb_enable_plan_replayer_capture=1;
|
|
execute s;
|
|
execute s;
|
|
connect (conn1, localhost, root, , executor__point_get);
|
|
connection conn1;
|
|
update t1 set v=v+1;
|
|
connection default;
|
|
disconnect conn1;
|
|
execute s;
|
|
set @@tidb_enable_plan_replayer_capture=default;
|
|
|
|
# TestPointGetWriteLock
|
|
drop table if exists point;
|
|
create table point (id int primary key, c int, d varchar(10), unique c_d (c, d));
|
|
insert point values (1, 1, 'a');
|
|
insert point values (2, 2, 'b');
|
|
lock tables point write;
|
|
select * from point where id = 1;
|
|
--replace_regex /.*num_rpc.*/.*num_rpc.*/
|
|
explain analyze select * from point where id = 1;
|
|
unlock tables;
|
|
|
|
update point set c = 3 where id = 1;
|
|
lock tables point write;
|
|
select * from point where id = 1;
|
|
--replace_regex /.*num_rpc.*/.*num_rpc.*/
|
|
explain analyze select * from point where id = 1;
|
|
unlock tables;
|
|
|