Bug #2544 MySql crash if HAVING clause has an aggregate and a not aggregate condition

• Submitted: 28 Jan 2004 7:54
• Modified: 28 Jan 2004 9:22
• Reporter: Boldizsár Kavas
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.0-alpha
• OS: Windows (Windows 2000)
• CPU Architecture: Any

[28 Jan 2004 7:54] Boldizsár Kavas

Description:
MySql crashes if
 - a HAVING clause has (at least) an aggregate (MAX, MIN, AVG...) and a not aggregate condition,
 - the columns in the not aggregate condition are not listed in the preceeding GROUP BY,
 - the table type is InnoDB

How to repeat:
CREATE TABLE `dokumentum` (
  `DokumentumGUID` varchar(32) NOT NULL default '',
  `Verzio` smallint(5) unsigned NOT NULL default '1',
  `KategoriaGUID` varchar(32) default '',
  `Nev` varchar(100) NOT NULL default '',
  `Leiras` varchar(255) default NULL,
  `ErvenyesTol` date NOT NULL default '0000-00-00',
  `DokumentumHash` varchar(40) default NULL,
  `Tipus` varchar(25) default NULL,
  PRIMARY KEY  (`DokumentumGUID`,`Verzio`),
  KEY `KategoriaGUID` (`KategoriaGUID`),
) TYPE=InnoDB CHARSET=latin1\

Issue the following SQL statement, MySql will crash regardless whether you have data in the table or not:

select * from dokumentum
  group by DokumentumGUID
  having Verzio=MAX(Verzio) AND ErvenyesTol>'2003-12-02';

Notice, if you add ErvenyesTol to the group by part, then MySql will not fail:

select * from `test`.`dokumentum`
  group by DokumentumGUID, ErvenyesTol
  having Verzio=MAX(Verzio) AND ErvenyesTol>'2003-12-02';

[28 Jan 2004 9:22] Heikki Tuuri

Hi!

I cannot repeat the crash on Linux with 4.1.2. Please test with 4.1.2 when it is out!

Regards,

Heikki

heikki@hundin:~/mysql-4.1/client> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.2-alpha-debug

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

mysql> CREATE TABLE `dokumentum` (
    ->   `DokumentumGUID` varchar(32) NOT NULL default '',
    ->   `Verzio` smallint(5) unsigned NOT NULL default '1',
    ->   `KategoriaGUID` varchar(32) default '',
    ->   `Nev` varchar(100) NOT NULL default '',
    ->   `Leiras` varchar(255) default NULL,
    ->   `ErvenyesTol` date NOT NULL default '0000-00-00',
    ->   `DokumentumHash` varchar(40) default NULL,
    ->   `Tipus` varchar(25) default NULL,
    ->   PRIMARY KEY  (`DokumentumGUID`,`Verzio`),
    ->   KEY `KategoriaGUID` (`KategoriaGUID`),
    -> ) TYPE=InnoDB CHARSET=latin1\
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from dokumentum
    ->   group by DokumentumGUID
    ->   having Verzio=MAX(Verzio) AND ErvenyesTol>'2003-12-02';
Empty set (0.00 sec)

mysql> select * from `test`.`dokumentum`
    ->   group by DokumentumGUID, ErvenyesTol
    ->   having Verzio=MAX(Verzio) AND ErvenyesTol>'2003-12-02';
Empty set (0.00 sec)

mysql>