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
|
#
# Range optimizer (and related) tests that need InnoDB.
#
drop table if exists t0, t1, t2;
#
# MDEV-6735: Range checked for each record used with key
#
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int);
insert into t1 select A.a + B.a* 10 + C.a * 100 + D.a * 1000
from t0 A, t0 B, t0 C, t0 D;
create table t2 (
a int,
b int,
filler1 char(100),
filler2 char(100),
filler3 char(100),
filler4 char(100),
key(a),
key(b)
) engine=innodb;
insert into t2
select
a,a,
repeat('0123456789', 10),
repeat('0123456789', 10),
repeat('0123456789', 10),
repeat('0123456789', 10)
from t1;
analyze table t2;
Table Op Msg_type Msg_text
test.t2 analyze status Engine-independent statistics collected
test.t2 analyze status OK
# The following must not use "Range checked for each record":
explain select * from t0 left join t2 on t2.a <t0.a and t2.b between 50 and 250;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 ALL NULL NULL NULL NULL 10
1 SIMPLE t2 range a,b b 5 NULL 201 Using where; Using join buffer (flat, BNL join)
drop table t0,t1,t2;
#
# MDEV-10466: constructing an invalid SEL_ARG
#
create table t1 (
pk int, a int, b int,
primary key (pk), index idx1(b), index idx2(b)
) engine=innodb;
Warnings:
Note 1831 Duplicate index `idx2`. This is deprecated and will be disallowed in a future release
insert into t1 values (1,6,0),(2,1,0),(3,5,2),(4,8,0);
create table t2 (c int) engine=innodb;
insert into t2 values (1),(2);
create table t3 (d int) engine=innodb;
insert into t3 values (3),(-1),(4);
set @save_optimizer_switch=@@optimizer_switch;
set optimizer_switch='extended_keys=on';
explain
select pk, a, b from t1,t2,t3 where b >= d and pk < c and b = '0';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
1 SIMPLE t1 ref PRIMARY,idx1,idx2 idx1 5 const 3 Using index condition
1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
select pk, a, b from t1,t2,t3 where b >= d and pk < c and b = '0';
pk a b
1 6 0
set optimizer_switch=@save_optimizer_switch;
drop table t1,t2,t3;
CREATE TABLE t1 (
pk INT PRIMARY KEY, f1 INT, f2 CHAR(1), f3 CHAR(1),
KEY(f1), KEY(f2)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES
(1,4,'v',NULL),(2,6,'v',NULL),(3,7,'c',NULL),(4,1,'e',NULL),(5,0,'x',NULL),
(6,7,'i',NULL),(7,7,'e',NULL),(8,1,'p',NULL),(9,7,'s',NULL),(10,1,'j',NULL),
(11,5,'z',NULL),(12,2,'c',NULL),(13,0,'a',NULL),(14,1,'q',NULL),(15,8,'y',NULL),
(16,1,'m',NULL),(17,1,'r',NULL),(18,9,'v',NULL),(19,1,'n',NULL);
CREATE TABLE t2 (f4 INT, f5 CHAR(1)) ENGINE=InnoDB;
INSERT INTO t2 VALUES (4,'q'),(NULL,'j');
SELECT * FROM t1 AS t1_1, t1 AS t1_2, t2
WHERE f5 = t1_2.f2 AND ( t1_1.f1 = 103 AND t1_1.f2 = 'o' OR t1_1.pk < f4 );
pk f1 f2 f3 pk f1 f2 f3 f4 f5
1 4 v NULL 14 1 q NULL 4 q
2 6 v NULL 14 1 q NULL 4 q
3 7 c NULL 14 1 q NULL 4 q
drop table t1,t2;
#
# MDEV-14440: Server crash in in handler::ha_external_lock or Assertion `inited==RND'
# failed in handler::ha_rnd_end upon SELECT from partitioned table
#
set @optimizer_switch_save= @@optimizer_switch;
set optimizer_switch='index_merge_sort_intersection=off';
create table t0 (a int)engine=innodb;
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
a int, b int, c int,
key(a),key(b),key(c)
)engine=innodb;
insert into t1
select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a
from t0 A, t0 B, t0 C, t0 D where D.a<5;
SET @saved_dbug = @@GLOBAL.debug_dbug;
set @@global.debug_dbug="+d,ha_index_init_fail";
explain select * from t1 where a=10 and b=10;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where
select * from t1 where a=10 and b=10;
ERROR HY000: Table definition has changed, please retry transaction
DROP TABLE t0,t1;
SET @@GLOBAL.debug_dbug = @saved_dbug;
set @@optimizer_switch= @optimizer_switch_save;
# End of 10.1 tests
#
# MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase,
# [Warning] InnoDB: Using a partial-field key prefix in search
#
CREATE TABLE t1 (
pk INT,
a VARCHAR(1),
b INT,
PRIMARY KEY (pk),
KEY (a,b)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,'a',1),(2,'b',2);
explain SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,a PRIMARY 4 NULL 1 Using where
SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0;
a
drop table t1;
# End of 10.4 tests
|
