bk commit into 4.1 tree (evgen:1.2525) BUG#8804

[eugene]

Below is the list of changes that have just been committed into a local
4.1 repository of evgen. When evgen 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, 2006-10-13 20:25:39+04:00, evgen@stripped +6 -0
  Bug#8804: Wrong result of NULL IN (subselect with index)
  
  If left expression of the IN (subselect with index) predicate is NULL then
  IN() was always returning NULL independently of the result of the subquery.
  
  Now subselect_indexsubquery_engine and subselect_uniquesubquery_engine engines
  checks whether the subquery has an empty result set when the ref key conatins a
  NULL part. Depending on the check result the Item_in_optimizer will return
  NULL or FALSE values to the caller.

  mysql-test/r/subselect.result@stripped, 2006-10-13 19:42:54+04:00, evgen@stripped +82
-18
    Added a test case for bug#8804: Wrong result of NULL IN (subselect with index)

  mysql-test/t/subselect.test@stripped, 2006-10-13 19:42:27+04:00, evgen@stripped +22
-0
    Added a test case for bug#8804: Wrong result of NULL IN (subselect with index)
    

  sql/item_cmpfunc.cc@stripped, 2006-10-13 20:22:52+04:00, evgen@stripped +22 -0
    Bug#8804: Wrong result of NULL IN (subselect with index)
    Added correct handling of NULL IN (subselect with index) to Item_in_optimizer.

  sql/item_subselect.cc@stripped, 2006-10-13 20:22:22+04:00, evgen@stripped +156 -38
    Bug#8804: Wrong result of NULL IN (subselect with index)
    Added correct handling of NULL IN (subselect with index) to
subselect_uniquesubquery_engine and subselect_indexsubquery_engine subselect engines.

  sql/item_subselect.h@stripped, 2006-10-13 19:44:08+04:00, evgen@stripped +18 -2
    Bug#8804: Wrong result of NULL IN (subselect with index)
    Added an is_empty flag to the Item_in_subselect class.
    The subselect_engine class now can return it's type by the means of the engine_type()
function.

  sql/sql_select.h@stripped, 2006-10-13 19:41:50+04:00, evgen@stripped +6 -3
    Bug#8804: Wrong result of NULL IN (subselect with index)
    Added a null_part field to the store_key class.

# 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:	evgen
# Host:	moonbone.local
# Root:	/work/8804-bug-4.1-opt-mysql

--- 1.212/sql/item_cmpfunc.cc	2006-10-13 20:25:42 +04:00
+++ 1.213/sql/item_cmpfunc.cc	2006-10-13 20:25:42 +04:00
@@ -780,10 +780,32 @@
 
 longlong Item_in_optimizer::val_int()
 {
+  Item_in_subselect *sub= (Item_in_subselect*)args[1];
   DBUG_ASSERT(fixed == 1);
   cache->store(args[0]);
   if (cache->null_value)
   {
+    subselect_engine::subselect_type subtype= sub->engine->engine_type();
+
+    /*
+      Handling of NULL IN (subselect with and index engine).
+      Single selects are allowed too as of they can be changed to an index
+      ones during the optimization stage.
+      See also the comment for the subselect_uniquesubquery_engine::exec()
+      function.
+    */
+    if (sub->substype() == Item_in_subselect::IN_SUBS &&
+        (subtype == subselect_engine::INDEX ||
+         subtype == subselect_engine::UNIQUE ||
+         (subtype == subselect_engine::SINGLE &&
+          !((subselect_single_select_engine*)sub->engine)->is_optimized())
+         ))
+    {
+      args[1]->val_int();
+      null_value= (sub->engine->engine_type() != subselect_engine::SINGLE) ?
+                   (!sub->is_empty) : 1;
+      return 0;
+    }
     null_value= 1;
     return 0;
   }

--- 1.81/sql/sql_select.h	2006-10-13 20:25:42 +04:00
+++ 1.82/sql/sql_select.h	2006-10-13 20:25:42 +04:00
@@ -364,9 +364,10 @@
   char *null_ptr;
   char err;
  public:
+  bool null_part; /* TRUE <=> the value of the key has a null part */
   enum store_key_result { STORE_KEY_OK, STORE_KEY_FATAL, STORE_KEY_CONV };
   store_key(THD *thd, Field *field_arg, char *ptr, char *null, uint length)
-    :null_ptr(null),err(0)
+    :null_ptr(null),err(0), null_part(0)
   {
     if (field_arg->type() == FIELD_TYPE_BLOB)
       to_field=new Field_varstring(ptr, length, (uchar*) null, 1, 
@@ -404,6 +405,7 @@
   enum store_key_result copy()
   {
     copy_field.do_copy(&copy_field);
+    null_part= to_field->is_null();
     return err != 0 ? STORE_KEY_FATAL : STORE_KEY_OK;
   }
   const char *name() const { return field_name; }
@@ -424,8 +426,8 @@
   enum store_key_result copy()
   {
     int res= item->save_in_field(to_field, 1);
-    return (err != 0 || res > 2 ? STORE_KEY_FATAL : (store_key_result) res); 
-	                 
+    null_part= to_field->is_null() | item->is_null();
+    return (err != 0 || res > 2 ? STORE_KEY_FATAL : (store_key_result) res);
   }
   const char *name() const { return "func"; }
 };
@@ -455,6 +457,7 @@
           err= res;
       }
     }
