Below is the list of changes that have just been committed into a local
5.0 repository of gshchepa. When gshchepa 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-02-22 05:41:21+04:00, gshchepa@stripped +7 -0
Fixed bug #27219: mixing of columns and aggregate
functions (COUNT/MIN/SUM...) in SELECT and ORDER BY
expression list in the query without GROUP BY clause
doesn't raise the ER_MIX_OF_GROUP_FUNC_AND_FIELDS
error in the ONLY_FULL_GROUP_BY mode.
Examples:
SELECT a FROM t ORDER BY COUNT(a);
SELECT 1 FROM t ORDER BY COUNT(*), a;
That has been fixed. Also recognition of parts in
the expressions like "SUM(a) + b" has been implemented
(queries like "SELECT SUM(a) + b FROM t" evaluated
without a error in the ER_MIX_OF_GROUP_FUNC_AND_FIELDS
mode).
mysql-test/r/group_by.result@stripped, 2008-02-22 05:41:09+04:00, gshchepa@stripped +150 -0
Updated test case for bug #27219.
mysql-test/t/group_by.test@stripped, 2008-02-22 05:41:11+04:00, gshchepa@stripped +109 -0
Updated test case for bug #27219.
sql/item.cc@stripped, 2008-02-22 05:41:12+04:00, gshchepa@stripped +35 -0
Fixed bug #27219.
Implementation of the Item::find_grouping_mix_processor function
has been added.
sql/item.h@stripped, 2008-02-22 05:41:13+04:00, gshchepa@stripped +6 -0
Fixed bug #27219.
Declaration of the Item::find_grouping_mix_processor function
has been added.
sql/item_sum.cc@stripped, 2008-02-22 05:41:13+04:00, gshchepa@stripped +6 -1
Fixed bug #27219.
The Item_sum::walk function has been modified to skip
argument processing if a processor function is equal
to Item::find_grouping_mix_processor.
sql/item_sum.h@stripped, 2008-02-22 05:41:14+04:00, gshchepa@stripped +2 -2
Fixed bug #27219.
Typo in the commentary has been fixed.
sql/sql_select.cc@stripped, 2008-02-22 05:41:15+04:00, gshchepa@stripped +8 -18
Fixed bug #27219.
JOIN::prepare: shallow testing of SELECT/HAVING item list
has been improved to recursively test items (see
Item::find_grouping_mix_processor) to take into account
not only simple items, but complex expression like
SUM(field1) + field2 (previously field2 was silently
ignored in this expression).
Also testing for grouping mixtures in the ORDER BY clause
has been added.
TODO: see bug#34751.
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 22:00:24 +04:00
+++ b/mysql-test/r/group_by.result 2008-02-22 05:41:09 +04:00
@@ -1213,4 +1213,154 @@ FROM t1;
ERROR 21000: Subquery returns more than 1 row
DROP TABLE t1;
SET @@sql_mode = @old_sql_mode;
+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 t1.a, SUM(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), 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)
+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 22:00:24 +04:00
+++ b/mysql-test/t/group_by.test 2008-02-22 05:41:11 +04:00
@@ -893,4 +893,113 @@ FROM t1;
DROP TABLE t1;
SET @@sql_mode = @old_sql_mode;
+
+#
+# Bug #27219: count(*) 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(*);
+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 t1.a, 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;
+
+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-02-12 23:51:00 +04:00
+++ b/sql/item.cc 2008-02-22 05:41:12 +04:00
@@ -473,6 +473,41 @@ bool Item::cleanup_processor(byte *arg)
return FALSE;
}
+/*
+ Find mixtures of set functions and non-grouping fields
+
+ SYNOPSIS
+ find_grouping_mix_processor()
+ arg - a pointer to the level_and_flag structure.
+
+ RETURN
+ TRUE if a mixture is found
+
+ NOTE
+ level_and_flag::flag is an input/output parameter and
+ a bitmask:
+ 1st bit is set if some set function was aggregated
+ at the level_and_flag::level.
+ 2nd bit is set if some non-constant field was
+ found.
+ TODO: grouping fields of an outer query should
+ be recognized as constant (bug#34751).
+*/
+
+bool Item::find_grouping_mix_processor(byte *arg)
+{
+ level_and_flag *lf= (level_and_flag *) arg;
+ if (lf->flag == 3)
+ return TRUE;
+ if (!(lf->flag & 1) && type() == SUM_FUNC_ITEM &&
+ ((Item_sum *) this)->aggr_level == lf->level)
+ lf->flag|= 1;
+ else if (!(lf->flag & 2) && type() == FIELD_ITEM &&
+ !const_during_execution())
+ lf->flag|= 2;
+ return lf->flag == 3;
+}
+
/*
rename item (used for views, cleanup() return original name)
diff -Nrup a/sql/item.h b/sql/item.h
--- a/sql/item.h 2008-01-27 09:45:30 +04:00
+++ b/sql/item.h 2008-02-22 05:41:13 +04:00
@@ -811,6 +811,12 @@ public:
*arg= NULL;
return TRUE;
}
+ /* Helper structure for the find_grouping_mix_processor argument passing */
+ struct level_and_flag {
+ int8 level; // current nesting level to match with the aggr_level
+ uint flag; // input/output bitmask (see processor function definition)
+ };
+ virtual bool find_grouping_mix_processor(byte *arg);
virtual Item *equal_fields_propagator(byte * arg) { return this; }
virtual bool set_no_const_sub(byte *arg) { return FALSE; }
diff -Nrup a/sql/item_sum.cc b/sql/item_sum.cc
--- a/sql/item_sum.cc 2007-12-21 14:44:21 +04:00
+++ b/sql/item_sum.cc 2008-02-22 05:41:13 +04:00
@@ -404,7 +404,12 @@ Item *Item_sum::get_tmp_table_item(THD *
bool Item_sum::walk (Item_processor processor, byte *argument)
{
- if (arg_count)
+ /*
+ Item::find_grouping_mix_processor collects matches with the
+ aggr_level values, but set function arguments may generate
+ wrong matches, so they should be ignored.
+ */
+ if (arg_count && processor != &Item::find_grouping_mix_processor)
{
Item **arg,**arg_end;
for (arg= args, arg_end= args+arg_count; arg != arg_end; arg++)
diff -Nrup a/sql/item_sum.h b/sql/item_sum.h
--- a/sql/item_sum.h 2007-12-14 15:24:18 +04:00
+++ b/sql/item_sum.h 2008-02-22 05:41:14 +04:00
@@ -179,8 +179,8 @@
nest level is less than max_arg_level. (Yet it can be aggregated in the
subqueries whose nest level is greater than max_arg_level.)
In the query
- SELECT t.a FROM t1 HAVING AVG(t1.a+(SELECT MIN(t2.c) FROM t2))
- the value of the max_arg_level for the AVG set function is 0 since
+ SELECT t1.a FROM t1 HAVING AVG(t1.a+(SELECT MIN(t2.c) FROM t2))
+ the value of the max_arg_level for the AVG set function is 1 since
the reference t2.c is bound in the subquery.
The field 'max_sum_func_level' is to contain the maximum of the
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2008-01-27 09:45:30 +04:00
+++ b/sql/sql_select.cc 2008-02-22 05:41:15 +04:00
@@ -568,31 +568,21 @@ 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 (!group_list)
{
- uint flag=0;
+ Item::level_and_flag lf= { thd->lex->current_select->nest_level, 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)
+ while ((item= it++) && lf.flag != 3)
+ item->walk(&Item::find_grouping_mix_processor, (byte *) &lf);
+ if (having && lf.flag != 3)
+ having->walk(&Item::find_grouping_mix_processor, (byte *) &lf);
+ for (ORDER *ord= order; ord && lf.flag != 3; ord= ord->next)
+ (*ord->item)->walk(&Item::find_grouping_mix_processor, (byte *) &lf);
+ if (lf.flag == 3)
{
my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
| Thread |
|---|
| • bk commit into 5.0 tree (gshchepa:1.2607) BUG#27219 | gshchepa | 22 Feb |
