MySQL Bugs: #58050: BIT_OR and BIT_XOR return incorrect results when semijoin=on

Bug #58050	BIT_OR and BIT_XOR return incorrect results when semijoin=on
Submitted:	8 Nov 2010 12:33	Modified:	1 Dec 2010 16:36
Reporter:	SaiKumar V	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	mysql-next-mr-opt-backporting,5.5	OS:	Linux (RedHat x64)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	bit_or, bit_xor, join, regression, semijoin

Description:
BIT_OR and BIT_XOR return incorrect results when semijoin=on i.e. when rows are not matched they are supposed to return 0.

mysql> Set OPTIMIZER_switch='SEMIJOIN=ON';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT  BIT_OR( `col_int_nokey`  )  FROM B  WHERE ( `col_varchar_nokey`   )  IN (  SELECT `col_varchar_key`  FROM BB  )  AND `pk`  =  1   ;
+----------------------------+
| BIT_OR( `col_int_nokey`  ) |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

mysql> Set OPTIMIZER_switch='SEMIJOIN=OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT  BIT_OR( `col_int_nokey`  )  FROM B  WHERE ( `col_varchar_nokey`   )  IN (  SELECT `col_varchar_key`  FROM BB  )  AND `pk`  =  1   ;
+----------------------------+
| BIT_OR( `col_int_nokey`  ) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

AND,

mysql> Set OPTIMIZER_switch='SEMIJOIN=ON';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT  BIT_XOR( pk  )  FROM C  WHERE col_varchar_nokey  IN (  SELECT col_varchar_key  FROM CC  )  AND pk  =  8   ;
+----------------+
| BIT_XOR( pk  ) |
+----------------+
|              8 |
+----------------+
1 row in set (0.00 sec)

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

mysql> SELECT  BIT_XOR( pk  )  FROM C  WHERE col_varchar_nokey  IN (  SELECT col_varchar_key  FROM CC  )  AND pk  =  8   ;
+----------------+
| BIT_XOR( pk  ) |
+----------------+
|              0 |
+----------------+
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

TESTCASE BIT_OR

