summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/range_innodb.result
blob: eddfbfd0d62dcdba1479dad290b970a44a63549a (plain)
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
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
#
# Range optimizer (and related) tests that need InnoDB.
# 
#
# 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 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 
seq,seq,
repeat('0123456789', 10), 
repeat('0123456789', 10), 
repeat('0123456789', 10),
repeat('0123456789', 10)
from seq_0_to_9999;
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,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 STATS_AUTO_RECALC=0;
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 STATS_AUTO_RECALC=0;
insert into t2 values (1),(2);
create table t3 (d int) engine=innodb STATS_AUTO_RECALC=0;
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);
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.seq/10, a.seq/10, a.seq from seq_0_to_499 a, seq_0_to_4 b;
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-27262: Index intersection with full scan over an index
#
CREATE TABLE t1 (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
p char(32) DEFAULT NULL,
es tinyint(3) unsigned NOT NULL DEFAULT 0,
er tinyint(3) unsigned NOT NULL DEFAULT 0,
x mediumint(8) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (id),
INDEX es (es),
INDEX x (x),
INDEX er (er,x),
INDEX p (p)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into t1(es,er) select 0, 1 from seq_1_to_45;
insert into t1(es,er) select 0, 2 from seq_1_to_49;
insert into t1(es,er) select 0, 3 from seq_1_to_951;
insert into t1(es,er) select 0, 3 from seq_1_to_1054;
insert into t1(es,er) select 0, 6 from seq_1_to_25;
insert into t1(es,er) select 0, 11 from seq_1_to_1;
insert into t1(es,er) select 1, 1 from seq_1_to_45;
insert into t1(es,er) select 1, 2 from seq_1_to_16;
insert into t1(es,er) select 1, 3 from seq_1_to_511;
insert into t1(es,er) select 1, 4 from seq_1_to_687;
insert into t1(es,er) select 1, 6 from seq_1_to_50;
insert into t1(es,er) select 1, 7 from seq_1_to_4;
insert into t1(es,er) select 1, 11 from seq_1_to_1;
insert into t1(es,er) select 2, 1 from seq_1_to_82;
insert into t1(es,er) select 2, 2 from seq_1_to_82;
insert into t1(es,er) select 2, 3 from seq_1_to_1626;
insert into t1(es,er) select 2, 4 from seq_1_to_977;
insert into t1(es,er) select 2, 6 from seq_1_to_33;
insert into t1(es,er) select 2, 11 from seq_1_to_1;
insert into t1(es,er) select 3, 1 from seq_1_to_245;
insert into t1(es,er) select 3, 2 from seq_1_to_81;
insert into t1(es,er) select 3, 3 from seq_1_to_852;
insert into t1(es,er) select 3, 4 from seq_1_to_2243;
insert into t1(es,er) select 3, 6 from seq_1_to_44;
insert into t1(es,er) select 3, 11 from seq_1_to_1;
insert into t1(es,er) select 4, 1 from seq_1_to_91;
insert into t1(es,er) select 4, 2 from seq_1_to_83;
insert into t1(es,er) select 4, 3 from seq_1_to_297;
insert into t1(es,er) select 4, 4 from seq_1_to_2456;
insert into t1(es,er) select 4, 6 from seq_1_to_19;
insert into t1(es,er) select 4, 11 from seq_1_to_1;
update t1 set p='foobar';
update t1 set x=0;
set @save_isp=@@innodb_stats_persistent;
set global innodb_stats_persistent= 1;
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
set optimizer_switch='index_merge_sort_intersection=on';
SELECT * FROM t1
WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
id	p	es	er	x
14645	foobar	4	4	0
14646	foobar	4	4	0
EXPLAIN EXTENDED SELECT * FROM t1
WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	es,er,p	es	1	NULL	#	100.00	Using index condition; Using where
Warnings:
Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2
set optimizer_switch='index_merge_sort_intersection=off';
SELECT * FROM t1
WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
id	p	es	er	x
14645	foobar	4	4	0
14646	foobar	4	4	0
EXPLAIN EXTENDED SELECT * FROM t1
WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	es,er,p	es	1	NULL	#	100.00	Using index condition; Using where
Warnings:
Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2
set optimizer_switch='index_merge_sort_intersection=on';
SELECT * FROM t1
WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
id	p	es	er	x
14007	foobar	4	2	0
14008	foobar	4	2	0
EXPLAIN EXTENDED SELECT * FROM t1
WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	es,er,p	es	1	NULL	#	100.00	Using index condition; Using where
Warnings:
Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` < 6 or `test`.`t1`.`er` >= 2) and `test`.`t1`.`es` >= 4 limit 2
set optimizer_switch='index_merge_sort_intersection=off';
SELECT * FROM t1
WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
id	p	es	er	x
14007	foobar	4	2	0
14008	foobar	4	2	0
EXPLAIN EXTENDED SELECT * FROM t1
WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	es,er,p	es	1	NULL	#	100.00	Using index condition; Using where
Warnings:
Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` < 6 or `test`.`t1`.`er` >= 2) and `test`.`t1`.`es` >= 4 limit 2
set optimizer_switch='index_merge_sort_intersection=default';
set global innodb_stats_persistent= @save_isp;
DROP TABLE t1;
# End of 10.2 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