[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-internals
Subject:    bk commit into 4.1 tree (ram:1.1688)
From:       ramil () mysql ! com
Date:       2004-01-31 6:04:19
Message-ID: 200401310604.i0V64J7l087533 () gw ! mysql ! r18 ! ru
[Download RAW message or body]

Below is the list of changes that have just been committed into a local
4.1 repository of ram. When ram 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://www.mysql.com/doc/I/n/Installing_source_tree.html

ChangeSet
  1.1688 04/01/31 10:04:16 ram@gw.mysql.r18.ru +6 -0
  fix for the bug #2419: order by ignores rows.
  null_ref_key moved to TABLE_REF.
  new null range created if necessary.

  sql/sql_select.h
    1.64 04/01/31 10:03:53 ram@gw.mysql.r18.ru +2 -1
    fix for the bug #2419: order by ignores rows

  sql/sql_select.cc
    1.295 04/01/31 10:03:53 ram@gw.mysql.r18.ru +9 -7
    fix for the bug #2419: order by ignores rows

  sql/opt_range.cc
    1.102 04/01/31 10:03:53 ram@gw.mysql.r18.ru +19 -2
    fix for the bug #2419: order by ignores rows

  sql/item_subselect.cc
    1.95 04/01/31 10:03:53 ram@gw.mysql.r18.ru +2 -2
    fix for the bug #2419: order by ignores rows

  mysql-test/t/order_by.test
    1.21 04/01/31 10:03:53 ram@gw.mysql.r18.ru +12 -0
    fix for the bug #2419: order by ignores rows

  mysql-test/r/order_by.result
    1.34 04/01/31 10:03:53 ram@gw.mysql.r18.ru +21 -0
    fix for the bug #2419: order by ignores rows

# 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:	ram
# Host:	gw.mysql.r18.ru
# Root:	/usr/home/ram/work/4.1.b2419

--- 1.101/sql/opt_range.cc	Fri Dec 19 18:36:11 2003
+++ 1.102/sql/opt_range.cc	Sat Jan 31 10:03:53 2004
@@ -2513,8 +2513,25 @@
       key_part->part_length+=HA_KEY_BLOB_LENGTH;
     key_part->null_bit=     key_info->key_part[part].null_bit;
   }
-  if (!quick->ranges.push_back(range))
-    return quick;
+  if (quick->ranges.push_back(range))
+    goto err;
+
+  if (ref->null_ref_key)
+  {
+    QUICK_RANGE *null_range;
+
+    *ref->null_ref_key= 1;		// Set null byte then create a range
+    if (!(null_range= new QUICK_RANGE(ref->key_buff, ref->key_length,
+				      ref->key_buff, ref->key_length,
+				      EQ_RANGE)))
+      goto err;
+    *ref->null_ref_key= 0;		// Clear null byte
+    /* Do we need to do something with key_parts here? Looks like we don't */
+    if (quick->ranges.push_back(null_range))
+      goto err;
+  }
+
+  return quick;
 
 err:
   delete quick;

--- 1.294/sql/sql_select.cc	Fri Jan 30 11:39:57 2004
+++ 1.295/sql/sql_select.cc	Sat Jan 31 10:03:53 2004
@@ -860,8 +860,10 @@
     as in other cases the join is done before the sort.
   */
   if (const_tables != tables &&
-      (order || group_list) && join_tab[const_tables].type != JT_ALL &&
+      (order || group_list) && 
+      join_tab[const_tables].type != JT_ALL &&
       join_tab[const_tables].type != JT_FT &&
+      join_tab[const_tables].type != JT_REF_OR_NULL &&
       (order && simple_order || group_list && simple_group))
   {
     if (add_ref_to_table_cond(thd,&join_tab[const_tables]))
@@ -3257,7 +3259,7 @@
   {
     /* Must read with repeat */
     j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF;
-    j->null_ref_key= null_ref_key;
+    j->ref.null_ref_key= null_ref_key;
   }
   else if (ref_key == j->ref.key_copy)
   {
@@ -6208,12 +6210,12 @@
   int res;
 
   /* First read according to key which is NOT NULL */
-  *tab->null_ref_key=0;
+  *tab->ref.null_ref_key= 0;			// Clear null byte
   if ((res= join_read_always_key(tab)) >= 0)
     return res;
 
   /* Then read key with null value */
-  *tab->null_ref_key= 1;
+  *tab->ref.null_ref_key= 1;			// Set null byte
   return safe_index_read(tab);
 }
 
@@ -6227,10 +6229,10 @@
   JOIN_TAB *tab= info->table->reginfo.join_tab;
 
   /* Test if we have already done a read after null key */
-  if (*tab->null_ref_key)
+  if (*tab->ref.null_ref_key)
     return -1;					// All keys read
-  *tab->null_ref_key= 1;			// Read null key
-  return safe_index_read(tab);
+  *tab->ref.null_ref_key= 1;			// Set null byte
+  return safe_index_read(tab);			// then read null keys
 }
 
 

