From 33446269e18d0506802ca89df30f89e3e5978154 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 11 Dec 2006 18:57:23 -0800 Subject: Fixed bug #24670: optimizations that are legal only for subqueries without tables and no WHERE condition were applied for any subquery without tables. mysql-test/r/subselect.result: Added a test case for bug #24670. mysql-test/t/subselect.test: Added a test case for bug #24670. sql/item_subselect.cc: Fixed bug #24670: optimizations that are legal only for subqueries without tables and no WHERE condition were applied for any subquery without tables. Removed an assertion that caused an abort for subqueries without tables and no WHERE condition. Blocked substitution of a single-row subquery without tables for the constant row from its select list when the subquery contained a WHERE condition. This optimization is valid only for subquries without tables with no conditions. Any subquery without tables with WHERE clause returns NULL if the WHERE condition is FALSE. Erroneously it was always considered as non-nullable that could trigger another optimization concerning IS NULL predicates which is applicable only for non-nullable expressions and ultimately led to a wrong result returned by the outer query. Added a proper implementation of the virtual method may_be_null for class subselect_single_select_engine. sql/item_subselect.h: Fixed bug #24670: optimizations that are legal only for subqueries without tables and no WHERE condition were applied for any subquery without tables. Made method may_by_null for class subselect_engine vvirtual. --- sql/item_subselect.cc | 19 +++++++++++++++++-- sql/item_subselect.h | 3 ++- 2 files changed, 19 insertions(+), 3 deletions(-) (limited to 'sql') diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index cd1f8f83821..20a092d7607 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -350,6 +350,7 @@ Item_singlerow_subselect::select_transformer(JOIN *join) */ !(select_lex->item_list.head()->type() == FIELD_ITEM || select_lex->item_list.head()->type() == REF_ITEM) && + !join->conds && !join->having && /* switch off this optimisation for prepare statement, because we do not rollback this changes @@ -374,8 +375,6 @@ Item_singlerow_subselect::select_transformer(JOIN *join) */ substitution->walk(&Item::remove_dependence_processor, (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; @@ -1795,6 +1794,22 @@ bool subselect_single_select_engine::no_tables() } +/* + 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); +} + + /* Report about presence of tables in subquery diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 7b064bfe92c..539dcc5676a 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -301,7 +301,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; @@ -335,6 +335,7 @@ public: void print (String *str); int change_item(Item_subselect *si, select_subselect *result); bool no_tables(); + bool may_be_null(); }; -- cgit v1.2.1