1
2create table t1(a int);
3show create table t1;
4insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
5create table t2(a int);
6insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C;
7
8
9create table t3 (
10  a char(8) not null, b char(8) not null, filler char(200),
11  key(a)
12);
13insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A;
14insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'),
15                      'filler-1' from t2 A;
16insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'),
17                      'filler-2' from t2 A;
18
19# Test empty result set
20select a,filler from t3 where a >= 'c-9011=w';
21
22# Ok, t3.ref_length=6, limit is 64 => 10 elements fit into the buffer
23# Test the cases when buffer gets exhausted at different points in source
24# intervals:
25
26# 1. Split is in the middle of the range
27select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w';
28
29# 2. Split is at range edge
30select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
31                              (a>='c-1014=w' and a <= 'c-1015=w');
32
33# 3. Split is at range edge, with some rows between ranges.
34insert into t3 values ('c-1013=z', 'c-1013=z', 'err');
35insert into t3 values ('a-1014=w', 'a-1014=w', 'err');
36
37select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or
38                              (a>='c-1014=w' and a <= 'c-1015=w');
39delete from t3 where b in ('c-1013=z', 'a-1014=w');
40
41# 4. Split is within the equality range.
42select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
43                              a='c-1014=w' or a='c-1015=w';
44
45# 5. Split is at the edge of equality range.
46insert into t3 values ('c-1013=w', 'del-me', 'inserted');
47select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or
48                              a='c-1014=w' or a='c-1015=w';
49delete from t3 where b='del-me';
50
51# PK tests are not included here.
52
53alter table t3 add primary key(b);
54
55##  PK scan tests
56# 6. Split is between 'unique' PK ranges
57select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or
58                              b IN ('c-1019=w', 'c-1020=w', 'c-1021=w',
59                                    'c-1022=w', 'c-1023=w', 'c-1024=w');
60
61# 7. Between non-uniq and uniq range
62select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or
63                              b IN ('c-1021=w', 'c-1022=w', 'c-1023=w');
64
65# 8. Between uniq and non-uniq range
66select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or
67                              b IN ('c-1019=w', 'c-1020=w') or
68                              (b>='c-1021=w' and b<= 'c-1023=w');
69## End of PK scan tests
70
71#
72# Now try different keypart types and special values
73#
74--disable_warnings
75drop table if exists t4;
76--enable_warnings
77create table t4 (a varchar(10), b int, c char(10), filler char(200),
78                 key idx1 (a, b, c));
79
80# insert buffer_size * 1.5 all-NULL tuples
81insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15;
82
83insert into t4 (a,b,c,filler)
84  select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15;
85insert into t4 (a,b,c,filler)
86  select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15;
87insert into t4 (a,b,c,filler)
88  select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15;
89insert into t4 (a,b,c,filler)
90  select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500;
91
92explain
93  select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
94                                                      or c='no-such-row2');
95select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
96                                                    or c='no-such-row2');
97
98explain
99  select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
100select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
101
102select * from t4 ignore index(idx1) where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2');
103drop table t1, t2, t3, t4;
104
105#
106# Check how ICP works with NULLs and partially-covered indexes
107#
108create table t1 (a int, b int not null,unique key (a,b),index(b));
109insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6);
110create table t2 like t1;
111insert into t2 select * from t1;
112alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10));
113
114select * from t1 where a is null;
115select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
116
117select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
118drop table t1, t2;
119