MySQL Bugs: #28700: VIEWs using the MERGE algorithm ignore STRAIGHT

Bug #28700	VIEWs using the MERGE algorithm ignore STRAIGHT_JOIN
Submitted:	27 May 2007 2:16	Modified:	5 Oct 2009 11:56
Reporter:	Björn Steinbrink	Email Updates:
Status:	In progress	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.0.44-BK, 5.0.41	OS:	Linux (Debian Unstable)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When a VIEW is defined using a STRAIGHT_JOIN, the optimizer simply ignores that request and uses a different join order. Giving an extra STRAIGHT_JOIN in the query that accesses the VIEW works, but that's weird.

I'm filing that under performance, as STRAIGHT_JOIN is usually used for that reason.

How to repeat:
mysql> CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, val INT NOT NULL, PRIMARY KEY(id), KEY (id, val)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
Query OK, 15 rows affected, 1 warning (0.00 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
Query OK, 15 rows affected (0.00 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> CREATE ALGORITHM=MERGE VIEW v1 AS SELECT STRAIGHT_JOIN * FROM t1 INNER JOIN t2 USING (id) ORDER BY id, val;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT STRAIGHT_JOIN * FROM t1 INNER JOIN t2 USING (id) ORDER BY id, val;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
|  1 | SIMPLE      | t1    | index  | PRIMARY,id    | id      | 8       | NULL       |   15 | Using index | 
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.id |    1 | Using index | 
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM v1;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+----------------------------------------------+
|  1 | SIMPLE      | t2    | index  | PRIMARY       | PRIMARY | 4       | NULL       |   15 | Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY,id    | PRIMARY | 4       | test.t2.id |    1 |                                              | 
+----+-------------+-------+--------+---------------+---------+---------+------------+------+----------------------------------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT STRAIGHT_JOIN * FROM v1;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
|  1 | SIMPLE      | t1    | index  | PRIMARY,id    | id      | 8       | NULL       |   15 | Using index | 
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.id |    1 | Using index | 
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
2 rows in set (0.00 sec)

Thank you for a bug report. Verified just as described with latest 5.0.44-BK.

The MERGE algorithm means that the query gets textually expanded into the referencing query without becoming a subquery. Suppose we have a very big table 
t3( id INT PRIMARY KEY ).

In the given example the query

SELECT * FROM t3 join v1 using(id);

Gets expanded to:

SELECT * FROM t3 JOIN (t1 INNER JOIN t2 USING (id)) using(id);

The STRAIGHT_JOIN hint can only be used on a per-query basis, this is why it is 
ignored when the view is expanded. Were it not ignored, the STRAIGHT_JOIN would
silently apply to the whole query that accesses the view, which would probably cause more damage than help.

Hm, I just discovered that MySQL 5 also supports the "t1 STRAIGHT_JOIN t2 ON condition" syntax. I made it a habit to use the STRAIGHT_JOIN option because earlier versions did not support the "ON condition" part for STRAIGHT_JOIN joins.

Using that syntax is an acceptable "workaround" for me. Would still be nice if the STRAIGHT_JOIN option on views could cause an implicit conversion of inner joins to straight joins though.

Hi Björn!
Ok, now I understand what you want to do. I will close this bug then, since there is a way of getting exactly the beavior you call for. The real bug is that SELECT ... FROM t1 STRAIGHT_JOIN t2 USING(column) is not supported. I have submitted this as a separate bug.

Hi Martin!

Actually, I'd like the views to behave like this:
CREATE VIEW v1 AS SELECT STRAIGHT_JOIN t1 INNER JOIN t2 INNER JOIN t3;
should be (internally) treated as:
CREATE VIEW v1 AS SELECT t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3;

IOW, both forms should result in
SELECT * FROM t0 INNER JOIN v1;
being treated as:
SELECT * FROM t0 INNER JOIN (t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3);

Maybe that needs to be assigned to the view category instead of the optimizer category?

If such an extension is not (easily) possible, I'd prefer views to reject the STRAIGHT_JOIN option, and force the user to use STRAIGHT_JOIN joins instead.

Hi,
Yes, I agree that there should least be a warning.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29711

ChangeSet@1.2498, 2007-06-27 15:11:37+03:00, mhansson@linux-st28.site +4 -0
  Bug #28700: VIEWs using the MERGE algorithm ignore STRAIGHT_JOIN
  
  When an ALGORITHM = MERGE VIEW is defined using a STRAIGHT_JOIN, the optimizer 
  ignores that hint, since it would affect any query using the view.
  Fixed by rewriting all queries such as SELECT STRAIGHT_JOIN ... t1, ..., tn to 
  SELECT STRAIGHT_JOIN ... t1 STRAIGHT JOIN ... STRAIGHT_JOIN tn. The
  initial STRAIGHT_JOIN hint is now redundant but is left as is, since this lets the 
  optimizer work as previously in all other cases.

This bug is in fact a feature request that will change
the behavior of the Optimizer, and that may affect
existing queries over views, thus many of our current
users. Therefore such a change:
- cannot go into versions 5.0 or 5.1,
- should be discussed as a feature for 5.2.