bk commit into 5.1 tree (mhansson:1.2482) BUG#24778

[mhansson]

Below is the list of changes that have just been committed into a local
5.1 repository of martin. When martin 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-03-14 12:15:14+01:00, mhansson@stripped +3 -0
  Bug #24778: Innodb: No result when using ORDER BY
  
  This bug was intruduced by the fix for bug#17212 (in 4.1). It is not 
  ok to call test_if_skip_sort_order since this function will 
  alter the execution plan. By contract it is not ok to call 
  test_if_skip_sort_order in this context.
  
  This bug appears only in the case when the optimizer has chosen 
  an index for accessing a particular table but finds a covering 
  index that enables it to skip ORDER BY. This happens in 
  test_if_skip_sort_order.

  mysql-test/r/key.result@stripped, 2007-03-14 12:15:09+01:00, mhansson@stripped +41 -0
    Bug#24778
    
    test case.
    
    The bug causes the result to be the empty set.

  mysql-test/t/key.test@stripped, 2007-03-14 12:15:09+01:00, mhansson@stripped +48 -0
    Bug#24778
    
    The minimal test case that reveals the bug. The reason for such a 
    complicated schema is that we have to convince the optimizer to 
    pick one index, then discard it in order to be able to skip 
    ORDER BY.

  sql/sql_select.cc@stripped, 2007-03-14 12:15:10+01:00, mhansson@stripped +1 -4
    bug#24778
    
    Removed the call to test_if_skip_sort_order that constituted the
    bug.

# 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:	mhansson
# Host:	linux-st28.site
# Root:	/home/martin/mysql/src/5.1o-bug24778

--- 1.505/sql/sql_select.cc	2007-03-12 11:17:20 +01:00
+++ 1.506/sql/sql_select.cc	2007-03-14 12:15:10 +01:00
@@ -6106,10 +6106,7 @@ make_join_readinfo(JOIN *join, ulonglong
     */
     if (!ordered_set && 
         (table == join->sort_by_table &&
-         (!join->order || join->skip_sort_order ||
-          test_if_skip_sort_order(tab, join->order, join->select_limit,
-                                  1, &table->keys_in_use_for_order_by))
-        ) ||
+         (!join->order || join->skip_sort_order)) ||
         (join->sort_by_table == (TABLE *) 1 && i != join->const_tables))
       ordered_set= 1;
 

--- 1.41/mysql-test/r/key.result	2007-01-29 15:07:08 +01:00
+++ 1.42/mysql-test/r/key.result	2007-03-14 12:15:09 +01:00
@@ -489,3 +489,44 @@ EXPLAIN SELECT MAX(a) FROM t1 FORCE INDE
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
 drop table t1;
+CREATE TABLE t1 (
+a INTEGER auto_increment PRIMARY KEY,
+b INTEGER NOT NULL,
+c INTEGER NOT NULL,
+d CHAR(64)
+);
+CREATE TABLE t2 (
+a INTEGER auto_increment PRIMARY KEY,
+b INTEGER NOT NULL,
+c SMALLINT NOT NULL,
+d DATETIME NOT NULL,
+e SMALLINT NOT NULL,
+f INTEGER NOT NULL,
+g INTEGER NOT NULL,  
+h SMALLINT NOT NULL,
+i INTEGER NOT NULL,
+j INTEGER NOT NULL,
+UNIQUE INDEX (b),
+INDEX (b, d, e, f, g, h, i, j, c),
+INDEX (c)
+);
+INSERT INTO t2 VALUES 
+(NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0),
+(NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0),
+(NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0),
+(NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0),
+(NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0),
+(NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0),
+(NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0),
+(NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0);
+INSERT INTO t1 (b, c, d) VALUES
+(3388000, -553000, NULL),
+(3388000, -553000, NULL);
+SELECT *
+FROM t2 c JOIN t1 pa ON c.b = pa.a 
+WHERE c.c = 1
+ORDER BY c.b, c.d
+;
+a	b	c	d	e	f	g	h	i	j	a	b	c	d
[phone]:00:00	1	0	0	0	0	0	2	3388000	-553000	NULL
+DROP TABLE t1, t2;

--- 1.33/mysql-test/t/key.test	2007-01-29 16:16:15 +01:00
+++ 1.34/mysql-test/t/key.test	2007-03-14 12:15:09 +01:00
@@ -453,3 +453,51 @@ ALTER TABLE t1 DISABLE KEYS;
 EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
 
 drop table t1;
+
+#
+# Bug #24778: Innodb: No result when using ORDER BY
+#
+CREATE TABLE t1 (
+  a INTEGER auto_increment PRIMARY KEY,
+  b INTEGER NOT NULL,
+  c INTEGER NOT NULL,
+  d CHAR(64)
+);
+
+CREATE TABLE t2 (
+  a INTEGER auto_increment PRIMARY KEY,
+  b INTEGER NOT NULL,
+  c SMALLINT NOT NULL,
+  d DATETIME NOT NULL,
+  e SMALLINT NOT NULL,
+  f INTEGER NOT NULL,
+  g INTEGER NOT NULL,  
+  h SMALLINT NOT NULL,
+  i INTEGER NOT NULL,
+  j INTEGER NOT NULL,
+  UNIQUE INDEX (b),
+  INDEX (b, d, e, f, g, h, i, j, c),
+  INDEX (c)
+);
+
+INSERT INTO t2 VALUES 
+  (NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0),
+  (NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0),
+  (NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0),
+  (NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0),
+  (NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0),
+  (NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0),
+  (NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0),
+  (NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0);
+
+INSERT INTO t1 (b, c, d) VALUES
+  (3388000, -553000, NULL),
+  (3388000, -553000, NULL);
+
+SELECT *
+FROM t2 c JOIN t1 pa ON c.b = pa.a 
+WHERE c.c = 1
+ORDER BY c.b, c.d
+;
+
+DROP TABLE t1, t2;