From 9d661efd7f85b4208b9220434a0d5b5f21ac5070 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 27 Mar 2008 19:49:32 +0300 Subject: Bug#27219: Aggregate functions in ORDER BY. Mixing aggregate functions and non-grouping columns is not allowed in the ONLY_FULL_GROUP_BY mode. However in some cases the error wasn't thrown because of insufficient check. In order to check more thoroughly the new algorithm employs a list of outer fields used in a sum function and a SELECT_LEX::full_group_by_flag. Each non-outer field checked to find out whether it's aggregated or not and the current select is marked accordingly. All outer fields that are used under an aggregate function are added to the Item_sum::outer_fields list and later checked by the Item_sum::check_sum_func function. mysql-test/t/group_by.test: Added a test case for the bug#27219: Aggregate functions in ORDER BY. mysql-test/r/group_by.result: Added a test case for the bug#27219: Aggregate functions in ORDER BY. sql/sql_select.cc: Bug#27219: Aggregate functions in ORDER BY. Implementation of new check for mixing non aggregated fields and aggregation function in the ONLY_FULL_GROUP_BY mode. sql/sql_lex.cc: Bug#27219: Aggregate functions in ORDER BY. Initialization of the full_group_by_flag bitmap. SELECT_LEX::test_limit function doesn't reset ORDER BY clause anymore. sql/sql_lex.h: Bug#27219: Aggregate functions in ORDER BY. The full_group_by_flag is added to the SELECT_LEX class. sql/item_sum.h: Bug#27219: Aggregate functions in ORDER BY. The outer_fields list is added to the Item_sum class. sql/mysql_priv.h: Bug#27219: Aggregate functions in ORDER BY. Defined a set of constants used in the new check for mixing non aggregated fields and sum functions in the ONLY_FULL_GROUP_BY_MODE. sql/item_subselect.cc: Bug#27219: Aggregate functions in ORDER BY. The Item_in_subselect::select_in_like_transformer function now drops ORDER BY clause in all selects in a subquery. sql/item_sum.cc: Bug#27219: Aggregate functions in ORDER BY. Now the Item_sum::check_sum_func function now checks whether fields in the outer_fields list are aggregated or not and marks selects accordingly. sql/item.cc: Bug#27219: Aggregate functions in ORDER BY. Now the Item_field::fix_fields function checks whether the field is aggregated or not and marks its select_lex accordingly. --- mysql-test/t/group_by.test | 117 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 117 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 636544f7854..82bb8a45faa 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -893,4 +893,121 @@ FROM t1; DROP TABLE t1; SET @@sql_mode = @old_sql_mode; +--echo # +--echo # Bug#27219: Aggregate functions in ORDER BY. +--echo # +SET @save_sql_mode=@@sql_mode; +SET @@sql_mode='ONLY_FULL_GROUP_BY'; + +CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0); +INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4); +CREATE TABLE t2 SELECT * FROM t1; + +SELECT 1 FROM t1 ORDER BY COUNT(*); +SELECT 1 FROM t1 ORDER BY COUNT(*) + 1; +--error 1140 +SELECT 1 FROM t1 ORDER BY COUNT(*) + a; +SELECT 1 FROM t1 ORDER BY COUNT(*), 1; +--error 1140 +SELECT 1 FROM t1 ORDER BY COUNT(*), a; + +SELECT 1 FROM t1 ORDER BY SUM(a); +SELECT 1 FROM t1 ORDER BY SUM(a + 1); +SELECT 1 FROM t1 ORDER BY SUM(a) + 1; +--error 1140 +SELECT 1 FROM t1 ORDER BY SUM(a), b; + +--error 1140 +SELECT a FROM t1 ORDER BY COUNT(b); + +SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2); + +--error 1140 +SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a), t2.a FROM t2); +--error 1140 +SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a); +--error 1140 +SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1); + +--error 1140 +SELECT t1.a FROM t1 + WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1); +--error 1140 +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1); + +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b)); +--error 1140 +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b)); +--error 1140 +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b)); + +--error 1140 +SELECT t1.a FROM t1 + WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b)); + +SELECT 1 FROM t1 GROUP BY t1.a + HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); +SELECT 1 FROM t1 GROUP BY t1.a + HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); +SELECT 1 FROM t1 GROUP BY t1.a + HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1); + +--error 1140 +SELECT 1 FROM t1 GROUP BY t1.a + HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1); +--error 1140 +SELECT 1 FROM t1 GROUP BY t1.a + HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1); +--error 1140 +SELECT 1 FROM t1 GROUP BY t1.a + HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1); + +# Both SUMs are aggregated in the subquery, no mixture: +SELECT t1.a FROM t1 + WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a + ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1); + +# SUM(t1.b) is aggregated in the subquery, no mixture: +SELECT t1.a, SUM(t1.b) FROM t1 + WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a + ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1) + GROUP BY t1.a; + +# 2nd SUM(t1.b) is aggregated in the subquery, no mixture: +SELECT t1.a, SUM(t1.b) FROM t1 + WHERE t1.a = (SELECT SUM(t2.b) FROM t2 + ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1) + GROUP BY t1.a; + +# SUM(t2.b + t1.a) is aggregated in the subquery, no mixture: +SELECT t1.a, SUM(t1.b) FROM t1 + WHERE t1.a = (SELECT SUM(t2.b) FROM t2 + ORDER BY SUM(t2.b + t1.a) LIMIT 1) + GROUP BY t1.a; + +SELECT t1.a FROM t1 GROUP BY t1.a + HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1); + +select avg ( + (select + (select sum(outr.a + innr.a) from t1 as innr limit 1) as tt + from t1 as outr order by outr.a limit 1)) +from t1 as most_outer; + +--error 1140 +select avg ( + (select ( + (select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt + from t1 as outr order by count(outr.a) limit 1)) as tt +from t1 as most_outer; + +select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a; + +SET sql_mode=@save_sql_mode; +DROP TABLE t1, t2; + --echo End of 5.0 tests -- cgit v1.2.1