MySQL Bugs: #23800: Correlated sub query returning incorrect results when operated upon

Bug #23800	Correlated sub query returning incorrect results when operated upon
Submitted:	31 Oct 2006 10:47	Modified:	6 Mar 2007 19:30
Reporter:	Andrew Rose	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.26-27/4.1BK/5.0BK/5.1BK	OS:	GNU/Linux (Archlinux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	correlated sub query operated upon incorrect results

Description:
When running a query with a correlated sub query the results are fine untill you operate upon them with count(parent.table.col):

select ((select sum(tab1.weight) from tab1 where tab1.linkid = tab0.id) * count(tab0.id)) as col0 from tab0 group by tab0.id;

From what I can see, when the sub query is operated apon with a value from the parent table only the last results from the group by are used.

I have recreated this in 5.0.26 and 5.0.27, 5.0.24 is fine, I have not checked 5.0.25.

I have provided a better example in the "How to repeat" section.

How to repeat:
create table students (
 id int primary key auto_increment,
 name varchar(32)
);

create table slips (
 id int primary key auto_increment,
 studentid int,
 pevent int
);

create table events (
 id int primary key auto_increment,
 weight int
);

insert into students(name) values('Andrew'), ('Chris'), ('Clare');
insert into events(weight) values(1),(3),(6),(9);

insert into slips(studentid, pevent) values(1, 1), (1,2), (1,3), (1,4);
insert into slips(studentid, pevent) values(2, 1), (2,2), (2,3), (2,1);
insert into slips(studentid, pevent) values(3, 1), (3,1), (3,1), (3,4);

select (select sum(c.weight) from slips as b, events as c where b.pevent = c.id and b.studentid = a.id) as BI from students as a, slips as d where
a.id = d.studentid group by a.id;

select ((select sum(c.weight) from slips as b, events as c where b.pevent = c.id and b.studentid = a.id) * count(d.id)) as BI from students as a, slips as d where
a.id = d.studentid group by a.id;

Version 5.0.24

mysql> select (select sum(c.weight) from slips as b, events as c where b.pevent = c.id and b.studentid = a.id) as BI from students as a, slips as d where
    -> a.id = d.studentid group by a.id;
+------+
| BI   |
+------+
|   19 |
|   11 |
|   12 |
+------+
3 rows in set (0.00 sec)

mysql>
mysql> select ((select sum(c.weight) from slips as b, events as c where b.pevent = c.id and b.studentid = a.id) * count(d.id)) as BI from students as a, slips as d where
    -> a.id = d.studentid group by a.id;
+------+
| BI   |
+------+
|   76 |
|   44 |
|   48 |
+------+
3 rows in set (0.00 sec)

Version 5.0.26-27

mysql> select (select sum(c.weight) from slips as b, events as c where b.pevent = c.id and b.studentid = a.id) as BI from students as a, slips as d where
    -> a.id = d.studentid group by a.id;
+------+
| BI   |
+------+
|   19 |
|   11 |
|   12 |
+------+
3 rows in set (0.00 sec)

mysql>
mysql> select ((select sum(c.weight) from slips as b, events as c where b.pevent = c.id and b.studentid = a.id) * count(d.id)) as BI from students as a, slips as d where
    -> a.id = d.studentid group by a.id;
+------+
| BI   |
+------+
|   48 |
|   48 |
|   48 |
+------+
3 rows in set (0.00 sec)

Thank you for the bug report. Verfied as described.

mysql> select ((select sum(c.weight) from slips as b, events as c where b.pevent = c.id
    -> and b.studentid = a.id) * count(d.id)) as BI from students as a, slips as d
    -> where
    -> a.id = d.studentid group by a.id;
+------+
| BI   |
+------+
|   48 | 
|   48 | 
|   48 | 
+------+
3 rows in set (0.01 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.29-debug | 
+--------------+
1 row in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19049

ChangeSet@1.2386, 2007-01-31 01:07:38+03:00, evgen@moonbone.local +12 -0
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  
  All outer fields in a subquery were represented by Item_field objects.
  If the outer select employs a temporary table all such fields should be
  replaced with fields from that temporary table in order to point to the 
  actual data. This replacement wasn't done and that resulted in a wrong
  subquery result and wrong result of the whole query.
  
  Now any outer field is represented by two objects - Item_field placed in the
  outer select and Item_ref in the subquery. Item_field object is processed as
  a normal field and takes place in the ref_pointer_array, if there is a 
  temporary table it is copied there, so on. Thus the Item_ref is always
  references the correct field. Original field is substituted with the reference
  in the Item_field::fix_outer_field() function.
  
  New function called fix_inner_refs() is added to fix fields referenced from
  inner selects and references (Item_ref objects) to these fields.
  
  Item_ref class is updated to behave more like Item_field when used to
  represent an outer reference.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20183

ChangeSet@1.2408, 2007-02-20 16:12:14+03:00, evgen@moonbone.local +11 -0
  Bug#23800: Outer fields in correlated subqueries is used in a temporary table
  created for sorting.
  
  Any outer reference in a subquery was represented by an Item_field object.
  If the outer select employs a temporary table all such fields should be
  replaced with fields from that temporary table in order to point to the 
  actual data. This replacement wasn't done and that resulted in a wrong
  subquery evaluation and a wrong result of the whole query.
  
  Now any outer field is represented by two objects - Item_field placed in the
  outer select and Item_outer_ref in the subquery. Item_field object is
  processed as a normal field and the reference to it is saved in the
  ref_pointer_array. Thus the Item_outer_ref is always references the correct
  field. The original field is substituted for a reference in the
  Item_field::fix_outer_field() function.
  
  New function called fix_inner_refs() is added to fix fields referenced from
  inner selects and to fix references (Item_ref objects) to these fields.
  
  The new Item_outer_ref class is a descendant of the Item_direct_ref class.
  It additionally stores a reference to the original field and designed to
  behave more like a field.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20314

ChangeSet@1.2408, 2007-02-21 23:00:32+03:00, evgen@moonbone.local +11 -0
  Bug#23800: Outer fields in correlated subqueries is used in a temporary table
  created for sorting.
  
  Any outer reference in a subquery was represented by an Item_field object.
  If the outer select employs a temporary table all such fields should be
  replaced with fields from that temporary table in order to point to the 
  actual data. This replacement wasn't done and that resulted in a wrong
  subquery evaluation and a wrong result of the whole query.
  
  Now any outer field is represented by two objects - Item_field placed in the
  outer select and Item_outer_ref in the subquery. Item_field object is
  processed as a normal field and the reference to it is saved in the
  ref_pointer_array. Thus the Item_outer_ref is always references the correct
  field. The original field is substituted for a reference in the
  Item_field::fix_outer_field() function.
  
  New function called fix_inner_refs() is added to fix fields referenced from
  inner selects and to fix references (Item_ref objects) to these fields.
  
  The new Item_outer_ref class is a descendant of the Item_direct_ref class.
  It additionally stores a reference to the original field and designed to
  behave more like a field.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20485

ChangeSet@1.2418, 2007-02-23 20:44:31+03:00, evgen@moonbone.local +4 -0
  item.h:
    Post fix for bug#23800.
    Added the subselect_items_processor() method to the Item class.
  item_subselect.h, item_subselect.cc:
    Post fix for bug#23800.
    Added function Item_subselect::subselect_items_processor().
  sql_lex.cc:
    Post fix for bug#23800.
    A subselect might add fields it refers to to the outer selects'
    ref_pointer_array. Not taking this into account in the 
    st_select_lex::setup_ref_array() function may lead to a memory corruption
    and to a crash.

Fixed in 5.0.38, 5.1.17-beta

Noted in 5.0.38, 5.1.17 changelogs.

Queries that used a temporary table for the outer query when
evaluating a correlated subquery could return incorrect results.