MySQL Bugs: #27321: Wrong result for outer references in scalar subqueries with aggregates

Bug #27321	Wrong result for outer references in scalar subqueries with aggregates
Submitted:	21 Mar 2007 8:59	Modified:	17 Apr 2007 20:01
Reporter:	Georgi Kodinov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0-bk	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
I get wrong values for references to columns from the outer context in a subquery when the subquery contains aggregate functions that are aggregated in outer context. This is probably related to bug#23800.

How to repeat:
CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'),
(1,9,'m');

# this is the "control statement". It uses loose index scan to calculate GROUP BY. Returns correct results
SELECT a, MAX(b),
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test 
FROM t1 GROUP BY a;
a       MAX(b)  test
1       9       m
2       3       h
3       4       i

# this one has the "+0" part to turn off loose index scan. As a result we get wrong results : note the "test" column : must be the same as in the above select.
SELECT a, MAX(b),
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0) LIMIT 1) as test 
FROM t1 GROUP BY a;
a       MAX(b)  test
1       9       NULL
2       3       j
3       4       i

Suggested fix:
Make a "smart" Item_ref: a class that is an union of the Item_ref and Item_outer_ref functionality and can be switched from one to the other after instantiation. And set this up for outer references based on the presence of aggregate functions in the subquery that are aggregated in an outer context.

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/23479

ChangeSet@1.2421, 2007-04-01 02:00:09+04:00, evgen@moonbone.local +7 -0
  Bug#27321: Wrong subquery result in a grouping select.
  
  The Item_outer_ref class based on the Item_direct_ref class was always used
  to represent an outer field. But if the outer select is a grouping one and the 
  outer field isn't under an aggregate function which is aggregated in that
  outer select the Item_ref should be used to represent such 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/23480

ChangeSet@1.2421, 2007-04-01 03:16:50+04:00, evgen@moonbone.local +7 -0
  Bug#27321: Wrong subquery result in a grouping select.
  
  The Item_outer_ref class based on the Item_direct_ref class was always used
  to represent an outer field. But if the outer select is a grouping one and the 
  outer field isn't under an aggregate function which is aggregated in that
  outer select the Item_ref should be used to represent such a field.
  If the outer select in which the outer field is resolved isn't grouping then
  the Item_field class should be used to represent such a field.
  This logic also should be used for an outer field resolved through its alias
  name.
  
  Now the Item_field::fix_outer_field() uses Item_outer_field objects to
  represent aliased and non-aliased outer fields for grouping outer selects
  only.
  Item_outer_ref::val_xxx() methods were added.
  
  The direct_ref and the found_in_select_list fields were added to the
  Item_outer_ref class.
  
  Now the fix_inner_refs() function resolves which set of functions, val_xxx() or
  val_xxx_result(), will be used by an Item_outer_ref object.

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/23555

ChangeSet@1.2421, 2007-04-02 18:42:41+04:00, evgen@moonbone.local +8 -0
  Bug#27321: Wrong subquery result in a grouping select.
  
  The Item_outer_ref class based on the Item_direct_ref class was always used
  to represent an outer field. But if the outer select is a grouping one and the 
  outer field isn't under an aggregate function which is aggregated in that
  outer select the Item_ref should be used to represent such a field.
  If the outer select in which the outer field is resolved isn't grouping then
  the Item_field class should be used to represent such a field.
  This logic also should be used for an outer field resolved through its alias
  name.
  
  Now the Item_field::fix_outer_field() uses Item_outer_field objects to
  represent aliased and non-aliased outer fields for grouping outer selects
  only.
  Item_outer_ref::val_xxx() methods were added.
  
  The direct_ref and the found_in_select_list fields were added to the
  Item_outer_ref class.
  
  Now the fix_inner_refs() function resolves which set of functions, val_xxx() or
  val_xxx_result(), will be used by an Item_outer_ref object.

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/23707

ChangeSet@1.2421, 2007-04-03 22:49:31+04:00, evgen@moonbone.local +8 -0
  Bug#27321: Wrong subquery result in a grouping select.
  
  The Item_outer_ref class based on the Item_direct_ref class was always used
  to represent an outer field. But if the outer select is a grouping one and the 
  outer field isn't under an aggregate function which is aggregated in that
  outer select the Item_ref should be used to represent such a field.
  If the outer select in which the outer field is resolved isn't grouping then
  the Item_field class should be used to represent such a field.
  This logic also should be used for an outer field resolved through its alias
  name.
  
  Now the Item_field::fix_outer_field() uses Item_outer_field objects to
  represent aliased and non-aliased outer fields for grouping outer selects
  only.
  Now the fix_inner_refs() function choose which class to use to access outer
  field - the Item_ref or the Item_direct_ref. An object of chosen class
  substitutes the original field in the Item_outer_ref object.
  The direct_ref and the found_in_select_list fields were added to the
  Item_outer_ref class.

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/24514

ChangeSet@1.2421, 2007-04-13 21:47:38+04:00, evgen@moonbone.local +8 -0
  Bug#27321: Wrong subquery result in a grouping select.
  
  The Item_outer_ref class based on the Item_direct_ref class was always used
  to represent an outer field. But if the outer select is a grouping one and the 
  outer field isn't under an aggregate function which is aggregated in that
  outer select the Item_ref should be used to represent such a field.
  If the outer select in which the outer field is resolved isn't grouping then
  the Item_field class should be used to represent such a field.
  This logic also should be used for an outer field resolved through its alias
  name.
  
  Now the Item_field::fix_outer_field() uses Item_outer_field objects to
  represent aliased and non-aliased outer fields for grouping outer selects
  only.
  Now the fix_inner_refs() function choose which class to use to access outer
  field - the Item_ref or the Item_direct_ref. An object of chosen class
  substitutes the original field in the Item_outer_ref object.
  The direct_ref and the found_in_select_list fields were added to the
  Item_outer_ref class.

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/24557

ChangeSet@1.2421, 2007-04-15 00:18:13+04:00, evgen@moonbone.local +8 -0
  Bug#27321: Wrong subquery result in a grouping select.
  
  The Item_outer_ref class based on the Item_direct_ref class was always used
  to represent an outer field. But if the outer select is a grouping one and the 
  outer field isn't under an aggregate function which is aggregated in that
  outer select an Item_ref object should be used to represent such a field.
  If the outer select in which the outer field is resolved isn't grouping then
  the Item_field class should be used to represent such a field.
  This logic also should be used for an outer field resolved through its alias
  name.
  
  Now the Item_field::fix_outer_field() uses Item_outer_field objects to
  represent aliased and non-aliased outer fields for grouping outer selects
  only.
  Now the fix_inner_refs() function chooses which class to use to access outer
  field - the Item_ref or the Item_direct_ref. An object of the chosen class
  substitutes the original field in the Item_outer_ref object.
  The direct_ref and the found_in_select_list fields were added to the
  Item_outer_ref class.

Pushed into 5.1.18-beta

Pushed into 5.0.40

Pushed into 5.1.18-beta
Pushed into 5.0.40
Pushed into 4.1.23

Noted in 4.1.23, 5.0.40, 5.1.18 changelogs.

A subquery could get incorrect values for references to outer query
columns when it contained aggregate functions that were aggregated in
outer context.

Fix for this bug was pushed to 5.0+ only.

We moved to 5.0.41 recently and one of our queries broke due to this bug. The subquery is referencing a column from the outer query and there's an aggregate function used in the outer query context.