bzr commit into mysql-5.1 branch (gshchepa:2669) Bug#36632

[Gleb Shchepa]

#At file:///work/bzr/5.1-bugteam-36632/

 2669 Gleb Shchepa	2008-06-27
      Fixed bug #36632: SELECT DISTINCT from a simple view on an
                        InnoDB table, where all selected columns
                        belong to the same unique index key, returns
                        incorrect results
      
      Server executes some queries via QUICK_GROUP_MIN_MAX_SELECT
      (MIN/MAX optimization for queries with GROUP BY or DISTINCT
      clause) and that optimization implies loose index scan, so all
      grouping is done by the QUICK_GROUP_MIN_MAX_SELECT::get_next
      method.
      
      The server does not set the precomputed_group_by flag for some
      QUICK_GROUP_MIN_MAX_SELECT queries and duplicates grouping by
      call to the end_send_group function.
      
      Fix: when the test_if_skip_sort_order function selects loose 
      index scan as a best way to satisfy an ORDER BY/GROUP BY type
      of query, the precomputed_group_by flag has been set to use 
      end_send/end_write functions instead of end_send_group/
      end_write_group functions.
modified:
  mysql-test/r/group_min_max_innodb.result
  mysql-test/t/group_min_max_innodb.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/group_min_max_innodb.result
    Fixed bug #36632: SELECT DISTINCT from a simple view on an
                      InnoDB table, where all selected columns
                      belong to the same unique index key, returns
                      incorrect results
  mysql-test/t/group_min_max_innodb.test
    Fixed bug #36632: SELECT DISTINCT from a simple view on an
                      InnoDB table, where all selected columns
                      belong to the same unique index key, returns
                      incorrect results
  sql/sql_select.cc
    Fixed bug #36632: SELECT DISTINCT from a simple view on an
                      InnoDB table, where all selected columns
                      belong to the same unique index key, returns
                      incorrect results
=== modified file 'mysql-test/r/group_min_max_innodb.result'
--- a/mysql-test/r/group_min_max_innodb.result	2006-05-22 12:10:02 +0000
+++ b/mysql-test/r/group_min_max_innodb.result	2008-06-27 18:50:53 +0000
@@ -1,3 +1,5 @@
+drop view if exists v1;
+drop table if exists t1,t4;
 create table t4 (
 pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16)
not null, d char(16), dummy char(64) default ' '
 ) engine=innodb;
@@ -70,3 +72,25 @@ explain select distinct f1, f2 from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	NULL	PRIMARY	5	NULL	3	Using index for group-by; Using temporary
 drop table t1;
+create table t1(pk int primary key) engine=innodb;
+create view v1 as select pk from t1 where pk < 20;
+insert into t1 values (1), (2), (3), (4);
+select distinct pk from v1;
+pk
+1
+2
+3
+4
+insert into t1 values (5), (6), (7);
+select distinct pk from v1;
+pk
+1
+2
+3
+4
+5
+6
+7
+drop view v1;
+drop table t1;
+End of 5.1 tests

=== modified file 'mysql-test/t/group_min_max_innodb.test'
--- a/mysql-test/t/group_min_max_innodb.test	2006-05-22 12:10:02 +0000
+++ b/mysql-test/t/group_min_max_innodb.test	2008-06-27 18:50:53 +0000
@@ -6,6 +6,11 @@
 
 --source include/have_innodb.inc
 
+--disable_warnings
+drop view if exists v1;
+drop table if exists t1,t4;
+--enable_warnings
+
 #
 # Bug #12672: primary key implcitly included in every innodb index
 #
@@ -93,3 +98,22 @@ alter table t1 drop primary key, add pri
 explain select distinct f1 a, f1 b from t1;
 explain select distinct f1, f2 from t1;
 drop table t1;
+
+
+#
+# Bug #36632: Select distinct from a simple view on an InnoDB table
+#             returns incorrect results
+#
+create table t1(pk int primary key) engine=innodb;
+create view v1 as select pk from t1 where pk < 20;
+
+insert into t1 values (1), (2), (3), (4);
+select distinct pk from v1;
+
+insert into t1 values (5), (6), (7);
+select distinct pk from v1;
+
+drop view v1;
+drop table t1;
+
+--echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-05-16 16:03:50 +0000
+++ b/sql/sql_select.cc	2008-06-27 18:50:53 +0000
@@ -13177,6 +13177,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
           tab->ref.key= -1;
           tab->ref.key_parts=0;		// Don't use ref key.
           tab->read_first_record= join_init_read_record;
+          if (tab->is_using_loose_index_scan())
+            join->tmp_table_param.precomputed_group_by= TRUE;
           /*
             TODO: update the number of records in join->best_positions[tablenr]
           */