bk commit into 5.0 tree (igor:1.2032) BUG#16203

[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
  1.2032 06/02/06 11:35:13 igor@stripped +3 -0
  Fixed bug #16203.
  If check_quick_select returns non-empty range then the function cost_group_min_max
  cannot return 0 as an estimate of the number of retrieved records.
  Yet the function erroneously returned 0 as the estimate in some situations.

  sql/opt_range.cc
    1.204 06/02/06 11:35:06 igor@stripped +1 -0
    Fixed bug #16203.
    If check_quick_select returns non-empty range then the function cost_group_min_max
    cannot return 0 as an estimate of the number of retrieved records.
    Yet the function erroneously returned 0 as the estimate in some situations. 

  mysql-test/t/group_min_max.test
    1.18 06/02/06 11:35:06 igor@stripped +21 -0
    Added a test case for bug #16203.

  mysql-test/r/group_min_max.result
    1.20 06/02/06 11:35:05 igor@stripped +27 -0
    Added a test case for bug #16203.

# 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:	rurik.mysql.com
# Root:	/home/igor/dev/mysql-5.0-0

--- 1.203/sql/opt_range.cc	2006-01-06 08:36:09 -08:00
+++ 1.204/sql/opt_range.cc	2006-02-06 11:35:06 -08:00
@@ -7780,6 +7780,7 @@
     quick_prefix_selectivity= (double) quick_prefix_records /
                               (double) table_records;
     num_groups= (uint) rint(num_groups * quick_prefix_selectivity);
+    set_if_bigger(num_groups, 1);
   }
 
   if (used_key_parts > group_key_parts)

--- 1.19/mysql-test/r/group_min_max.result	2005-11-30 23:26:14 -08:00
+++ 1.20/mysql-test/r/group_min_max.result	2006-02-06 11:35:05 -08:00
@@ -2043,3 +2043,30 @@
 30	8
 30	9
 drop table t1;
+CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b));
+INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	OK
+SELECT a FROM t1 WHERE a='AA' GROUP BY a;
+a
+AA
+SELECT a FROM t1 WHERE a='BB' GROUP BY a;
+a
+BB
+EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	PRIMARY	PRIMARY	7	const	3	Using where; Using index
+EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	PRIMARY	PRIMARY	7	const	1	Using where; Using index
+SELECT DISTINCT a FROM t1 WHERE a='BB';
+a
+BB
+SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
+a
+BB
+SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;
+a
+BB
+DROP TABLE t1;

--- 1.17/mysql-test/t/group_min_max.test	2005-11-30 23:26:14 -08:00
+++ 1.18/mysql-test/t/group_min_max.test	2006-02-06 11:35:06 -08:00
@@ -715,3 +715,24 @@
 select c1,min(c2) as c2 from t1 group by c1 order by c2;
 select c1,c2 from t1 group by c1,c2 order by c2;
 drop table t1;
+
+#
+# Bug #16203: Analysis for possible min/max optimization erroneously
+#             returns impossible range
+#
+
+CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b));
+INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
+OPTIMIZE TABLE t1;
+
+SELECT a FROM t1 WHERE a='AA' GROUP BY a;
+SELECT a FROM t1 WHERE a='BB' GROUP BY a;
+
+EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;
+EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;
+
+SELECT DISTINCT a FROM t1 WHERE a='BB';
+SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
+SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;
+
+DROP TABLE t1;