bk commit into 5.0 tree (mhansson:1.2459) BUG#24778

[mhansson]

Below is the list of changes that have just been committed into a local
5.0 repository of mhansson. When mhansson 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-04-20 11:01:53+02: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-04-20 11:01:51+02: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-04-20 11:01:51+02: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-04-20 11:01:51+02: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:	dl145s.mysql.com
# Root:	/dev/shm/5.0o-bug24778

--- 1.509/sql/sql_select.cc	2007-04-15 06:34:13 +02:00
+++ 1.510/sql/sql_select.cc	2007-04-20 11:01:51 +02:00
@@ -5982,10 +5982,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))
-        ) ||
+         (!join->order || join->skip_sort_order)) ||
         (join->sort_by_table == (TABLE *) 1 && i != join->const_tables))
       ordered_set= 1;
 

--- 1.35/mysql-test/r/key.result	2006-08-02 14:15:46 +02:00
+++ 1.36/mysql-test/r/key.result	2007-04-20 11:01:51 +02:00
@@ -414,3 +414,44 @@ a	int(11)	NO	PRI		
 b	varchar(20)	NO	MUL		
 c	varchar(20)	NO			
 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.28/mysql-test/t/key.test	2006-07-06 21:59:35 +02:00
+++ 1.29/mysql-test/t/key.test	2007-04-20 11:01:51 +02:00
@@ -384,3 +384,51 @@ desc t1;
 drop table t1;
 
 # End of 4.1 tests
+
+#
+# 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;