summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/func_in.result87
-rw-r--r--mysql-test/r/subselect.result102
-rw-r--r--mysql-test/t/func_in.test32
-rw-r--r--mysql-test/t/subselect.test45
-rw-r--r--sql/item.cc43
-rw-r--r--sql/item.h83
-rw-r--r--sql/item_cmpfunc.cc62
-rw-r--r--sql/item_cmpfunc.h24
-rw-r--r--sql/item_subselect.cc118
-rw-r--r--sql/item_subselect.h23
-rw-r--r--sql/sql_class.cc2
-rw-r--r--sql/sql_union.cc2
12 files changed, 572 insertions, 51 deletions
diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index ba33ee0831d..01d70c7c4c6 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -1,11 +1,95 @@
+select 1 in (1,2,3);
+1 in (1,2,3)
+1
+select 10 in (1,2,3);
+10 in (1,2,3)
+0
+select NULL in (1,2,3);
+NULL in (1,2,3)
+NULL
+select 1 in (1,NULL,3);
+1 in (1,NULL,3)
+1
+select 3 in (1,NULL,3);
+3 in (1,NULL,3)
+1
+select 10 in (1,NULL,3);
+10 in (1,NULL,3)
+NULL
+select 1.5 in (1.5,2.5,3.5);
+1.5 in (1.5,2.5,3.5)
+1
+select 10.5 in (1.5,2.5,3.5);
+10.5 in (1.5,2.5,3.5)
+0
+select NULL in (1.5,2.5,3.5);
+NULL in (1.5,2.5,3.5)
+NULL
+select 1.5 in (1.5,NULL,3.5);
+1.5 in (1.5,NULL,3.5)
+1
+select 3.5 in (1.5,NULL,3.5);
+3.5 in (1.5,NULL,3.5)
+1
+select 10.5 in (1.5,NULL,3.5);
+10.5 in (1.5,NULL,3.5)
+NULL
drop table if exists t1;
+CREATE TABLE t1 (a int, b int, c int);
+insert into t1 values (1,2,3), (1,NULL,3);
+select 1 in (a,b,c) from t1;
+1 in (a,b,c)
+1
+1
+select 3 in (a,b,c) from t1;
+3 in (a,b,c)
+1
+1
+select 10 in (a,b,c) from t1;
+10 in (a,b,c)
+0
+NULL
+select NULL in (a,b,c) from t1;
+NULL in (a,b,c)
+NULL
+NULL
+drop table t1;
+CREATE TABLE t1 (a float, b float, c float);
+insert into t1 values (1.5,2.5,3.5), (1.5,NULL,3.5);
+select 1.5 in (a,b,c) from t1;
+1.5 in (a,b,c)
+1
+1
+select 3.5 in (a,b,c) from t1;
+3.5 in (a,b,c)
+1
+1
+select 10.5 in (a,b,c) from t1;
+10.5 in (a,b,c)
+0
+NULL
+drop table t1;
+CREATE TABLE t1 (a varchar(10), b varchar(10), c varchar(10));
+insert into t1 values ('A','BC','EFD'), ('A',NULL,'EFD');
+select 'A' in (a,b,c) from t1;
+'A' in (a,b,c)
+1
+1
+select 'EFD' in (a,b,c) from t1;
+'EFD' in (a,b,c)
+1
+1
+select 'XSFGGHF' in (a,b,c) from t1;
+'XSFGGHF' in (a,b,c)
+0
+NULL
+drop table t1;
CREATE TABLE t1 (field char(1));
INSERT INTO t1 VALUES ('A'),(NULL);
SELECT * from t1 WHERE field IN (NULL);
field
SELECT * from t1 WHERE field NOT IN (NULL);
field
-A
SELECT * from t1 where field = field;
field
A
@@ -16,6 +100,7 @@ NULL
DELETE FROM t1 WHERE field NOT IN (NULL);
SELECT * FROM t1;
field
+A
NULL
drop table t1;
create table t1 (id int(10) primary key);
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index d442e4d97ce..8f3914fe493 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -598,3 +598,105 @@ INSERT INTO t1 values (1),(1);
UPDATE t SET id=(SELECT * FROM t1);
Subselect returns more than 1 record
drop table t;
+create table t (a int);
+insert into t values (1),(2),(3);
+select 1 IN (SELECT * from t);
+1 IN (SELECT * from t)
+1
+select 10 IN (SELECT * from t);
+10 IN (SELECT * from t)
+0
+select NULL IN (SELECT * from t);
+NULL IN (SELECT * from t)
+NULL
+update t set a=NULL where a=2;
+select 1 IN (SELECT * from t);
+1 IN (SELECT * from t)
+1
+select 3 IN (SELECT * from t);
+3 IN (SELECT * from t)
+1
+select 10 IN (SELECT * from t);
+10 IN (SELECT * from t)
+NULL
+select 1 > ALL (SELECT * from t);
+1 > ALL (SELECT * from t)
+0
+select 10 > ALL (SELECT * from t);
+10 > ALL (SELECT * from t)
+NULL
+select 1 > ANY (SELECT * from t);
+1 > ANY (SELECT * from t)
+NULL
+select 10 > ANY (SELECT * from t);
+10 > ANY (SELECT * from t)
+1
+drop table t;
+create table t (a varchar(20));
+insert into t values ('A'),('BC'),('DEF');
+select 'A' IN (SELECT * from t);
+'A' IN (SELECT * from t)
+1
+select 'XYZS' IN (SELECT * from t);
+'XYZS' IN (SELECT * from t)
+0
+select NULL IN (SELECT * from t);
+NULL IN (SELECT * from t)
+NULL
+update t set a=NULL where a='BC';
+select 'A' IN (SELECT * from t);
+'A' IN (SELECT * from t)
+1
+select 'DEF' IN (SELECT * from t);
+'DEF' IN (SELECT * from t)
+1
+select 'XYZS' IN (SELECT * from t);
+'XYZS' IN (SELECT * from t)
+NULL
+select 'A' > ALL (SELECT * from t);
+'A' > ALL (SELECT * from t)
+0
+select 'XYZS' > ALL (SELECT * from t);
+'XYZS' > ALL (SELECT * from t)
+NULL
+select 'A' > ANY (SELECT * from t);
+'A' > ANY (SELECT * from t)
+NULL
+select 'XYZS' > ANY (SELECT * from t);
+'XYZS' > ANY (SELECT * from t)
+1
+drop table t;
+create table t (a float);
+insert into t values (1.5),(2.5),(3.5);
+select 1.5 IN (SELECT * from t);
+1.5 IN (SELECT * from t)
+1
+select 10.5 IN (SELECT * from t);
+10.5 IN (SELECT * from t)
+0
+select NULL IN (SELECT * from t);
+NULL IN (SELECT * from t)
+NULL
+update t set a=NULL where a=2.5;
+select 1.5 IN (SELECT * from t);
+1.5 IN (SELECT * from t)
+1
+select 3.5 IN (SELECT * from t);
+3.5 IN (SELECT * from t)
+1
+select 10.5 IN (SELECT * from t);
+10.5 IN (SELECT * from t)
+NULL
+select 1.5 > ALL (SELECT * from t);
+1.5 > ALL (SELECT * from t)
+0
+select 10.5 > ALL (SELECT * from t);
+10.5 > ALL (SELECT * from t)
+NULL
+select 1.5 > ANY (SELECT * from t);
+1.5 > ANY (SELECT * from t)
+NULL
+select 10.5 > ANY (SELECT * from t);
+10.5 > ANY (SELECT * from t)
+1
+drop table t;
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index e5d42ec25c4..7bbc560276f 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -2,7 +2,39 @@
# test of IN (NULL)
#
+select 1 in (1,2,3);
+select 10 in (1,2,3);
+select NULL in (1,2,3);
+select 1 in (1,NULL,3);
+select 3 in (1,NULL,3);
+select 10 in (1,NULL,3);
+select 1.5 in (1.5,2.5,3.5);
+select 10.5 in (1.5,2.5,3.5);
+select NULL in (1.5,2.5,3.5);
+select 1.5 in (1.5,NULL,3.5);
+select 3.5 in (1.5,NULL,3.5);
+select 10.5 in (1.5,NULL,3.5);
drop table if exists t1;
+CREATE TABLE t1 (a int, b int, c int);
+insert into t1 values (1,2,3), (1,NULL,3);
+select 1 in (a,b,c) from t1;
+select 3 in (a,b,c) from t1;
+select 10 in (a,b,c) from t1;
+select NULL in (a,b,c) from t1;
+drop table t1;
+CREATE TABLE t1 (a float, b float, c float);
+insert into t1 values (1.5,2.5,3.5), (1.5,NULL,3.5);
+select 1.5 in (a,b,c) from t1;
+select 3.5 in (a,b,c) from t1;
+select 10.5 in (a,b,c) from t1;
+drop table t1;
+CREATE TABLE t1 (a varchar(10), b varchar(10), c varchar(10));
+insert into t1 values ('A','BC','EFD'), ('A',NULL,'EFD');
+select 'A' in (a,b,c) from t1;
+select 'EFD' in (a,b,c) from t1;
+select 'XSFGGHF' in (a,b,c) from t1;
+drop table t1;
+
CREATE TABLE t1 (field char(1));
INSERT INTO t1 VALUES ('A'),(NULL);
SELECT * from t1 WHERE field IN (NULL);
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 8b174882bc6..6a212c38255 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -362,3 +362,48 @@ INSERT INTO t1 values (1),(1);
-- error 1240
UPDATE t SET id=(SELECT * FROM t1);
drop table t;
+
+
+#NULL test
+create table t (a int);
+insert into t values (1),(2),(3);
+select 1 IN (SELECT * from t);
+select 10 IN (SELECT * from t);
+select NULL IN (SELECT * from t);
+update t set a=NULL where a=2;
+select 1 IN (SELECT * from t);
+select 3 IN (SELECT * from t);
+select 10 IN (SELECT * from t);
+select 1 > ALL (SELECT * from t);
+select 10 > ALL (SELECT * from t);
+select 1 > ANY (SELECT * from t);
+select 10 > ANY (SELECT * from t);
+drop table t;
+create table t (a varchar(20));
+insert into t values ('A'),('BC'),('DEF');
+select 'A' IN (SELECT * from t);
+select 'XYZS' IN (SELECT * from t);
+select NULL IN (SELECT * from t);
+update t set a=NULL where a='BC';
+select 'A' IN (SELECT * from t);
+select 'DEF' IN (SELECT * from t);
+select 'XYZS' IN (SELECT * from t);
+select 'A' > ALL (SELECT * from t);
+select 'XYZS' > ALL (SELECT * from t);
+select 'A' > ANY (SELECT * from t);
+select 'XYZS' > ANY (SELECT * from t);
+drop table t;
+create table t (a float);
+insert into t values (1.5),(2.5),(3.5);
+select 1.5 IN (SELECT * from t);
+select 10.5 IN (SELECT * from t);
+select NULL IN (SELECT * from t);
+update t set a=NULL where a=2.5;
+select 1.5 IN (SELECT * from t);
+select 3.5 IN (SELECT * from t);
+select 10.5 IN (SELECT * from t);
+select 1.5 > ALL (SELECT * from t);
+select 10.5 > ALL (SELECT * from t);
+select 1.5 > ANY (SELECT * from t);
+select 10.5 > ANY (SELECT * from t);
+drop table t;
diff --git a/sql/item.cc b/sql/item.cc
index 1c46f9abb7e..5cd3f64afd4 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -46,6 +46,12 @@ Item::Item():
loop_id= 0;
}
+Item_ref_in_optimizer::Item_ref_in_optimizer(Item_in_optimizer *master,
+ char *table_name_par,
+ char *field_name_par):
+ Item_ref(master->args, table_name_par, field_name_par), owner(master) {}
+
+
bool Item::check_loop(uint id)
{
DBUG_ENTER("Item::check_loop");
@@ -436,6 +442,20 @@ String *Item_copy_string::val_str(String *str)
return &str_value;
}
+double Item_ref_in_optimizer::val()
+{
+ return owner->get_cache();
+}
+longlong Item_ref_in_optimizer::val_int()
+{
+ return owner->get_cache_int();
+}
+String* Item_ref_in_optimizer::val_str(String* s)
+{
+ return owner->get_cache_str(s);
+}
+
+
/*
Functions to convert item to field (for send_fields)
*/
@@ -511,10 +531,31 @@ bool Item_asterisk_remover::fix_fields(THD *thd,
res= item->fix_fields(thd, list, &item);
else
thd->fatal_error= 1; // no item given => out of memory
- *ref= item;
DBUG_RETURN(res);
}
+double Item_ref_null_helper::val()
+{
+ double tmp= (*ref)->val_result();
+ owner->was_null|= null_value= (*ref)->is_null_result();
+ return tmp;
+}
+longlong Item_ref_null_helper::val_int()
+{
+ longlong tmp= (*ref)->val_int_result();
+ owner->was_null|= null_value= (*ref)->is_null_result();
+ return tmp;
+}
+String* Item_ref_null_helper::val_str(String* s)
+{
+ String* tmp= (*ref)->str_result(s);
+ owner->was_null|= null_value= (*ref)->is_null_result();
+ return tmp;
+}
+bool Item_ref_null_helper::get_date(TIME *ltime, bool fuzzydate)
+{
+ return (owner->was_null|= null_value= (*ref)->get_date(ltime, fuzzydate));
+}
bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
{
diff --git a/sql/item.h b/sql/item.h
index ae671c5141c..37c8e42c832 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -122,6 +122,18 @@ public:
String* val_str(String* s) { return item->val_str(s); }
void make_field(Send_field* f) { item->make_field(f); }
bool check_cols(uint col) { return item->check_cols(col); }
+ bool eq(const Item *item, bool binary_cmp) const
+ { return item->eq(item, binary_cmp); }
+ bool is_null() { return item->is_null_result(); }
+ bool get_date(TIME *ltime, bool fuzzydate)
+ {
+ return (null_value=item->get_date(ltime, fuzzydate));
+ }
+ bool send(THD *thd, String *tmp) { return item->send(thd, tmp); }
+ int save_in_field(Field *field) { return item->save_in_field(field); }
+ void save_org_in_field(Field *field) { item->save_org_in_field(field); }
+ enum Item_result result_type () const { return item->result_type(); }
+ table_map used_tables() const { return item->used_tables(); }
};
@@ -138,19 +150,6 @@ public:
bool fix_fields(THD *, struct st_table_list *, Item ** ref);
};
-/*
- To resolve '*' field moved to condition
-*/
-class Item_asterisk_remover :public Item_wrapper
-{
-public:
- Item_asterisk_remover(Item *it)
- {
- item= it;
- }
- bool fix_fields(THD *, struct st_table_list *, Item ** ref);
-};
-
class st_select_lex;
class Item_ident :public Item
{
@@ -378,9 +377,13 @@ public:
enum Item_result result_type () const { return STRING_RESULT; }
bool basic_const_item() const { return 1; }
bool eq(const Item *item, bool binary_cmp) const;
- Item *new_item() { return new Item_string(name,str_value.ptr(),max_length,default_charset_info); }
+ Item *new_item()
+ {
+ return new Item_string(name, str_value.ptr(), max_length,
+ default_charset_info);
+ }
String *const_string() { return &str_value; }
- inline void append(char *str,uint length) { str_value.append(str,length); }
+ inline void append(char *str, uint length) { str_value.append(str, length); }
void print(String *str);
};
@@ -491,13 +494,61 @@ public:
bool send(THD *thd, String *tmp) { return (*ref)->send(thd, tmp); }
void make_field(Send_field *field) { (*ref)->make_field(field); }
bool fix_fields(THD *, struct st_table_list *, Item **);
- int save_in_field(Field *field) { return (*ref)->save_in_field(field); }
+ int save_in_field(Field *field) { return (*ref)->save_in_field(field); }
void save_org_in_field(Field *field) { (*ref)->save_org_in_field(field); }
enum Item_result result_type () const { return (*ref)->result_type(); }
table_map used_tables() const { return (*ref)->used_tables(); }
bool check_loop(uint id);
};
+class Item_in_subselect;
+class Item_ref_null_helper: public Item_ref
+{
+protected:
+ Item_in_subselect* owner;
+public:
+ Item_ref_null_helper(Item_in_subselect* master, Item **item,
+ char *table_name_par,char *field_name_par):
+ Item_ref(item, table_name_par, field_name_par), owner(master) {}
+ double val();
+ longlong val_int();
+ String* val_str(String* s);
+ bool get_date(TIME *ltime, bool fuzzydate);
+};
+
+/*
+ To resolve '*' field moved to condition
+ and register NULL values
+*/
+class Item_asterisk_remover :public Item_ref_null_helper
+{
+ Item *item;
+public:
+ Item_asterisk_remover(Item_in_subselect *master, Item *it,
+ char *table, char *field):
+ Item_ref_null_helper(master, &item, table, field),
+ item(it)
+ {}
+ bool fix_fields(THD *, struct st_table_list *, Item ** ref);
+};
+
+class Item_in_optimizer;
+class Item_ref_in_optimizer: public Item_ref
+{
+protected:
+ Item_in_optimizer* owner;
+public:
+ Item_ref_in_optimizer(Item_in_optimizer* master,
+ char *table_name_par,char *field_name_par);
+ double val();
+ longlong val_int();
+ String* val_str(String* s);
+ bool fix_fields(THD *, struct st_table_list *, Item ** ref)
+ {
+ fixed= 1;
+ return 0;
+ }
+};
/*
The following class is used to optimize comparing of date columns
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 055dad2781a..ebdac083d1b 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -263,6 +263,61 @@ int Arg_comparator::compare_e_row()
return 1;
}
+longlong Item_in_optimizer::val_int()
+{
+ int_cache_ok= 1;
+ flt_cache_ok= 0;
+ str_cache_ok= 0;
+ int_cache= args[0]->val_int_result();
+ if (args[0]->is_null_result())
+ {
+ null_value= 1;
+ return 0;
+ }
+ longlong tmp= args[1]->val_int_result();
+ null_value= args[1]->is_null_result();
+ return tmp;
+}
+
+longlong Item_in_optimizer::get_cache_int()
+{
+ if (!int_cache_ok)
+ {
+ int_cache_ok= 1;
+ flt_cache_ok= 0;
+ str_cache_ok= 0;
+ int_cache= args[0]->val_int_result();
+ null_value= args[0]->is_null_result();
+ }
+ return int_cache;
+}
+
+double Item_in_optimizer::get_cache()
+{
+ if (!flt_cache_ok)
+ {
+ int_cache_ok= 0;
+ flt_cache_ok= 1;
+ str_cache_ok= 0;
+ flt_cache= args[0]->val_result();
+ null_value= args[0]->is_null_result();
+ }
+ return flt_cache;
+}
+
+String *Item_in_optimizer::get_cache_str(String *s)
+{
+ if (!str_cache_ok)
+ {
+ int_cache_ok= 0;
+ flt_cache_ok= 0;
+ str_cache_ok= 1;
+ str_cache_buff.set(buffer, sizeof(buffer), s->charset());
+ str_cache= args[0]->str_result(&str_cache_buff);
+ null_value= args[0]->is_null_result();
+ }
+ return str_cache;
+}
longlong Item_func_eq::val_int()
{
@@ -1092,6 +1147,8 @@ void Item_func_in::fix_length_and_dec()
array->set(j,args[i]);
if (!args[i]->null_value) // Skip NULL values
j++;
+ else
+ have_null= 1;
}
if ((array->used_count=j))
array->sort();
@@ -1138,17 +1195,20 @@ longlong Item_func_in::val_int()
if (array)
{
int tmp=array->find(item);
- null_value=item->null_value;
+ null_value=item->null_value || (!tmp && have_null);
return tmp;
}
in_item->store_value(item);
if ((null_value=item->null_value))
return 0;
+ have_null= 0;
for (uint i=0 ; i < arg_count ; i++)
{
if (!in_item->cmp(args[i]) && !args[i]->null_value)
return 1; // Would maybe be nice with i ?
+ have_null|= args[i]->null_value;
}
+ null_value= have_null;
return 0;
}
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 83d1050dd9c..a18f5179169 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -87,6 +87,27 @@ public:
void fix_length_and_dec() { decimals=0; max_length=1; }
};
+class Item_in_optimizer: public Item_bool_func
+{
+protected:
+ char buffer[80];
+ longlong int_cache;
+ double flt_cache;
+ String str_cache_buff, *str_cache;
+ bool int_cache_ok, flt_cache_ok, str_cache_ok;
+public:
+ Item_in_optimizer(Item *a,Item *b):
+ Item_bool_func(a,b), int_cache_ok(0), flt_cache_ok(0), str_cache_ok(0) {}
+ bool is_null() { return test(args[0]->is_null() || args[1]->is_null()); }
+ longlong val_int();
+
+ double get_cache();
+ longlong get_cache_int();
+ String *get_cache_str(String *s);
+
+ friend class Item_ref_in_optimizer;
+};
+
class Item_bool_func2 :public Item_int_func
{ /* Bool with 2 string args */
protected:
@@ -488,9 +509,10 @@ class Item_func_in :public Item_int_func
Item *item;
in_vector *array;
cmp_item *in_item;
+ bool have_null;
public:
Item_func_in(Item *a,List<Item> &list)
- :Item_int_func(list),item(a),array(0),in_item(0) {}
+ :Item_int_func(list), item(a), array(0), in_item(0), have_null(0) {}
longlong val_int();
bool fix_fields(THD *thd, struct st_table_list *tlist, Item **ref)
{
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index e087664e060..7dd57ef228e 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -33,9 +33,10 @@ SUBSELECT TODO:
#include "sql_select.h"
Item_subselect::Item_subselect():
- Item_result_field(), engine_owner(1), value_assigned(0), substitution(0)
+ Item_result_field(), engine_owner(1), value_assigned(0), substitution(0),
+ have_to_be_excluded(0)
{
- assign_null();
+ reset();
/*
item value is NULL if select_subselect not changed this value
(i.e. some rows will be found returned)
@@ -93,8 +94,10 @@ bool Item_subselect::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
{
(*ref)= substitution;
substitution->name= name;
- engine->exclude();
- return substitution->fix_fields(thd, tables, ref);
+ if (have_to_be_excluded)
+ engine->exclude();
+ substitution= 0;
+ return (*ref)->fix_fields(thd, tables, ref);
}
char const *save_where= thd->where;
@@ -159,7 +162,7 @@ double Item_singleval_subselect::val ()
{
if (engine->exec())
{
- assign_null();
+ reset();
return 0;
}
return real_value;
@@ -169,7 +172,7 @@ longlong Item_singleval_subselect::val_int ()
{
if (engine->exec())
{
- assign_null();
+ reset();
return 0;
}
return int_value;
@@ -179,7 +182,7 @@ String *Item_singleval_subselect::val_str (String *str)
{
if (engine->exec() || null_value)
{
- assign_null();
+ reset();
return 0;
}
return &string_value;
@@ -208,9 +211,8 @@ Item_in_subselect::Item_in_subselect(THD *thd, Item * left_exp,
left_expr= left_exp;
init(thd, select_lex, new select_exists_subselect(this));
max_columns= UINT_MAX;
- null_value= 0; //can't be NULL
- maybe_null= 0; //can't be NULL
- value= 0;
+ maybe_null= 1;
+ reset();
// We need only 1 row to determinate existence
select_lex->master_unit()->global_parameters->select_limit= 1;
DBUG_VOID_RETURN;
@@ -226,9 +228,7 @@ Item_allany_subselect::Item_allany_subselect(THD *thd, Item * left_exp,
func= f;
init(thd, select_lex, new select_exists_subselect(this));
max_columns= UINT_MAX;
- null_value= 0; //can't be NULL
- maybe_null= 0; //can't be NULL
- value= 0;
+ reset();
// We need only 1 row to determinate existence
select_lex->master_unit()->global_parameters->select_limit= 1;
DBUG_VOID_RETURN;
@@ -237,14 +237,15 @@ Item_allany_subselect::Item_allany_subselect(THD *thd, Item * left_exp,
void Item_exists_subselect::fix_length_and_dec()
{
- max_length= 1;
+ decimals=0;
+ max_length= 1;
}
double Item_exists_subselect::val ()
{
if (engine->exec())
{
- assign_null();
+ reset();
return 0;
}
return (double) value;
@@ -254,7 +255,7 @@ longlong Item_exists_subselect::val_int ()
{
if (engine->exec())
{
- assign_null();
+ reset();
return 0;
}
return value;
@@ -264,7 +265,50 @@ String *Item_exists_subselect::val_str(String *str)
{
if (engine->exec())
{
- assign_null();
+ reset();
+ return 0;
+ }
+ str->set(value,thd_charset());
+ return str;
+}
+
+double Item_in_subselect::val ()
+{
+ if (engine->exec())
+ {
+ reset();
+ null_value= 1;
+ return 0;
+ }
+ if (was_null && !value)
+ null_value= 1;
+ return (double) value;
+}
+
+longlong Item_in_subselect::val_int ()
+{
+ if (engine->exec())
+ {
+ reset();
+ null_value= 1;
+ return 0;
+ }
+ if (was_null && !value)
+ null_value= 1;
+ return value;
+}
+
+String *Item_in_subselect::val_str(String *str)
+{
+ if (engine->exec())
+ {
+ reset();
+ null_value= 1;
+ return 0;
+ }
+ if (was_null && !value)
+ {
+ null_value= 1;
return 0;
}
str->set(value,thd_charset());
@@ -288,8 +332,23 @@ void Item_in_subselect::single_value_transformer(st_select_lex *select_lex,
compare_func_creator func)
{
DBUG_ENTER("Item_in_subselect::single_value_transformer");
+ Item_in_optimizer *optimizer;
+ substitution= optimizer= new Item_in_optimizer(left_expr, this);
+ if (!optimizer)
+ {
+ current_thd->fatal_error= 1;
+ DBUG_VOID_RETURN;
+ }
+ /*
+ As far as Item_ref_in_optimizer do not substitude itself on fix_fields
+ we can use same item for all selects.
+ */
+ Item *expr= new Item_ref_in_optimizer(optimizer, (char *)"<no matter>",
+ (char*)"<left expr>");
+ select_lex->master_unit()->dependent= 1;
for (SELECT_LEX * sl= select_lex; sl; sl= sl->next_select())
{
+ select_lex->dependent= 1;
Item *item;
if (sl->item_list.elements > 1)
{
@@ -299,14 +358,14 @@ void Item_in_subselect::single_value_transformer(st_select_lex *select_lex,
else
item= (Item*) sl->item_list.pop();
- Item *expr= new Item_outer_select_context_saver(left_expr);
-
if (sl->having || sl->with_sum_func || sl->group_list.first ||
sl->order_list.first)
{
sl->item_list.push_back(item);
- item= (*func)(expr, new Item_ref(sl->item_list.head_ref(),
- 0, (char*)"<result>"));
+ item= (*func)(expr, new Item_ref_null_helper(this,
+ sl->item_list.head_ref(),
+ (char *)"<no matter>",
+ (char*)"<result>"));
if (sl->having || sl->with_sum_func || sl->group_list.first)
if (sl->having)
sl->having= new Item_cond_and(sl->having, item);
@@ -324,7 +383,9 @@ void Item_in_subselect::single_value_transformer(st_select_lex *select_lex,
sl->item_list.push_back(new Item_int(1));
if (sl->table_list.elements)
{
- item= (*func)(expr, new Item_asterisk_remover(item));
+ item= (*func)(expr, new Item_asterisk_remover(this, item,
+ (char *)"<no matter>",
+ (char*)"<result>"));
if (sl->where)
sl->where= new Item_cond_and(sl->where, item);
else
@@ -340,14 +401,21 @@ void Item_in_subselect::single_value_transformer(st_select_lex *select_lex,
}
if (select_lex->next_select())
{
- // it is in union => we should perform it
- sl->having= (*func)(expr, item);
+ /*
+ It is in union => we should perform it.
+ Item_asterisk_remover used only as wrapper to receine NULL value
+ */
+ sl->having= (*func)(expr,
+ new Item_asterisk_remover(this, item,
+ (char *)"<no matter>",
+ (char*)"<result>"));
}
else
{
// it is single select without tables => possible optimization
item= (*func)(left_expr, item);
substitution= item;
+ have_to_be_excluded= 1;
THD *thd= current_thd;
if (thd->lex.describe)
{
@@ -489,7 +557,7 @@ int subselect_single_select_engine::exec()
join->thd->where= save_where;
DBUG_RETURN(1);
}
- item->assign_null();
+ item->reset();
item->assigned((executed= 0));
}
if (!executed)
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index d323dab51f1..adae0831c22 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -42,6 +42,8 @@ protected:
subselect_engine *engine;
/* allowed number of columns (1 for single value subqueries) */
uint max_columns;
+ /* work with 'substitution' */
+ bool have_to_be_excluded;
public:
Item_subselect();
@@ -65,7 +67,7 @@ public:
select_subselect *result);
~Item_subselect();
- virtual void assign_null()
+ virtual void reset()
{
null_value= 1;
}
@@ -110,7 +112,7 @@ public:
decimals= item->decimals;
res_type= item->res_type;
}
- virtual void assign_null()
+ virtual void reset()
{
null_value= 1;
int_value= 0;
@@ -144,7 +146,7 @@ public:
}
Item_exists_subselect(): Item_subselect() {}
- virtual void assign_null()
+ virtual void reset()
{
value= 0;
}
@@ -155,6 +157,7 @@ public:
double val();
String *val_str(String*);
void fix_length_and_dec();
+
friend class select_exists_subselect;
};
@@ -164,14 +167,26 @@ class Item_in_subselect :public Item_exists_subselect
{
protected:
Item * left_expr;
-
+ bool was_null;
public:
Item_in_subselect(THD *thd, Item * left_expr, st_select_lex *select_lex);
Item_in_subselect(Item_in_subselect *item);
Item_in_subselect(): Item_exists_subselect() {}
+ void reset()
+ {
+ value= 0;
+ null_value= 0;
+ was_null= 0;
+ }
virtual void select_transformer(st_select_lex *select_lex);
void single_value_transformer(st_select_lex *select_lex,
Item *left_expr, compare_func_creator func);
+ longlong val_int();
+ double val();
+ String *val_str(String*);
+
+ friend class Item_asterisk_remover;
+ friend class Item_ref_null_helper;
};
/* ALL/ANY/SOME subselect */
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 57cd0e7a13d..412bbc29d8f 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -937,7 +937,7 @@ bool select_singleval_subselect::send_data(List<Item> &items)
it->real_value= val_item->val_result();
if ((it->null_value= val_item->is_null_result()))
{
- it->assign_null();
+ it->reset();
}
else
{
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index e170f6c040e..43340ad723f 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -216,7 +216,7 @@ int st_select_lex_unit::exec()
if (optimized && item && item->assigned())
{
item->assigned(0); // We will reinit & rexecute unit
- item->assign_null();
+ item->reset();
table->file->delete_all_rows();
}
for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select())
View Lorry Controller Status