Description:
A 3 way join query when executed with a small join_buffer (join_cache_level is set to 5) returns a wrong result set.
How to repeat:
The following demonstrates the problem:
mysql> CREATE TABLE t1 (a1 int, filler1 char(64) default ' ' );
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE t2 (
-> a2 int, b2 int, filler2 char(64) default ' ',
-> PRIMARY KEY idx(a2,b2,filler2)
-> ) ;
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE t3 (b3 int, c3 int, INDEX idx(b3));
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO t1(a1) VALUES
-> (4), (7), (1), (9), (8), (5), (3), (6), (2);
Query OK, 9 rows affected (0.01 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t2(a2,b2) VALUES
-> (1,30), (3,40), (2,61), (6,73), (8,92), (9,27), (4,18), (5,84), (7,56),
-> (4,14), (6,76), (8,98), (7,55), (1,39), (2,68), (3,45), (9,21), (5,81),
-> (5,88), (2,65), (6,74), (9,23), (1,37), (3,44), (4,17), (8,99), (7,51),
-> (9,28), (7,52), (1,33), (4,13), (5,87), (3,43), (8,91), (2,62), (6,79),
-> (3,49), (8,93), (7,34), (5,82), (6,78), (2,63), (1,32), (9,22), (4,11);
Query OK, 45 rows affected (0.00 sec)
Records: 45 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t3 VALUES
-> (30,302), (92,923), (18,187), (45,459), (30,309),
-> (39,393), (68,685), (45,458), (21,210), (81,817),
-> (40,405), (61,618), (73,738), (92,929), (27,275),
-> (18,188), (84,846), (56,564), (14,144), (76,763),
-> (98,982), (55,551), (17,174), (99,998), (51,513),
-> (28,282), (52,527), (33,336), (13,138), (87,878),
-> (43,431), (91,916), (62,624), (79,797), (49,494),
-> (93,933), (34,347), (82,829), (78,780), (63,634),
-> (32,329), (22,228), (11,114), (74,749), (23,236);
Query OK, 45 rows affected (0.01 sec)
Records: 45 Duplicates: 0 Warnings: 0
mysql> set join_cache_level=1;
Query OK, 0 rows affected (0.00 sec)
mysql> EXPLAIN
-> SELECT a1<>a2, a1, a2, b2, b3, c3,
-> SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
-> FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
+----+-------------+-------+------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+------------+------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 9 | |
| 1 | SIMPLE | t2 | ref | PRIMARY | PRIMARY | 4 | test.t1.a1 | 1 | Using index |
| 1 | SIMPLE | t3 | ref | idx | idx | 5 | test.t2.b2 | 5 | Using where |
+----+-------------+-------+------+---------------+---------+---------+------------+------+-------------+
3 rows in set (0.00 sec)
mysql> SELECT a1<>a2, a1, a2, b2, b3, c3,
-> SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
-> FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
+--------+------+----+----+------+------+------+----+
| a1<>a2 | a1 | a2 | b2 | b3 | c3 | s1 | s2 |
+--------+------+----+----+------+------+------+----+
| 0 | 4 | 4 | 13 | 13 | 138 | | |
| 0 | 4 | 4 | 18 | 18 | 188 | | |
| 0 | 1 | 1 | 30 | 30 | 309 | | |
| 0 | 1 | 1 | 32 | 32 | 329 | | |
| 0 | 9 | 9 | 22 | 22 | 228 | | |
| 0 | 8 | 8 | 92 | 92 | 929 | | |
| 0 | 8 | 8 | 99 | 99 | 998 | | |
| 0 | 5 | 5 | 82 | 82 | 829 | | |
| 0 | 5 | 5 | 87 | 87 | 878 | | |
| 0 | 3 | 3 | 45 | 45 | 459 | | |
| 0 | 3 | 3 | 45 | 45 | 458 | | |
| 0 | 6 | 6 | 73 | 73 | 738 | | |
| 0 | 6 | 6 | 74 | 74 | 749 | | |
| 0 | 2 | 2 | 61 | 61 | 618 | | |
+--------+------+----+----+------+------+------+----+
14 rows in set (0.01 sec)
mysql> set join_cache_level=5;
Query OK, 0 rows affected (0.00 sec)
mysql> set join_buffer_size=512;
Query OK, 0 rows affected (0.00 sec)
mysql> EXPLAIN
-> SELECT a1<>a2, a1, a2, b2, b3, c3,
-> SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
-> FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
+----+-------------+-------+------+---------------+---------+---------+------------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+------------+------+--------------------------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 9 | |
| 1 | SIMPLE | t2 | ref | PRIMARY | PRIMARY | 4 | test.t1.a1 | 1 | Using index |
| 1 | SIMPLE | t3 | ref | idx | idx | 5 | test.t2.b2 | 5 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+---------+---------+------------+------+--------------------------------+
3 rows in set (0.01 sec)
mysql> SELECT a1<>a2, a1, a2, b2, b3, c3,
-> SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
-> FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
+--------+------+----+----+------+------+------+----+
| a1<>a2 | a1 | a2 | b2 | b3 | c3 | s1 | s2 |
+--------+------+----+----+------+------+------+----+
| 0 | 4 | 4 | 18 | 18 | 188 | | |
| 0 | 4 | 4 | 13 | 13 | 138 | | |
| 0 | 1 | 1 | 30 | 30 | 309 | | |
| 0 | 1 | 1 | 32 | 32 | 329 | | |
| 0 | 8 | 8 | 92 | 92 | 929 | | |
| 1 | 1 | 9 | 22 | 22 | 228 | | |
| 1 | 1 | 8 | 99 | 99 | 998 | | |
| 0 | 5 | 5 | 87 | 87 | 878 | | |
| 0 | 5 | 5 | 82 | 82 | 829 | | |
| 1 | 5 | 3 | 45 | 45 | 459 | | |
| 1 | 5 | 3 | 45 | 45 | 458 | | |
| 0 | 6 | 6 | 73 | 73 | 738 | | |
| 0 | 6 | 6 | 74 | 74 | 749 | | |
| 0 | 2 | 2 | 61 | 61 | 618 | | |
+--------+------+----+----+------+------+------+----+
14 rows in set (0.01 sec)