+    null_part= to_field->is_null() | item->null_value;
     return (err > 2 ?  STORE_KEY_FATAL : (store_key_result) err);
   }
   const char *name() const { return "const"; }

--- 1.176/mysql-test/r/subselect.result	2006-10-13 20:25:42 +04:00
+++ 1.177/mysql-test/r/subselect.result	2006-10-13 20:25:42 +04:00
@@ -773,16 +773,16 @@
 1
 select 10 IN (SELECT * from t1);
 10 IN (SELECT * from t1)
-NULL
+0
 select 1 > ALL (SELECT * from t1);
 1 > ALL (SELECT * from t1)
 0
 select 10 > ALL (SELECT * from t1);
 10 > ALL (SELECT * from t1)
-NULL
+1
 select 1 > ANY (SELECT * from t1);
 1 > ANY (SELECT * from t1)
-NULL
+0
 select 10 > ANY (SELECT * from t1);
 10 > ANY (SELECT * from t1)
 1
@@ -807,16 +807,16 @@
 1
 select 'XYZS' IN (SELECT * from t1);
 'XYZS' IN (SELECT * from t1)
-NULL
+0
 select 'A' > ALL (SELECT * from t1);
 'A' > ALL (SELECT * from t1)
 0
 select 'XYZS' > ALL (SELECT * from t1);
 'XYZS' > ALL (SELECT * from t1)
-NULL
+1
 select 'A' > ANY (SELECT * from t1);
 'A' > ANY (SELECT * from t1)
-NULL
+0
 select 'XYZS' > ANY (SELECT * from t1);
 'XYZS' > ANY (SELECT * from t1)
 1
@@ -841,16 +841,16 @@
 1
 select 10.5 IN (SELECT * from t1);
 10.5 IN (SELECT * from t1)
-NULL
+0
 select 1.5 > ALL (SELECT * from t1);
 1.5 > ALL (SELECT * from t1)
 0
 select 10.5 > ALL (SELECT * from t1);
 10.5 > ALL (SELECT * from t1)
-NULL
+1
 select 1.5 > ANY (SELECT * from t1);
 1.5 > ANY (SELECT * from t1)
-NULL
+0
 select 10.5 > ANY (SELECT * from t1);
 10.5 > ANY (SELECT * from t1)
 1
@@ -882,7 +882,7 @@
 1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	4	Using index
 2	DEPENDENT SUBQUERY	t2	index_subquery	a	a	5	func	2	Using index
 Warnings:
-Note	1003	select test.t1.a AS
`a`,<in_optimizer>(test.t1.a,<exists>(<index_lookup>(<cache>(test.t1.a)
in t2 on a chicking NULL))) AS `t1.a in (select t2.a from t2)` from test.t1
+Note	1003	select test.t1.a AS
`a`,<in_optimizer>(test.t1.a,<exists>(<index_lookup>(<cache>(test.t1.a)
in t2 on a))) AS `t1.a in (select t2.a from t2)` from test.t1
 CREATE TABLE t3 (a int(11) default '0');
 INSERT INTO t3 VALUES (1),(2),(3);
 SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
@@ -894,10 +894,10 @@
 explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	4	Using index
-2	DEPENDENT SUBQUERY	t2	ref_or_null	a	a	5	func	2	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ref	a	a	5	func	2	Using where; Using index
 2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
 Warnings:
