Bug #37131 Query returns different results when executed with join buffer and without
Submitted: 1 Jun 2008 22:47 Modified: 15 Dec 2008 18:39
Reporter: Igor Babaev
Status: Closed
Category:Server: Optimizer Severity:S1 (Critical)
Version:mysql-6.0-bka-preview OS:Any
Assigned to: Bugs System Target Version:6.0-beta
Triage: Triaged: D2 (Serious)

[1 Jun 2008 22: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 3: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 12: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 18: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.