# 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;