Merge lapi.mysql.com:/home/tkatchaounov/mysql/src/5.0#32694

into  lapi.mysql.com:/home/tkatchaounov/mysql/src/5.1#32694


mysql-test/r/type_datetime.result:
  Auto merged
mysql-test/t/type_datetime.test:
  Manual merge for BUG#32694.
sql/item_cmpfunc.cc:
  Manual merge for BUG#32694.

3 files changed, 130 insertions, 22 deletions

diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index c15deb8b1e5..e5592dc5dc8 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -505,6 +505,61 @@ select sum(a) from t1 group by convert(a, datetime);
 sum(a)
 NULL
 drop table t1;
+create table t1 (id int(10) not null, cur_date datetime not null);
+create table t2 (id int(10) not null, cur_date date not null);
+insert into t1 (id, cur_date) values (1, '2007-04-25 18:30:22');
+insert into t2 (id, cur_date) values (1, '2007-04-25');
+explain extended
+select * from t1
+where id in (select id from t1 as x1 where (t1.cur_date is null));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1276	Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
+Note	1003	select '1' AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where 0))
+select * from t1
+where id in (select id from t1 as x1 where (t1.cur_date is null));
+id	cur_date
+explain extended
+select * from t2
+where id in (select id from t2 as x1 where (t2.cur_date is null));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1276	Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
+Note	1003	select '1' AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where 0))
+select * from t2
+where id in (select id from t2 as x1 where (t2.cur_date is null));
+id	cur_date
+insert into t1 (id, cur_date) values (2, '2007-04-26 18:30:22');
+insert into t2 (id, cur_date) values (2, '2007-04-26');
+explain extended
+select * from t1
+where id in (select id from t1 as x1 where (t1.cur_date is null));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	x1	ALL	NULL	NULL	NULL	NULL	2	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`id`,<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where ((`test`.`t1`.`cur_date` = 0) and (<cache>(`test`.`t1`.`id`) = `test`.`x1`.`id`))))
+select * from t1
+where id in (select id from t1 as x1 where (t1.cur_date is null));
+id	cur_date
+explain extended
+select * from t2
+where id in (select id from t2 as x1 where (t2.cur_date is null));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	x1	ALL	NULL	NULL	NULL	NULL	2	Using where
+Warnings:
+Note	1276	Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where ((`test`.`t2`.`cur_date` = 0) and (<cache>(`test`.`t2`.`id`) = `test`.`x1`.`id`))))
+select * from t2
+where id in (select id from t2 as x1 where (t2.cur_date is null));
+id	cur_date
+drop table t1,t2;
 End of 5.0 tests
 set @org_mode=@@sql_mode;
 create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index fada7983c2c..7b2d2b46351 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -338,6 +338,43 @@ insert into t1 values (), (), ();
 select sum(a) from t1 group by convert(a, datetime);
 drop table t1;
 
+#
+# Bug #32694: NOT NULL table field in a subquery produces invalid results
+#
+create table t1 (id int(10) not null, cur_date datetime not null);
+create table t2 (id int(10) not null, cur_date date not null);
+insert into t1 (id, cur_date) values (1, '2007-04-25 18:30:22');
+insert into t2 (id, cur_date) values (1, '2007-04-25');
+
+explain extended
+select * from t1
+where id in (select id from t1 as x1 where (t1.cur_date is null));
+select * from t1
+where id in (select id from t1 as x1 where (t1.cur_date is null));
+
+explain extended
+select * from t2
+where id in (select id from t2 as x1 where (t2.cur_date is null));
+select * from t2
+where id in (select id from t2 as x1 where (t2.cur_date is null));
+
+insert into t1 (id, cur_date) values (2, '2007-04-26 18:30:22');
+insert into t2 (id, cur_date) values (2, '2007-04-26');
+
+explain extended
+select * from t1
+where id in (select id from t1 as x1 where (t1.cur_date is null));
+select * from t1
+where id in (select id from t1 as x1 where (t1.cur_date is null));
+
+explain extended
+select * from t2
+where id in (select id from t2 as x1 where (t2.cur_date is null));
+select * from t2
+where id in (select id from t2 as x1 where (t2.cur_date is null));
+
+drop table t1,t2;
+
 --echo End of 5.0 tests
 #
 # Test of storing datetime into date fields
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 235cce21fb1..94e24e51df5 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -24,7 +24,8 @@
 #include <m_ctype.h>
 #include "sql_select.h"
 
-static bool convert_constant_item(THD *thd, Field *field, Item **item);
+static bool convert_constant_item(THD *thd, Item_field *field_item,
+                                  Item **item);
 
 static Item_result item_store_type(Item_result a, Item *item,
                                    my_bool unsigned_flag)
@@ -351,7 +352,7 @@ longlong Item_func_nop_all::val_int()
   SYNOPSIS
     convert_constant_item()
     thd             thread handle
-    field           item will be converted using the type of this field
+    field_item      item will be converted using the type of this field
     item  [in/out]  reference to the item to convert
 
   DESCRIPTION
@@ -374,8 +375,10 @@ longlong Item_func_nop_all::val_int()
   1	Item was replaced with an integer version of the item
 */
 
