MySQL Bugs: #60080: STRAIGHT_JOIN is not enforced when right-hand table is itself a JOIN

Bug #60080	STRAIGHT_JOIN is not enforced when right-hand table is itself a JOIN
Submitted:	10 Feb 2011 9:29	Modified:	10 Feb 2012 3:11
Reporter:	Roy Lyseng	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5, mysql-trunk	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This problem was noticed when looking at the fix for bug#56529: Crash due to long semaphore wait in InnoDB with ICP and subqueries.

The test case for this bug contains a query on the form:
  t1 STRAIGHT_JOIN (t2 JOIN t3)

The reference manual for MySQL 5.5 contains this explanation for STRAIGHT_JOIN (sect 12.2.9.1):

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

According to my interpretation, the join optimizer should read t1 first, but is free to reorder t2 and t3. However, the plan reported from this bug case is t3 followed by t2 followed by t1.

How to repeat:
See test case for bug#56529 in mysql_test/include/icp_tests.inc:

CREATE TABLE t1 (
  col_int_nokey INTEGER,
  col_int_key INTEGER,
  col_varchar_key VARCHAR(1),

  KEY (col_int_key),
  KEY (col_varchar_key, col_int_key)
);

INSERT INTO t1 VALUES (NULL,2,'w');
INSERT INTO t1 VALUES (7,9,'m');
INSERT INTO t1 VALUES (9,3,'m');
INSERT INTO t1 VALUES (7,9,'k');
INSERT INTO t1 VALUES (4,NULL,'r');
INSERT INTO t1 VALUES (2,9,'t');
INSERT INTO t1 VALUES (6,3,'j');
INSERT INTO t1 VALUES (8,8,'u');
INSERT INTO t1 VALUES (NULL,8,'h');
INSERT INTO t1 VALUES (5,53,'o');
INSERT INTO t1 VALUES (NULL,0,NULL);
INSERT INTO t1 VALUES (6,5,'k');
INSERT INTO t1 VALUES (188,166,'e');
INSERT INTO t1 VALUES (2,3,'n');
INSERT INTO t1 VALUES (1,0,'t');
INSERT INTO t1 VALUES (1,1,'c');
INSERT INTO t1 VALUES (0,9,'m');
INSERT INTO t1 VALUES (9,5,'y');
INSERT INTO t1 VALUES (NULL,6,'f');

CREATE TABLE t2 (
  c1 INTEGER NOT NULL
);

let query=
SELECT table1.col_int_nokey
FROM t1 AS table1 STRAIGHT_JOIN (
                  t1 AS table2 INNER JOIN t1 AS table3
                  ON table3.col_varchar_key = table2.col_varchar_key)
     ON table3.col_int_nokey = table1.col_int_key
WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );

eval EXPLAIN $query;
eval $query;

DROP TABLE t1, t2;

More STRAIGHT_JOIN test cases can be found in the attachment to bug#56529: http://bugs.mysql.com/file.php?id=15677&text=1

Verified with current mysql-trunk on 32-bit Ubuntu 10.04:

openxs@ubuntu:/home2/openxs/dbs/trunk$ bin/mysql --no-defaults -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.2-m5-valgrind-max-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1, t2, t3;
ERROR 1051 (42S02): Unknown table 'test.t1,test.t2,test.t3'
mysql> CREATE TABLE t1 (
    ->   col_int_nokey INTEGER,
    ->   col_int_key INTEGER,
    ->   col_varchar_key VARCHAR(1),
    -> 
    ->   KEY (col_int_key),
    ->   KEY (col_varchar_key, col_int_key)
    -> );
Query OK, 0 rows affected (0.48 sec)

mysql> INSERT INTO t1 VALUES (NULL,2,'w');
Query OK, 1 row affected (0.22 sec)

mysql> INSERT INTO t1 VALUES (7,9,'m');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (9,3,'m');
Query OK, 1 row affected (0.00 sec)

...

mysql> INSERT INTO t1 VALUES (NULL,6,'f');
Query OK, 1 row affected (0.01 sec)

mysql> CREATE TABLE t2 (
    ->   c1 INTEGER NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> explain SELECT table1.col_int_nokey
    -> FROM t1 AS table1 STRAIGHT_JOIN (
    ->                   t1 AS table2 INNER JOIN t1 AS table3
    ->                   ON table3.col_varchar_key = table2.col_varchar_key)
    ->      ON table3.col_int_nokey = table1.col_int_key
    -> WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );
+----+--------------------+--------+-------+-----------------+-----------------+---------+-----------------------------+------+--------------------------+
| id | select_type        | table  | type  | possible_keys   | key             | key_len | ref                         | rows | Extra                    |
+----+--------------------+--------+-------+-----------------+-----------------+---------+-----------------------------+------+--------------------------+
|  1 | PRIMARY            | table2 | index | col_varchar_key | col_varchar_key | 9       | NULL                        |   19 | Using where; Using index |
|  1 | PRIMARY            | table3 | ref   | col_varchar_key | col_varchar_key | 4       | test.table2.col_varchar_key |    1 | Using where              |
|  1 | PRIMARY            | table1 | ref   | col_int_key     | col_int_key     | 5       | test.table3.col_int_nokey   |    1 |                          |
|  2 | DEPENDENT SUBQUERY | t2     | ALL   | NULL            | NULL            | NULL    | NULL                        |    1 | Using where              |
+----+--------------------+--------+-------+-----------------+-----------------+---------+-----------------------------+------+--------------------------+
4 rows in set (0.12 sec)

As you can see, optimizer does NOT start with table1.

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.