MySQL Bugs: #10084: Straight Join syntax consistancy

Bug #10084	Straight Join syntax consistancy
Submitted:	22 Apr 2005 4:14	Modified:	22 Jun 2005 8:43
Reporter:	Cornelius Sybrandy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	ALL	OS:	Any (ALL)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
Currently, the STRAIGHT_JOIN syntax for joining two tables is inconsistant with the syntax for other joins, such as LEFT JOIN.  There is no requirement for a join, thus it becomes difficult to modify queries since the joins are defined differently since the joins must be defined in the where clause instead of the from clause.

How to repeat:
See the documentation for JOINS.

Suggested fix:
If the STRAIGHT_JOIN syntax is to be retained, it is suggested that the syntax be the same as the ANSI SQL join syntax.

The STRAIGHT_JOIN option affects the whole query, not just a single join
and is used in the same way no matter if the join conditions are in the
WHERE clause or INNER JOINs are used.

Switching from 

  SELECT STRAIGHT_JOIN ... FROM t1 INNER JOIN t2 ON ...

to 

  SELECT STRAIGHT_JOIN ... FROM t2 INNER JOIN t1 ON ...

changes the table order in EXPLAIN output the same way as switching from

  SELECT STRAIGHT_JOIN ... FROM t1, t2 WHERE ...

to 

  SELECT STRAIGHT_JOIN ... FROM t2, t1 WHERE ...

To Quote the documentation found on: http://dev.mysql.com/doc/mysql/en/join.html

"table_reference STRAIGHT_JOIN table_reference

STRAIGHT_JOIN is identical 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."

This to me implies that this is a join since it is described as a join and it found on the page describing joins.

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/internals/25823

A new syntax supporting the queries like
  
  SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a 

was added, but only to 5.0.7.

(We strictly follow the rule not to change any execution plans for any
 versions in production).

ChangeSet
  1.1961 05/06/09 09:16:16 igor@rurik.mysql.com +3 -0
  select.test, select.result:
    Added a test case for bug #10084.
  sql_yacc.yy:
    Fixed bug #10084: STRAIGHT_JOIN for expressions with ON was
    added.

Per developer comments, I am closing this bug and directing further enquiries/discussion of the matter to Bug#11225 - http://bugs.mysql.com/11225