Bug #59487 wrong result with STRAIGHT_JOIN and RIGHT JOIN
Submitted: 13 Jan 2011 21:15 Modified: 10 Feb 2012 3:11
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1, 5.5, 5.6 OS:Any
Assigned to:
Triage: Triaged: D2 (Serious) / R3 (Medium) / E5 (Major)

[13 Jan 2011 21:15] Guilhem Bichot
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;
[13 Jan 2011 21:42] Guilhem Bichot
Note: bug is not related to:
- "not exists" optimization
- join buffering
- const tables
[14 Jan 2011 5:12] Valerii Kravchuk
Verified with current mysql-5.5 from bzr on Mac OS X.
[14 Jan 2011 13:11] Guilhem Bichot
BUG#58494 was marked as duplicate of this one. When fixing 59487 don't forget to double-check that it also fixes 58494.
[10 Feb 2012 3:11] Paul Dubois
Noted in 5.6.5 changelog.

The optimizer mishandled STRAIGHT_JOIN used with nested joins; for
example, by not evaluating tables in the specified order.