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
• Status: Closed
• 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 [e-mail] +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