bk commit into 5.1 tree (mhansson:1.2409) 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-06 13:44:28+01:00, mhansson@stripped +4 -0
  Bug #24778: Innodb: No result when using ORDER BY
  
  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.
  
  The bug is in the assumption that: If we change the index for accessing
  a table in order to skip ORDER BY for a given plan operator, this plan 
  operator must be for the first non-const table in the plan. This is not 
  true: There can be other tables preceding it and the test case is an
  example of that. What happens is that
  create_ref_for_key is called with the set of const tables, in this case 
  the empty set. So here no key gets chosen, and we get the empty set.
  
  We should have to find all tables preceding the plan operator at hand 
  in this case. On the other hand, since we know that create_ref_for_key
  picked a key once, and we will simply swap it for another key for the same
  plan operator, it is enough to send in the set of just the previous table.
  It follows from the above reasoing that tab->key_dependent can be used,
  which is precomputed.

  mysql-test/r/key.result@stripped, 2007-03-06 13:44:21+01:00, mhansson@stripped +41 -0
    Bug#24791
    
    test case.
    
    The bug causes the result to be the empty set.

  mysql-test/t/key.test@stripped, 2007-03-06 13:44:21+01:00, mhansson@stripped +47 -0
    Bug#24791
    
    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-06 13:44:21+01:00, mhansson@stripped +36 -10
    Bug#24791
    
    In order of appearance:
    
    - Added documantation about key_dependent to add_key_field.
    
    - Aligned comments to the same column
    
    - Corrected typo
    
    - completed comments to test_if_subkey
    
    - The change that fixes the bug. See cset comment

  sql/sql_select.h@stripped, 2007-03-06 13:44:21+01:00, mhansson@stripped +23 -5
    Bug#24791
    
    In order of appearance:
    
    - I spent a long time to find out the semantics of JOIN_TAB::key_dependent
    so I hope this comment will save time for anyone else who needs to use it.
    
    - Added comment above the field JOIN::tables

