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
);
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 );