-Note	1003	select test.t1.a AS `a`,<in_optimizer>(test.t1.a,<exists>(select 1
AS `Not_used` from test.t2 join test.t3 where ((test.t3.a = test.t2.a) and
((<cache>(test.t1.a) = test.t2.a) or isnull(test.t2.a))) having
<is_not_null_test>(test.t2.a))) AS `t1.a in (select t2.a from t2,t3 where
t3.a=t2.a)` from test.t1
+Note	1003	select test.t1.a AS `a`,<in_optimizer>(test.t1.a,<exists>(select 1
AS `Not_used` from test.t2 join test.t3 where ((test.t3.a = test.t2.a) and
(<cache>(test.t1.a) = test.t2.a)))) AS `t1.a in (select t2.a from t2,t3 where
t3.a=t2.a)` from test.t1
 drop table t1,t2,t3;
 create table t1 (a float);
 select 10.5 IN (SELECT * from t1 LIMIT 1);
@@ -1216,7 +1216,7 @@
 insert into t1 values (-1), (-4), (-2), (NULL);
 select -10 IN (select a from t1 FORCE INDEX (indexa));
 -10 IN (select a from t1 FORCE INDEX (indexa))
-NULL
+0
 drop table t1;
 create table t1 (id int not null auto_increment primary key, salary int, key(salary));
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
@@ -1454,25 +1454,25 @@
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
 2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
 Warnings:
-Note	1003	select test.t1.s1 AS
`s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1)
in t2 on s1 chicking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from test.t1
+Note	1003	select test.t1.s1 AS
`s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1)
in t2 on s1)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from test.t1
 explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
 2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
 Warnings:
-Note	1003	select test.t1.s1 AS
`s1`,<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1)
in t2 on s1 chicking NULL))) AS `s1 = ANY (SELECT s1 FROM t2)` from test.t1
+Note	1003	select test.t1.s1 AS
`s1`,<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1)
in t2 on s1))) AS `s1 = ANY (SELECT s1 FROM t2)` from test.t1
 explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
 2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
 Warnings:
-Note	1003	select test.t1.s1 AS
`s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1)
in t2 on s1 chicking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from test.t1
+Note	1003	select test.t1.s1 AS
`s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1)
in t2 on s1)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from test.t1
 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
-2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	1	Using index; Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index; Using where
 Warnings:
