Bug #44250 Memory corruption when using BKA joins for a query that select blob values
Submitted: 13 Apr 2009 23:07 Modified: 20 Nov 2010 23:21
Reporter: Igor Babaev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:6.0 bzr OS:Any
Assigned to: Igor Babaev CPU Architecture:Any
Tags: BKA

[13 Apr 2009 23:07] Igor Babaev
Description:
When using the BKA algorithm with incremental join buffers to execute
join operations for a query over several tables that selects blob values
a memory corruption of join buffers may be watched.

How to repeat:
Load data from the attached file.

Execute the following commands:

set join_cache_level=8;
set join_buffer_size=2048;

SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
  FROM t1 JOIN  t2 JOIN  t3 JOIN  t4 JOIN  t5 
    WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and  t4.id2=t1.id2 AND
          t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D';

You'll see the following traces of a memory corruption:

mysql> SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
    ->   FROM t1 JOIN  t2 JOIN  t3 JOIN  t4 JOIN  t5
    ->     WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and  t4.id2=t1.id2 AND
    ->           t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D';
+-----------+------+-----------------------------------------------------------------------------------------+------------+------------+-------+
| id1       | num3 | text1                                                                                   | id4        | id3        | dummy |
+-----------+------+-----------------------------------------------------------------------------------------+------------+------------+-------+
| 228172702 |   14 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          | 2567095402 | 2667134182 |     0 |
| 228172702 |   15 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          | 2567095402 | 2667134182 |     0 |
| 228172702 |    3 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          | 2567095402 | 2667134182 |     0 |
| 228172702 |  134 | AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          | 2567095402 | 2667134182 |     0 |
| 228808822 |   61 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC |  826928662 |  935693782 |     0 |
| 228808822 |   13 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC |  826928662 |  935693782 |     0 |
| 228808822 |   60 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC |  826928662 |  935693782 |     0 |
| 228808822 |   13 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC |  826928662 |  935693782 |     0 |
| 228808822 |    3 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC |  826928662 |  935693782 |     0 |
| 228808822 |    4 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC |  826928662 |  935693782 |     0 |
| 228808822 |    6 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC |  826928662 |  935693782 |     0 |
| 228808822 |   17 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCd �1   |  826928662 |  935693782 |     0 |
| 228808822 |   50 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCd �1   |  826928662 |  935693782 |     0 |
| 228808822 |   18 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC |  826928662 |  935693782 |     0 |
| 228808822 |    1 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC |  826928662 |  935693782 |     0 |
| 228808822 |    3 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC |  826928662 |  935693782 |     0 |
| 228808822 |    4 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC |  826928662 |  935693782 |     0 |
| 228808822 |   89 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
| 228808822 |   19 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
| 228808822 |   84 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
| 228808822 |   14 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
| 228808822 |    9 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
| 228808822 |    1 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
| 228808822 |   10 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
| 228808822 |   26 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
| 228808822 |    4 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
| 228808822 |    3 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
| 228808822 |    1 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
| 228808822 |    3 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
| 228808822 |   28 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
| 228808822 |   62 | CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2381969632 | 2482416112 |     0 |
+-----------+------+-----------------------------------------------------------------------------------------+------------+------------+-------+
31 rows in set (0.03 sec)
[13 Apr 2009 23:12] Igor Babaev
This dump file is to be uploaded to reproduce thw bug.

Attachment: bug44250.sql (text/x-sql), 43.67 KiB.

[14 Apr 2009 6:21] Sveta Smirnova
Thank you for the report.

Verified as described.
[16 Apr 2009 11:36] 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/72276

2737 Igor Babaev	2009-04-15
      Fixed bug #44250.
      When the BKA algorithm is used for a join operation the records
      from the employed join buffer are uploaded into the join records
      buffers in order the pushdown condition to be checked. In a general
      case the records are uploaded not in the same order as they have
      been written into the join buffer. As a consequence when all
      matches for the records in the join buffer have been found the 
      record buffers only incidentally may contain the fields of the last
      record put into the join buffer. This breaks the proper flow of
      the BKA algorithm in the cases when incremental join buffers are
      employed. To fix this problem the values of the the fields of the
      last record written into the buffer are restored in record buffer
      just before the join buffer is reset for the next portion of records.
      These fields were restored in the previous code, but erroneously
      the restoration happened in the JOIN_CACHE::join_records method
      during the invocation of the function join_matching_records.
      The fact is the succeeded call of next_cache->join_records may
      overwrite that last record fields. This may lead to wrong result
      sets or even memory corruption if blob values are written into
      the join buffer. The test case reported in the bug demonstrated
      such a corruption.
      The submitted fix moved the restoration of the fields of the last
      record into the code of JOIN_CACHE::join_records. This restoration
      is performed before the join buffer is reset for writing or reading.
      A reset for reading is needed when an outer join operation is executed.
      modified:
        mysql-test/r/join_cache.result
        mysql-test/t/join_cache.test
        sql/sql_join_cache.cc
        sql/sql_select.h
[16 Apr 2009 13:10] 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/72296

2737 Igor Babaev	2009-04-15
      Fixed bug #44250.
      When the BKA algorithm is used for a join operation the records
      from the employed join buffer are uploaded into the join records
      buffers in order the pushdown condition to be checked. In a general
      case the records are uploaded not in the same order as they have
      been written into the join buffer. As a consequence when all
      matches for the records in the join buffer have been found the 
      record buffers only incidentally may contain the fields of the last
      record put into the join buffer. This breaks the proper flow of
      the BKA algorithm in the cases when incremental join buffers are
      employed. To fix this problem the values of the the fields of the
      last record written into the buffer are restored in record buffer
      just before the join buffer is reset for the next portion of records.
      These fields were restored in the previous code, but erroneously
      the restoration happened in the JOIN_CACHE::join_records method
      during the invocation of the function join_matching_records.
      The fact is the succeeded call of next_cache->join_records may
      overwrite that last record fields. This may lead to wrong result
      sets or even memory corruption if blob values are written into
      the join buffer. The test case reported in the bug demonstrated
      such a corruption.
      The submitted fix moved the restoration of the fields of the last
      record into the code of JOIN_CACHE::join_records. This restoration
      is performed before the join buffer is reset for writing or reading.
      A reset for reading is needed when an outer join operation is executed.
      modified:
        mysql-test/r/join_cache.result
        mysql-test/t/join_cache.test
        sql/sql_join_cache.cc
        sql/sql_select.h
[20 Apr 2009 15:47] Bugs System
Pushed into 6.0.11-alpha (revid:sergefp@mysql.com-20090417211236-fy28y9o1w8p4ic9m) (version source revid:igor@mysql.com-20090416022527-jd1s9z9hhiofqzya) (merge vers: 6.0.11-alpha) (pib:6)
[23 Apr 2009 1:36] Paul DuBois
Noted in 6.0.11 changelog.

Memory corruption of join buffers could occur when using the Batched
Key Access algorithm with incremental join buffers to execute join
operations for a query over several tables that selects BLOB values.
[16 Aug 2010 6:40] 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:15] 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:21] Paul DuBois
Noted in 5.6.1 changelog.
[23 Nov 2010 2:12] Paul DuBois
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.