bk commit into 5.0 tree (kaa:1.2548) BUG#30666

[Alexey Kopytov]

Below is the list of changes that have just been committed into a local
5.0 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, 2007-11-07 14:00:45+03:00, kaa@polly.(none) +3 -0
  Fix for bug #30666: Incorrect order when using range conditions on 2
  tables or more
  
  The problem was that the optimizer used the join buffer in cases when
  the result set is ordered by filesort. This resulted in the ORDER BY
  clause being ignored, and the records being returned in the order
  determined by the order of matching records in the last table in join.
  
  Fixed by relaxing the condition in make_join_readinfo() to take
  filesort-ordered result sets into account, not only index-ordered ones.

  mysql-test/r/select.result@stripped, 2007-11-07 14:00:41+03:00, kaa@polly.(none) +39 -0
    Added a test case for bug #30666.

  mysql-test/t/select.test@stripped, 2007-11-07 14:00:41+03:00, kaa@polly.(none) +36 -0
    Added a test case for bug #30666.

  sql/sql_select.cc@stripped, 2007-11-07 14:00:42+03:00, kaa@polly.(none) +3 -4
    Relaxed the condition to determine when the join buffer usage must be
    disabled. The condition is now true for cases when the result set is
    ordered by filesort, that is when 'join->order &&
    !join->skip_sort_order' is true.

diff -Nrup a/mysql-test/r/select.result b/mysql-test/r/select.result
--- a/mysql-test/r/select.result	2007-09-13 17:31:07 +04:00
+++ b/mysql-test/r/select.result	2007-11-07 14:00:41 +03:00
@@ -4096,4 +4096,43 @@ SELECT `x` FROM v3;
 x
 1
 DROP VIEW v1, v2, v3;
+CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, 
+c22 INT DEFAULT NULL, 
+KEY(c21, c22));
+CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, 
+c32 INT DEFAULT NULL, 
+c33 INT NOT NULL, 
+c34 INT UNSIGNED DEFAULT 0,
+KEY (c33, c34, c32));
+INSERT INTO t1 values (),(),(),(),();
+INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b;
+INSERT INTO t3 VALUES (1, 1, 1, 0), 
+(2, 2, 0, 0), 
+(3, 3, 1, 0), 
+(4, 4, 0, 0), 
+(5, 5, 1, 0);
+SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 
+t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 
+t3.c33 = 1 AND t2.c22 in (1, 3) 
+ORDER BY c32;
+c32
+1
+1
+3
+3
+5
+5
+SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 
+t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 
+t3.c33 = 1 AND t2.c22 in (1, 3) 
+ORDER BY c32 DESC;
+c32
+5
+5
+3
+3
+1
+1
+DROP TABLE t1, t2, t3;
 End of 5.0 tests
diff -Nrup a/mysql-test/t/select.test b/mysql-test/t/select.test
--- a/mysql-test/t/select.test	2007-09-15 09:02:02 +04:00
+++ b/mysql-test/t/select.test	2007-11-07 14:00:41 +03:00
@@ -3484,4 +3484,40 @@ DROP VIEW v1, v2, v3;
 
 --enable_ps_protocol
 
+#
+# Bug #30666: Incorrect order when using range conditions on 2 tables or more
+#
+
+CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, 
+                 c22 INT DEFAULT NULL, 
+                 KEY(c21, c22));
+CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, 
+                 c32 INT DEFAULT NULL, 
+                 c33 INT NOT NULL, 
+                 c34 INT UNSIGNED DEFAULT 0,
+                 KEY (c33, c34, c32));
+
+INSERT INTO t1 values (),(),(),(),();
+INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b;
+INSERT INTO t3 VALUES (1, 1, 1, 0), 
+                      (2, 2, 0, 0), 
+                      (3, 3, 1, 0), 
+                      (4, 4, 0, 0), 
+                      (5, 5, 1, 0);
+
+# Show that ORDER BY produces the correct results order
+SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 
+                                 t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 
+                                 t3.c33 = 1 AND t2.c22 in (1, 3) 
+                           ORDER BY c32; 
+
+# Show that ORDER BY DESC produces the correct results order
+SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 
+                                 t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 
+                                 t3.c33 = 1 AND t2.c22 in (1, 3) 
+                           ORDER BY c32 DESC; 
+
+DROP TABLE t1, t2, t3;
+
 --echo End of 5.0 tests
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-10-02 18:45:48 +04:00
+++ b/sql/sql_select.cc	2007-11-07 14:00:42 +03:00
@@ -6071,10 +6071,9 @@ make_join_readinfo(JOIN *join, ulonglong
       ordered. If there is a temp table the ordering is done as a last
       operation and doesn't prevent join cache usage.
     */
-    if (!ordered_set && !join->need_tmp &&
-        ((table == join->sort_by_table &&
-         (!join->order || join->skip_sort_order)) ||
-        (join->sort_by_table == (TABLE *) 1 && i != join->const_tables)))
+    if (!ordered_set && !join->need_tmp && 
+        (table == join->sort_by_table ||
+         (join->sort_by_table == (TABLE *) 1 && i != join->const_tables)))
       ordered_set= 1;
 
     switch (tab->type) {