-Note	1003	select test.t1.s1 AS
`s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1)
in t2 on s1 chicking NULL where (test.t2.s1 < _latin1'a2'))))) AS `s1 NOT IN (SELECT s1
FROM t2 WHERE s1 < 'a2')` from test.t1
+Note	1003	select test.t1.s1 AS
`s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1)
in t2 on s1 where (test.t2.s1 < _latin1'a2'))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE
s1 < 'a2')` from test.t1
 drop table t1,t2;
 create table t2 (a int, b int);
 create table t3 (a int);
@@ -2850,3 +2850,67 @@
 f1
 1
 DROP TABLE t1, t2;
+create table t1(f1 int, f11 int);
+create table t2(f2 int, f22 int, index(f2));
+create table t3(f3 int, index(f3));
+insert into t1 values (null, null), (null, 0), (null, 3), (0, 0), (1, 1);
+insert into t2 values (null, null), (0, 0), (2, 2);
+select *, f1 not in (select f2 from t2) sub from t1;
+f1	f11	sub
+NULL	NULL	NULL
+NULL	0	NULL
+NULL	3	NULL
+0	0	0
+1	1	1
+select *, f1 in (select f2 from t2) sub from t1;
+f1	f11	sub
+NULL	NULL	NULL
+NULL	0	NULL
+NULL	3	NULL
+0	0	1
+1	1	0
+select *, f1 not in (select f2 from t2 where f22=f11) sub from t1;
+f1	f11	sub
+NULL	NULL	1
+NULL	0	NULL
+NULL	3	1
+0	0	0
+1	1	1
+select *, f1 in (select f2 from t2 where f22=f11) sub from t1;
+f1	f11	sub
+NULL	NULL	0
+NULL	0	NULL
+NULL	3	0
+0	0	1
+1	1	0
+create table t4(f4 int primary key, f44 int);
+insert into t4 values (0, 0), (2, 2);
+select *, f1 not in (select f4 from t4) sub from t1;
+f1	f11	sub
+NULL	NULL	NULL
+NULL	0	NULL
+NULL	3	NULL
+0	0	0
+1	1	1
+select *, f1 in (select f4 from t4) sub from t1;
+f1	f11	sub
+NULL	NULL	NULL
+NULL	0	NULL
+NULL	3	NULL
+0	0	1
+1	1	0
+select *, f1 not in (select f4 from t4 where f44=f11) sub from t1;
+f1	f11	sub
+NULL	NULL	1
+NULL	0	NULL
+NULL	3	1
+0	0	0
+1	1	1
+select *, f1 in (select f4 from t4 where f44=f11) sub from t1;
+f1	f11	sub
+NULL	NULL	0
+NULL	0	NULL
+NULL	3	0
+0	0	1
+1	1	0
+drop table t1,t2,t3,t4;

--- 1.154/mysql-test/t/subselect.test	2006-10-13 20:25:42 +04:00
+++ 1.155/mysql-test/t/subselect.test	2006-10-13 20:25:42 +04:00
@@ -1834,4 +1834,26 @@
 INSERT INTO t2 VALUES (2);
 SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0);
 DROP TABLE t1, t2;
+
+
+#
+# Bug 8804: Wrong NULL IN (subselect) result when an index for subselect is
+#           used
+#
+create table t1(f1 int, f11 int);
+create table t2(f2 int, f22 int, index(f2));
+create table t3(f3 int, index(f3));
+insert into t1 values (null, null), (null, 0), (null, 3), (0, 0), (1, 1); 
+insert into t2 values (null, null), (0, 0), (2, 2);
+select *, f1 not in (select f2 from t2) sub from t1; 
+select *, f1 in (select f2 from t2) sub from t1; 
+select *, f1 not in (select f2 from t2 where f22=f11) sub from t1; 
+select *, f1 in (select f2 from t2 where f22=f11) sub from t1; 
+create table t4(f4 int primary key, f44 int);
+insert into t4 values (0, 0), (2, 2);
+select *, f1 not in (select f4 from t4) sub from t1; 
+select *, f1 in (select f4 from t4) sub from t1; 
+select *, f1 not in (select f4 from t4 where f44=f11) sub from t1; 
+select *, f1 in (select f4 from t4 where f44=f11) sub from t1; 
+drop table t1,t2,t3,t4;
 # End of 4.1 tests

--- 1.140/sql/item_subselect.cc	2006-10-13 20:25:42 +04:00
+++ 1.141/sql/item_subselect.cc	2006-10-13 20:25:42 +04:00
@@ -527,7 +527,8 @@
 
 Item_in_subselect::Item_in_subselect(Item * left_exp,
 				     st_select_lex *select_lex):
-  Item_exists_subselect(), optimizer(0), transformed(0), upper_item(0)
+  Item_exists_subselect(), optimizer(0), transformed(0), upper_item(0),
+  is_empty(1)
 {
   DBUG_ENTER("Item_in_subselect::Item_in_subselect");
   left_expr= left_exp;
@@ -625,6 +626,9 @@
 longlong Item_in_subselect::val_int()
 {
   DBUG_ASSERT(fixed == 1);
+
+  null_value= 0;
+
   if (exec())
   {
     reset();
@@ -830,28 +834,7 @@
                                                    (longlong) 1, 21));
       select_lex->ref_pointer_array[0]= select_lex->item_list.head();
       item= func->create(expr, item);
-      if (!abort_on_null && orig_item->maybe_null)
-      {
-	having= new Item_is_not_null_test(this, having);
-	/*
-	  Item_is_not_null_test can't be changed during fix_fields()
-	  we can assign select_lex->having here, and pass 0 as last
-	  argument (reference) to fix_fields()
-	*/
-	select_lex->having=
-	  join->having= (join->having ?
-			 new Item_cond_and(having, join->having) :
-			 having);
-	select_lex->having_fix_field= 1;
-	if (join->having->fix_fields(thd, join->tables_list, 0))
-	{
-	  select_lex->having_fix_field= 0;
-	  DBUG_RETURN(RES_ERROR);
-	}
-	select_lex->having_fix_field= 0;
-	item= new Item_cond_or(item,
-			       new Item_func_isnull(orig_item));
-      }
+
       item->name= (char *)in_additional_cond;
       /*
 	AND can't be changed during fix_fields()
@@ -1460,19 +1443,155 @@
 }
 
 
-int subselect_uniquesubquery_engine::exec()
+/*
+  Copy ref key and check for null parts in it
+
+  SYNOPSIS
+    subselect_uniquesubquery_engine::copy_ref_key()
+
+  DESCRIPTION
+    Copy ref key and check for null parts in it.
+
+  RETURN
+    FALSE - ok
+    TRUE  - an error occured while copying the key
+*/
+
+bool subselect_uniquesubquery_engine::copy_ref_key()
 {
-  DBUG_ENTER("subselect_uniquesubquery_engine::exec");
-  int error;
-  TABLE *table= tab->table;
+  DBUG_ENTER("subselect_uniquesubquery_engine::copy_ref_key");
+
   for (store_key **copy=tab->ref.key_copy ; *copy ; copy++)
   {
-    if ((tab->ref.key_err= (*copy)->copy()) & 1)
+    tab->ref.key_err= (*copy)->copy();
+
+    /*
+      When there is a NULL part in the key we don't need to make index
+      lookup for such key thus we don't need to copy whole key.
+      If we later should do a sequential scan return OK. Fail otherwise.
+
+      See also the comment for the subselect_uniquesubquery_engine::exec()
+      function.
+    */
+    if ((*copy)->null_part && !((Item_in_subselect *) item)->abort_on_null)
+    {
+      null_part= 1;
+      break;
+    }
+    if ((tab->ref.key_err) & 1)
     {
-      table->status= STATUS_NOT_FOUND;
+      tab->table->status= STATUS_NOT_FOUND;
       DBUG_RETURN(1);
     }
   }
+  DBUG_RETURN(0);
+}
+
+
+/*
+  Search for at least on row satisfying select condition
+
+  SYNOPSIS
+    subselect_uniquesubquery_engine::scan_table()
+
+  DESCRIPTION
+    Scan the table using sequential access until we find at least one row
+    satisfying select condition. If the subquery is uncorrelated cache the
+    result and use it on the next call.
+
+  RETURN
+    FALSE - ok
+    TRUE  - an error occured while scanning
+*/
+
+int subselect_uniquesubquery_engine::scan_table()
+{
+  int error;
+  TABLE *table= tab->table;
+  DBUG_ENTER("subselect_uniquesubquery_engine::scan_table");
+
+  bool is_uncorrelated= !cond || !(cond->used_tables() & OUTER_REF_TABLE_BIT);
+
+  /* Use cached result for uncorrelated subqueries */
+  if (is_uncorrelated && is_empty_cached)
+  {
+    ((Item_in_subselect *) item)->is_empty= is_empty;
+    DBUG_RETURN(0);
+  }
+
+  if (table->file->inited)
+    table->file->ha_index_end();
+
+  table->file->ha_rnd_init(1);
+  table->file->extra_opt(HA_EXTRA_CACHE,
+                         current_thd->variables.read_buff_size);
+  table->null_row= 0;
+  for (;;)
+  {
+    error=table->file->rnd_next(table->record[0]);
+    if (error && error != HA_ERR_END_OF_FILE)
+    {
+      error= report_error(table, error);
+      break;
+    }
+    /* No more rows */
+    if (table->status)
+      break;
+
+    if (!cond || cond->val_int())
+    {
+      ((Item_in_subselect *) item)->is_empty= 0;
+      break;
+    }
+  }
+  /* Cache is_empty flag for uncorrelated subqueries */
+  if (is_uncorrelated && !is_empty_cached)
+  {
+    is_empty= ((Item_in_subselect *) item)->is_empty;
+    is_empty_cached= 1;
+  }
+
+  table->file->ha_rnd_end();
+  DBUG_RETURN(error != 0);
+}
+
+
+/*
+  Execute subselect
+
+  SYNOPSIS
+    subselect_uniquesubquery_engine::exec()
+
+  DESCRIPTION
+    Find rows corresponding to the ref key using index access.
+    If some part of the lookup key is NULL, then we're evaluating
+      NULL IN (SELECT ... )
+    This is a special case, we don't need to search for NULL in the table,
+    instead
+      the result value is NULL if select produces empty row set
+      the result value is FALSE otherwise.
+    In some cases (IN subselect is a top level item, i.e.
+    abort_on_null==TRUE) the caller doesn't distinguish between
+    the two possible results thus we just return FALSE. Otherwise we
+    make a full table scan to see if we could find one matching row.
+
+  RETURN
+    FALSE - ok
+    TRUE  - an error occured while scanning
+*/
+int subselect_uniquesubquery_engine::exec()
+{
+  DBUG_ENTER("subselect_uniquesubquery_engine::exec");
+  int error;
+  TABLE *table= tab->table;
+  ((Item_in_subselect *) item)->is_empty= 1;
+  null_part= 0;
+
+  if (copy_ref_key())
+    DBUG_RETURN(1);
+
+  if (null_part)
+    DBUG_RETURN(scan_table());
 
   if (!table->file->inited)
     table->file->ha_index_init(tab->ref.key);
@@ -1504,12 +1623,14 @@
 
 int subselect_indexsubquery_engine::exec()
 {
-  DBUG_ENTER("subselect_indexsubselect_engine::exec");
+  DBUG_ENTER("subselect_indexsubquery_engine::exec");
   int error;
   bool null_finding= 0;
   TABLE *table= tab->table;
 
   ((Item_in_subselect *) item)->value= 0;
+  ((Item_in_subselect *) item)->is_empty= 1;
+  null_part= 0;
 
   if (check_null)
   {
@@ -1518,14 +1639,11 @@
     ((Item_in_subselect *) item)->was_null= 0;
   }
 
-  for (store_key **copy=tab->ref.key_copy ; *copy ; copy++)
-  {
-    if ((tab->ref.key_err= (*copy)->copy()) & 1)
-    {
-      table->status= STATUS_NOT_FOUND;
-      DBUG_RETURN(1);
-    }
-  }
+  if (copy_ref_key())
+    DBUG_RETURN(1);
+
+  if (null_part)
+    DBUG_RETURN(scan_table());
 
   if (!table->file->inited)
     table->file->ha_index_init(tab->ref.key);

--- 1.62/sql/item_subselect.h	2006-10-13 20:25:42 +04:00
+++ 1.63/sql/item_subselect.h	2006-10-13 20:25:42 +04:00
@@ -217,11 +217,12 @@
   bool transformed;
 public:
   Item_func_not_all *upper_item; // point on NOT/NOP before ALL/SOME subquery
+  bool is_empty; // TRUE <=> the subselect has an empty result
 
   Item_in_subselect(Item * left_expr, st_select_lex *select_lex);
   Item_in_subselect()
     :Item_exists_subselect(), optimizer(0), abort_on_null(0), transformed(0),
-     upper_item(0)
+     upper_item(0), is_empty(1)
   {}
 
   subs_type substype() { return IN_SUBS; }
@@ -244,6 +245,7 @@
 
   friend class Item_ref_null_helper;
   friend class Item_is_not_null_test;
+  friend class subselect_uniquesubquery_engine;
   friend class subselect_indexsubquery_engine;
 };
 
@@ -276,6 +278,7 @@
   enum Item_result res_type; /* type of results */
   bool maybe_null; /* may be null (first item in select) */
 public:
+  enum subselect_type { SINGLE, UNION, INDEX, UNIQUE };
 
   subselect_engine(Item_subselect *si, select_subselect *res)
     :thd(0)
@@ -304,6 +307,7 @@
   virtual void print(String *str)= 0;
   virtual int change_item(Item_subselect *si, select_subselect *result)= 0;
   virtual bool no_tables()= 0;
+  virtual enum subselect_type engine_type()= 0;
 };
 
 
@@ -329,6 +333,8 @@
   void print (String *str);
   int change_item(Item_subselect *si, select_subselect *result);
   bool no_tables();
+  enum subselect_type engine_type() { return SINGLE; };
+  bool is_optimized() { return optimized; };
 };
 
 
@@ -350,6 +356,7 @@
   void print (String *str);
   int change_item(Item_subselect *si, select_subselect *result);
   bool no_tables();
+  enum subselect_type engine_type() { return UNION; };
 };
 
 
@@ -359,12 +366,17 @@
 protected:
   st_join_table *tab;
   Item *cond;
+  bool is_empty_cached; /* TRUE <=> the subquery is uncorrelated and it's
+                           is_empty flag is already cached */
+  bool is_empty; /* Cached is_empty flag for the uncorrelated subquery */
+  bool null_part;
 public:
 
   // constructor can assign THD because it will be called after JOIN::prepare
   subselect_uniquesubquery_engine(THD *thd_arg, st_join_table *tab_arg,
 				  Item_subselect *subs, Item *where)
-    :subselect_engine(subs, 0), tab(tab_arg), cond(where)
+    :subselect_engine(subs, 0), tab(tab_arg), cond(where), is_empty_cached(0),
+    is_empty(0)
   {
     set_thd(thd_arg);
   }
@@ -380,6 +392,9 @@
   void print (String *str);
   int change_item(Item_subselect *si, select_subselect *result);
   bool no_tables();
+  enum subselect_type engine_type() { return UNIQUE; };
+  bool copy_ref_key();
+  int scan_table();
 };
 
 
@@ -397,4 +412,5 @@
   {}
   int exec();
   void print (String *str);
+  enum subselect_type type() { return INDEX; };
 };