Bug #37131 Query returns different results when executed with join buffer and without
Submitted: 1 Jun 2008 20:47 Modified: 20 Nov 2010 23:07
Reporter: Igor Babaev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:mysql-6.0-bka-preview OS:Any
Assigned to: Igor Babaev CPU Architecture:Any

[1 Jun 2008 20:47] Igor Babaev
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)
[2 Jun 2008 1:37] Bugs System
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/47295

ChangeSet@1.2629, 2008-06-01 18:43:42-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #37131.
  At the very end of the function JOIN_CACHE_BKA::join_matching_records
  the the fields of the last record written into the join buffer must
  be copied back into the record buffers. 
  It is easy to show that if not to do it for the fields from the tables
  that precede that the join table to which the join buffer is attached
  the returned result set in many cases will be wrong.
[14 Dec 2008 11:07] Bugs System
Pushed into 6.0.5-alpha  (revid:sp1r-igor@olga.mysql.com-20080602014342-25296) (version source revid:sp1r-igor@olga.mysql.com-20080602014342-25296) (pib:5)
[15 Dec 2008 17:39] Paul DuBois
Noted in 6.0.5 changelog.

Queries could return different results depending on whether the join
buffer was or was not used.
[16 Aug 2010 6:35] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:06] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[20 Nov 2010 23:07] Paul DuBois
Noted in 5.6.1 changelog.
[23 Nov 2010 2:09] Paul DuBois
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.