Bug #51492 STRAIGHT_JOIN in combination with 'RIGHT OUTER JOIN' produce incorrect result.

• Submitted: 25 Feb 2010 8:59
• Modified: 25 Feb 2010 11:47
• Reporter: Ole John Aske
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 5.1.41, 5.1.45
• OS: Any
• CPU Architecture: Any

[25 Feb 2010 8:59] Ole John Aske

Description:
Specifying STRAIGHT_JOIN in combination with outer join cause incorrect join plan to be produced by the optimizer. The join plan for the testcase query looks like:

+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra                          |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------------+
|  1 | SIMPLE      | t1    | ALL    | NULL          | NULL    | NULL    | NULL       |    3 |                                |
|  1 | SIMPLE      | t2    | range  | PRIMARY       | PRIMARY | 4       | NULL       |    2 | Using where; Using join buffer |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.pk |    1 |                                |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------------+ 

As t1 is an outer table it can't possible be accesed before the inner tables t2 and t3 - In this case it looks like the optimizer pays more attention to the 'STRAIGHT' optimizer hint than doing what is required to produce a join plan to correctly execute the query.

Removing the STRAIGHT we get the more sensible join plan:

+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
|  1 | SIMPLE      | t2    | range  | PRIMARY       | PRIMARY | 4       | NULL       |    2 | Using where |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.pk |    1 |             |
|  1 | SIMPLE      | t1    | ALL    | NULL          | NULL    | NULL    | NULL       |    3 |             |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+

Which also produce the correct result:

+------+------+----+------+----+------+
| pk   | i32  | pk | i32  | pk | i32  |
+------+------+----+------+----+------+
| NULL | NULL |  3 |    9 |  3 |    9 |
+------+------+----+------+----+------+ 

Possible a duplicate of bug#46091.

How to repeat:
create table t (pk int primary key, i32 int);
insert into t values (1,9), (2,9), (3,9); 

SELECT STRAIGHT_JOIN *
FROM t as t1
RIGHT OUTER JOIN (t as t2 JOIN t as t3 ON t2.pk = t3.pk)
   ON t1.i32 = t3.pk
WHERE t2.pk > 2; 

+------+------+----+------+----+------+
| pk   | i32  | pk | i32  | pk | i32  |
+------+------+----+------+----+------+
|    1 |    9 |  3 |    9 |  3 |    9 |
|    2 |    9 |  3 |    9 |  3 |    9 |
|    3 |    9 |  3 |    9 |  3 |    9 |
+------+------+----+------+----+------+ 

Where none of the result rows fulfills the join condition 't1.i32 = t3.pk'
or is a NULL 'outer row'

[25 Feb 2010 11:47] Valeriy Kravchuk

The same wrong results are produced by current 5.1.45:

openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot test
Reading 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 2
Server version: 5.1.45-debug Source distribution

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

mysql> drop table t;
\Query OK, 0 rows affected (0.22 sec)

mysql> create table t (pk int primary key, i32 int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values (1,9), (2,9), (3,9);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT STRAIGHT_JOIN *
    -> FROM t as t1
    -> RIGHT OUTER JOIN (t as t2 JOIN t as t3 ON t2.pk = t3.pk)
    ->    ON t1.i32 = t3.pk
    -> WHERE t2.pk > 2;
+------+------+----+------+----+------+
| pk   | i32  | pk | i32  | pk | i32  |
+------+------+----+------+----+------+
|    1 |    9 |  3 |    9 |  3 |    9 |
|    2 |    9 |  3 |    9 |  3 |    9 |
|    3 |    9 |  3 |    9 |  3 |    9 |
+------+------+----+------+----+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t as t1 RIGHT OUTER JOIN (t as t2 JOIN t as t3 ON t2.pk = t3.pk)    ON t1.i32 = t3.pk WHERE t2.pk > 2;
+------+------+----+------+----+------+
| pk   | i32  | pk | i32  | pk | i32  |
+------+------+----+------+----+------+
| NULL | NULL |  3 |    9 |  3 |    9 |
+------+------+----+------+----+------+
1 row in set (0.00 sec)

and indeed, this looks like a duplicate of bug #46091.