From 994c0f83083ceee30430f28b8f1ced9b71944dbb Mon Sep 17 00:00:00 2001 From: Gleb Shchepa Date: Sat, 6 Feb 2010 23:54:30 +0400 Subject: 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. --- mysql-test/t/group_by.test | 50 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 50 insertions(+) (limited to 'mysql-test/t/group_by.test') diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 5b96213034a..e6ea5ecc7f6 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1158,3 +1158,53 @@ SELECT COUNT(i) FROM t1 WHERE i > 1; DROP TABLE t1; SET @@sql_mode = @old_sql_mode; +--echo # +--echo # Bug #45640: optimizer bug produces wrong results +--echo # + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (4, 40), (1, 10), (2, 20), (2, 20), (3, 30); + +--echo # should return 4 ordered records: +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa; + +SELECT (SELECT (SELECT t1.a)) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa; + +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa+0; + +--echo # should return the same result in a reverse order: +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa; + +--echo # execution plan should not use temporary table: +EXPLAIN EXTENDED +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa+0; + +EXPLAIN EXTENDED +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa; + +--echo # should return only one record +SELECT (SELECT tt.a FROM t1 tt LIMIT 1) aa, COUNT(DISTINCT b) FROM t1 + GROUP BY aa; + +CREATE TABLE t2 SELECT DISTINCT a FROM t1; + +--echo # originally reported queries (1st two columns of next two query +--echo # 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; +SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT( b) + FROM t1 GROUP BY aa, b; + +--echo # 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; +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; + +DROP TABLE t1, t2; + +--echo # + +--echo # End of 5.1 tests -- cgit v1.2.1