Description:
With STRAIGHT_JOIN, MySQL may choose a wrong execution plan, where ordering of tables is wrong and leads to wrong results.
In the "how-to-repeat", the first SELECT (with STRAIGH_JOIN) gives result
1 1
which is incorrect. The second SELECT (without STRAIGHT_JOIN) gives result
1 NULL
which is correct.
Ordering with STRAIGHT_JOIN is wrong. For example because t2 is accessed before t5 though the ON condition of t2 involves t5.
The wrong query result is observed with 5.1, 5.5. In trunk (5.6) an assertion fires (the same as in BUG#58494 which is about a very similar query) (this assertion was added to 5.6; if added to 5.1/5.5 it fires too).
Tested with 5.1 version: luis.soares@oracle.com-20110113114100-82kaxaq975qkd8i2
5.5: dmitry.shulga@oracle.com-20110111152631-jezu2dbglg05g6ff
trunk: magne.mahre@oracle.com-20110104152359-l51xr7j27ca4mc1s
How to repeat:
--source include/have_innodb.inc
CREATE TABLE `t1` (
`pk` int(11) NOT NULL,
`col_varchar_10_latin1_key` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `t1` VALUES (1,'1');
CREATE TABLE `t2` (
`pk` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `t2` VALUES (1);
CREATE TABLE `t3` (
`pk` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `t3` VALUES (1);
CREATE TABLE `t4` (
`pk` int(11) NOT NULL,
`col_int` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_10_latin1_key` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `t4` VALUES (1,1,1,'1');
CREATE TABLE `t5` (
`col_int` int(11) DEFAULT NULL,
`col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `t5` VALUES (1,'1');
CREATE TABLE `t6` (
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_10_latin1_key` varchar(10) DEFAULT NULL,
`pk` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `t6` VALUES (1,'1',1);
# wrong result
SELECT STRAIGHT_JOIN t6a.pk, t2.pk
FROM
t6 AS t6a
LEFT JOIN ( t2 right join
(
( t1 left join
(t4
JOIN
t3 ON t4.col_int
)
ON t4.col_int_key = t1.pk
)
LEFT JOIN
(t5
JOIN
t6 AS t6b ON t5.col_varchar_10_utf8_key =
t6b.col_varchar_10_latin1_key
)
ON t1.pk = t5.col_int
)
ON t4.col_varchar_10_latin1_key =
t1.col_varchar_10_latin1_key and t5.col_varchar_10_utf8_key=0
)
ON t6a.pk IS TRUE
WHERE t6b.col_int_key IS TRUE
;
# EXPLAIN of query above (t2 is before t5 in plan)
EXPLAIN SELECT STRAIGHT_JOIN t6a.pk, t2.pk
FROM
t6 AS t6a
LEFT JOIN ( t2 right join
(
( t1 left join
(t4
JOIN
t3 ON t4.col_int
)
ON t4.col_int_key = t1.pk
)
LEFT JOIN
(t5
JOIN
t6 AS t6b ON t5.col_varchar_10_utf8_key =
t6b.col_varchar_10_latin1_key
)
ON t1.pk = t5.col_int
)
ON t4.col_varchar_10_latin1_key =
t1.col_varchar_10_latin1_key and t5.col_varchar_10_utf8_key=0
)
ON t6a.pk IS TRUE
WHERE t6b.col_int_key IS TRUE
;
# right result (same query, just remove STRAIGHT_JOIN):
SELECT t6a.pk, t2.pk
FROM
t6 AS t6a
LEFT JOIN ( t2 right join
(
( t1 left join
(t4
JOIN
t3 ON t4.col_int
)
ON t4.col_int_key = t1.pk
)
LEFT JOIN
(t5
JOIN
t6 AS t6b ON t5.col_varchar_10_utf8_key =
t6b.col_varchar_10_latin1_key
)
ON t1.pk = t5.col_int
)
ON t4.col_varchar_10_latin1_key =
t1.col_varchar_10_latin1_key and t5.col_varchar_10_utf8_key=0
)
ON t6a.pk IS TRUE
WHERE t6b.col_int_key IS TRUE
;
# EXPLAIN of query above, t2 is last in plan
EXPLAIN SELECT t6a.pk, t2.pk
FROM
t6 AS t6a
LEFT JOIN ( t2 right join
(
( t1 left join
(t4
JOIN
t3 ON t4.col_int
)
ON t4.col_int_key = t1.pk
)
LEFT JOIN
(t5
JOIN
t6 AS t6b ON t5.col_varchar_10_utf8_key =
t6b.col_varchar_10_latin1_key
)
ON t1.pk = t5.col_int
)
ON t4.col_varchar_10_latin1_key =
t1.col_varchar_10_latin1_key and t5.col_varchar_10_utf8_key=0
)
ON t6a.pk IS TRUE
WHERE t6b.col_int_key IS TRUE
;
drop table t1,t2,t3,t4,t5,t6;
