1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
|
set @old_handler_icp_attempts=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_attempts');
set @old_handler_icp_match=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_match');
CREATE TABLE t1 (a int PRIMARY KEY, b BLOB, c varchar(16) DEFAULT 'Filler...',
INDEX (b(4), a)) ENGINE = MyISAM PARTITION BY HASH (a) PARTITIONS 3;
INSERT INTO t1 (a, b) VALUES (1, 0xdeadbeef), (2, "text filler"),
(3, 'filler...'), (4, " more filler "), (5, "test text"), (6, "testing...");
SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2);
a HEX(b)
5 746573742074657874
1 DEADBEEF
set @handler_icp_attempts=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_attempts');
set @handler_icp_match=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_match');
select @old_handler_icp_attempts = @handler_icp_attempts;
@old_handler_icp_attempts = @handler_icp_attempts
1
select @old_handler_icp_match = @handler_icp_match;
@old_handler_icp_match = @handler_icp_match
1
CREATE TABLE t2 (a int PRIMARY KEY, b BLOB, c varchar(16) DEFAULT 'Filler...',
INDEX (b(4), a)) ENGINE = innodb PARTITION BY HASH (a) PARTITIONS 3;
INSERT INTO t2 (a, b) VALUES (1, 0xdeadbeef), (2, "text filler"),
(3, 'filler...'), (4, " more filler "), (5, "test text"), (6, "testing...");
set @@optimizer_switch='index_condition_pushdown=off';
SELECT a, HEX(b) FROM t2 WHERE b >= 'te' and (a % 2);
a HEX(b)
5 746573742074657874
1 DEADBEEF
set @handler_icp_attempts=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_attempts');
set @handler_icp_match=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_match');
select @old_handler_icp_attempts = @handler_icp_attempts;
@old_handler_icp_attempts = @handler_icp_attempts
1
select @old_handler_icp_match = @handler_icp_match;
@old_handler_icp_match = @handler_icp_match
1
set @@optimizer_switch='index_condition_pushdown=on';
SELECT a, HEX(b) FROM t2 WHERE b >= 'te' and (a % 2);
a HEX(b)
5 746573742074657874
1 DEADBEEF
set @handler_icp_attempts=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_attempts');
set @handler_icp_match=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_match');
select @old_handler_icp_attempts < @handler_icp_attempts;
@old_handler_icp_attempts < @handler_icp_attempts
1
select @old_handler_icp_match < @handler_icp_match;
@old_handler_icp_match < @handler_icp_match
1
CREATE TABLE t3 (
pk INT PRIMARY KEY,
a INT,
b INT,
c INT,
filler varchar(100),
key(a,b,c)
) engine=innodb partition by hash(pk) partitions 4;
INSERT INTO t3 SELECT seq, seq, seq, seq, 'hello' FROM seq_1_to_10000;
set @@optimizer_switch='index_condition_pushdown=off';
set @old_handler_icp_attempts = @handler_icp_attempts;
set @old_handler_icp_match = @handler_icp_match;
SELECT * from t3 where a < 10 AND (b+1>3);
pk a b c filler
3 3 3 3 hello
4 4 4 4 hello
5 5 5 5 hello
6 6 6 6 hello
7 7 7 7 hello
8 8 8 8 hello
9 9 9 9 hello
set @handler_icp_attempts=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_attempts');
set @handler_icp_match=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_match');
select @old_handler_icp_attempts = @handler_icp_attempts;
@old_handler_icp_attempts = @handler_icp_attempts
1
select @old_handler_icp_match = @handler_icp_match;
@old_handler_icp_match = @handler_icp_match
1
set @@optimizer_switch='index_condition_pushdown=on';
SELECT * from t3 where a < 10 AND (b+1>3);
pk a b c filler
3 3 3 3 hello
4 4 4 4 hello
5 5 5 5 hello
6 6 6 6 hello
7 7 7 7 hello
8 8 8 8 hello
9 9 9 9 hello
set @handler_icp_attempts=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_attempts');
set @handler_icp_match=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_match');
select @old_handler_icp_attempts < @handler_icp_attempts;
@old_handler_icp_attempts < @handler_icp_attempts
1
select @old_handler_icp_match < @handler_icp_match;
@old_handler_icp_match < @handler_icp_match
1
CREATE TABLE t4 (
pk INT PRIMARY KEY,
a INT,
b INT,
c INT,
filler varchar(100),
key(a,b,c)
) engine=myisam partition by hash(pk) partitions 4;
INSERT INTO t4 SELECT seq, seq, seq, seq, 'hello' FROM seq_1_to_10000;
set @@optimizer_switch='index_condition_pushdown=off';
set @old_handler_icp_attempts = @handler_icp_attempts;
set @old_handler_icp_match = @handler_icp_match;
SELECT * from t4 where a < 10 AND (b+1>3);
pk a b c filler
3 3 3 3 hello
4 4 4 4 hello
5 5 5 5 hello
6 6 6 6 hello
7 7 7 7 hello
8 8 8 8 hello
9 9 9 9 hello
set @handler_icp_attempts=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_attempts');
set @handler_icp_match=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_match');
select @old_handler_icp_attempts = @handler_icp_attempts;
@old_handler_icp_attempts = @handler_icp_attempts
1
select @old_handler_icp_match = @handler_icp_match;
@old_handler_icp_match = @handler_icp_match
1
set @@optimizer_switch='index_condition_pushdown=on';
SELECT * from t4 where a < 10 AND (b+1>3);
pk a b c filler
3 3 3 3 hello
4 4 4 4 hello
5 5 5 5 hello
6 6 6 6 hello
7 7 7 7 hello
8 8 8 8 hello
9 9 9 9 hello
set @handler_icp_attempts=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_attempts');
set @handler_icp_match=(select cast(variable_value as int) from information_schema.session_status where variable_name='handler_icp_match');
select @old_handler_icp_attempts < @handler_icp_attempts;
@old_handler_icp_attempts < @handler_icp_attempts
1
select @old_handler_icp_match < @handler_icp_match;
@old_handler_icp_match < @handler_icp_match
1
create table t5(pk int primary key, kp1 int, kp2 int, filler int, key(kp1, kp2)) partition by hash(pk) partitions 2;
insert into t5 select seq, seq/40, seq, seq from seq_1_to_4000;
select * from t5 where t5.kp1=10 and mod(t5.kp2,2)=1 and t5.kp2+1<401;
pk kp1 kp2 filler
381 10 381 381
383 10 383 383
385 10 385 385
387 10 387 387
389 10 389 389
391 10 391 391
393 10 393 393
395 10 395 395
397 10 397 397
399 10 399 399
drop table t1, t2, t3, t4, t5;
#
# End of 11.4 tests
#
|
