Bug #860 Wrong result with subselect in the select list and with IN() clause
Submitted: 17 Jul 2003 1:19 Modified: 1 Sep 2003 4:02
Reporter: Victoria Reznichenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.0 OS:Any (any)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[17 Jul 2003 1:19] Victoria Reznichenko
Description:
Subselect in the SELECT list is executed only for the first value from IN().

mysql> select userid,pmtotal,pmnew, (select count(rd) from m where
    -> toid=p.userid) calc_total, (select count(rd) from m where rd=0 and
    -> toid=p.userid) calc_new from p where userid in (select distinct toid from m);
+--------+---------+-------+------------+----------+
| userid | pmtotal | pmnew | calc_total | calc_new |
+--------+---------+-------+------------+----------+
|      1 |       0 |     0 |          9 |        3 |
|      2 |       0 |     0 |       NULL |     NULL |
+--------+---------+-------+------------+----------+
2 rows in set (0.00 sec)

How to repeat:
create table m(
toid int,
rd int);

create table p(
userid int,
pmnew int,
pmtotal int);

insert into p values
(1,0,0),
(2,0,0);

insert into m values
(1,0),
(1,0),
(1,0),
(1,12),
(1,15),
(1,123),
(1,12312),
(1,12312),
(1,123),
(2,0),
(2,0),
(2,1),
(2,2);
[27 Aug 2003 14:28] Oleksandr Byelkin
ChangeSet 
  1.1587 03/08/28 03:10:14 bell@laptop.sanja.is.com.ua +7 -0 
  reset() split in 2 function clear() & add() 
  aggregate function reinitialization (if no rows was found) made with help of 
clear() 
  (fixed BUG#860)
[1 Sep 2003 4:02] Oleksandr Byelkin
patch have been pushed