Bug #57954 BIT_AND function returns incorrect results when semijoin=on
Submitted: 3 Nov 2010 13:43 Modified: 6 Jan 2011 14:55
Reporter: SaiKumar V Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:mysql-next-mr-opt-backporting,5.5 OS:Linux (Rqd Hat x86)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: bit_and, join, regression, semijoin

[3 Nov 2010 13:43] SaiKumar V 
Description:
BIT_AND function returns incorrect results when semijoin is set to on.

mysql> SELECT  BIT_AND( `pk`  )   FROM C   WHERE `col_varchar_key`  IN (   SELECT `col_varchar_key`   FROM BB  ) order by pk;
+------------------+
| BIT_AND( `pk`  ) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> set optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT  BIT_AND( `pk`  )   FROM C   WHERE `col_varchar_key`  IN (   SELECT `col_varchar_key`   FROM BB  ) order by pk;
+----------------------+
| BIT_AND( `pk`  )     |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

How to repeat:
Bug found in :- mysql-next-mr-opt-backporting
and revision id :- roy.lyseng@oracle.com-20101026104350-eawnur5qjrsp0342.

This is a RQG failure.
Optimizer settings used for RQG are as below:
SET GLOBAL OPTIMIZER_SWITCH = 'semijoin=on';

CREATE TABLE C (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO C VALUES (11,0,NULL);
INSERT INTO C VALUES (16,1,'c');
INSERT INTO C VALUES (20,2,'d');
INSERT INTO C VALUES (13,166,'e');
INSERT INTO C VALUES (19,6,'f');
INSERT INTO C VALUES (9,8,'h');
INSERT INTO C VALUES (7,3,'j');
INSERT INTO C VALUES (12,5,'k');
INSERT INTO C VALUES (4,9,'k');
INSERT INTO C VALUES (3,3,'m');
INSERT INTO C VALUES (2,9,'m');
INSERT INTO C VALUES (17,9,'m');
INSERT INTO C VALUES (14,3,'n');
INSERT INTO C VALUES (10,53,'o');
INSERT INTO C VALUES (5,NULL,'r');
INSERT INTO C VALUES (15,0,'t');
INSERT INTO C VALUES (6,9,'t');
INSERT INTO C VALUES (8,8,'u');
INSERT INTO C VALUES (1,2,'w');
INSERT INTO C VALUES (18,5,'y');
CREATE TABLE BB (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO BB VALUES (10,8,NULL);

 
SELECT  BIT_AND( pk  )  
FROM C  
WHERE col_varchar_key  IN (  
SELECT col_varchar_key  
FROM BB  )  
ORDER  BY pk   ;
[10 Nov 2010 11:24] Roy Lyseng 
This is a join query that is fairly equivalent to the above subquery with BIT_AND():

SELECT c.* FROM c JOIN bb ON c.col_varchar_key=bb.col_varchar_key
ORDER BY c.pk;

It returns an empty result set.

Then we apply a BIT_AND aggregation to this query:

SELECT BIT_AND(c.pk) FROM c JOIN bb ON c.col_varchar_key=bb.col_varchar_key
ORDER BY c.pk;

+---------------+
| BIT_AND(c.pk) |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

According to the manual, this should return 18446744073709551615 if there are no matching rows.

Thus, it appears to be a general join problem and not a semijoin problem.
[10 Nov 2010 22:22] Sveta Smirnova 
Thank you for the feedback.

Additional circumstances verified as described.
[15 Nov 2010 18:33] Roy Lyseng 
This problem does not happen in 5.1
[16 Nov 2010 8:55] Roy Lyseng 
The source for the regression is probably the same as the source for bug#58050.
[1 Dec 2010 15:59] 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/125674

3135 Georgi Kodinov	2010-12-01
      Bug #57954: BIT_AND function returns incorrect results 
       when semijoin=on
      
      When setting the aggregate function as having no rows to report
      the function no_rows_in_result() was calling Item_sum::reset().
      However this function in addition to cleaning up the aggregate 
      value by calling aggregator_clear() was also adding the current
      value to the aggregate value by calling aggregator_add().
      Fixed by making no_rows_in_result() to call aggregator_clear()
      directly.
[1 Dec 2010 16:36] Georgi Kodinov 
bug #58050 marked as a duplicate of this one
[2 Dec 2010 13:54] Guilhem Bichot 
approved with comments about doc
[2 Dec 2010 14:30] 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/125816

3135 Georgi Kodinov	2010-12-02
      Bug #57954: BIT_AND function returns incorrect results 
       when semijoin=on
      
      When setting the aggregate function as having no rows to report
      the function no_rows_in_result() was calling Item_sum::reset().
      However this function in addition to cleaning up the aggregate 
      value by calling aggregator_clear() was also adding the current
      value to the aggregate value by calling aggregator_add().
      Fixed by making no_rows_in_result() to call aggregator_clear()
      directly.
      Renamed Item_sum::reset to Item_sum::reset_and_add() to
      and added a comment to avoid misinterpretation of what the
      function does.
[3 Dec 2010 9: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/125909

3135 Georgi Kodinov	2010-12-03
      Bug #57954: BIT_AND function returns incorrect results 
       when semijoin=on
      
      When setting the aggregate function as having no rows to report
      the function no_rows_in_result() was calling Item_sum::reset().
      However this function in addition to cleaning up the aggregate 
      value by calling aggregator_clear() was also adding the current
      value to the aggregate value by calling aggregator_add().
      Fixed by making no_rows_in_result() to call aggregator_clear()
      directly.
      Renamed Item_sum::reset to Item_sum::reset_and_add() to
      and added a comment to avoid misinterpretation of what the
      function does.
[8 Dec 2010 12:28] 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/126310

3135 Georgi Kodinov	2010-12-08
      Bug #57954: BIT_AND function returns incorrect results 
       when semijoin=on
      
      When setting the aggregate function as having no rows to report
      the function no_rows_in_result() was calling Item_sum::reset().
      However this function in addition to cleaning up the aggregate 
      value by calling aggregator_clear() was also adding the current
      value to the aggregate value by calling aggregator_add().
      Fixed by making no_rows_in_result() to call aggregator_clear()
      directly.
      Renamed Item_sum::reset to Item_sum::reset_and_add() to
      and added a comment to avoid misinterpretation of what the
      function does.
[8 Dec 2010 12:31] 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/126312

3179 Georgi Kodinov	2010-12-08
      Bug #57954: BIT_AND function returns incorrect results 
       when semijoin=on
      
      When setting the aggregate function as having no rows to report
      the function no_rows_in_result() was calling Item_sum::reset().
      However this function in addition to cleaning up the aggregate 
      value by calling aggregator_clear() was also adding the current
      value to the aggregate value by calling aggregator_add().
      Fixed by making no_rows_in_result() to call aggregator_clear()
      directly.
      Renamed Item_sum::reset to Item_sum::reset_and_add() to
      and added a comment to avoid misinterpretation of what the
      function does.
[17 Dec 2010 12:53] Bugs System 
Pushed into mysql-5.5 5.5.9 (revid:georgi.kodinov@oracle.com-20101217124733-p1ivu6higouawv8l) (version source revid:georgi.kodinov@oracle.com-20101208122806-h366jl20zen35xm4) (merge vers: 5.5.8) (pib:24)
[17 Dec 2010 12:57] Bugs System 
Pushed into mysql-trunk 5.6.1 (revid:georgi.kodinov@oracle.com-20101217125013-y8pb3az32rtbplc9) (version source revid:anitha.gopi@sun.com-20101210041312-50t9adyhwwybsm6x) (merge vers: 5.6.1) (pib:24)
[6 Jan 2011 14:55] Paul DuBois 
Noted in 5.5.9 changelog.

The BIT_AND() function could return incorrect results when a join
returned no matching rows.