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 |
+-----------+-----------+
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 | +-----------+-----------+