Bug #31399 Wrong query result when doing join buffering over BIT fields
Submitted: 4 Oct 2007 15:04 Modified: 23 Nov 2010 3:32
Reporter: Timour Katchaounov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1-bk, 6.0 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any

[4 Oct 2007 15:04] Timour Katchaounov 
Description:
In the case when:
- BIT fields are stored in Field_bit (MyISAM, InnoDB, but not MEMORY), and
- the number of bits is not a multiple of 8,
when running the query below with the semi-join algorithm, the
result is incorrect. Most likely a coincidence, but  I could only
reproduce the bug with < 8 bits (e.g. 9 bits work fine).

The test case below executes the same query with all three possible
methods. One can see that when semi-join is used, the result is wrong.

How to repeat:
/* Test with 7-BIT columns. */
drop table if exists t1bit7, t2bit7;

create table t1bit7 (a1 bit(7) not null) engine=MyISAM;
create table t2bit7 (b1 bit(7) not null) engine=MyISAM;

insert into t1bit7 values (b'1100000');
insert into t1bit7 values (b'1100001');
insert into t1bit7 values (b'1100010');

insert into t2bit7 values (b'1100001');
insert into t2bit7 values (b'1100010');
insert into t2bit7 values (b'1100110');

-- IN=>EXISTS
set @@optimizer_switch='no_materialization,no_semijoin';

explain
select bin(a1) from t1bit7
where a1 in (select b1 from t2bit7);

select bin(a1) from t1bit7
where a1 in (select b1 from t2bit7);

-- semi-join
set @@optimizer_switch='no_materialization';

explain
select bin(a1) from t1bit7
where a1 in (select b1 from t2bit7);

select bin(a1) from t1bit7
where a1 in (select b1 from t2bit7);

-- materialization
set @@optimizer_switch='no_semijoin';

explain
select bin(a1) from t1bit7
where a1 in (select b1 from t2bit7);

select bin(a1) from t1bit7
where a1 in (select b1 from t2bit7);

Suggested fix:
Most likely the problem is in the methods used to compare BIT
fields. Few of those methods compare directly the data in
table->record[0], but do not take into account that bit fields
[29 Apr 2008 22:02] Sergey Petrunya 
The problem is not in semi-join implementation per se, one can get the same error with joins:

mysql> explain select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1; 
+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t1bit7 | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                | 
|  1 | SIMPLE      | t2bit7 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer | 
+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+
2 rows in set (0.01 sec)

mysql>  select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1; 
+---------+
| bin(a1) |
+---------+
| 1100010 | 
| 1100010 | 
| 1100010 | 
+---------+
3 rows in set (0.01 sec)
[29 Apr 2008 23:15] Sergey Petrunya 
The problem is actually incorrect handling of BIT fields by join buffering. MySQL 5.1 is also affected (see 5.1.25-bk, tip cset ChangeSet@1.2581, 2008-04-23 20:17:14+05:00, gshchepa@host.loc +1 -0 ):

#
# This uses join buffering and produces incorrect result:
#

mysql> explain select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1; 
+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t1bit7 | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                | 
|  1 | SIMPLE      | t2bit7 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer | 
+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+

mysql> select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1; 
+---------+
| bin(a1) |
+---------+
| 1100010 | 
| 1100010 | 
| 1100010 | 
+---------+

# Now, making this change in the source to disable join buffering:
--- 1.589/sql/sql_select.cc
+++ edited/sql_select.cc
@@ -6444,6 +6444,7 @@ make_join_readinfo(JOIN *join, ulonglong
         If the incoming data set is already sorted don't use cache.
       */
       table->status=STATUS_NO_RECORD;
+      options|= SELECT_NO_JOIN_CACHE;
       if (i != join->const_tables && !(options & SELECT_NO_JOIN_CACHE) &&
           tab->use_quick != 2 && !tab->first_inner && !ordered_set)
       {

#
# This disables join buffering:
#
mysql> explain  select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1; 
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1bit7 | ALL  | NULL          | NULL | NULL    | NULL |    3 |             | 
|  1 | SIMPLE      | t2bit7 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where | 
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

#
# And the query result is now correct:
#
mysql>  select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1; 
+---------+
| bin(a1) |
+---------+
| 1100001 | 
| 1100010 | 
+---------+
2 rows in set (0.00 sec)
[29 Apr 2008 23:16] Sergey Petrunya 
Changing synopsis and version to reflect the actual bug cause.
[4 Jul 2008 11:45] 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/49009

2657 Sergey Glukhov	2008-07-04
      Bug#31399 Wrong query result when doing join buffering over BIT fields
      set correct position for bit field in the record
[20 Nov 2008 11:09] 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/59354

2717 Sergey Glukhov	2008-11-20
      Bug#31399 Wrong query result when doing join buffering over BIT fields
      if table has bit fields then uneven bits(if exist) are stored into null bits place.
      So we need to copy null bits in case of bit field presence.
[5 Dec 2008 10: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/60711

2717 Sergey Glukhov	2008-12-05
      Bug#31399 Wrong query result when doing join buffering over BIT fields
      if table has bit fields then uneven bits(if exist) are stored into null bits place.
      So we need to copy null bits in case of uneven bit field presence.
[9 Dec 2008 13:00] 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/61056

2741 Sergey Glukhov	2008-12-09
      Bug#31399 Wrong query result when doing join buffering over BIT fields
      if table has bit fields then uneven bits(if exist) are stored into null bits place.
      So we need to copy null bits in case of uneven bit field presence.
[6 Jan 2009 13:56] Bugs System 
Pushed into 5.0.76 (revid:joro@sun.com-20090105160414-8q9j4bi1klkfwiup) (version source revid:azundris@mysql.com-20081230114734-nmsc37ak330zlygn) (merge vers: 5.0.76) (pib:6)
[6 Jan 2009 14:21] 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/62528

2944 Georgi Kodinov	2009-01-06
      - merged bug #31399 to 6.0-main (has BKA)
      - updated a 6.0 specific test suite file to reflect 5.x test additions.
[9 Jan 2009 1:33] Paul DuBois 
Noted in 5.0.76 changelog.

Queries executed using join buffering of BIT columns could produce
incorrect results. 

Setting report to NDI pending push into 5.1.x/6.0.x.
[15 Jan 2009 6:35] Bugs System 
Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)
[15 Jan 2009 16:17] Paul DuBois 
Noted in 5.1.31 changelog.

Setting report to NDI pending push into 6.0.x.
[19 Jan 2009 11:27] Bugs System 
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:05] Bugs System 
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 15:11] Jon Stephens 
Setting status back to NDI pending merge to 6.0 tree.
[19 Jan 2009 16:11] Bugs System 
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[20 Jan 2009 18:57] Bugs System 
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:joro@sun.com-20090106141937-pnfrfzgwe371dsaa) (merge vers: 6.0.10-alpha) (pib:6)
[28 Jan 2009 22:00] Paul DuBois 
Noted in 6.0.10 changelog.
[11 May 2010 16:20] 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/108011

3164 Evgeny Potemkin	2010-05-11
      Backporting: fixed lame merge of the fix for the bug#31399.
      Test case result adjusted accordingly.
[16 Aug 2010 6:38] 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:07] 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)
[23 Nov 2010 3:32] Paul DuBois 
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.