Bug #11571 server-crash while performing subquery with "group by" and "where"
Submitted: 25 Jun 2005 13:40 Modified: 25 Jun 2005 14:02
Reporter: Markus Becker Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.4-beta OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[25 Jun 2005 13:40] Markus Becker
Description:
The mysql-service in windows xp completeley crashes while performing a subquery containing "group by" in combination with "where".
(without the "where"-condition in the subquery the query is performed properly)

How to repeat:
drop table if exists status_at_date;

CREATE TABLE `status_at_date` (
  `dsince` date NOT NULL,
  `id` int(10) unsigned NOT NULL,
  `k01` varchar(45) default NULL,
  `k02` varchar(45) default NULL,
  PRIMARY KEY  (`dsince`,`id`)
);

INSERT INTO STATUS_AT_DATE (dsince,id,k01,k02)
values
("2004-01-01",1,"1","1"),
("2005-01-01",1,"2","1"),
("2005-05-01",1,"2","2"),
("2004-01-01",2,"1","1"),
("2005-01-01",2,"2","1"),
("2005-06-01",2,"2","2");

# the following query should show the status of the
# k01 and k02 coloumn for each id to the given date
# e.g. "2005-05-01"
#
# expected result:
#   dsince      id    k01    k02
#   2005-05-01  1     2      2
#   2005-01-01  2     2      1
#
# but it causes the server-crash:

select * from status_at_date where (id,dsince)
IN (
  select id,max(dsince) from status_at_date
  where dsince <= "2005-05-01" group by id
);
[25 Jun 2005 14:02] Vasily Kishkin
Sorry. I was not able to reproduce the bug on 5.0.8 beta. Probably the bug was fixed.