#At file:///localhome/jl208045/mysql/mysql-5.1-bugteam-46518/ based on revid:joro@stripped
3155 Jorgen Loland 2009-10-05
Bug#47280 - strange results from count(*) with order by multiple
columns without where/group
Simple SELECT with implicit grouping returns many rows if the query
orders by the aggregated column in the SELECT list. Queries with
implicit grouping should only return a single record.
The problem is that the "if" in JOIN:exec() that decides if execution
needs to handle grouping assumes that tmp_table_param.sum_func_count==0
means that there are no aggregate functions in the query. This is
incorrect if sum_func_count was recounted after optimizing the aggregate
functions away. Consider two queries:
1) SELECT MAX(pk) max, pk FROM D ORDER BY max;
2) SELECT MAX(pk) max, pk FROM D;
max(pk) is optimized away by opt_sum_query() in JOIN::optimize for both
queries, but the sum_func_count is only recalculated for the
former query in the "if(need_tmp)" block in JOIN:exec().
Hence, when evaluating whether grouping is needed in JOIN::exec(),
sum_func_count is 0 for the first query and 1 for the second
query.
The fix for this bug is to remove the assumption that sum_func_count==0
means that there is no need for grouping. This is done by introducing
variable "bool implicit_grouping" in the JOIN object.
@ mysql-test/r/func_group.result
Add test for BUG#47280
@ mysql-test/t/func_group.test
Add test for BUG#47280
@ sql/sql_select.cc
Introduce and use variable implicit_grouping instead of (!group_list && sum_func_count) in places that need to test if grouping is required.
@ sql/sql_select.h
Add variable implicit_grouping, which will be TRUE for queries that contain aggregate functions but no GROUP BY clause.
modified:
mysql-test/r/func_group.result
mysql-test/t/func_group.test
sql/sql_select.cc
sql/sql_select.h
=== modified file 'mysql-test/r/func_group.result'
--- a/mysql-test/r/func_group.result 2009-04-01 11:10:03 +0000
+++ b/mysql-test/r/func_group.result 2009-10-05 12:35:54 +0000
@@ -1477,3 +1477,47 @@ COUNT(*)
SET SQL_MODE=default;
DROP TABLE t1;
End of 5.0 tests
+#
+# BUG#47280 - strange results from count(*) with order by multiple
+# columns without where/group
+#
+#
+# Initialize test
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+i INT,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,11),(2,12),(3,13);
+#
+# Start test
+# All the following queries shall return 1 record
+#
+
+# Masking all correct values {11...13} for column i in this result.
+SELECT MAX(pk) as max, i
+FROM t1
+ORDER BY max;
+max i
+3 #
+
+EXPLAIN
+SELECT MAX(pk) as max, i
+FROM t1
+ORDER BY max;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary
+
+# Only 11 is correct for collumn i in this result
+SELECT MAX(pk) as max, i
+FROM t1
+WHERE pk<2
+ORDER BY max;
+max i
+1 11
+#
+# Cleanup
+#
+DROP TABLE t1;
+End of 5.1 tests
=== modified file 'mysql-test/t/func_group.test'
--- a/mysql-test/t/func_group.test 2009-04-01 11:10:03 +0000
+++ b/mysql-test/t/func_group.test 2009-10-05 12:35:54 +0000
@@ -1006,3 +1006,51 @@ DROP TABLE t1;
###
--echo End of 5.0 tests
+
+--echo #
+--echo # BUG#47280 - strange results from count(*) with order by multiple
+--echo # columns without where/group
+--echo #
+
+--echo #
+--echo # Initialize test
+--echo #
+
+CREATE TABLE t1 (
+ pk INT NOT NULL,
+ i INT,
+ PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,11),(2,12),(3,13);
+
+--echo #
+--echo # Start test
+--echo # All the following queries shall return 1 record
+--echo #
+
+--echo
+--echo # Masking all correct values {11...13} for column i in this result.
+--replace_regex /1[1-3]/#/
+SELECT MAX(pk) as max, i
+FROM t1
+ORDER BY max;
+
+--echo
+EXPLAIN
+SELECT MAX(pk) as max, i
+FROM t1
+ORDER BY max;
+
+--echo
+--echo # Only 11 is correct for collumn i in this result
+SELECT MAX(pk) as max, i
+FROM t1
+WHERE pk<2
+ORDER BY max;
+
+--echo #
+--echo # Cleanup
+--echo #
+DROP TABLE t1;
+
+--echo End of 5.1 tests
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-09-28 13:48:40 +0000
+++ b/sql/sql_select.cc 2009-10-05 12:35:54 +0000
@@ -644,8 +644,11 @@ JOIN::prepare(Item ***rref_pointer_array
this->group= group_list != 0;
unit= unit_arg;
+ if (tmp_table_param.sum_func_count && !group_list)
+ implicit_grouping= TRUE;
+
#ifdef RESTRICTED_GROUP
- if (sum_func_count && !group_list && (func_count || field_count))
+ if (implicit_grouping)
{
my_message(ER_WRONG_SUM_SELECT,ER(ER_WRONG_SUM_SELECT),MYF(0));
goto err;
@@ -882,7 +885,7 @@ JOIN::optimize()
#endif
/* Optimize count(*), min() and max() */
- if (tables_list && tmp_table_param.sum_func_count && ! group_list)
+ if (tables_list && implicit_grouping)
{
int res;
/*
@@ -2015,7 +2018,7 @@ JOIN::exec()
count_field_types(select_lex, &curr_join->tmp_table_param,
*curr_all_fields, 0);
- if (curr_join->group || curr_join->tmp_table_param.sum_func_count ||
+ if (curr_join->group || curr_join->implicit_grouping ||
(procedure && (procedure->flags & PROC_GROUP)))
{
if (make_group_fields(this, curr_join))
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2009-07-16 13:17:47 +0000
+++ b/sql/sql_select.h 2009-10-05 12:35:54 +0000
@@ -278,6 +278,11 @@ public:
TABLE **table,**all_tables,*sort_by_table;
uint tables,const_tables;
uint send_group_parts;
+ /**
+ TRUE if the query contains an aggregate function but has no GROUP
+ BY clause.
+ */
+ bool implicit_grouping;
bool sort_and_group,first_record,full_join,group, no_field_update;
bool do_send_rows;
/**
@@ -426,6 +431,7 @@ public:
tables= 0;
const_tables= 0;
join_list= 0;
+ implicit_grouping= FALSE;
sort_and_group= 0;
first_record= 0;
do_send_rows= 1;
Attachment: [text/bzr-bundle] bzr/jorgen.loland@sun.com-20091005123554-nhhdyb5k79jzviy3.bundle
