Bug #49322 Server is adding extra NULL row on processing a WHERE clause
Submitted: 2 Dec 2009 3:20 Modified: 11 Mar 2011 12:48
Reporter: Patrick Crews Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: extra rows, where

[2 Dec 2009 3:20] Patrick Crews 
Description:
The server (all versions) is returning an extra NULL row when processing a WHERE clause:

SELECT table2.pk, table1.col_int_key, table2.col_int_key, table4.col_int_key, table1.pk FROM K table1  RIGHT  JOIN H table2  ON table1 .`col_int_key`  = table2 .`col_int_key`  RIGHT  JOIN M table4  ON table2 .`col_int_key`  = table4 .`col_int_key`;
pk	col_int_key	col_int_key	col_int_key	pk
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL
4	2	2	2	2
4	2	2	2	4
5	2	2	2	2
5	2	2	2	4
NULL	NULL	NULL	5	NULL
NULL	NULL	NULL	5	NULL
NULL	NULL	NULL	7	NULL
NULL	NULL	NULL	7	NULL
NULL	NULL	NULL	9	NULL

SELECT table2.pk, table1.col_int_key, table2.col_int_key, table4.col_int_key, table1.pk FROM K table1  RIGHT  JOIN H table2  ON table1 .`col_int_key`  = table2 .`col_int_key`  RIGHT  JOIN M table4  ON table2 .`col_int_key`  = table4 .`col_int_key` WHERE table1.pk IS NULL ;
pk	col_int_key	col_int_key	col_int_key	pk
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	2	NULL    <- This shouldn't be here
NULL	NULL	NULL	5	NULL
NULL	NULL	NULL	5	NULL
NULL	NULL	NULL	7	NULL
NULL	NULL	NULL	7	NULL
NULL	NULL	NULL	9	NULL

How to repeat:
MTR test case.  This is setup for use with 6.0, please comment out SET statements as needed for earlier versions of the server

#/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */

/*!50400 SET SESSION optimizer_switch = 'firstmatch=off,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=off,materialization=off,semijoin=off' */;
/*!50400 SET SESSION optimizer_use_mrr = 'force' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
/*!50400 SET SESSION join_cache_level = 1 */;
/*!50400 SET SESSION debug = 'd,optimizer_no_icp,optimizer_innodb_ds_mrr' */;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ H;
DROP TABLE /*! IF EXISTS */ M;
DROP TABLE /*! IF EXISTS */ K;
--enable_warnings

CREATE TABLE `H` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
INSERT INTO `H` VALUES (1,NULL);
INSERT INTO `H` VALUES (4,2);
INSERT INTO `H` VALUES (5,2);
INSERT INTO `H` VALUES (3,4);
INSERT INTO `H` VALUES (2,8);
CREATE TABLE `M` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
INSERT INTO `M` VALUES (1,2);
INSERT INTO `M` VALUES (2,7);
INSERT INTO `M` VALUES (3,5);
INSERT INTO `M` VALUES (4,7);
INSERT INTO `M` VALUES (5,5);
INSERT INTO `M` VALUES (6,NULL);
INSERT INTO `M` VALUES (7,NULL);
INSERT INTO `M` VALUES (8,9);
CREATE TABLE `K` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
INSERT INTO `K` VALUES (1,9);
INSERT INTO `K` VALUES (2,2);
INSERT INTO `K` VALUES (3,5);
INSERT INTO `K` VALUES (4,2);
INSERT INTO `K` VALUES (5,7);
INSERT INTO `K` VALUES (6,0);
INSERT INTO `K` VALUES (7,5);

 # original failing query
SELECT table2 .pk  
FROM K table1  RIGHT  JOIN H table2  ON table1 .`col_int_key`  = table2 .`col_int_key`  RIGHT  JOIN M table4  ON table2 .`col_int_key`  = table4 .`col_int_key`  
WHERE table1 .`pk`  IS  NULL   ;

# these next two are just to illustrate what is happening
SELECT table2.pk, table1.col_int_key, table2.col_int_key, table4.col_int_key, table1.pk FROM K table1  RIGHT  JOIN H table2  ON table1 .`col_int_key`  = table2 .`col_int_key`  RIGHT  JOIN M table4  ON table2 .`col_int_key`  = table4 .`col_int_key`;

