# TestBatchPointGetExec
drop table if exists t;
create table t(a int primary key auto_increment not null, b int, c int, unique key idx_abc(a, b, c));
insert into t values(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 5);
--sorted_result
select * from t;
--sorted_result
select a, b, c from t where (a, b, c) in ((1, 1, 1), (1, 1, 1), (1, 1, 1));
--sorted_result
select a, b, c from t where (a, b, c) in ((1, 1, 1), (2, 2, 2), (1, 1, 1));
--sorted_result
select a, b, c from t where (a, b, c) in ((1, 1, 1), (2, 2, 2), (100, 1, 1));
--sorted_result
select a, b, c from t where (a, b, c) in ((1, 1, 1), (2, 2, 2), (100, 1, 1), (4, 4, 5));
--sorted_result
select * from t where a in (1, 2, 4, 1, 2);
--sorted_result
select * from t where a in (1, 2, 4, 1, 2, 100);
--sorted_result
select a from t where a in (1, 2, 4, 1, 2, 100);

# TestBatchPointGetInTxn
drop table if exists t;
create table t (id int primary key auto_increment, name varchar(30));
begin;
insert into t values (4, 'name');
select * from t where id in (4);
select * from t where id in (4) for update;
rollback;
begin pessimistic;
insert into t values (4, 'name');
select * from t where id in (4);
select * from t where id in (4) for update;
rollback;
create table s (a int, b int, c int, primary key (a, b));
insert s values (1, 1, 1), (3, 3, 3), (5, 5, 5);
begin pessimistic;
update s set c = 10 where a = 3;
--sorted_result
select * from s where (a, b) in ((1, 1), (2, 2), (3, 3)) for update;
rollback;

# TestBatchPointGetCache
drop table if exists customers;
create table customers (id int primary key, token varchar(255) unique);
INSERT INTO executor__batch_point_get.customers (id, token) VALUES (28, '07j');
INSERT INTO executor__batch_point_get.customers (id, token) VALUES (29, '03j');
BEGIN;
SELECT id, token FROM executor__batch_point_get.customers WHERE id IN (28);
SELECT id, token FROM executor__batch_point_get.customers WHERE id IN (28, 29);
rollback;

# TestIssue18843
drop table if exists t18843;
create table t18843 ( id bigint(10) primary key, f varchar(191) default null, unique key `idx_f` (`f`));
insert into t18843 values (1, '');
select * from t18843 where f in (null);
insert into t18843 values (2, null);
select * from t18843 where f in (null);
select * from t18843 where f is null;

# TestIssue24562
drop table if exists ttt;
create table ttt(a enum("a","b","c","d"), primary key(a));
insert into ttt values(1);
select * from ttt where ttt.a in ("1","b");
select * from ttt where ttt.a in (1,"b");

# TestBatchPointGetUnsignedHandleWithSort
drop table if exists t2;
create table t2 (id bigint(20) unsigned, primary key(id));
insert into t2 values (8738875760185212610);
insert into t2 values (9814441339970117597);
insert into t2 values (1);
select id from t2 where id in (8738875760185212610, 1, 9814441339970117597) order by id;
select id from t2 where id in (8738875760185212610, 1, 9814441339970117597) order by id desc;

# TestBatchPointGetIssue25167
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 in (1,2,3);
drop table if exists t;

# TestBatchPointGetIssue46779
drop table if exists t1;
CREATE TABLE t1 (id int, c varchar(128), primary key (id)) PARTITION BY HASH (id) PARTITIONS 3;
insert into t1 values (1, "a"), (11, "b"), (21, "c");
explain format='plan_tree' select * from t1 where id in (1, 1, 11);
--sorted_result
select * from t1 where id in (1, 1, 11);
explain format='plan_tree' select * from t1 where id in (1, 11, 11, 21);
--sorted_result
select * from t1 where id in (1, 11, 11, 21);
drop table if exists t2;
CREATE TABLE t2 (id int, c varchar(128), primary key (id)) partition by range (id)(
		partition p0 values less than (10), 
		partition p1 values less than (20), 
		partition p2 values less than (30));
insert into t2 values (1, "a"), (11, "b"), (21, "c");
explain format='plan_tree' select * from t2 where id in (1, 1, 11);
--sorted_result
select * from t2 where id in (1, 1, 11);
explain format='plan_tree' select * from t2 where id in (1, 1, 11);
--sorted_result
select * from t2 where id in (1, 11, 11, 21);

# test BatchPointGet panic issue(#51313) when KeyPartition column is part of multiColumn index.
drop table if exists tkey;
create table tkey (col1 int not null, col2 varchar(32) not null, col3 int not null, unique(col1, col2)) partition by key(col2) partitions 4;
insert into tkey values(1, 'a', 1), (2, 'b', 2);
set session tidb_skip_missing_partition_stats=0;
set session tidb_opt_fix_control = "";
explain format='plan_tree' select col1, col2, col3 from tkey where col1 = 1 and col2 = 'a' or col1 = 3 and col2 = 'c';
select col1, col2, col3 from tkey where col1 = 1 and col2 = 'a' or col1 = 3 and col2 = 'c';
drop table tkey;