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: | |
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
[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>