SELECT table2.pk, table1.col_int_key, table2.col_int_key, table4.col_int_key, table1.pk FROM K table1  RIGHT  JOIN H table2  ON table1 .`col_int_key`  = table2 .`col_int_key`  RIGHT  JOIN M table4  ON table2 .`col_int_key`  = table4 .`col_int_key` WHERE table1.pk IS NULL ;

/* Diff: */

/* --- /tmp//randgen7417-1259627817-server0.dump	2009-11-30 19:36:57.000000000 -0500
# +++ /tmp//randgen7417-1259627817-server1.dump	2009-11-30 19:36:57.000000000 -0500
# @@ -5,4 +5,3 @@
#  NULL
#  NULL
#  NULL
# -NULL */

DROP TABLE H;
DROP TABLE M;
DROP TABLE K;
#/* End of test case for query 0 */
[23 Nov 2010 8:53] MySQL Verification Team 
a neater testcase from from 5.1.53

drop table if exists h,m,k;
create table `h`(`pk` int not null,`col_int_key` int) engine=myisam;
insert into `h` values (1,null),(4,2),(5,2),(3,4),(2,8);

create table `m` (`pk` int not null,`col_int_key` int) engine=myisam ;
insert into `m` values (1,2),(2,7),(3,5),(4,7),(5,5),(6,null),(7,null),(8,9);
create table `k` (`pk` int not null,`col_int_key` int) engine=myisam;
insert into `k` values (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5);

/*exhibit A*/select table1.pk from k table1
right join h table2 on table1.`col_int_key`=table2.`col_int_key` 
right join m table4 on table2.`col_int_key`=table4.`col_int_key`;

/*exhibit B*/select table1.pk from k table1
right join h table2 on table1.`col_int_key`=table2.`col_int_key` 
right join m table4 on table2.`col_int_key`=table4.`col_int_key`
where table1.pk is null;

Notice the extra NULL that appears in exhibit B:

mysql> /*exhibit A*/select table1.pk from k table1
    -> right join h table2 on table1.`col_int_key`=table2.`col_int_key`
    -> right join m table4 on table2.`col_int_key`=table4.`col_int_key`
+------+
| pk   |
+------+
|    2 |
|    4 |
|    2 |
|    4 |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+
11 rows in set (0.00 sec)

mysql> /*exhibit B*/select table1.pk from k table1
    -> right join h table2 on table1.`col_int_key`=table2.`col_int_key`
    -> right join m table4 on table2.`col_int_key`=table4.`col_int_key`
    -> where table1.pk is null;
+------+
| pk   |
+------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+
8 rows in set (0.00 sec)
[25 Nov 2010 17:54] Ole John Aske 
I suspect this to be a duplicate of bug#58490 which there has been committed a (still unreviewed) fix for.
[25 Nov 2010 22:24] Ole John Aske 
Has tested these testcases with proposed fix for bug#58490. All reported issues are fixed with this, http://lists.mysql.com/commits/125027
[26 Jan 2011 7:25] Ole John Aske 
Closed - as it is a duplicate of bug#58490.
[1 Feb 2011 14:26] Bugs System 
Pushed into mysql-trunk 5.6.2 (revid:ole.john.aske@oracle.com-20110201142457-3hdefsxj12q0acvl) (version source revid:ole.john.aske@oracle.com-20110201142457-3hdefsxj12q0acvl) (merge vers: 5.6.2) (pib:24)
[1 Feb 2011 14:26] Bugs System 
Pushed into mysql-5.5 5.5.10 (revid:ole.john.aske@oracle.com-20110201141934-ppx2z4tn6vv0u8q9) (version source revid:ole.john.aske@oracle.com-20110201141934-ppx2z4tn6vv0u8q9) (merge vers: 5.5.10) (pib:24)
[2 Feb 2011 7:48] 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/130176

4163 Ole John Aske	2011-02-02
      Fix for bug#58490: 'Incorrect result in multi level OUTER JOIN', cherry picked into telco branches.
      Also fix the duplicate bug#49322.
      
      Se original commit http://lists.mysql.com/commits/130129 for comments
[2 Feb 2011 7:48] Bugs System 
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.22 (revid:ole.john.aske@oracle.com-20110202074752-4c6jzaiwxvf7rngl) (version source revid:ole.john.aske@oracle.com-20110202074752-4c6jzaiwxvf7rngl) (merge vers: 5.1.51-ndb-7.0.22) (pib:24)
[11 Mar 2011 12:48] Jon Stephens 
Closed as duplicate of BUG#58490 per Ole John's comments above.