Bug #32455 Problem in innodb with a HAVING xxx IS NULL While using a GROUP BY
Submitted: 16 Nov 2007 18:29 Modified: 16 Nov 2007 22:40
Reporter: Ian Lord Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.45-log OS:FreeBSD
Assigned to: CPU Architecture:Any
Tags: having, innodb, IS NULL

[16 Nov 2007 18:29] Ian Lord
Description:
There is a bug in innodb when you use a "HAVING IS NULL" while using a group by. 

Check below for further details

How to repeat:
### Table creation ###
 CREATE TABLE `tTest` (
`pkiTestID` TINYINT UNSIGNED NOT NULL ,
PRIMARY KEY ( `pkiTestID` )
) ENGINE = MYISAM;

 CREATE TABLE `tTest2` (
`pkiTest2ID` TINYINT UNSIGNED NOT NULL ,
`fkiTestID` TINYINT UNSIGNED NULL ,
PRIMARY KEY ( `pkiTest2ID` )
) ENGINE = MYISAM;

INSERT INTO `tTest` (
`pkiTestID`
)
VALUES (
'1'
), (
'2'
), (
'3'
), (
'4'
), (
'5'
);

INSERT INTO `tTest2` (
`pkiTest2ID` ,
`fkiTestID`
)
VALUES (
'1', '1'
), (
'2', '2'
), (
'3', NULL
), (
'4', '4'
), (
'5', '5'
);

### Select into the mysql TABLE Showing proper results ###
SELECT
	tTest2.fkiTestID
	
FROM
	tTest2
	
LEFT JOIN (
	tTest
)
ON (
	tTest.pkiTestID = tTest2.fkiTestID
)
	
GROUP BY
	tTest2.pkiTest2ID
HAVING
	tTest2.fkiTestID IS NULL;

+-----------+
| fkiTestID |
+-----------+
|      NULL | 
+-----------+

### Changing the engine to INNODB To reproduce the problem ###
ALTER TABLE `tTest2`  ENGINE = InnoDB;

### Same query, wrong result ###

SELECT
	tTest2.fkiTestID
	
FROM
	tTest2
	
LEFT JOIN (
	tTest
)
ON (
	tTest.pkiTestID = tTest2.fkiTestID
)
	
GROUP BY
	tTest2.pkiTest2ID
HAVING
	tTest2.fkiTestID IS NULL;

+-----------+
| fkiTestID |
+-----------+
|         2 | 
+-----------+

Suggested fix:
It appears that in INNODB, the row preceding the good one is returned instead.

Row three should be returned, but in this case the row before gets returned.

We can further diagnose this by doing this:

### Adding another row as null ###
UPDATE `tTest2` SET `fkiTestID` = NULL WHERE `tTest2`.`pkiTest2ID` =2;

### Issuing the same query again ###
SELECT
	tTest2.fkiTestID
	
FROM
	tTest2
	
LEFT JOIN (
	tTest
)
ON (
	tTest.pkiTestID = tTest2.fkiTestID
)
	
GROUP BY
	tTest2.pkiTest2ID
HAVING
	tTest2.fkiTestID IS NULL;
+-----------+
| fkiTestID |
+-----------+
|         1 | 
|      NULL | 
+-----------+

###

As we can see, rows 1 and 2 have been returned instead of rows 2 and 3

### Finally, if we change the first one, let see what happens ###
UPDATE `tTest2` SET `fkiTestID` = NULL WHERE `tTest2`.`pkiTest2ID` =1;

### Issuing the same query again ###
SELECT
	tTest2.fkiTestID
	
FROM
	tTest2
	
LEFT JOIN (
	tTest
)
ON (
	tTest.pkiTestID = tTest2.fkiTestID
)
	
GROUP BY
	tTest2.pkiTest2ID
HAVING
	tTest2.fkiTestID IS NULL;
+-----------+
| fkiTestID |
+-----------+
|      NULL | 
|      NULL | 
+-----------+

###
We can see that only rows 1 and 2 gets returned instead of 1,2 and 3 so It should confirm that it is really a problem with an index...

It's probably a small bug with someone starting to count at 0 vs another starting to count at 1

Good luck
[16 Nov 2007 22:40] MySQL Verification Team
Thank you for the bug report. I wasn't able to repeat with server built with
current source tree, please wait for next release or build from the source:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.52-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>  CREATE TABLE `tTest` (
    -> `pkiTestID` TINYINT UNSIGNED NOT NULL ,
    -> PRIMARY KEY ( `pkiTestID` )
    -> ) ENGINE = MYISAM;
Query OK, 0 rows affected (0.01 sec)

mysql>  CREATE TABLE `tTest2` (
    -> `pkiTest2ID` TINYINT UNSIGNED NOT NULL ,
    -> `fkiTestID` TINYINT UNSIGNED NULL ,
    -> PRIMARY KEY ( `pkiTest2ID` )
    -> ) ENGINE = MYISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> INSERT INTO `tTest` (`pkiTestID` VALUES ('1'), ('2'), ('3'), ('4'), ('5');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES ('1'), ('2'), ('3'), ('4'), ('5')' at line 1
mysql> INSERT INTO `tTest2` (`pkiTest2ID` ,`fkiTestID`) VALUES ('1', '1'), ('2', '2'),
    -> ('3', NULL), ('4', '4'), ('5', '5');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT tTest2.fkiTestID FROM tTest2 LEFT JOIN (tTest ) ON
    -> (tTest.pkiTestID = tTest2.fkiTestID) GROUP BY tTest2.pkiTest2ID
    -> HAVING tTest2.fkiTestID IS NULL;
+-----------+
| fkiTestID |
+-----------+
|      NULL | 
+-----------+
1 row in set (0.03 sec)

mysql> 
mysql> ALTER TABLE `tTest2`  ENGINE = InnoDB;
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT tTest2.fkiTestID FROM tTest2 LEFT JOIN (tTest ) ON
    -> (tTest.pkiTestID = tTest2.fkiTestID) GROUP BY tTest2.pkiTest2ID
    -> HAVING tTest2.fkiTestID IS NULL;
+-----------+
| fkiTestID |
+-----------+
|      NULL | 
+-----------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE tTest2\G
*************************** 1. row ***************************
       Table: tTest2
Create Table: CREATE TABLE `tTest2` (
  `pkiTest2ID` tinyint(3) unsigned NOT NULL,
  `fkiTestID` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`pkiTest2ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>