Bug #45640: optimizer bug produces wrong results

Grouping by a subquery in a query with a distinct aggregate
function lead to a wrong result (wrong and unordered
grouping values).

There are two related problems:

1) The query like this:

   SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) c
   FROM t1 GROUP BY aa

returned wrong result, because the outer reference "t1.a"
in the subquery was substituted with the Item_ref item.

The Item_ref item obtains data from the result_field object
that refreshes once after the end of each group. This data
is not applicable to filesort since filesort() doesn't care
about groups (and doesn't update result_field objects with
copy_fields() and so on). Also that data is not applicable
to group separation algorithm: end_send_group() checks every
record with test_if_group_changed() that evaluates Item_ref
items, but it refreshes those Item_ref-s only after the end
of group, that is a vicious circle and the grouped column
values in the output are shifted.

Fix: if
       a) we grouping by a subquery and
       b) that subquery has outer references to FROM list
          of the grouping query,
     then we substitute these outer references with
     Item_direct_ref like references under aggregate
     functions: Item_direct_ref obtains data directly
     from the current record.

2) The query with a non-trivial grouping expression like:

   SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) c
   FROM t1 GROUP BY aa+0

also returned wrong result, since JOIN::exec() substitutes
references to top-level aliases in SELECT list with Item_copy
caching items. Item_copy items have same refreshing policy
as Item_ref items, so the whole groping expression with
Item_copy inside returns wrong result in filesort() and
end_send_group().

Fix: include aliased items into GROUP BY item tree instead
     of Item_ref references to them.



mysql-test/r/group_by.result:
  Test case for bug #45640
mysql-test/t/group_by.test:
  Test case for bug #45640
sql/item.cc:
  Bug #45640: optimizer bug produces wrong results
  
  Item_field::fix_fields() has been modified to resolve
  aliases in GROUP BY item trees into aliased items instead
  of Item_ref items.
sql/item.h:
  Bug #45640: optimizer bug produces wrong results
  
  - Item::find_item_processor() has been introduced.
  - Item_ref::walk() has been modified to apply processors
    to itself too (not only to referenced item).
sql/mysql_priv.h:
  Bug #45640: optimizer bug produces wrong results
  
  fix_inner_refs() has been modified to accept group_list
  parameter.
sql/sql_lex.cc:
  Bug #45640: optimizer bug produces wrong results
  
  Initialization of st_select_lex::group_fix_field has
  been added.
sql/sql_lex.h:
  Bug #45640: optimizer bug produces wrong results
  
  The st_select_lex::group_fix_field field has been introduced
  to control alias resolution in Itef_fied::fix_fields.
sql/sql_select.cc:
  Bug #45640: optimizer bug produces wrong results
  
  - The fix_inner_refs function has been modified to treat
    subquery outer references like outer fields under aggregate
    functions, if they are included in GROUP BY item tree.
  
  - The find_order_in_list function has been modified to
    fix Item_field alias fields included in the GROUP BY item
    trees in a special manner.

1 files changed, 88 insertions, 0 deletions

diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 742d4b90807..6ca08df40ea 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1703,3 +1703,91 @@ COUNT(i)
 1
 DROP TABLE t1;
 SET @@sql_mode = @old_sql_mode;
+#
+# Bug #45640: optimizer bug produces wrong results
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (4, 40), (1, 10), (2, 20), (2, 20), (3, 30);
+# should return 4 ordered records:
+SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa;
+aa	COUNT(DISTINCT b)
+1	1
+2	1
+3	1
+4	1
+SELECT (SELECT (SELECT t1.a)) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa;
+aa	COUNT(DISTINCT b)
+1	1
+2	1
+3	1
+4	1
+SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa+0;
+aa	COUNT(DISTINCT b)
+1	1
+2	1
+3	1
+4	1
+# should return the same result in a reverse order:
+SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa;
+aa	COUNT(DISTINCT b)
+4	1
+3	1
+2	1
+1	1
+# execution plan should not use temporary table:
+EXPLAIN EXTENDED
+SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa+0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note	1003	select (select `test`.`t1`.`a` AS `a`) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by ((select `test`.`t1`.`a` AS `a`) + 0)
+EXPLAIN EXTENDED
+SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note	1003	select (select `test`.`t1`.`a` AS `a`) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by -((select `test`.`t1`.`a` AS `a`))
+# should return only one record
+SELECT (SELECT tt.a FROM t1 tt LIMIT 1) aa, COUNT(DISTINCT b) FROM t1
+GROUP BY aa;
+aa	COUNT(DISTINCT b)
+4	4
+CREATE TABLE t2 SELECT DISTINCT a FROM t1;
+# originally reported queries (1st two columns of next two query
+# results should be same):
+SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(DISTINCT b)
+FROM t1 GROUP BY aa, b;
+aa	b	COUNT(DISTINCT b)
+1	10	1
+2	20	1
+3	30	1
+4	40	1
+SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(         b)
+FROM t1 GROUP BY aa, b;
+aa	b	COUNT(         b)
+1	10	1
+2	20	2
+3	30	1
+4	40	1
+# ORDER BY for sure:
+SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(DISTINCT b)
+FROM t1 GROUP BY aa, b ORDER BY -aa, -b;
+aa	b	COUNT(DISTINCT b)
+4	40	1
+3	30	1
+2	20	1
+1	10	1
+SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(         b)
+FROM t1 GROUP BY aa, b ORDER BY -aa, -b;
+aa	b	COUNT(         b)
+4	40	1
+3	30	1
+2	20	2
+1	10	1
+DROP TABLE t1, t2;
+#
+# End of 5.1 tests