Merge timka@10.100.64.80:/home/timka/mysql/src/4.1-bug-21787

into  dl145s.mysql.com:/data/tkatchaounov/5.0-bug-21787


mysql-test/r/limit.result:
  Auto merged
mysql-test/t/limit.test:
  Auto merged
sql/sql_select.cc:
  Adjust the fix for BUG#21787 for 5.0

3 files changed, 38 insertions, 7 deletions

diff --git a/mysql-test/r/limit.result b/mysql-test/r/limit.result
index 1e38f762dd1..be2776ef533 100644
--- a/mysql-test/r/limit.result
+++ b/mysql-test/r/limit.result
@@ -76,3 +76,17 @@ a
 a
 1
 drop table t1;
+create table t1 (a int);
+insert into t1 values (1),(2),(3),(4),(5),(6),(7);
+explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where; Using temporary
+select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+c
+7
+explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where; Using temporary
+select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+c
+28
diff --git a/mysql-test/t/limit.test b/mysql-test/t/limit.test
index 6df865278f6..cf7789428b2 100644
--- a/mysql-test/t/limit.test
+++ b/mysql-test/t/limit.test
@@ -60,4 +60,14 @@ select 1 as a from t1 union all select 1 from dual limit 1;
 (select 1 as a from t1) union all (select 1 from dual) limit 1;
 drop table t1;
 
+#
+# Bug #21787: COUNT(*) + ORDER BY + LIMIT returns wrong result
+#
+create table t1 (a int);
+insert into t1 values (1),(2),(3),(4),(5),(6),(7);
+explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
+
 # End of 4.1 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 2f16b350d04..3cfce32b603 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8939,11 +8939,6 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
       keyinfo->key_length+=  key_part_info->length;
     }
   }
-  else
-  {
-    set_if_smaller(table->s->max_rows, rows_limit);
-    param->end_write_records= rows_limit;
-  }
 
   if (distinct && field_count != param->hidden_field_count)
   {
@@ -8958,8 +8953,6 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
     null_pack_length-=hidden_null_pack_length;
     keyinfo->key_parts= ((field_count-param->hidden_field_count)+
 			 test(null_pack_length));
-    set_if_smaller(table->s->max_rows, rows_limit);
-    param->end_write_records= rows_limit;
     table->distinct= 1;
     table->s->keys= 1;
     if (blob_count)
@@ -9011,6 +9004,20 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
 	0 : FIELDFLAG_BINARY;
     }
   }
+
+  /*
+    Push the LIMIT clause to the temporary table creation, so that we
+    materialize only up to 'rows_limit' records instead of all result records.
+    This optimization is not applicable when there is GROUP BY or there is
+    no GROUP BY, but there are aggregate functions, because both must be
+    computed for all result rows.
+  */
+  if (!group && !thd->lex->current_select->with_sum_func)
+  {
+    set_if_smaller(table->s->max_rows, rows_limit);
+    param->end_write_records= rows_limit;
+  }
+
   if (thd->is_fatal_error)				// If end of memory
     goto err;					 /* purecov: inspected */
   table->s->db_record_offset= 1;