-static bool convert_constant_item(THD *thd, Field *field, Item **item)
+static bool convert_constant_item(THD *thd, Item_field *field_item,
+                                  Item **item)
 {
+  Field *field= field_item->field;
   int result= 0;
 
   if (!(*item)->with_subselect && (*item)->const_item())
@@ -385,6 +388,7 @@ static bool convert_constant_item(THD *thd, Field *field, Item **item)
     enum_check_fields orig_count_cuted_fields= thd->count_cuted_fields;
     my_bitmap_map *old_write_map;
     my_bitmap_map *old_read_map;
+    ulonglong orig_field_val; /* original field value if valid */
 
     LINT_INIT(old_write_map);
     LINT_INIT(old_read_map);
@@ -398,6 +402,13 @@ static bool convert_constant_item(THD *thd, Field *field, Item **item)
     thd->variables.sql_mode= (orig_sql_mode & ~MODE_NO_ZERO_DATE) | 
                              MODE_INVALID_DATES;
     thd->count_cuted_fields= CHECK_FIELD_IGNORE;
+
+    /*
+      Store the value of the field if it references an outer field because
+      the call to save_in_field below overrides that value.
+    */
+    if (field_item->depended_from)
+      orig_field_val= field->val_int();
     if (!(*item)->is_null() && !(*item)->save_in_field(field, 1))
     {
       Item *tmp= new Item_int_with_ref(field->val_int(), *item,
@@ -406,6 +417,13 @@ static bool convert_constant_item(THD *thd, Field *field, Item **item)
         thd->change_item_tree(item, tmp);
       result= 1;					// Item was replaced
     }
+    /* Restore the original field value. */
+    if (field_item->depended_from)
+    {
+      result= field->store(orig_field_val, TRUE);
+      /* orig_field_val must be a valid value that can be restored back. */
+      DBUG_ASSERT(!result);
+    }
     thd->variables.sql_mode= orig_sql_mode;
     thd->count_cuted_fields= orig_count_cuted_fields;
     if (table)
@@ -462,15 +480,14 @@ void Item_bool_func2::fix_length_and_dec()
   thd= current_thd;
   if (!thd->is_context_analysis_only())
   {
-    Item *arg_real_item= args[0]->real_item();
-    if (arg_real_item->type() == FIELD_ITEM)
+    if (args[0]->real_item()->type() == FIELD_ITEM)
     {
-      Field *field=((Item_field*) arg_real_item)->field;
-      if (field->can_be_compared_as_longlong() &&
-          !(arg_real_item->is_datetime() &&
+      Item_field *field_item= (Item_field*) (args[0]->real_item());
+      if (field_item->field->can_be_compared_as_longlong() &&
+          !(field_item->is_datetime() &&
             args[1]->result_type() == STRING_RESULT))
       {
-        if (convert_constant_item(thd, field,&args[1]))
+        if (convert_constant_item(thd, field_item, &args[1]))
         {
           cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
                            INT_RESULT);		// Works for all types.
@@ -479,15 +496,14 @@ void Item_bool_func2::fix_length_and_dec()
         }
       }
     }
-    arg_real_item= args[1]->real_item();
-    if (arg_real_item->type() == FIELD_ITEM)
+    if (args[1]->real_item()->type() == FIELD_ITEM)
     {
-      Field *field=((Item_field*) arg_real_item)->field;
-      if (field->can_be_compared_as_longlong() &&
-          !(arg_real_item->is_datetime() &&
+      Item_field *field_item= (Item_field*) (args[1]->real_item());
+      if (field_item->field->can_be_compared_as_longlong() &&
+          !(field_item->is_datetime() &&
             args[0]->result_type() == STRING_RESULT))
       {
-        if (convert_constant_item(thd, field,&args[0]))
+        if (convert_constant_item(thd, field_item, &args[0]))
         {
           cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
                            INT_RESULT); // Works for all types.
@@ -1943,16 +1959,16 @@ void Item_func_between::fix_length_and_dec()
            thd->lex->sql_command != SQLCOM_CREATE_VIEW &&
            thd->lex->sql_command != SQLCOM_SHOW_CREATE)
   {
-    Field *field=((Item_field*) (args[0]->real_item()))->field;
-    if (field->can_be_compared_as_longlong())
+    Item_field *field_item= (Item_field*) (args[0]->real_item());
+    if (field_item->field->can_be_compared_as_longlong())
     {
       /*
         The following can't be recoded with || as convert_constant_item
         changes the argument
       */
-      if (convert_constant_item(thd, field,&args[1]))
+      if (convert_constant_item(thd, field_item, &args[1]))
         cmp_type=INT_RESULT;			// Works for all types.
-      if (convert_constant_item(thd, field,&args[2]))
+      if (convert_constant_item(thd, field_item, &args[2]))
         cmp_type=INT_RESULT;			// Works for all types.
     }
   }
@@ -3563,13 +3579,13 @@ void Item_func_in::fix_length_and_dec()
           thd->lex->sql_command != SQLCOM_SHOW_CREATE &&
           cmp_type != INT_RESULT)
       {
-        Field *field= ((Item_field*) (args[0]->real_item()))->field;
-        if (field->can_be_compared_as_longlong())
+        Item_field *field_item= (Item_field*) (args[0]->real_item());
+        if (field_item->field->can_be_compared_as_longlong())
         {
           bool all_converted= TRUE;
           for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++)
           {
-            if (!convert_constant_item (thd, field, &arg[0]))
+            if (!convert_constant_item (thd, field_item, &arg[0]))
               all_converted= FALSE;
           }
           if (all_converted)