Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2008-03-14 17:28:40+03:00, evgen@stripped +7 -0
Bug#27219: Aggregate functions in ORDER BY.
Mixing of aggregate functions and non-grouping columns aren't 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 two bitmaps
lex->non_agg_fields_used and lex->agg_funcs_used. Each bit in them represents
a level of nesting. When it is set to 1 it means that respectively a non
aggregated field or an aggregate function were used on this level of select
nesting. An error is thrown when bits are set in both variables for a nesting
level. Bits are set by the Item_field::fix_fields and the
Item_sum::check_sum_func functions.
mysql-test/r/group_by.result@stripped, 2008-03-14 17:14:19+03:00, evgen@stripped +154 -0
Added a test case for the bug#27219: Aggregate functions in ORDER BY.
mysql-test/t/group_by.test@stripped, 2008-03-14 17:13:23+03:00, evgen@stripped +107 -0
Added a test case for the bug#27219: Aggregate functions in ORDER BY.
sql/item.cc@stripped, 2008-03-14 17:24:24+03:00, evgen@stripped +2 -0
Bug#27219: Aggregate functions in ORDER BY.
Now the Item_field::fix_fields function sets bits in the non_agg_fields_used bitmap.
sql/item_sum.cc@stripped, 2008-03-14 17:21:10+03:00, evgen@stripped +1 -0
Bug#27219: Aggregate functions in ORDER BY.
Now the Item_sum::check_sum_func function sets bits in the sum_func_used bitmap.
sql/sql_lex.cc@stripped, 2008-03-14 17:17:23+03:00, evgen@stripped +2 -0
Bug#27219: Aggregate functions in ORDER BY.
Initialization of the sum_func_used and the non_agg_fields_used bitmaps.
sql/sql_lex.h@stripped, 2008-03-14 17:15:47+03:00, evgen@stripped +2 -0
Bug#27219: Aggregate functions in ORDER BY.
Two bitmaps, sum_func_used and non_agg_fields_used, are added to the st_lex class.
sql/sql_select.cc@stripped, 2008-03-14 17:25:36+03:00, evgen@stripped +6 -29
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.
diff -Nrup a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
--- a/mysql-test/r/group_by.result 2008-01-11 21:00:24 +03:00
+++ b/mysql-test/r/group_by.result 2008-03-14 17:14:19 +03:00
@@ -1213,4 +1213,158 @@ FROM t1;
ERROR 21000: Subquery returns more than 1 row
DROP TABLE t1;
SET @@sql_mode = @old_sql_mode;
+#
+# Bug#27219: Aggregate functions in ORDER BY.
+#
+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 (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
+CREATE TABLE t2 SELECT * FROM t1;
+SELECT 1 FROM t1 ORDER BY COUNT(*);
+1
+1
+SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
+1
+1
+SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
+1
+1
+SELECT 1 FROM t1 ORDER BY COUNT(*), a;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT 1 FROM t1 ORDER BY SUM(a);
+1
+1
+SELECT 1 FROM t1 ORDER BY SUM(a + 1);
+1
+1
+SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
+1
+1
+SELECT 1 FROM t1 ORDER BY SUM(a), b;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT a FROM t1 ORDER BY COUNT(b);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1 ORDER BY (SELECT t1.a FROM t1 LIMIT 1);
+a
+2
+2
+3
+3
+3
+4
+SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 LIMIT 1);
+a
+2
+2
+3
+3
+3
+4
+SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t1.a) FROM t1);
+a
+2
+2
+3
+3
+3
+4
+SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
+a
+2
+2
+3
+3
+3
+4
+SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a), t2.a FROM t2);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1
+WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1 GROUP BY t1.a
+HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+# ORDER BY exprs are optimized out --> no error
+SELECT t1.a FROM t1 GROUP BY t1.a
+HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
+a
+2
+3
+4
+# ORDER BY exprs are optimized out --> no error
+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));
+a
+2
+3
+4
+# ORDER BY exprs are optimized out --> no error
+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));
+a
+3
+4
+SELECT t1.a FROM t1
+WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT 1 FROM t1 GROUP BY t1.a
+HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
+1
+1
+1
+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);
+1
+1
+1
+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);
+1
+1
+1
+1
+SELECT 1 FROM t1 GROUP BY t1.a
+HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+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 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT 1 FROM t1 GROUP BY t1.a
+HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+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);
+a
+4
+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;
+a SUM(t1.b)
+4 4
+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;
+a SUM(t1.b)
+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;
+a SUM(t1.b)
+SELECT t1.a FROM t1 GROUP BY t1.a
+HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
+a
+SET sql_mode=@save_sql_mode;
+DROP TABLE t1, t2;
End of 5.0 tests
diff -Nrup a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
--- a/mysql-test/t/group_by.test 2008-01-11 21:00:24 +03:00
+++ b/mysql-test/t/group_by.test 2008-03-14 17:13:23 +03:00
@@ -893,4 +893,111 @@ 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 (2,2), (2,2), (3,3), (3,3), (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 t1.a FROM t1 LIMIT 1);
+SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 LIMIT 1);
+SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t1.a) FROM t1);
+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);
+
+--echo # ORDER BY exprs are optimized out --> no error
+SELECT t1.a FROM t1 GROUP BY t1.a
+ HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
+--echo # ORDER BY exprs are optimized out --> no error
+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));
+
+--echo # ORDER BY exprs are optimized out --> no error
+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);
+
+SET sql_mode=@save_sql_mode;
+DROP TABLE t1, t2;
+
--echo End of 5.0 tests
diff -Nrup a/sql/item.cc b/sql/item.cc
--- a/sql/item.cc 2008-03-10 09:11:10 +03:00
+++ b/sql/item.cc 2008-03-14 17:24:24 +03:00
@@ -3965,6 +3965,8 @@ bool Item_field::fix_fields(THD *thd, It
thd->lex->current_select->nest_level)
set_if_bigger(thd->lex->in_sum_func->max_arg_level,
thd->lex->current_select->nest_level);
+ if (!thd->lex->in_sum_func && !outer_fixed)
+ thd->lex->non_agg_fields_used|= 1 << thd->lex->current_select->nest_level;
}
else if (thd->set_query_id && field->query_id != thd->query_id)
{
diff -Nrup a/sql/item_sum.cc b/sql/item_sum.cc
--- a/sql/item_sum.cc 2008-02-28 14:31:18 +03:00
+++ b/sql/item_sum.cc 2008-03-14 17:21:10 +03:00
@@ -195,6 +195,7 @@ bool Item_sum::check_sum_func(THD *thd,
set_if_bigger(in_sum_func->max_sum_func_level, aggr_level);
set_if_bigger(in_sum_func->max_sum_func_level, max_sum_func_level);
}
+ thd->lex->sum_func_used|= 1 << aggr_level;
update_used_tables();
thd->lex->in_sum_func= in_sum_func;
return FALSE;
diff -Nrup a/sql/sql_lex.cc b/sql/sql_lex.cc
--- a/sql/sql_lex.cc 2007-08-31 03:23:37 +04:00
+++ b/sql/sql_lex.cc 2008-03-14 17:17:23 +03:00
@@ -204,6 +204,8 @@ void lex_start(THD *thd)
lex->nest_level=0 ;
lex->allow_sum_func= 0;
+ lex->sum_func_used= 0;
+ lex->non_agg_fields_used= 0;
lex->in_sum_func= NULL;
DBUG_VOID_RETURN;
}
diff -Nrup a/sql/sql_lex.h b/sql/sql_lex.h
--- a/sql/sql_lex.h 2007-11-19 19:59:43 +03:00
+++ b/sql/sql_lex.h 2008-03-14 17:15:47 +03:00
@@ -1054,6 +1054,8 @@ typedef struct st_lex : public Query_tab
the variable can contain 0 or 1 for each nest level.
*/
nesting_map allow_sum_func;
+ nesting_map sum_func_used;
+ nesting_map non_agg_fields_used;
enum_sql_command sql_command, orig_sql_command;
thr_lock_type lock_option;
enum SSL_type ssl_type; /* defined in violite.h */
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2008-03-03 20:35:38 +03:00
+++ b/sql/sql_select.cc 2008-03-14 17:25:36 +03:00
@@ -568,37 +568,14 @@ JOIN::prepare(Item ***rref_pointer_array
/*
Check if there are references to un-aggregated columns when computing
aggregate functions with implicit grouping (there is no GROUP BY).
- TODO: Add check of calculation of GROUP functions and fields:
- SELECT COUNT(*)+table.col1 from table1;
*/
- if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
+ if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY && !group_list &&
+ ((1 << thd->lex->current_select->nest_level) &
+ thd->lex->sum_func_used & thd->lex->non_agg_fields_used))
{
- if (!group_list)
- {
- uint flag=0;
- List_iterator_fast<Item> it(fields_list);
- Item *item;
- while ((item= it++))
- {
- if (item->with_sum_func)
- flag|=1;
- else if (!(flag & 2) && !item->const_during_execution())
- flag|=2;
- }
- if (having)
- {
- if (having->with_sum_func)
- flag |= 1;
- else if (!having->const_during_execution())
- flag |= 2;
- }
- if (flag == 3)
- {
- my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
- ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
- DBUG_RETURN(-1);
- }
- }
+ my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
+ ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
+ DBUG_RETURN(-1);
}
{
/* Caclulate the number of groups */
| Thread |
|---|
| • bk commit into 5.0 tree (evgen:1.2604) BUG#27219 | eugene | 14 Mar |
