Bug #1536 wrong result when using count and subquery
Submitted: 11 Oct 2003 22:21 Modified: 17 Oct 2003 5:22
Reporter: hey boy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S1 (Critical)
Version:4.1.0 OS:Linux (red hat linux 8.0)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[11 Oct 2003 22:21] hey boy 
Description:
I am using the following table

create table TABLE_A (
E1 INTEGER UNSIGNED NOT NULL, 
E2 INTEGER UNSIGNED NOT NULL, 
E3 INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(E1)
);

insert into TABLE_A VALUES(1,1,1);
insert into TABLE_A VALUES(2,2,1);

The number of records return should be 2.
select count(*) from TABLE_A
INNER JOIN 
(
    SELECT A.E1, A.E2, A.E3 FROM TABLE_A AS A WHERE
        A.E3 = (SELECT MAX(B.E3) FROM TABLE_A AS B WHERE A.E2 = B.E2)
) AS THEMAX
ON TABLE_A.E1 = THEMAX.E2 AND TABLE_A.E1 = TABLE_A.E2

However, it returns 1.

How to repeat:
as above
[17 Oct 2003 5:22] Oleksandr Byelkin 
ChangeSet 
  1.1612 03/10/17 15:18:57 bell@sanja.is.com.ua +5 -0 
  fixed deleting derived table tree after using (BUG#1536) 
 
above patch is pushed in source repository and will be present in next 
release. 
 
Thank you for good bugreport.