bk commit into 5.0 tree (igor:1.2504) BUG#25602

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor 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, 2007-06-23 23:33:55-07:00, igor@stripped +3 -0
  Fixed bug #25602. A query with DISTINCT in the select list to which
  the loose scan optimization for grouping queries was applied returned 
  a wrong result set when the query was used with the SQL_BIG_RESULT
  option.
  
  The SQL_BIG_RESULT option forces to use sorting algorithm for grouping
  queries instead of employing a suitable index. The current loose scan
  optimization is applied only for one table queries when the suitable
  index is covering. It does not make sense to use sort algorithm in this
  case. However the create_sort_index function does not take into account
  the possible choice of the loose scan to implement the DISTINCT operator
  which makes sorting unnecessary. Moreover the current implementation of
  the loose scan for queries with distinct assumes that sorting will
  never happen. Thus in this case create_sort_index should not call
  the function filesort.

  mysql-test/r/group_min_max.result@stripped, 2007-06-23 23:33:52-07:00, igor@stripped +21 -0
    Added a test case for bug #25602.

  mysql-test/t/group_min_max.test@stripped, 2007-06-23 23:33:52-07:00, igor@stripped +22 -0
    Added a test case for bug #25602.

  sql/sql_select.cc@stripped, 2007-06-23 23:33:52-07:00, igor@stripped +6 -2
    Fixed bug #25602. A query with DISTINCT in the select list to which
    the loose scan optimization for grouping queries was applied returned 
    a wrong result set when the query was used with the SQL_BIG_RESULT
    option.
    
    The SQL_BIG_RESULT option forces to use sorting algorithm for grouping
    queries instead of employing a suitable index. The current loose scan
    optimization is applied only for one table queries when the suitable
    index is covering. It does not make sense to use sort algorithm in this
    case. However the create_sort_index function does not take into account
    the possible choice of the loose scan to implement the DISTINCT operator
    which makes sorting unnecessary. Moreover the current implementation of
    the loose scan for queries with distinct assumes that sorting will
    never happen. Thus in this case create_sort_index should not call
    the function filesort.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	olga.mysql.com
# Root:	/home/igor/dev-opt/mysql-5.0-opt-bug25602

--- 1.533/sql/sql_select.cc	2007-06-23 23:33:59 -07:00
+++ 1.534/sql/sql_select.cc	2007-06-23 23:33:59 -07:00
@@ -12552,10 +12552,14 @@
 
   /*
     When there is SQL_BIG_RESULT do not sort using index for GROUP BY,
-    and thus force sorting on disk.
+    and thus force sorting on disk unless a group min-max optimization
+    is going to be used as it is applied now only for one table queries
+    with covering indexes.
   */
   if ((order != join->group_list || 
-       !(join->select_options & SELECT_BIG_RESULT)) &&
+       !(join->select_options & SELECT_BIG_RESULT) ||
+       select && select->quick &&
+       select->quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) &&
       test_if_skip_sort_order(tab,order,select_limit,0))
     DBUG_RETURN(0);
   for (ORDER *ord= join->order; ord; ord= ord->next)

--- 1.28/mysql-test/r/group_min_max.result	2007-06-23 23:33:59 -07:00
+++ 1.29/mysql-test/r/group_min_max.result	2007-06-23 23:33:59 -07:00
@@ -2286,3 +2286,24 @@
 Handler_read_key	8
 Handler_read_next	1
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a int, INDEX idx(a));
+INSERT INTO t1 VALUES
+(4), (2), (1), (2), (4), (2), (1), (4),
+(4), (2), (1), (2), (2), (4), (1), (4);
+EXPLAIN SELECT DISTINCT(a) FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	idx	5	NULL	9	Using index for group-by
+SELECT DISTINCT(a) FROM t1;
+a
+1
+2
+4
+EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	idx	5	NULL	9	Using index for group-by
+SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
+a
+1
+2
+4
+DROP TABLE t1;

--- 1.27/mysql-test/t/group_min_max.test	2007-06-23 23:33:59 -07:00
+++ 1.28/mysql-test/t/group_min_max.test	2007-06-23 23:33:59 -07:00
@@ -870,3 +870,25 @@
 SHOW STATUS LIKE 'handler_read__e%';
 
 DROP TABLE t1,t2,t3;
+
+#
+# Bug#25602: queries with DISTINCT and SQL_BIG_RESULT hint 
+#            for which loose scan optimization is applied
+#
+
+CREATE TABLE t1 (a int, INDEX idx(a));
+INSERT INTO t1 VALUES
+  (4), (2), (1), (2), (4), (2), (1), (4),
+  (4), (2), (1), (2), (2), (4), (1), (4);
+
+EXPLAIN SELECT DISTINCT(a) FROM t1;
+SELECT DISTINCT(a) FROM t1;
+EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
+SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
+
+DROP TABLE t1;
+
+
+
+
+