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
|
-- source include/have_innodb.inc
--disable_warnings
drop table if exists t1,t2,t3,t4;
--enable_warnings
set @save_storage_engine= @@storage_engine;
set storage_engine=InnoDB;
--source include/mrr_tests.inc
set storage_engine= @save_storage_engine;
# Try big rowid sizes
set @mrr_buffer_size_save= @@mrr_buffer_size;
set mrr_buffer_size=64;
# By default InnoDB will fill values only for key parts used by the query,
# which will cause DS-MRR to supply an invalid tuple on scan restoration.
# Verify that DS-MRR's code extra(HA_EXTRA_RETRIEVE_ALL_COLS) call has effect:
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) ) engine=InnoDB;
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
concat('b-', 1000 + B.a, '-b'),
concat('c-', 1000 + C.a, '-c'),
'filler'
from t1 A, t1 B, t1 C;
explain
select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
drop table t2;
# Try a very big rowid
create table t2 (a char(100), b char(100), c char(100), d int,
filler char(10), key(d), primary key (a,b,c)) engine= innodb;
insert into t2 select A.a, B.a, B.a, A.a, 'filler' from t1 A, t1 B;
--replace_column 9 #
explain select * from t2 force index (d) where d < 10;
drop table t2;
drop table t1;
set @@mrr_buffer_size= @mrr_buffer_size_save;
#
# BUG#33033 "MySQL/InnoDB crashes with simple select range query"
#
create table t1 (f1 int not null, f2 int not null,f3 int not null, f4 char(1), primary key (f1,f2), key ix(f3))Engine=InnoDB;
--disable_query_log
let $1=55;
while ($1)
{
eval insert into t1(f1,f2,f3,f4) values ($1,$1,$1,'A');
dec $1;
}
--enable_query_log
# The following must not crash:
select * from t1 where (f3>=5 and f3<=10) or (f3>=1 and f3<=4);
drop table t1;
--echo
--echo BUG#37977: Wrong result returned on GROUP BY + OR + Innodb
--echo
CREATE TABLE t1 (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) NOT NULL,
`int_key` int(11) NOT NULL,
`date_key` date NOT NULL,
`date_nokey` date NOT NULL,
`time_key` time NOT NULL,
`time_nokey` time NOT NULL,
`datetime_key` datetime NOT NULL,
`datetime_nokey` datetime NOT NULL,
`varchar_key` varchar(5) DEFAULT NULL,
`varchar_nokey` varchar(5) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `date_key` (`date_key`),
KEY `time_key` (`time_key`),
KEY `datetime_key` (`datetime_key`),
KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES
(1,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14 05:34:08','2007-09-14 05:34:08','qk','qk'),
(2,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'),
(3,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04 15:42:50','aew','aew'),
(4,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23 13:23:35',NULL,NULL),
(5,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19 11:01:28','qu','qu');
select pk from t1 WHERE `varchar_key` > 'kr' group by pk;
select pk from t1 WHERE `int_nokey` IS NULL OR `varchar_key` > 'kr' group by pk;
drop table t1;
--echo #
--echo # BUG#39447: Error with NOT NULL condition and LIMIT 1
--echo #
CREATE TABLE t1 (
id int(11) NOT NULL,
parent_id int(11) DEFAULT NULL,
name varchar(10) DEFAULT NULL,
PRIMARY KEY (id),
KEY ind_parent_id (parent_id)
) ENGINE=InnoDB;
insert into t1 (id, parent_id, name) values
(10,NULL,'A'),
(20,10,'B'),
(30,10,'C'),
(40,NULL,'D'),
(50,40,'E'),
(60,40,'F'),
(70,NULL,'J');
SELECT id FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
--echo This must show type=index, extra=Using where
explain SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
drop table t1;
|