# 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.485/sql/sql_select.cc	2007-01-25 00:30:31 +01:00
+++ 1.486/sql/sql_select.cc	2007-03-06 13:44:21 +01:00
@@ -2746,6 +2746,9 @@ merge_key_fields(KEY_FIELD *start,KEY_FI
     If we are doing a NOT NULL comparison on a NOT NULL field in a outer join
     table, we store this to be able to do not exists optimization later.
 
+    This function updates key_dependent for the field's JOIN_TAB by looping 
+    through the items in 'value' and collecting all tables.
+
   RETURN
     *key_fields is incremented if we stored a key in the array
 */
@@ -2799,8 +2802,8 @@ add_key_field(KEY_FIELD **key_fields,uin
 	Field op formula
 	Field IS NULL
 	Field IS NOT NULL
-         Field BETWEEN ...
-         Field IN ...
+	Field BETWEEN ...
+	Field IN ...
       */
       stat[0].key_dependent|=used_tables;
 
@@ -8272,7 +8275,7 @@ static void reset_nj_counters(List<TABLE
 
 
 /*
-  Check interleaving with an inner tables of an outer join for extension table 
+  Check interleaving with an inner table of an outer join for extension table 
 
   SYNOPSIS
     check_interleaving_with_nj()
@@ -12066,16 +12069,33 @@ is_subkey(KEY_PART_INFO *key_part, KEY_P
 }
 
 /*
-  Test if we can use one of the 'usable_keys' instead of 'ref' key for sorting
+  Test if we can use one of the 'usable_keys' instead of 'ref' key for sorting.
+  The function searches the shortest key that is sorted according to 'order'
+  and also covers 'ref', i.e. it consists of the same or more keyparts.
+  'ref' is typically used for ref access to the table.
 
   SYNOPSIS
     test_if_subkey()
-    ref			Number of key, used for WHERE clause
-    usable_keys		Keys for testing
+    order               Only keys sorted accordingly are considered
+    table               The table whose usable keys we scan through
+    ref                 Ordinal number of key, used for WHERE clause
+    ref_key_parts       Number of keyparts that make up the key
+    usable_keys         Keys that may be used
 
   RETURN
-    MAX_KEY			If we can't use other key
-    the number of found key	Otherwise
+    MAX_KEY			If we can't use any other key in usable_keys
+    the ordinal of found key	Otherwise
+
+  IMPLEMENTATION
+    Scans through all usable keys for 'table' and selects the one that:
+    
+      - Has the shortest key_length
+      - Has the greatest number of key parts.
+      - Is a superkey of 'ref'
+      - Is ordered in the same way
+
+    If several keys are eligible, the last one in table->key_info will be 
+    chosen.
 */
 
 static uint
@@ -12345,8 +12365,14 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
           KEYUSE *keyuse= tab->keyuse;
           while (keyuse->key != new_ref_key && keyuse->table ==
tab->table)
             keyuse++;
-          if (create_ref_for_key(tab->join, tab, keyuse, 
-                                 tab->join->const_table_map))
+	  /*
+	    We will now create the ref access method for the new key,
+	    and we know that another key was being used until now, so
+	    we know that key will be in the set tab->key_dependent. Therefore 
+	    we don't need to send the whole set of previous tables in the plan 
+	    to create_ref_for_key, as we would normally have to.
+	  */
+          if (create_ref_for_key(tab->join, tab, keyuse, tab->key_dependent))
             DBUG_RETURN(0);
 	}
 	else

--- 1.117/sql/sql_select.h	2007-01-24 20:24:50 +01:00
+++ 1.118/sql/sql_select.h	2007-03-06 13:44:21 +01:00
@@ -180,7 +180,22 @@ typedef struct st_join_table {
   */
   ha_rows       read_time;
   
-  table_map	dependent,key_dependent;
+  table_map	dependent;
+
+  /*
+    This member has a different purpose for outer and inner joins.
+
+    For outer joins:
+      This member echoes 'JOIN_TAB::dependent', i.e. they are the same. This is
+      initialized in make_join_statistics
+      
+    For inner joins:
+      The set of all tables that access to this table depends upon, such that 
+      there is a key that can be used for accessing this table. In other words, 
+      this is not the set of *all* tables whose access must precede access to 
+      this table.
+   */
+  table_map	key_dependent;
   uint		use_quick,index;
   uint		status;				// Save status for cache
   uint		used_fields,used_fieldlength,used_blobs;
@@ -258,10 +273,13 @@ public:
   JOIN_TAB **map2table;    // mapping between table indexes and JOIN_TABs
   JOIN_TAB *join_tab_save; // saved join_tab for subquery reexecution
   TABLE    **table,**all_tables,*sort_by_table;
-  uint	   tables,const_tables;
-  uint	   send_group_parts;
-  bool	   sort_and_group,first_record,full_join,group, no_field_update;
-  bool	   do_send_rows;
+
+  /* Total number of tables in the query */
+  uint     tables;
+  uint     const_tables;
+  uint     send_group_parts;
+  bool     sort_and_group, first_record, full_join,group, no_field_update;
+  bool     do_send_rows;
   /*
     TRUE when we want to resume nested loop iterations when
     fetching data from a cursor

--- 1.40/mysql-test/r/key.result	2006-08-11 23:06:20 +02:00
+++ 1.41/mysql-test/r/key.result	2007-03-06 13:44:21 +01:00
@@ -482,3 +482,44 @@ alter table t1 drop index i3, drop index
 alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);
 ERROR 23000: Duplicate entry '1' for key 'i1'
 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.31/mysql-test/t/key.test	2006-07-20 20:41:52 +02:00
+++ 1.32/mysql-test/t/key.test	2007-03-06 13:44:21 +01:00
@@ -442,3 +442,50 @@ alter table t1 drop index i3, drop index
 alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);
 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;