bk commit into 5.1 tree (gkodinov:1.2379)

[kgeorge]

Below is the list of changes that have just been committed into a local
5.1 repository of kgeorge. When kgeorge 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-01-08 12:32:48+02:00, gkodinov@stripped +15 -0
  Merge macbook.local:/Users/kgeorge/mysql/work/mysql-5.0-opt
  into  macbook.local:/Users/kgeorge/mysql/work/merge-5.1-opt
  MERGE: 1.1810.2362.43

  mysql-test/r/distinct.result@stripped, 2007-01-08 12:30:57+02:00, gkodinov@stripped +0
-0
    Auto merged
    MERGE: 1.46.1.4

  mysql-test/r/gis-rtree.result@stripped, 2007-01-08 12:30:57+02:00, gkodinov@stripped +0
-0
    Auto merged
    MERGE: 1.14.1.9

  mysql-test/r/ps.result@stripped, 2007-01-08 12:30:57+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.56.1.28

  mysql-test/r/subselect.result@stripped, 2007-01-08 12:30:58+02:00, gkodinov@stripped
+0 -0
    Auto merged
    MERGE: 1.134.1.33

  mysql-test/r/udf.result@stripped, 2007-01-08 12:30:58+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.3.1.7

  mysql-test/t/gis-rtree.test@stripped, 2007-01-08 12:30:58+02:00, gkodinov@stripped +0
-0
    Auto merged
    MERGE: 1.18.1.2

  mysql-test/t/ps.test@stripped, 2007-01-08 12:30:59+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.56.1.25

  mysql-test/t/subselect.test@stripped, 2007-01-08 12:30:59+02:00, gkodinov@stripped +0
-0
    Auto merged
    MERGE: 1.129.1.7

  mysql-test/t/udf.test@stripped, 2007-01-08 12:30:59+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.8.1.3

  sql/item_subselect.cc@stripped, 2007-01-08 12:31:00+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.113.1.32

  sql/item_subselect.h@stripped, 2007-01-08 12:31:00+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.77.1.8

  sql/sql_lex.cc@stripped, 2007-01-08 12:31:01+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.142.1.66

  sql/sql_select.cc@stripped, 2007-01-08 12:31:02+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.312.1.169

  sql/sql_udf.cc@stripped, 2007-01-08 12:32:41+02:00, gkodinov@stripped +0 -0
    SCCS merged
    MERGE: 1.45.1.17

  storage/myisam/mi_check.c@stripped, 2007-01-08 12:31:02+02:00, gkodinov@stripped +0 -0
    Auto merged
    MERGE: 1.129.24.2

  storage/myisam/mi_check.c@stripped, 2007-01-08 12:30:56+02:00, gkodinov@stripped
+0 -0
    Merge rename: myisam/mi_check.c -> storage/myisam/mi_check.c

# 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:	gkodinov
# Host:	macbook.local
# Root:	/Users/kgeorge/mysql/work/merge-5.1-opt/RESYNC

