bk commit into 5.1 tree (kaa:1.2569) BUG#34928

[Alexey Kopytov]

Below is the list of changes that have just been committed into a local
5.1 repository of kaa.  When kaa 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, 2008-03-26 19:37:36+03:00, kaa@kaamos.(none) +3 -0
  Fix for bug #34928: Confusion by having Primary Key and Index
  
  The bug is a regression introduced in 5.1 by the patch for bug28404.
  
  Under some circumstances test_if_skip_sort_order() could leave some
  data structures in an inconsistent state so that some parts of code
  could assume the selected execution strategy for GROUP BY/DISTINCT as
  a loose index scan (e.g. JOIN_TAB::is_using_loose_index_scan()), while
  the actual strategy chosen was an ordered index scan, which led to
  wrong data being returned.
  
  Fixed test_if_skip_sort_order() so that when changing the type for a
  join table, select->quick is reset not only for EXPLAIN, but for the 
  actual join execution as well, to not confuse code that depends on its
  value to determine the chosen GROUP BY/DISTINCT strategy.

  mysql-test/r/distinct.result@stripped, 2008-03-26 19:37:34+03:00, kaa@kaamos.(none) +23 -0
    Added a test case for bug #34928.

  mysql-test/t/distinct.test@stripped, 2008-03-26 19:37:34+03:00, kaa@kaamos.(none) +23 -0
    Added a test case for bug #34928.

  sql/sql_select.cc@stripped, 2008-03-26 19:37:34+03:00, kaa@kaamos.(none) +5 -5
    When changing the table's join type to JT_NEXT in 
    test_if_skip_sort_order(), also reset select->quick because other
    code may depend on its value to determine the GROUP BY/DISTINCT
    execution strategy.

diff -Nrup a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
--- a/mysql-test/r/distinct.result	2007-09-06 17:52:08 +04:00
+++ b/mysql-test/r/distinct.result	2008-03-26 19:37:34 +03:00
@@ -682,3 +682,26 @@ a	a	b
 1	1	3
 DROP TABLE t1;
 End of 5.0 tests
+CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT,
+PRIMARY KEY(a,b,c,d,e),
+KEY(a,b,d,c)
+);
+INSERT INTO t1(a, b, c) VALUES (1, 1, 1),
+(1, 1, 2),
+(1, 1, 3),
+(1, 2, 1),
+(1, 2, 2),
+(1, 2, 3);
+EXPLAIN SELECT DISTINCT a, b, d, c FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	16	NULL	6	Using index
+SELECT DISTINCT a, b, d, c FROM t1;
+a	b	d	c
+1	1	0	1
+1	1	0	2
+1	1	0	3
+1	2	0	1
+1	2	0	2
+1	2	0	3
+DROP TABLE t1;
+End of 5.1 tests
diff -Nrup a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
--- a/mysql-test/t/distinct.test	2007-08-02 23:45:49 +04:00
+++ b/mysql-test/t/distinct.test	2008-03-26 19:37:34 +03:00
@@ -553,3 +553,26 @@ SELECT DISTINCT a, a, b FROM t1;
 DROP TABLE t1;
 
 --echo End of 5.0 tests
+
+#
+# Bug #34928: Confusion by having Primary Key and Index
+#
+CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT,
+                PRIMARY KEY(a,b,c,d,e),
+                KEY(a,b,d,c)
+);
+
+INSERT INTO t1(a, b, c) VALUES (1, 1, 1),
+                               (1, 1, 2),
+                               (1, 1, 3),
+                               (1, 2, 1),
+                               (1, 2, 2),
+                               (1, 2, 3);
+
+EXPLAIN SELECT DISTINCT a, b, d, c FROM t1;
+
+SELECT DISTINCT a, b, d, c FROM t1;
+
+DROP TABLE t1;
+
+--echo End of 5.1 tests
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2008-03-12 11:13:22 +03:00
+++ b/sql/sql_select.cc	2008-03-26 19:37:34 +03:00
@@ -13158,6 +13158,11 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
           tab->read_first_record= best_key_direction > 0 ?
                                   join_read_first:join_read_last;
           tab->type=JT_NEXT;           // Read with index_first(), index_next()
+          if (select && select->quick)
+          {
+            delete select->quick;
+            select->quick= 0;
+          }
           if (table->covering_keys.is_set(best_key))
           {
             table->key_read=1;
@@ -13168,11 +13173,6 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
           {
             tab->ref.key= -1;
             tab->ref.key_parts= 0;
-            if (select && select->quick)
-            {
-              delete select->quick;
-              select->quick= 0;
-            }
             if (select_limit < table_records) 
               tab->limit= select_limit;
           }