Bug #76837 Inconsistent JOIN evaluation order
Submitted: 26 Apr 2015 3:56 Modified: 6 Dec 2017 19:46
Reporter: Elliot Levin Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: queries joins evaluation order

[26 Apr 2015 3:56] Elliot Levin
Description:
The order in which MySQL performs the evaluation of JOIN clauses is very inconsistent to that of other RDBMSs. A simple <source> LEFT JOIN <to> ON 1=1 clause will evaluate the <source> for every <to> row. 

This also is very unintuitive as it often helps to think of the JOIN clause as a nested loop with <source> being the outer loop and <to> being the inner loop.

The converse of this is also true for RIGHT JOIN but this does not affect INNER JOINs as much as that should be commutative but the order is still unexpected.

How to repeat:
Test script:
CREATE TABLE test_data (
    x INT NOT NULL PRIMARY KEY
);

INSERT INTO test_data (x)
VALUES
(1), (2), (3);

SELECT
  outer_nums.x AS outer_num,
  inner_nums.x AS inner_num 
FROM
test_data outer_nums
LEFT JOIN 
test_data inner_nums
ON 1=1;

This produces:
+-----------+-----------+
| outer_num | inner_num |
+-----------+-----------+
|         1 |         1 |
|         2 |         1 |
|         3 |         1 |
|         1 |         2 |
|         2 |         2 |
|         3 |         2 |
|         1 |         3 |
|         2 |         3 |
|         3 |         3 |
+-----------+-----------+

Suggested fix:
The expected results should be as follows:
+-----------+-----------+
| outer_num | inner_num |
+-----------+-----------+
|         1 |         1 |
|         1 |         2 |
|         1 |         3 |
|         2 |         1 |
|         2 |         2 |
|         2 |         3 |
|         3 |         1 |
|         3 |         2 |
|         3 |         3 |
+-----------+-----------+
[6 Dec 2017 19:46] MySQL Verification Team
Thank you for the bug report. Sorry for the delay, even others RDBMS behave like you expect, MySQL server behaves according standard (free to use any order when not ORDER BY clause is used), so with MySQL use the ORDER BY clause for.