MySQL Bugs: #29297: Wrong result with aggregation and a subquery

Bug #29297	Wrong result with aggregation and a subquery
Submitted:	22 Jun 2007 9:06	Modified:	26 Jun 2007 14:52
Reporter:	Aleksey Karyakin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.37, 5.0 BK, 5.1 BK	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	aggregate, subquery

Description:
This problem may be related to bug 27333. 

Prerequisites:

create table t1 (i int);
create table t2 (i int);
insert into t1 values(1);
insert into t1 values(2);
insert into t2 values(1);
insert into t2 values(2);

The following query should return a single NULL value:
select (select sum(t1.i) from t2 where i=0) from t1;

The following query should return an error "subquery returned more than one row":
select (select sum(t1.i) from t2 where i!=0) from t1;

The following query should return value '3':
select (select sum(t1.i) from t2 where i=1) from t1;

However, nowdays all three queries return '3' regardless of the condition in the subselect. The server seems to process the aggregate correctly (it is taken on the outer query). However, a single value sum(t1.i) is taken regardless of how many rows are actually returned from the inner subquery. 

How to repeat:
create table t1 (i int);
create table t2 (i int);
insert into t1 values(1);
insert into t1 values(2);
insert into t2 values(1);
insert into t2 values(2);
select (select sum(t1.i) from t2 where i=0) from t1;
select (select sum(t1.i) from t2 where i!=0) from t1;
select (select sum(t1.i) from t2 where i=1) from t1;

All three last queries return '3' which is incorrect.

Thank you for the report.

Verified as described.

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

It is indeed a duplicate of bug #27333.