maria_icp.result 4.68 KB
Newer Older
1 2
set @save_storage_engine= @@storage_engine;
set storage_engine=Maria;
3 4
set @maria_icp_tmp=@@optimizer_switch;
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
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
# Bug#36981 - "innodb crash when selecting for update"
#
CREATE TABLE t1 (
c1 CHAR(1),
c2 CHAR(10),
KEY (c1)
);
INSERT INTO t1 VALUES ('3', null);
SELECT * FROM t1 WHERE c1='3' FOR UPDATE;
c1	c2
3	NULL
DROP TABLE t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE t2 (a INT);
INSERT INTO t2 SELECT A.a + 10*(B.a + 10*C.a) FROM t1 A, t1 B, t1 C;
CREATE TABLE t3 (
c1 CHAR(10) NOT NULL,
c2 CHAR(10) NOT NULL,
c3 CHAR(200) NOT NULL,
KEY (c1)
);
INSERT INTO t3 
SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',1000+ t2.a,'=w'), 'filler'
  FROM t2;
INSERT INTO t3 
SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',2000+t2.a,'=w'), 'filler-1'
  FROM t2;
INSERT INTO t3
SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',3000+t2.a,'=w'), 'filler-2'
  FROM t2;
SELECT c1,c3 FROM t3 WHERE c1 >= 'c-1994=w' and c1 != 'c-1996=w' FOR UPDATE;
c1	c3
c-1994=w	filler
c-1994=w	filler-1
c-1994=w	filler-2
c-1995=w	filler
c-1995=w	filler-1
c-1995=w	filler-2
c-1997=w	filler
c-1997=w	filler-1
c-1997=w	filler-2
c-1998=w	filler
c-1998=w	filler-1
c-1998=w	filler-2
c-1999=w	filler
c-1999=w	filler-1
c-1999=w	filler-2
DROP TABLE t1,t2,t3;
#
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
# Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for
#             null-safe operator <=> NULL
#
CREATE TABLE t1(
c1 DATE NOT NULL,
c2 DATE NULL,
c3 DATETIME,
c4 TIMESTAMP,
PRIMARY KEY(c1),
UNIQUE(c2)
);

INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05');
INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26');
INSERT INTO t1 VALUES('2008-01-01', NULL        , '2008-01-02', '2008-01-03');
INSERT INTO t1 VALUES('2008-01-17', NULL        , NULL        , '2009-01-29');
INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29');

SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
c1	c2	c3	c4
2008-01-01	NULL	2008-01-02 00:00:00	2008-01-03 00:00:00
2008-01-17	NULL	NULL	2009-01-29 00:00:00

SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
c1	c2	c3	c4
2008-01-01	NULL	2008-01-02 00:00:00	2008-01-03 00:00:00
2008-01-17	NULL	NULL	2009-01-29 00:00:00

DROP TABLE t1;
Sergey Petrunya's avatar
Sergey Petrunya committed
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
#
# Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on
#
CREATE TABLE t (
dummy INT PRIMARY KEY, 
a INT UNIQUE, 
b INT
);
INSERT INTO t VALUES (1,1,1),(3,3,3),(5,5,5);
SELECT * FROM t WHERE a > 2 FOR UPDATE;
dummy	a	b
3	3	3
5	5	5
DROP TABLE t;
#
# Bug#35080 - Innodb crash at mem_block_get_len line 72
#
CREATE TABLE t1 (
t1_autoinc INT(11) NOT NULL AUTO_INCREMENT,
uuid VARCHAR(36) DEFAULT NULL,
PRIMARY KEY (t1_autoinc),
KEY k (uuid)
);
CREATE TABLE t2 (
t2_autoinc INT(11) NOT NULL AUTO_INCREMENT,
uuid VARCHAR(36) DEFAULT NULL,
date DATETIME DEFAULT NULL,
PRIMARY KEY (t2_autoinc),
KEY k (uuid)
);
CREATE VIEW v1 AS 
SELECT t1_autoinc, uuid
FROM t1
WHERE (ISNULL(uuid) OR (uuid like '%-%'));
CREATE VIEW v2 AS 
SELECT t2_autoinc, uuid, date 
FROM t2
WHERE (ISNULL(uuid) OR (LENGTH(uuid) = 36));
CREATE PROCEDURE delete_multi (IN uuid CHAR(36))
DELETE v1, v2 FROM v1 INNER JOIN v2
ON v1.uuid = v2.uuid
WHERE v1.uuid = @uuid;
SET @uuid = UUID();
INSERT INTO v1 (uuid) VALUES (@uuid);
INSERT INTO v2 (uuid, date) VALUES (@uuid, '2009-09-09');
CALL delete_multi(@uuid);
DROP procedure delete_multi;
DROP table t1,t2;
DROP view v1,v2;
#
# Bug#41996 - multi-table delete crashes server (InnoDB table)
#
CREATE TABLE t1 (
b BIGINT,
i INT, 
KEY (b)
);
INSERT INTO t1 VALUES (2, 2);
DELETE t1 FROM t1 a, t1 WHERE a.i=t1.b;
DROP TABLE t1;
#
# Bug#43448 - Server crashes on multi table delete with Innodb
#
CREATE TABLE t1 (
id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
t CHAR(12)
);
CREATE TABLE t2 (
id2 INT NOT NULL, 
t CHAR(12)
);
CREATE TABLE t3(
id3 INT NOT NULL, 
t CHAR(12), 
INDEX(id3)
);
CREATE PROCEDURE insert_data ()
BEGIN
DECLARE i1 INT DEFAULT 20;
DECLARE i2 INT;
DECLARE i3 INT;
WHILE (i1 > 0) DO
INSERT INTO t1(t) VALUES (i1);
SET i2 = 2;
WHILE (i2 > 0) DO
INSERT INTO t2(id2, t) VALUES (i1, i2);
SET i3 = 2;
WHILE (i3 > 0) DO
INSERT INTO t3(id3, t) VALUES (i1, i2);
SET i3 = i3 -1;
END WHILE;
SET i2 = i2 -1;
END WHILE;
SET i1 = i1 - 1;
END WHILE;
END |
CALL insert_data();
SELECT COUNT(*) FROM t1 WHERE id1 > 10;
COUNT(*)
10
SELECT COUNT(*) FROM t2 WHERE id2 > 10;
COUNT(*)
20
SELECT COUNT(*) FROM t3 WHERE id3 > 10;
COUNT(*)
40
DELETE t1, t2, t3 
FROM t1, t2, t3 
WHERE t1.id1 = t2.id2 AND t2.id2 = t3.id3 AND t1.id1 > 3;
SELECT COUNT(*) FROM t1;
COUNT(*)
3
SELECT COUNT(*) FROM t2;
COUNT(*)
6
SELECT COUNT(*) FROM t3;
COUNT(*)
12
DROP PROCEDURE insert_data;
DROP TABLE t1, t2, t3;
205
set storage_engine= @save_storage_engine;
206
set optimizer_switch=@maria_icp_tmp;