--- 1.63/sql/sql_select.h	Fri Nov 28 14:18:10 2003
+++ 1.64/sql/sql_select.h	Sat Jan 31 10:03:53 2004
@@ -46,6 +46,8 @@
   store_key     **key_copy;               //
   Item          **items;                  // val()'s for each keypart
   table_map	depend_map;		  // Table depends on these tables.
+  byte          *null_ref_key;		  // null byte position in the key_buf.
+  					  // used for REF_OR_NULL optimization.
 } TABLE_REF;
 
 /*
@@ -88,7 +90,6 @@
   QUICK_SELECT	*quick;
   Item		*on_expr;
   const char	*info;
-  byte		*null_ref_key;
   int		(*read_first_record)(struct st_join_table *tab);
   int		(*next_select)(JOIN *,struct st_join_table *,bool);
   READ_RECORD	read_record;

--- 1.94/sql/item_subselect.cc	Mon Jan 19 21:06:23 2004
+++ 1.95/sql/item_subselect.cc	Sat Jan 31 10:03:53 2004
@@ -1110,7 +1110,7 @@
   if (check_null)
   {
     /* We need to check for NULL if there wasn't a matching value */
-    *tab->null_ref_key= 0;			// Search first for not null
+    *tab->ref.null_ref_key= 0;			// Search first for not null
     ((Item_in_subselect *) item)->was_null= 0;
   }
 
@@ -1155,7 +1155,7 @@
 	{
 	  if (!check_null || null_finding)
 	    break;			/* We don't need to check nulls */
-	  *tab->null_ref_key= 1;
+	  *tab->ref.null_ref_key= 1;
 	  null_finding= 1;
 	  /* Check if there exists a row with a null value in the index */
 	  if ((error= (safe_index_read(tab) == 1)))

--- 1.33/mysql-test/r/order_by.result	Sun Dec 21 21:39:29 2003
+++ 1.34/mysql-test/r/order_by.result	Sat Jan 31 10:03:53 2004
@@ -590,3 +590,24 @@
 id
 11392
 drop table t1;
+create table t1(a int, b int, index(b));
+insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
+explain select * from t1 where b=1 or b is null order by a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref_or_null	b	b	5	const	3	Using where; Using filesort
+select * from t1 where b=1 or b is null order by a;
+a	b
+1	1
+2	1
+3	NULL
+4	NULL
+explain select * from t1 where b=2 or b is null order by a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref_or_null	b	b	5	const	4	Using where; Using filesort
+select * from t1 where b=2 or b is null order by a;
+a	b
+3	NULL
+4	NULL
+5	2
+6	2
+drop table t1;

--- 1.20/mysql-test/t/order_by.test	Sun Dec 21 21:39:29 2003
+++ 1.21/mysql-test/t/order_by.test	Sat Jan 31 10:03:53 2004
@@ -381,3 +381,15 @@
 INSERT INTO t1 VALUES (11384, 2),(11392, 2);
 SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ;
 drop table t1;
+
+#
+# REF_OR_NULL optimization + filesort (bug #2419)
+#
+
+create table t1(a int, b int, index(b));
+insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
+explain select * from t1 where b=1 or b is null order by a;
+select * from t1 where b=1 or b is null order by a;
+explain select * from t1 where b=2 or b is null order by a;
+select * from t1 where b=2 or b is null order by a;
+drop table t1;

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/internals?unsub=mysql-internals@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]