You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
83 lines
3.3 KiB
83 lines
3.3 KiB
#
|
|
# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT.
|
|
# MEMORY LEADING TO SYSTEM CRASH
|
|
#
|
|
CREATE TABLE ten (a INTEGER);
|
|
INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
CREATE TABLE hundred (a INTEGER);
|
|
INSERT INTO hundred
|
|
SELECT a1.a + 10 * a2.a FROM ten a1, ten a2;
|
|
CREATE TABLE thousand (a INTEGER);
|
|
INSERT INTO thousand
|
|
SELECT a1.a + 10 * a2.a + 100 * a3.a FROM ten a1, ten a2, ten a3;
|
|
CREATE TABLE t1 (
|
|
pk INTEGER NOT NULL,
|
|
i1 INTEGER NOT NULL,
|
|
c1 VARCHAR(10) NOT NULL,
|
|
PRIMARY KEY (pk)
|
|
) charset latin1;
|
|
INSERT INTO t1
|
|
SELECT a, 1, 'MySQL' FROM thousand;
|
|
CREATE TABLE t2 (
|
|
pk INTEGER NOT NULL,
|
|
c1 VARCHAR(10) NOT NULL,
|
|
c2 varchar(10) NOT NULL,
|
|
PRIMARY KEY (pk)
|
|
) charset latin1;
|
|
INSERT INTO t2
|
|
SELECT a, 'MySQL', 'MySQL' FROM ten;
|
|
CREATE TABLE t3 (
|
|
pk INTEGER NOT NULL,
|
|
c1 VARCHAR(10) NOT NULL,
|
|
PRIMARY KEY (pk)
|
|
) charset latin1;
|
|
INSERT INTO t3
|
|
SELECT a, 'MySQL' FROM hundred;
|
|
CREATE TABLE t4 (
|
|
pk int(11) NOT NULL,
|
|
c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
|
|
c2 varchar(10) NOT NULL,
|
|
c3 varchar(10) NOT NULL,
|
|
PRIMARY KEY (pk),
|
|
KEY k1 (c1_key)
|
|
) charset latin1;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
CREATE TABLE t5 (
|
|
pk INTEGER NOT NULL,
|
|
c1 VARCHAR(10) NOT NULL,
|
|
PRIMARY KEY (pk)
|
|
) charset latin1;
|
|
INSERT INTO t5
|
|
SELECT a, 'MySQL' FROM ten;
|
|
set optimizer_switch=`mrr=on,mrr_cost_based=off`;
|
|
EXPLAIN SELECT STRAIGHT_JOIN *
|
|
FROM
|
|
(t1 LEFT JOIN
|
|
(t2 LEFT JOIN
|
|
(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
|
|
ON t2.c1 = t4.c3)
|
|
ON t1.c1 = t4.c2)
|
|
RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
|
|
WHERE t1.i1 = 1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t5 NULL ALL NULL NULL NULL NULL 10 100.00 NULL
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1000 10.00 Using where; Using join buffer (Block Nested Loop)
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 10 33.33 Using where; Using join buffer (Block Nested Loop)
|
|
1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 100 100.00 Using join buffer (Block Nested Loop)
|
|
1 SIMPLE t4 NULL ALL k1 NULL NULL NULL 0 0.00 Range checked for each record (index map: 0x2)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select straight_join `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t4`.`pk` AS `pk`,`test`.`t4`.`c1_key` AS `c1_key`,`test`.`t4`.`c2` AS `c2`,`test`.`t4`.`c3` AS `c3`,`test`.`t5`.`pk` AS `pk`,`test`.`t5`.`c1` AS `c1` from `test`.`t5` join `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t4`.`c3` = `test`.`t2`.`c1`) and (`test`.`t4`.`c2` = `test`.`t1`.`c1`) and (`test`.`t1`.`i1` = 1) and (`test`.`t2`.`c2` <= `test`.`t5`.`c1`) and (convert(`test`.`t3`.`c1` using utf8) <= `test`.`t4`.`c1_key`))
|
|
SELECT STRAIGHT_JOIN *
|
|
FROM
|
|
(t1 LEFT JOIN
|
|
(t2 LEFT JOIN
|
|
(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
|
|
ON t2.c1 = t4.c3)
|
|
ON t1.c1 = t4.c2)
|
|
RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
|
|
WHERE t1.i1 = 1;
|
|
pk i1 c1 pk c1 c2 pk c1 pk c1_key c2 c3 pk c1
|
|
DROP TABLE ten, hundred, thousand;
|
|
DROP TABLE t1, t2, t3, t4, t5;
|
|
|