Below is the list of changes that have just been committed into a local
5.0 repository of martin. When martin 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-01-04 15:26:46+01:00, mhansson@stripped +4 -0
Bug #27219: count(*) in order by
Aggregate functions were allowed in ORDER BY clauses even when sql mode
ONLY_FULL_GROUP_BY was set. Previously this was checked only for GROUP BY
clauses. Fixed by checking for aggregate functions in ORDER BY clause also.
mysql-test/r/order_by.result@stripped, 2008-01-04 15:26:44+01:00, mhansson@stripped +19 -0
Bug#27219: Correct result.
mysql-test/t/order_by.test@stripped, 2008-01-04 15:26:44+01:00, mhansson@stripped +33 -0
Bug#27219: Test case.
sql/mysql_priv.h@stripped, 2008-01-04 15:26:44+01:00, mhansson@stripped +2 -2
Bug#27219: New signature of setup_group
sql/sql_select.cc@stripped, 2008-01-04 15:26:44+01:00, mhansson@stripped +29 -23
Bug#27219:
In setup_group:
- renamed GROUP BY parameter 'order' to 'group'.
- added ORDER BY parameter 'order'.
- added a check to avoid error checking if none of the above are present.
- Moved The test that looks for aggregate functions violating the
MODE_ONLY_FULL_GROUP_BY rules to before the code that deals
exclusively with GROUP BY.
diff -Nrup a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
--- a/mysql-test/r/order_by.result 2007-04-04 07:24:55 +02:00
+++ b/mysql-test/r/order_by.result 2008-01-04 15:26:44 +01:00
@@ -1064,3 +1064,22 @@ a b
10 00:00:10
0 00:00:00
DROP TABLE t1;
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
+SET SQL_MODE='ONLY_FULL_GROUP_BY';
+SELECT a FROM t1 ORDER BY COUNT(*);
+ERROR 42000: 'test.t1.a' isn't in GROUP BY
+SELECT a FROM t1 ORDER BY COUNT(b);
+ERROR 42000: 'test.t1.a' isn't in GROUP BY
+SELECT a FROM t1 ORDER BY SUM(b);
+ERROR 42000: 'test.t1.a' isn't in GROUP BY
+SELECT a FROM t1 ORDER BY MAX(b);
+ERROR 42000: 'test.t1.a' isn't in GROUP BY
+SELECT a FROM t1 ORDER BY MIN(b);
+ERROR 42000: 'test.t1.a' isn't in GROUP BY
+SELECT a FROM t1 ORDER BY SUM(b);
+ERROR 42000: 'test.t1.a' isn't in GROUP BY
+SELECT a FROM t1 ORDER BY AVG(b);
+ERROR 42000: 'test.t1.a' isn't in GROUP BY
+SET SQL_MODE = default;
+DROP TABLE t1;
diff -Nrup a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
--- a/mysql-test/t/order_by.test 2007-06-01 15:49:00 +02:00
+++ b/mysql-test/t/order_by.test 2008-01-04 15:26:44 +01:00
@@ -726,3 +726,36 @@ SELECT a, b FROM t1 ORDER BY b DESC;
SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC;
DROP TABLE t1;
+
+#
+# Bug #27219: count(*) in order by
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
+
+SET SQL_MODE='ONLY_FULL_GROUP_BY';
+
+--error ER_WRONG_FIELD_WITH_GROUP
+SELECT a FROM t1 ORDER BY COUNT(*);
+
+--error ER_WRONG_FIELD_WITH_GROUP
+SELECT a FROM t1 ORDER BY COUNT(b);
+
+--error ER_WRONG_FIELD_WITH_GROUP
+SELECT a FROM t1 ORDER BY SUM(b);
+
+--error ER_WRONG_FIELD_WITH_GROUP
+SELECT a FROM t1 ORDER BY MAX(b);
+
+--error ER_WRONG_FIELD_WITH_GROUP
+SELECT a FROM t1 ORDER BY MIN(b);
+
+--error ER_WRONG_FIELD_WITH_GROUP
+SELECT a FROM t1 ORDER BY SUM(b);
+
+--error ER_WRONG_FIELD_WITH_GROUP
+SELECT a FROM t1 ORDER BY AVG(b);
+
+SET SQL_MODE = default;
+
+DROP TABLE t1;
diff -Nrup a/sql/mysql_priv.h b/sql/mysql_priv.h
--- a/sql/mysql_priv.h 2007-09-22 09:48:35 +02:00
+++ b/sql/mysql_priv.h 2008-01-04 15:26:44 +01:00
@@ -775,8 +775,8 @@ SORT_FIELD * make_unireg_sortorder(ORDER
int setup_order(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
List<Item> &fields, List <Item> &all_fields, ORDER *order);
int setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
- List<Item> &fields, List<Item> &all_fields, ORDER *order,
- bool *hidden_group_fields);
+ List<Item> &fields, List<Item> &all_fields, ORDER *group,
+ ORDER *order, bool *hidden_group_fields);
bool fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select,
Item **ref_pointer_array);
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2007-10-02 16:45:48 +02:00
+++ b/sql/sql_select.cc 2008-01-04 15:26:44 +01:00
@@ -399,7 +399,7 @@ inline int setup_without_group(THD *thd,
order);
thd->lex->allow_sum_func&= ~(1 << thd->lex->current_select->nest_level);
res= res || setup_group(thd, ref_pointer_array, tables, fields, all_fields,
- group, hidden_group_fields);
+ group, order, hidden_group_fields);
thd->lex->allow_sum_func= save_allow_sum_func;
DBUG_RETURN(res);
}
@@ -13527,13 +13527,14 @@ int setup_order(THD *thd, Item **ref_poi
thd Thread handler
ref_pointer_array We store references to all fields that was not in
'fields' here.
- fields All fields in the select part. Any item in 'order'
+ fields All fields in the select part. Any item in 'group'
that is part of these list is replaced by a pointer
to this fields.
all_fields Total list of all unique fields used by the select.
- All items in 'order' that was not part of fields will
+ All items in 'group' that was not part of fields will
be added first to this list.
- order The fields we should do GROUP BY on.
+ group The fields we should do GROUP BY on.
+ order The fields we should do ORDER BY on.
hidden_group_fields Pointer to flag that is set to 1 if we added any fields
to all_fields.
@@ -13544,30 +13545,15 @@ int setup_order(THD *thd, Item **ref_poi
int
setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
- List<Item> &fields, List<Item> &all_fields, ORDER *order,
- bool *hidden_group_fields)
+ List<Item> &fields, List<Item> &all_fields, ORDER *group,
+ ORDER *order, bool *hidden_group_fields)
{
*hidden_group_fields=0;
ORDER *ord;
- if (!order)
+ if (!group && !order)
return 0; /* Everything is ok */
- uint org_fields=all_fields.elements;
-
- thd->where="group statement";
- for (ord= order; ord; ord= ord->next)
- {
- if (find_order_in_list(thd, ref_pointer_array, tables, ord, fields,
- all_fields, TRUE))
- return 1;
- (*ord->item)->marker= UNDEF_POS; /* Mark found */
- if ((*ord->item)->with_sum_func)
- {
- my_error(ER_WRONG_GROUP_FIELD, MYF(0), (*ord->item)->full_name());
- return 1;
- }
- }
if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
{
/*
@@ -13611,7 +13597,7 @@ setup_group(THD *thd, Item **ref_pointer
Check whether the field occur in the GROUP BY list.
Throw the error later if the field isn't found.
*/
- for (ord= order; ord; ord= ord->next)
+ for (ord= group; ord; ord= ord->next)
if ((*ord->item)->eq((Item*)field, 0))
goto next_field;
/*
@@ -13627,6 +13613,26 @@ next_field:
cur_pos_in_select_list++;
}
}
+
+ if (!group)
+ return 0; /* Everything is ok */
+
+ uint org_fields=all_fields.elements;
+
+ thd->where="group statement";
+ for (ord= group; ord; ord= ord->next)
+ {
+ if (find_order_in_list(thd, ref_pointer_array, tables, ord, fields,
+ all_fields, TRUE))
+ return 1;
+ (*ord->item)->marker= UNDEF_POS; /* Mark found */
+ if ((*ord->item)->with_sum_func)
+ {
+ my_error(ER_WRONG_GROUP_FIELD, MYF(0), (*ord->item)->full_name());
+ return 1;
+ }
+ }
+
if (org_fields != all_fields.elements)
*hidden_group_fields=1; // group fields is not used
return 0;
| Thread |
|---|
| • bk commit into 5.0 tree (mhansson:1.2546) BUG#27219 | mhansson | 4 Jan |
