summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result12
-rw-r--r--mysql-test/t/subselect.test8
-rw-r--r--sql/item.h1
-rw-r--r--sql/item_cmpfunc.cc37
-rw-r--r--sql/item_cmpfunc.h21
-rw-r--r--sql/item_subselect.cc28
-rw-r--r--sql/item_subselect.h1
-rw-r--r--sql/sql_select.cc5
8 files changed, 103 insertions, 10 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 078584b25d0..846e54f12ef 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -810,7 +810,7 @@ a t1.a in (select t2.a from t2)
explain SELECT t1.a, t1.a in (select t2.a from t2) 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 index NULL a 5 NULL 3 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index a a 5 NULL 3 Using where; Using index
drop table t1,t2;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
@@ -1062,7 +1062,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
EXPLAIN SELECT 0 IN (SELECT 1 FROM t1 a);
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 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
@@ -1070,7 +1070,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
EXPLAIN SELECT 0 IN (SELECT 1 FROM t1 a);
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 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
drop table t1;
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
@@ -1099,3 +1099,9 @@ id name
2 lenka
1 lenka
drop table t1,t2;
+create table t1 (a int, unique index indexa (a));
+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
+drop table t1;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index e6ae86aa839..97c3b0523b4 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -690,3 +690,11 @@ INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
select * from t2;
drop table t1,t2;
+
+#
+# correct NULL in <CONSTANT> IN (SELECT ...)
+#
+create table t1 (a int, unique index indexa (a));
+insert into t1 values (-1), (-4), (-2), (NULL);
+select -10 IN (select a from t1 FORCE INDEX (indexa));
+drop table t1;
diff --git a/sql/item.h b/sql/item.h
index 6a7ebd506ac..4862ad21fbe 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -603,6 +603,7 @@ public:
item(it)
{}
bool fix_fields(THD *, struct st_table_list *, Item ** ref);
+ Item **storage() {return &item;}
};
/*
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index ac2d0b4f847..0f21cf5a774 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1781,6 +1781,43 @@ longlong Item_func_isnull::val_int()
return args[0]->is_null() ? 1: 0;
}
+longlong Item_is_not_null_test::val_int()
+{
+ DBUG_ENTER("Item_is_not_null_test::val_int");
+ if (!used_tables_cache)
+ {
+ owner->was_null|= (!cached_value);
+ DBUG_PRINT("info", ("cached :%d", cached_value));
+ DBUG_RETURN(cached_value);
+ }
+ if (args[0]->is_null())
+ {
+ DBUG_PRINT("info", ("null"))
+ owner->was_null|= 1;
+ DBUG_RETURN(0);
+ }
+ else
+ DBUG_RETURN(1);
+}
+
+/* Optimize case of not_null_column IS NULL */
+void Item_is_not_null_test::update_used_tables()
+{
+ if (!args[0]->maybe_null)
+ {
+ used_tables_cache= 0; /* is always true */
+ cached_value= (longlong) 1;
+ }
+ else
+ {
+ args[0]->update_used_tables();
+ if (!(used_tables_cache=args[0]->used_tables()))
+ {
+ /* Remember if the value is always NULL or never NULL */
+ cached_value= (longlong) !args[0]->is_null();
+ }
+ }
+}
longlong Item_func_isnotnull::val_int()
{
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index e7670755396..5e246e3e285 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -648,6 +648,7 @@ class Item_func_in :public Item_int_func
class Item_func_isnull :public Item_bool_func
{
+protected:
longlong cached_value;
public:
Item_func_isnull(Item *a) :Item_bool_func(a) {}
@@ -656,11 +657,11 @@ public:
void fix_length_and_dec()
{
decimals=0; max_length=1; maybe_null=0;
- Item_func_isnull::update_used_tables();
+ update_used_tables();
}
const char *func_name() const { return "isnull"; }
/* Optimize case of not_null_column IS NULL */
- void update_used_tables()
+ virtual void update_used_tables()
{
if (!args[0]->maybe_null)
{
@@ -680,6 +681,22 @@ public:
optimize_type select_optimize() const { return OPTIMIZE_NULL; }
};
+/* Functions used by HAVING for rewriting IN subquery */
+
+class Item_in_subselect;
+class Item_is_not_null_test :public Item_func_isnull
+{
+ Item_in_subselect* owner;
+public:
+ Item_is_not_null_test(Item_in_subselect* ow, Item *a)
+ :Item_func_isnull(a), owner(ow)
+ {}
+ longlong val_int();
+ const char *func_name() const { return "is_not_null_test"; }
+ void update_used_tables();
+};
+
+
class Item_func_isnotnull :public Item_bool_func
{
public:
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 76451680b59..6c0b799b4de 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -508,9 +508,31 @@ void Item_in_subselect::single_value_transformer(THD *thd,
sl->item_list.push_back(new Item_int("Not_used", (longlong) 1, 21));
if (sl->table_list.elements)
{
- item= (*func)(expr, new Item_asterisk_remover(this, item,
- (char *)"<no matter>",
- (char*)"<result>"));
+ Item *having= item, *isnull= item;
+ if (item->type() == Item::FIELD_ITEM &&
+ ((Item_field*) item)->field_name[0] == '*')
+ {
+ Item_asterisk_remover *remover;
+ item= remover= new Item_asterisk_remover(this, item,
+ (char*)"<no matter>",
+ (char*)"<result>");
+ having=
+ new Item_is_not_null_test(this,
+ new Item_ref(remover->storage(),
+ (char*)"<no matter>",
+ (char*)"<null test>"));
+ isnull=
+ new Item_is_not_null_test(this,
+ new Item_ref(remover->storage(),
+ (char*)"<no matter>",
+ (char*)"<null test>"));
+ }
+ having= new Item_is_not_null_test(this, having);
+ sl->having= (sl->having ?
+ new Item_cond_and(having, sl->having) :
+ having);
+ item= new Item_cond_or((*func)(expr, item),
+ new Item_func_isnull(isnull));
sl->where= and_items(sl->where, item);
}
else
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 351c4af7f33..fc4dad5a6b3 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -183,6 +183,7 @@ public:
friend class Item_asterisk_remover;
friend class Item_ref_null_helper;
+ friend class Item_is_not_null_test;
};
/* ALL/ANY/SOME subselect */
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3fdff4e96a4..f2dc2b2afd6 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -299,7 +299,7 @@ JOIN::prepare(Item ***rref_pointer_array,
DBUG_RETURN(-1); /* purecov: inspected */
ref_pointer_array= *rref_pointer_array;
-
+
if (having)
{
thd->where="having clause";
@@ -313,6 +313,7 @@ JOIN::prepare(Item ***rref_pointer_array,
if (having->with_sum_func)
having->split_sum_func(ref_pointer_array, all_fields);
}
+
if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */
DBUG_RETURN(-1);
/*
@@ -426,7 +427,7 @@ JOIN::optimize()
#ifdef HAVE_REF_TO_FIELDS // Not done yet
/* Add HAVING to WHERE if possible */
- if (having && !group_list && ! sum_func_count)
+ if (having && !group_list && !sum_func_count)
{
if (!conds)
{
View Lorry Controller Status