--- 1.129.24.1/myisam/mi_check.c	2007-01-03 09:12:57 +02:00
+++ 1.157/storage/myisam/mi_check.c	2007-01-08 12:31:02 +02:00
@@ -414,7 +414,7 @@ int chk_key(MI_CHECK *param, register MI
     puts("- check key delete-chain");
 
   param->key_file_blocks=info->s->base.keystart;
-  for (key=0 ; key < info->s->state.header.max_block_size ; key++)
+  for (key=0 ; key < info->s->state.header.max_block_size_index ; key++)
     if (check_k_link(param,info,key))
     {
       if (param->testflag & T_VERBOSE) puts("");
@@ -1493,7 +1493,7 @@ int mi_repair(MI_CHECK *param, register 
     share->state.key_root[i]= HA_OFFSET_ERROR;
 
   /* Drop the delete chain. */
-  for (i=0 ; i < share->state.header.max_block_size ; i++)
+  for (i=0 ; i < share->state.header.max_block_size_index ; i++)
     share->state.key_del[i]=  HA_OFFSET_ERROR;
 
   /*
@@ -1885,7 +1885,7 @@ int mi_sort_index(MI_CHECK *param, regis
   info->update= (short) (HA_STATE_CHANGED | HA_STATE_ROW_CHANGED);
   for (key=0 ; key < info->s->base.keys ; key++)
     info->s->state.key_root[key]=index_pos[key];
-  for (key=0 ; key < info->s->state.header.max_block_size ; key++)
+  for (key=0 ; key < info->s->state.header.max_block_size_index ; key++)
     info->s->state.key_del[key]=  HA_OFFSET_ERROR;
 
   info->s->state.changed&= ~STATE_NOT_SORTED_PAGES;
@@ -2188,7 +2188,7 @@ int mi_repair_by_sort(MI_CHECK *param, r
     /* Clear the pointers to the given rows */
     for (i=0 ; i < share->base.keys ; i++)
       share->state.key_root[i]= HA_OFFSET_ERROR;
-    for (i=0 ; i < share->state.header.max_block_size ; i++)
+    for (i=0 ; i < share->state.header.max_block_size_index ; i++)
       share->state.key_del[i]=  HA_OFFSET_ERROR;
     info->state->key_file_length=share->base.keystart;
   }
@@ -2210,6 +2210,7 @@ int mi_repair_by_sort(MI_CHECK *param, r
     my_seek(param->read_cache.file,0L,MY_SEEK_END,MYF(0));
 
   sort_param.wordlist=NULL;
+  init_alloc_root(&sort_param.wordroot, FTPARSER_MEMROOT_ALLOC_SIZE, 0);
 
   if (share->data_file_type == DYNAMIC_RECORD)
     length=max(share->base.min_pack_length+1,share->base.min_block_length);
@@ -2272,12 +2273,36 @@ int mi_repair_by_sort(MI_CHECK *param, r
     {
       uint ft_max_word_len_for_sort=FT_MAX_WORD_LEN_FOR_SORT*
                                     sort_param.keyinfo->seg->charset->mbmaxlen;
-      sort_info.max_records=
-        (ha_rows) (sort_info.filelength/ft_min_word_len+1);
+      sort_param.key_length+=ft_max_word_len_for_sort-HA_FT_MAXBYTELEN;
+      /*
+        fulltext indexes may have much more entries than the
+        number of rows in the table. We estimate the number here.
+
+        Note, built-in parser is always nr. 0 - see ftparser_call_initializer()
+      */
+      if (sort_param.keyinfo->ftparser_nr == 0)
+      {
+        /*
+          for built-in parser the number of generated index entries
+          cannot be larger than the size of the data file divided
+          by the minimal word's length
+        */
+        sort_info.max_records=
+          (ha_rows) (sort_info.filelength/ft_min_word_len+1);
+      }
+      else
+      {
+        /*
+          for external plugin parser we cannot tell anything at all :(
+          so, we'll use all the sort memory and start from ~10 buffpeks.
+          (see _create_index_by_sort)
+        */
+        sort_info.max_records=
+          10*param->sort_buffer_length/sort_param.key_length;
+      }
 
       sort_param.key_read=sort_ft_key_read;
       sort_param.key_write=sort_ft_key_write;
-      sort_param.key_length+=ft_max_word_len_for_sort-HA_FT_MAXBYTELEN;
     }
     else
     {
@@ -2294,6 +2319,7 @@ int mi_repair_by_sort(MI_CHECK *param, r
     }
     /* No need to calculate checksum again. */
     sort_param.calc_checksum= 0;
+    free_root(&sort_param.wordroot, MYF(0));
 
     /* Set for next loop */
     sort_info.max_records= (ha_rows) info->state->records;
@@ -2617,7 +2643,7 @@ int mi_repair_parallel(MI_CHECK *param, 
     /* Clear the pointers to the given rows */
     for (i=0 ; i < share->base.keys ; i++)
       share->state.key_root[i]= HA_OFFSET_ERROR;
-    for (i=0 ; i < share->state.header.max_block_size ; i++)
+    for (i=0 ; i < share->state.header.max_block_size_index ; i++)
       share->state.key_del[i]=  HA_OFFSET_ERROR;
     info->state->key_file_length=share->base.keystart;
   }
@@ -2742,6 +2768,7 @@ int mi_repair_parallel(MI_CHECK *param, 
       uint ft_max_word_len_for_sort=FT_MAX_WORD_LEN_FOR_SORT*
                                    
sort_param[i].keyinfo->seg->charset->mbmaxlen;
       sort_param[i].key_length+=ft_max_word_len_for_sort-HA_FT_MAXBYTELEN;
+      init_alloc_root(&sort_param[i].wordroot, FTPARSER_MEMROOT_ALLOC_SIZE, 0);
     }
   }
   sort_info.total_keys=i;
@@ -3016,10 +3043,11 @@ static int sort_ft_key_read(MI_SORT_PARA
   {
     for (;;)
     {
-      my_free((char*) wptr, MYF(MY_ALLOW_ZERO_PTR));
+      free_root(&sort_param->wordroot, MYF(MY_MARK_BLOCKS_FREE));
       if ((error=sort_get_next_record(sort_param)))
         DBUG_RETURN(error);
-      if (!(wptr=_mi_ft_parserecord(info,sort_param->key,sort_param->record)))
+      if (!(wptr=_mi_ft_parserecord(info,sort_param->key,sort_param->record,
+                                    &sort_param->wordroot)))
         DBUG_RETURN(1);
       if (wptr->pos)
         break;
@@ -3043,7 +3071,7 @@ static int sort_ft_key_read(MI_SORT_PARA
 #endif
   if (!wptr->pos)
   {
-    my_free((char*) sort_param->wordlist, MYF(0));
+    free_root(&sort_param->wordroot, MYF(MY_MARK_BLOCKS_FREE));
     sort_param->wordlist=0;
     error=sort_write_record(sort_param);
   }
@@ -4175,9 +4203,6 @@ int recreate_table(MI_CHECK *param, MI_I
 
   create_info.data_file_length=file_length;
   create_info.auto_increment=share.state.auto_increment;
-  create_info.raid_type=   share.base.raid_type;
-  create_info.raid_chunks= share.base.raid_chunks;
-  create_info.raid_chunksize= share.base.raid_chunksize;
   create_info.language = (param->language ? param->language :
 			  share.state.header.language);
   create_info.key_file_length=  status_info.key_file_length;

--- 1.214/sql/sql_lex.cc	2006-12-31 02:06:36 +02:00
+++ 1.215/sql/sql_lex.cc	2007-01-08 12:31:01 +02:00
@@ -1934,7 +1934,6 @@ void st_select_lex_unit::set_limit(SELEC
 {
   ha_rows select_limit_val;
 
-  DBUG_ASSERT(! thd->stmt_arena->is_stmt_prepare());
   select_limit_val= (ha_rows)(sl->select_limit ? sl->select_limit->val_uint() :
                                                  HA_POS_ERROR);
   offset_limit_cnt= (ha_rows)(sl->offset_limit ? sl->offset_limit->val_uint() :

--- 1.476/sql/sql_select.cc	2006-12-31 02:06:37 +02:00
+++ 1.477/sql/sql_select.cc	2007-01-08 12:31:02 +02:00
@@ -8593,6 +8593,46 @@ remove_eq_conds(THD *thd, COND *cond, It
   return cond;					// Point at next and level
 }
 
+/* 
+  Check if equality can be used in removing components of GROUP BY/DISTINCT
+  
+  SYNOPSIS
+    test_if_equality_guarantees_uniqueness()
+      l          the left comparison argument (a field if any)
+      r          the right comparison argument (a const of any)
+  
+  DESCRIPTION    
+    Checks if an equality predicate can be used to take away 
+    DISTINCT/GROUP BY because it is known to be true for exactly one 
+    distinct value (e.g. <expr> == <const>).
+    Arguments must be of the same type because e.g. 
+    <string_field> = <int_const> may match more than 1 distinct value from 
+    the column. 
+    We must take into consideration and the optimization done for various 
+    string constants when compared to dates etc (see Item_int_with_ref) as
+    well as the collation of the arguments.
+  
+  RETURN VALUE  
+    TRUE    can be used
+    FALSE   cannot be used
+*/
+static bool
+test_if_equality_guarantees_uniqueness(Item *l, Item *r)
+{
+  return r->const_item() &&
+    /* elements must be of the same result type */
+    (r->result_type() == l->result_type() ||
+    /* or dates compared to longs */
+     (((l->type() == Item::FIELD_ITEM &&
+        ((Item_field *)l)->field->can_be_compared_as_longlong()) ||
+       (l->type() == Item::FUNC_ITEM &&
+        ((Item_func *)l)->result_as_longlong())) &&
+      r->result_type() == INT_RESULT))
+    /* and must have the same collation if compared as strings */
+    && (l->result_type() != STRING_RESULT ||
+        l->collation.collation == r->collation.collation);
+}
+
 /*
   Return 1 if the item is a const value in all the WHERE clause
 */
@@ -8629,7 +8669,7 @@ const_expression_in_where(COND *cond, It
     Item *right_item= ((Item_func*) cond)->arguments()[1];
     if (left_item->eq(comp_item,1))
     {
-      if (right_item->const_item())
+      if (test_if_equality_guarantees_uniqueness (left_item, right_item))
       {
 	if (*const_item)
 	  return right_item->eq(*const_item, 1);
@@ -8639,7 +8679,7 @@ const_expression_in_where(COND *cond, It
     }
     else if (right_item->eq(comp_item,1))
     {
-      if (left_item->const_item())
+      if (test_if_equality_guarantees_uniqueness (right_item, left_item))
       {
 	if (*const_item)
 	  return left_item->eq(*const_item, 1);

--- 1.69/sql/sql_udf.cc	2006-12-23 21:19:55 +02:00
+++ 1.70/sql/sql_udf.cc	2007-01-08 12:32:41 +02:00
@@ -481,6 +481,8 @@ int mysql_drop_function(THD *thd,const L
   TABLE *table;
   TABLE_LIST tables;
   udf_func *udf;
+  char *exact_name_str;
+  uint exact_name_len;
   DBUG_ENTER("mysql_drop_function");
   if (!initialized)
   {
@@ -494,6 +496,8 @@ int mysql_drop_function(THD *thd,const L
     my_error(ER_FUNCTION_NOT_DEFINED, MYF(0), udf_name->str);
     goto err;
   }
+  exact_name_str= udf->name.str;
+  exact_name_len= udf->name.length;
   del_udf(udf);
   /*
     Close the handle if this was function that was found during boot or
@@ -508,7 +512,7 @@ int mysql_drop_function(THD *thd,const L
   if (!(table = open_ltable(thd,&tables,TL_WRITE)))
     goto err;
   table->use_all_columns();
-  table->field[0]->store(udf_name->str, udf_name->length,
system_charset_info);
+  table->field[0]->store(exact_name_str, exact_name_len, &my_charset_bin);
   if (!table->file->index_read_idx(table->record[0], 0,
 				   (byte*) table->field[0]->ptr,
 				   table->key_info[0].key_length,

--- 1.159/mysql-test/r/subselect.result	2006-12-03 19:49:21 +02:00
+++ 1.160/mysql-test/r/subselect.result	2007-01-08 12:30:58 +02:00
@@ -3033,6 +3033,19 @@ t3	CREATE TABLE `t3` (
   `a` datetime DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;
+a
+SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
+a
+1
+2
+EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+DROP TABLE t1;
 create table t1 (df decimal(5,1));
 insert into t1 values(1.1);
 insert into t1 values(2.2);

--- 1.139/mysql-test/t/subselect.test	2006-12-06 10:56:04 +02:00
+++ 1.140/mysql-test/t/subselect.test	2007-01-08 12:30:59 +02:00
@@ -1988,6 +1988,18 @@ SHOW CREATE TABLE t3;
 
 DROP TABLE t1,t2,t3;
 
+#
+# Bug 24670: subquery witout tables but with a WHERE clause
+#
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+
+SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;
+SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
+EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
+
+DROP TABLE t1;
 # End of 4.1 tests
 
 #

--- 1.139/sql/item_subselect.cc	2006-12-23 21:19:50 +02:00
+++ 1.140/sql/item_subselect.cc	2007-01-08 12:31:00 +02:00
@@ -391,6 +391,7 @@ Item_singlerow_subselect::select_transfo
       */
       !(select_lex->item_list.head()->type() == FIELD_ITEM ||
 	select_lex->item_list.head()->type() == REF_ITEM) &&
+      !join->conds && !join->having &&
       /*
         switch off this optimization for prepare statement,
         because we do not rollback this changes
@@ -415,8 +416,6 @@ Item_singlerow_subselect::select_transfo
     */
     substitution->walk(&Item::remove_dependence_processor, 0,
 		       (byte *) select_lex->outer_select());
-    /* SELECT without FROM clause can't have WHERE or HAVING clause */
-    DBUG_ASSERT(join->conds == 0 && join->having == 0);
     return RES_REDUCE;
   }
   return RES_OK;
@@ -2316,6 +2315,22 @@ bool subselect_uniquesubquery_engine::ch
 bool subselect_single_select_engine::no_tables()
 {
   return(select_lex->table_list.elements == 0);
+}
+
+
+/*
+  Check statically whether the subquery can return NULL
+
+  SINOPSYS
+    subselect_single_select_engine::may_be_null()
+
+  RETURN
+    FALSE  can guarantee that the subquery never return NULL
+    TRUE   otherwise
+*/
+bool subselect_single_select_engine::may_be_null()
+{
+  return ((no_tables() && !join->conds && !join->having) ?
maybe_null : 1);
 }
 
 

--- 1.85/sql/item_subselect.h	2006-12-23 21:19:50 +02:00
+++ 1.86/sql/item_subselect.h	2007-01-08 12:31:00 +02:00
@@ -363,7 +363,7 @@ public:
   enum Item_result type() { return res_type; }
   enum_field_types field_type() { return res_field_type; }
   virtual void exclude()= 0;
-  bool may_be_null() { return maybe_null; };
+  virtual bool may_be_null() { return maybe_null; };
   virtual table_map upper_select_const_tables()= 0;
   static table_map calc_const_tables(TABLE_LIST *);
   virtual void print(String *str)= 0;
@@ -400,6 +400,7 @@ public:
   void print (String *str);
   bool change_result(Item_subselect *si, select_subselect *result);
   bool no_tables();
+  bool may_be_null();
   bool is_executed() const { return executed; }
   bool no_rows();
 };

--- 1.49/mysql-test/r/distinct.result	2006-12-04 19:51:20 +02:00
+++ 1.50/mysql-test/r/distinct.result	2007-01-08 12:30:57 +02:00
@@ -607,3 +607,40 @@ id	select_type	table	type	possible_keys	
 SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;
 a	a
 DROP TABLE t1;
+CREATE TABLE t1 (a CHAR(1));
+INSERT INTO t1 VALUES('A'), (0);
+SELECT a FROM t1 WHERE a=0;
+a
+A
+0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'A'
+SELECT DISTINCT a FROM t1 WHERE a=0;
+a
+A
+0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'A'
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06');
+EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1
+WHERE ADDDATE(a,1) = '2002-08-03');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using temporary
+CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci);
+INSERT INTO t2 VALUES (0xf6);
+INSERT INTO t2 VALUES ('oe');
+SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2) dt;
+COUNT(*)
+2
+SELECT COUNT(*) FROM 
+(SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt;
+COUNT(*)
+2
+DROP TABLE t1, t2;

--- 1.15/mysql-test/r/udf.result	2006-12-05 19:52:53 +02:00
+++ 1.16/mysql-test/r/udf.result	2007-01-08 12:30:58 +02:00
@@ -232,6 +232,17 @@ DROP FUNCTION sequence;
 DROP FUNCTION lookup;
 DROP FUNCTION reverse_lookup;
 DROP FUNCTION avgcost;
+select * from mysql.func;
+name	ret	dl	type
+CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
+select IS_const(3);
+IS_const(3)
+const
+drop function IS_const;
+select * from mysql.func;
+name	ret	dl	type
+select is_const(3);
+ERROR 42000: FUNCTION test.is_const does not exist
 CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
 select
 is_const(3) as const,

--- 1.15/mysql-test/t/udf.test	2006-12-05 19:52:53 +02:00
+++ 1.16/mysql-test/t/udf.test	2007-01-08 12:30:59 +02:00
@@ -240,6 +240,23 @@ DROP FUNCTION reverse_lookup;
 DROP FUNCTION avgcost;
 
 #
+# Bug #15439: UDF name case handling forces DELETE FROM mysql.func to remove 
+#             the UDF
+# 
+select * from mysql.func;
+--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
+eval CREATE FUNCTION is_const RETURNS STRING SONAME "$UDF_EXAMPLE_LIB";
+
+select IS_const(3);
+
+drop function IS_const;
+
+select * from mysql.func;
+
+--error 1305
+select is_const(3);
+
+#
 # Bug#18761: constant expression as UDF parameters not passed in as constant
 #
 --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB

--- 1.26/mysql-test/r/gis-rtree.result	2006-11-19 20:19:48 +02:00
+++ 1.27/mysql-test/r/gis-rtree.result	2007-01-08 12:30:57 +02:00
@@ -881,3 +881,14 @@ ERROR 22003: Cannot get geometry object 
 INSERT INTO t1(foo) VALUES ('');
 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
 DROP TABLE t1;
+CREATE TABLE t1 (a INT AUTO_INCREMENT, b POINT NOT NULL, KEY (a), SPATIAL KEY (b));
+INSERT INTO t1 (b) VALUES (GeomFromText('POINT(1 2)'));
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	OK
+DROP TABLE t1;

--- 1.20/mysql-test/t/gis-rtree.test	2006-11-08 16:51:41 +02:00
+++ 1.21/mysql-test/t/gis-rtree.test	2007-01-08 12:30:58 +02:00
@@ -254,3 +254,20 @@ INSERT INTO t1() VALUES ();
 --error 1416
 INSERT INTO t1(foo) VALUES ('');
 DROP TABLE t1;
+
+#
+# Bug #23578: Corruption prevents Optimize table from working properly with a 
+#             spatial index
+#
+
+CREATE TABLE t1 (a INT AUTO_INCREMENT, b POINT NOT NULL, KEY (a), SPATIAL KEY (b));
+
+INSERT INTO t1 (b) VALUES (GeomFromText('POINT(1 2)'));
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+
+OPTIMIZE TABLE t1;
+DROP TABLE t1;

--- 1.94/mysql-test/r/ps.result	2006-12-04 20:10:13 +02:00
+++ 1.95/mysql-test/r/ps.result	2007-01-08 12:30:57 +02:00
@@ -1522,6 +1522,21 @@ Variable_name	Value
 Slow_queries	1
 deallocate prepare no_index;
 deallocate prepare sq;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (b int);
+INSERT INTO t2 VALUES (NULL);
+SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL;
+a
+1
+2
+PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL';
+EXECUTE stmt;
+a
+1
+2
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1,t2;
 End of 5.0 tests.
 create procedure proc_1() reset query cache;
 call proc_1();

--- 1.94/mysql-test/t/ps.test	2006-12-31 10:41:37 +02:00
+++ 1.95/mysql-test/t/ps.test	2007-01-08 12:30:59 +02:00
@@ -1576,6 +1576,24 @@ execute sq;
 deallocate prepare no_index;
 deallocate prepare sq;
 
+#
+# Bug 25027: query with a single-row non-correlated subquery
+#            and IS NULL predicate
+#
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (b int);
+INSERT INTO t2 VALUES (NULL);
+
+SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL;
+PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL';
+
+EXECUTE stmt;
+
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests.
 
 #