CREATE TABLE BB (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_nokey int(11) DEFAULT NULL,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_varchar_nokey 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,8,NULL,NULL);
CREATE TABLE B (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_nokey int(11) DEFAULT NULL,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_varchar_nokey 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=2 DEFAULT CHARSET=latin1;
INSERT INTO B VALUES (1,1,7,'f','f');

 
SELECT  BIT_OR( col_int_nokey  )  FROM B  WHERE ( col_varchar_nokey   )  IN (  SELECT col_varchar_key  FROM BB  )  AND pk  =  1   ;

TESTCASE BIT_XOR

CREATE TABLE CC (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_varchar_nokey 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=30 DEFAULT CHARSET=latin1;
INSERT INTO CC VALUES (10,8,'v','v');
INSERT INTO CC VALUES (11,9,'r','r');
INSERT INTO CC VALUES (12,9,'a','a');
INSERT INTO CC VALUES (13,186,'m','m');
INSERT INTO CC VALUES (14,NULL,'y','y');
INSERT INTO CC VALUES (15,2,'j','j');
INSERT INTO CC VALUES (16,3,'d','d');
INSERT INTO CC VALUES (17,0,'z','z');
INSERT INTO CC VALUES (18,133,'e','e');
INSERT INTO CC VALUES (19,1,'h','h');
INSERT INTO CC VALUES (20,8,'b','b');
INSERT INTO CC VALUES (21,5,'s','s');
INSERT INTO CC VALUES (22,5,'e','e');
INSERT INTO CC VALUES (23,8,'j','j');
INSERT INTO CC VALUES (24,6,'e','e');
INSERT INTO CC VALUES (25,51,'f','f');
INSERT INTO CC VALUES (26,4,'v','v');
INSERT INTO CC VALUES (27,7,'x','x');
INSERT INTO CC VALUES (28,6,'m','m');
INSERT INTO CC VALUES (29,4,'c','c');
CREATE TABLE C (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_varchar_nokey 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 (1,2,'w','w');
INSERT INTO C VALUES (2,9,'m','m');
INSERT INTO C VALUES (3,3,'m','m');
INSERT INTO C VALUES (4,9,'k','k');
INSERT INTO C VALUES (5,NULL,'r','r');
INSERT INTO C VALUES (6,9,'t','t');
INSERT INTO C VALUES (7,3,'j','j');
INSERT INTO C VALUES (8,8,'u','u');
INSERT INTO C VALUES (9,8,'h','h');
INSERT INTO C VALUES (10,53,'o','o');
INSERT INTO C VALUES (11,0,NULL,NULL);
INSERT INTO C VALUES (12,5,'k','k');
INSERT INTO C VALUES (13,166,'e','e');
INSERT INTO C VALUES (14,3,'n','n');
INSERT INTO C VALUES (15,0,'t','t');
INSERT INTO C VALUES (16,1,'c','c');
INSERT INTO C VALUES (17,9,'m','m');
INSERT INTO C VALUES (18,5,'y','y');
INSERT INTO C VALUES (19,6,'f','f');
INSERT INTO C VALUES (20,2,'d','d');

 
SELECT  BIT_XOR( pk  )  FROM C  WHERE col_varchar_nokey  IN (  SELECT col_varchar_key  FROM CC  )  AND pk  =  8   ;

It appears that this is a general problem with joining and bit_or() and bit_xor().

Here is a join query that is fairly equivalent to the above semijoin query with BIT_OR():

select b.* from b join bb on b.col_varchar_nokey=bb.col_varchar_key and b.pk=1;

It returns an empty result set.

Then we apply a BIT_OR aggregation to this query:

select bit_or(b.col_int_nokey)
from b join bb on b.col_varchar_nokey=bb.col_varchar_key and b.pk=1;

+-------------------------+
| bit_or(b.col_int_nokey) |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

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

Another join query that is fairly equivalent to the above semijoin query with BIT_XOR():

SELECT c.* FROM c JOIN cc ON c.col_varchar_nokey=cc.col_varchar_key
  AND c.pk = 8;

It returns an empty result set.

Then we apply a BIT_XOR aggregation to this query:

SELECT BIT_XOR(c.pk) FROM c JOIN cc ON c.col_varchar_nokey=cc.col_varchar_key
  AND c.pk = 8;

+---------------+
| BIT_XOR(c.pk) |
+---------------+
|             8 |
+---------------+
1 row in set (0.00 sec)

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

Thus it appears that this is a general join problem and not a semijoin problem.

The problem with the join query appears in 5.5 as well as in next-mr, but it does not occur in 5.1

Simplified test case for bug#58050 and bug#57954:

CREATE TABLE t1 (
  pk int PRIMARY KEY,
  b int,
  c int
) ENGINE=InnoDB;
INSERT INTO t1 VALUES(1, 1, 1);

CREATE TABLE t2 (
  pk int PRIMARY KEY,
  b int,
  c int
) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1, 1, NULL);

SELECT t1.*
FROM t1 JOIN t2 ON t1.c=t2.c
WHERE t1.pk=1;

SELECT BIT_OR(t1.b)
FROM t1 JOIN t2 ON t1.c=t2.c
WHERE t1.pk=1;

SELECT BIT_AND(t1.b)
FROM t1 JOIN t2 ON t1.c=t2.c
WHERE t1.pk=1;

SELECT BIT_XOR(t1.b)
FROM t1 JOIN t2 ON t1.c=t2.c
WHERE t1.pk=1;

DROP TABLE t1, t2;

Running bzrfind on the 5.5 tree gives this source for regression:

            revno: 2875.8.8
            revision-id: joro@sun.com-20090928072125-cbx5j0v2oe5d7av6
            parent: alik@sun.com-20090925094758-j4rfs37jf3v482t9
            committer: Georgi Kodinov <joro@sun.com>
            branch nick: mysql-wl3220-next-mr
            timestamp: Mon 2009-09-28 10:21:25 +0300
            message:
              Ported WL#3220 to mysql-next-mr.

Fixed by the fix for bug #57954