summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/item.cc10
-rw-r--r--sql/item.h43
-rw-r--r--sql/item_cmpfunc.cc161
-rw-r--r--sql/item_cmpfunc.h11
-rw-r--r--sql/item_func.cc19
-rw-r--r--sql/item_func.h22
-rw-r--r--sql/item_row.cc19
-rw-r--r--sql/item_row.h1
-rw-r--r--sql/opt_range.cc10
-rw-r--r--sql/sql_priv.h1
-rw-r--r--sql/sql_select.cc285
-rw-r--r--sql/sql_select.h3
-rw-r--r--sql/sys_vars.cc1
-rw-r--r--sql/table.cc2
-rw-r--r--sql/table.h7
15 files changed, 593 insertions, 2 deletions
diff --git a/sql/item.cc b/sql/item.cc
index 3584230fb2a..9c956116f72 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3556,6 +3556,16 @@ table_map Item_field::all_used_tables() const
}
+bool Item_field::find_not_null_fields(table_map allowed)
+{
+ if (field->table->const_table)
+ return false;
+ if (!get_depended_from() && field->real_maybe_null())
+ bitmap_set_bit(&field->table->tmp_set, field->field_index);
+ return false;
+}
+
+
/*
@Note thd->fatal_error can be set in case of OOM
*/
diff --git a/sql/item.h b/sql/item.h
index 2adc111db03..c2dbdb135f1 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1818,6 +1818,44 @@ public:
virtual bool set_fields_as_dependent_processor(void *arg) { return 0; }
/*============== End of Item processor list ======================*/
+ /*
+ Given a condition P from the WHERE clause or from an ON expression of
+ the processed SELECT S and a set of join tables from S marked in the
+ parameter 'allowed'={T} a call of P->find_not_null_fields({T}) has to
+ find the set fields {F} of the tables from 'allowed' such that:
+ - each field from {F} is declared as nullable
+ - each record of table t from {T} that contains NULL as the value for at
+ at least one field from {F} can be ignored when building the result set
+ for S
+ It is assumed here that the condition P is conjunctive and all its column
+ references belong to T.
+
+ Examples:
+ CREATE TABLE t1 (a int, b int);
+ CREATE TABLE t2 (a int, b int);
+
+ SELECT * FROM t1,t2 WHERE t1.a=t2.a and t1.b > 5;
+ A call of find_not_null_fields() for the whole WHERE condition and {t1,t2}
+ should find {t1.a,t1.b,t2.a}
+
+ SELECT * FROM t1 LEFT JOIN ON (t1.a=t2.a and t2.a > t2.b);
+ A call of find_not_null_fields() for the ON expression and {t2}
+ should find {t2.a,t2.b}
+
+ The function returns TRUE if it succeeds to prove that all records of
+ a table from {T} can be ignored. Otherwise it always returns FALSE.
+
+ Example:
+ SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t2.a IS NULL;
+ A call of find_not_null_fields() for the WHERE condition and {t1,t2}
+ will return TRUE.
+
+ It is assumed that the implementation of this virtual function saves
+ the info on the found set of fields in the structures associates with
+ tables from {T}.
+ */
+ virtual bool find_not_null_fields(table_map allowed) { return false; }
+
virtual Item *get_copy(THD *thd)=0;
bool cache_const_expr_analyzer(uchar **arg);
@@ -3079,6 +3117,7 @@ public:
bool is_result_field() { return false; }
void save_in_result_field(bool no_conversions);
Item *get_tmp_table_item(THD *thd);
+ bool find_not_null_fields(table_map allowed);
bool collect_item_field_processor(void * arg);
bool add_field_to_set_processor(void * arg);
bool find_item_in_field_list_processor(void *arg);
@@ -4858,6 +4897,10 @@ public:
{
return depended_from ? 0 : (*ref)->not_null_tables();
}
+ bool find_not_null_fields(table_map allowed)
+ {
+ return depended_from ? false : (*ref)->find_not_null_fields(allowed);
+ }
void save_in_result_field(bool no_conversions)
{
(*ref)->save_in_field(result_field, no_conversions);
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 49e0a9466c0..c4b43a7459d 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1192,6 +1192,15 @@ bool Item_in_optimizer::eval_not_null_tables(void *opt_arg)
}
+bool Item_in_optimizer::find_not_null_fields(table_map allowed)
+{
+ if (!(~allowed & used_tables()) && is_top_level_item())
+ {
+ return args[0]->find_not_null_fields(allowed);
+ }
+ return false;
+}
+
void Item_in_optimizer::print(String *str, enum_query_type query_type)
{
restore_first_argument();
@@ -2035,7 +2044,17 @@ bool Item_func_between::eval_not_null_tables(void *opt_arg)
(args[1]->not_null_tables() &
args[2]->not_null_tables()));
return 0;
-}
+}
+
+
+bool Item_func_between::find_not_null_fields(table_map allowed)
+{
+ if (negated || !is_top_level_item() || (~allowed & used_tables()))
+ return false;
+ return args[0]->find_not_null_fields(allowed) ||
+ args[1]->find_not_null_fields(allowed) ||
+ args[3]->find_not_null_fields(allowed);
+}
bool Item_func_between::count_sargable_conds(void *arg)
@@ -4151,6 +4170,15 @@ Item_func_in::eval_not_null_tables(void *opt_arg)
}
+bool
+Item_func_in::find_not_null_fields(table_map allowed)
+{
+ if (negated || !is_top_level_item() || (~allowed & used_tables()))
+ return 0;
+ return args[0]->find_not_null_fields(allowed);
+}
+
+
void Item_func_in::fix_after_pullout(st_select_lex *new_parent, Item **ref,
bool merge)
{
@@ -4705,6 +4733,82 @@ Item_cond::eval_not_null_tables(void *opt_arg)
}
+/**
+ @note
+ This implementation of the virtual function find_not_null_fields()
+ infers null-rejectedness if fields from tables marked in 'allowed' from
+ this condition.
+ Currently only top level AND conjuncts that are not disjunctions are used
+ for the inference. Usage of any top level and-or formula with l OR levels
+ would require a stack of bitmaps for fields of the height h=2*l+1 So we
+ would have to allocate h-1 additional field bitmaps for each table marked
+ in 'allowed'.
+*/
+
+bool
+Item_cond::find_not_null_fields(table_map allowed)
+{
+ Item *item;
+ bool is_and_cond= functype() == Item_func::COND_AND_FUNC;
+ if (!is_and_cond)
+ {
+ /* Now only fields of top AND level conjuncts are taken into account */
+ return false;
+ }
+ uint isnull_func_cnt= 0;
+ List_iterator<Item> li(list);
+ while ((item=li++))
+ {
+ bool is_mult_eq= item->type() == Item::FUNC_ITEM &&
+ ((Item_func *) item)->functype() == Item_func::MULT_EQUAL_FUNC;
+ if (is_mult_eq)
+ {
+ if (!item->find_not_null_fields(allowed))
+ continue;
+ }
+
+ if (~allowed & item->used_tables())
+ continue;
+
+ /* It is assumed that all constant conjuncts are already eliminated */
+
+ /*
+ First infer null-rejectedness of fields from all conjuncts but
+ IS NULL predicates
+ */
+ bool isnull_func= item->type() == Item::FUNC_ITEM &&
+ ((Item_func *) item)->functype() == Item_func::ISNULL_FUNC;
+ if (isnull_func)
+ {
+ isnull_func_cnt++;
+ continue;
+ }
+ if (!item->find_not_null_fields(allowed))
+ continue;
+ }
+
+ /* Now try no get contradictions using IS NULL conjuncts */
+ if (isnull_func_cnt)
+ {
+ li.rewind();
+ while ((item=li++) && isnull_func_cnt)
+ {
+ if (~allowed & item->used_tables())
+ continue;
+
+ bool isnull_func= item->type() == Item::FUNC_ITEM &&
+ ((Item_func *) item)->functype() == Item_func::ISNULL_FUNC;
+ if (isnull_func)
+ {
+ if (item->find_not_null_fields(allowed))
+ return true;
+ isnull_func_cnt--;
+ }
+ }
+ }
+ return false;
+}
+
void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref,
bool merge)
{
@@ -5148,6 +5252,19 @@ longlong Item_func_isnull::val_int()
}
+bool Item_func_isnull::find_not_null_fields(table_map allowed)
+{
+ if (!(~allowed & used_tables()) &&
+ args[0]->real_item()->type() == Item::FIELD_ITEM)
+ {
+ Field *field= ((Item_field *)(args[0]->real_item()))->field;
+ if (bitmap_is_set(&field->table->tmp_set, field->field_index))
+ return true;
+ }
+ return false;
+}
+
+
void Item_func_isnull::print(String *str, enum_query_type query_type)
{
if (const_item() && !args[0]->maybe_null &&
@@ -6734,6 +6851,48 @@ void Item_equal::update_used_tables()
}
+/**
+ @note
+ This multiple equality can contains elements belonging not to tables {T}
+ marked in 'allowed' . So we can ascertain null-rejectedness of field f
+ belonging to table t from {T} only if one of the following equality
+ predicate can be extracted from this multiple equality:
+ - f=const
+ - f=f' where f' is a field of some table from {T}
+*/
+
+bool Item_equal::find_not_null_fields(table_map allowed)
+{
+ if (!(allowed & used_tables()))
+ return false;
+ bool checked= false;
+ Item_equal_fields_iterator it(*this);
+ Item *item;
+ while ((item= it++))
+ {
+ if (~allowed & item->used_tables())
+ continue;
+ if ((with_const || checked) && !item->find_not_null_fields(allowed))
+ continue;
+ Item_equal_fields_iterator it1(*this);
+ Item *item1;
+ while ((item1= it1++) && item1 != item)
+ {
+ if (~allowed & item1->used_tables())
+ continue;
+ if (!item->find_not_null_fields(allowed) &&
+ !item1->find_not_null_fields(allowed))
+ {
+ checked= true;
+ break;
+ }
+ }
+ }
+ return false;
+}
+
+
+
bool Item_equal::count_sargable_conds(void *arg)
{
SELECT_LEX *sel= (SELECT_LEX *) arg;
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 7d99cbdb936..8f01e4fa13f 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -284,6 +284,7 @@ public:
Item_func_truth(thd, a, true, false) {}
~Item_func_isnottrue() {}
virtual const char* func_name() const { return "isnottrue"; }
+ bool find_not_null_fields(table_map allowed) { return false; }
Item *get_copy(THD *thd)
{ return get_item_copy<Item_func_isnottrue>(thd, this); }
};
@@ -315,6 +316,7 @@ public:
Item_func_truth(thd, a, false, false) {}
~Item_func_isnotfalse() {}
virtual const char* func_name() const { return "isnotfalse"; }
+ bool find_not_null_fields(table_map allowed) { return false; }
Item *get_copy(THD *thd)
{ return get_item_copy<Item_func_isnotfalse>(thd, this); }
};
@@ -376,6 +378,7 @@ public:
virtual void get_cache_parameters(List<Item> &parameters);
bool is_top_level_item();
bool eval_not_null_tables(void *opt_arg);
+ bool find_not_null_fields(table_map allowed);
void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
bool invisible_mode();
void reset_cache() { cache= NULL; }
@@ -571,6 +574,7 @@ public:
void print(String *str, enum_query_type query_type)
{ Item_func::print_op(str, query_type); }
longlong val_int();
+ bool find_not_null_fields(table_map allowed) { return false; }
Item *neg_transformer(THD *thd);
Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond)
{
@@ -592,6 +596,7 @@ public:
longlong val_int();
enum Functype functype() const { return NOT_FUNC; }
const char *func_name() const { return "not"; }
+ bool find_not_null_fields(table_map allowed) { return false; }
enum precedence precedence() const { return BANG_PRECEDENCE; }
Item *neg_transformer(THD *thd);
bool fix_fields(THD *, Item **);
@@ -736,6 +741,7 @@ public:
longlong val_int();
bool fix_length_and_dec();
table_map not_null_tables() const { return 0; }
+ bool find_not_null_fields(table_map allowed) { return false; }
enum Functype functype() const { return EQUAL_FUNC; }
enum Functype rev_functype() const { return EQUAL_FUNC; }
cond_result eq_cmp_result() const { return COND_TRUE; }
@@ -912,6 +918,7 @@ public:
bool fix_length_and_dec_numeric(THD *);
virtual void print(String *str, enum_query_type query_type);
bool eval_not_null_tables(void *opt_arg);
+ bool find_not_null_fields(table_map allowed);
void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
bool count_sargable_conds(void *arg);
void add_key_fields(JOIN *join, KEY_FIELD **key_fields,
@@ -2396,6 +2403,7 @@ public:
const char *func_name() const { return "in"; }
enum precedence precedence() const { return CMP_PRECEDENCE; }
bool eval_not_null_tables(void *opt_arg);
+ bool find_not_null_fields(table_map allowed);
void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
bool count_sargable_conds(void *arg);
Item *get_copy(THD *thd)
@@ -2533,6 +2541,7 @@ public:
COND *remove_eq_conds(THD *thd, Item::cond_result *cond_value,
bool top_level);
table_map not_null_tables() const { return 0; }
+ bool find_not_null_fields(table_map allowed);
Item *neg_transformer(THD *thd);
Item *get_copy(THD *thd)
{ return get_item_copy<Item_func_isnull>(thd, this); }
@@ -2953,6 +2962,7 @@ public:
Item *compile(THD *thd, Item_analyzer analyzer, uchar **arg_p,
Item_transformer transformer, uchar *arg_t);
bool eval_not_null_tables(void *opt_arg);
+ bool find_not_null_fields(table_map allowed);
Item *build_clone(THD *thd);
bool excl_dep_on_table(table_map tab_map);
bool excl_dep_on_grouping_fields(st_select_lex *sel);
@@ -3119,6 +3129,7 @@ public:
eval_item= NULL;
}
void update_used_tables();
+ bool find_not_null_fields(table_map allowed);
COND *build_equal_items(THD *thd, COND_EQUAL *inherited,
bool link_item_fields,
COND_EQUAL **cond_equal_ref);
diff --git a/sql/item_func.cc b/sql/item_func.cc
index a030d2f8681..41bbd2ea8f0 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -403,6 +403,25 @@ Item_func::eval_not_null_tables(void *opt_arg)
}
+bool
+Item_func::find_not_null_fields(table_map allowed)
+{
+ if (~allowed & used_tables())
+ return false;
+
+ Item **arg,**arg_end;
+ if (arg_count)
+ {
+ for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
+ {
+ if (!(*arg)->find_not_null_fields(allowed))
+ continue;
+ }
+ }
+ return false;
+}
+
+
void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref,
bool merge)
{
diff --git a/sql/item_func.h b/sql/item_func.h
index 6345dd41f71..325a79600e9 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -210,6 +210,7 @@ public:
void traverse_cond(Cond_traverser traverser,
void * arg, traverse_order order);
bool eval_not_null_tables(void *opt_arg);
+ bool find_not_null_fields(table_map allowed);
// bool is_expensive_processor(void *arg);
// virtual bool is_expensive() { return 0; }
inline void raise_numeric_overflow(const char *type_name)
@@ -671,6 +672,7 @@ public:
Item_func_case_expression(THD *thd, List<Item> &list):
Item_func_hybrid_field_type(thd, list)
{ }
+ bool find_not_null_fields(table_map allowed) { return false; }
};
@@ -1759,6 +1761,10 @@ public:
not_null_tables_cache= 0;
return false;
}
+ bool find_not_null_fields(table_map allowed)
+ {
+ return false;
+ }
Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond)
{ return this; }
bool const_item() const { return true; }
@@ -2119,6 +2125,10 @@ public:
not_null_tables_cache= 0;
return 0;
}
+ bool find_not_null_fields(table_map allowed)
+ {
+ return false;
+ }
bool is_expensive() { return 1; }
virtual void print(String *str, enum_query_type query_type);
bool check_vcol_func_processor(void *arg)
@@ -2711,6 +2721,10 @@ public:
not_null_tables_cache= 0;
return 0;
}
+ bool find_not_null_fields(table_map allowed)
+ {
+ return false;
+ }
bool fix_fields(THD *thd, Item **ref);
bool eq(const Item *, bool binary_cmp) const;
/* The following should be safe, even if we compare doubles */
@@ -3000,6 +3014,10 @@ public:
not_null_tables_cache= 0;
return 0;
}
+ bool find_not_null_fields(table_map allowed)
+ {
+ return false;
+ }
};
@@ -3103,6 +3121,10 @@ public:
not_null_tables_cache= 0;
return 0;
}
+ bool find_not_null_fields(table_map allowed)
+ {
+ return false;
+ }
bool const_item() const { return 0; }
void evaluate_sideeffects();
void update_used_tables()
diff --git a/sql/item_row.cc b/sql/item_row.cc
index 70a9fe50c16..0b55ae5d022 100644
--- a/sql/item_row.cc
+++ b/sql/item_row.cc
@@ -87,6 +87,25 @@ Item_row::eval_not_null_tables(void *opt_arg)
}
+bool
+Item_row::find_not_null_fields(table_map allowed)
+{
+ if (~allowed & used_tables())
+ return false;
+
+ Item **arg,**arg_end;
+ if (arg_count)
+ {
+ for (arg= args, arg_end= args + arg_count; arg != arg_end ; arg++)
+ {
+ if (!(*arg)->find_not_null_fields(allowed))
+ continue;
+ }
+ }
+ return false;
+}
+
+
void Item_row::cleanup()
{
DBUG_ENTER("Item_row::cleanup");
diff --git a/sql/item_row.h b/sql/item_row.h
index 0d6a6db2e0e..0efa29f3d79 100644
--- a/sql/item_row.h
+++ b/sql/item_row.h
@@ -110,6 +110,7 @@ public:
}
Item *transform(THD *thd, Item_transformer transformer, uchar *arg);
bool eval_not_null_tables(void *opt_arg);
+ bool find_not_null_fields(table_map allowed);
uint cols() const { return arg_count; }
Item* element_index(uint i) { return args[i]; }
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index ec7b3dbbd7a..91103a5cf34 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -2399,6 +2399,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
{
uint idx;
double scan_time;
+ Item *notnull_cond= NULL;
DBUG_ENTER("SQL_SELECT::test_quick_select");
DBUG_PRINT("enter",("keys_to_use: %lu prev_tables: %lu const_tables: %lu",
(ulong) keys_to_use.to_ulonglong(), (ulong) prev_tables,
@@ -2412,6 +2413,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
if (keys_to_use.is_clear_all() || head->is_filled_at_execution())
DBUG_RETURN(0);
records= head->stat_records();
+ notnull_cond= head->notnull_cond;
if (!records)
records++; /* purecov: inspected */
scan_time= (double) records / TIME_FOR_COMPARE + 1;
@@ -2419,7 +2421,10 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
if (head->force_index)
scan_time= read_time= DBL_MAX;
if (limit < records)
+ {
read_time= (double) records + scan_time + 1; // Force to use index
+ notnull_cond= NULL;
+ }
possible_keys.clear_all();
@@ -2431,6 +2436,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
uchar buff[STACK_BUFF_ALLOC];
MEM_ROOT alloc;
SEL_TREE *tree= NULL;
+ SEL_TREE *notnull_cond_tree= NULL;
KEY_PART *key_parts;
KEY *key_info;
PARAM param;
@@ -2539,6 +2545,9 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
TRP_GROUP_MIN_MAX *group_trp;
double best_read_time= read_time;
+ if (notnull_cond)
+ notnull_cond_tree= notnull_cond->get_mm_tree(&param, &notnull_cond);
+
if (cond)
{
if ((tree= cond->get_mm_tree(&param, &cond)))
@@ -2557,6 +2566,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
tree= NULL;
}
}
+ tree= tree_and(&param, tree, notnull_cond_tree);
/*
Try to construct a QUICK_GROUP_MIN_MAX_SELECT.
diff --git a/sql/sql_priv.h b/sql/sql_priv.h
index 2f37e1106f0..ec7b2ccbdac 100644
--- a/sql/sql_priv.h
+++ b/sql/sql_priv.h
@@ -222,6 +222,7 @@
#define OPTIMIZER_SWITCH_ORDERBY_EQ_PROP (1ULL << 29)
#define OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED (1ULL << 30)
#define OPTIMIZER_SWITCH_SPLIT_MATERIALIZED (1ULL << 31)
+#define OPTIMIZER_SWITCH_NOT_NULL_RANGE_SCAN (1ULL << 32)
#define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \
OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 0fbc4bac7d8..dae2cd76308 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -295,6 +295,14 @@ void set_postjoin_aggr_write_func(JOIN_TAB *tab);
static Item **get_sargable_cond(JOIN *join, TABLE *table);
+static
+bool build_notnull_conds_for_range_scans(JOIN *join, COND *cond,
+ table_map allowed);
+static
+void build_notnull_conds_for_inner_nest_of_outer_join(JOIN *join,
+ TABLE_LIST *nest_tbl);
+
+
#ifndef DBUG_OFF
/*
@@ -4907,6 +4915,9 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
}
}
+ join->join_tab= stat;
+ join->make_notnull_conds_for_range_scans();
+
/* Calc how many (possible) matched records in each table */
for (s=stat ; s < stat_end ; s++)
@@ -27572,6 +27583,280 @@ Item *remove_pushed_top_conjuncts(THD *thd, Item *cond)
return cond;
}
+
+/**
+ @brief
+ Construct not null conditions for provingly not nullable fields
+
+ @details
+ For each non-constant joined table the function creates a conjunction
+ of IS NOT NULL predicates containing a predicate for each field used
+ in the WHERE clause or an OR expression such that
+ - is declared as nullable
+ - for which it can proved be that it is null-rejected
+ - is a part of some index.
+ This conjunction could be anded with either the WHERE condition or with
+ an ON expression and the modified join query would produce the same
+ result set as the original one.
+ If a conjunction of IS NOT NULL predicates is constructed for an inner
+ table of an outer join OJ that is not an inner table of embedded outer
+ joins then it is to be anded with the ON expression of OJ.
+ The constructed conjunctions of IS NOT NULL predicates are attached
+ to the corresponding tables. They used for range analysis complementary
+ to other sargable range conditions.
+
+ @note
+ Let f be a field of the joined table t. In the context of the upper
+ paragraph field f is called null-rejected if any the following holds:
+
+ - t is a table of a top inner join and a conjunctive formula that rejects
+ rows with null values for f can be extracted from the WHERE condition
+
+ - t is an outer table of a top outer join operation and a conjunctive
+ formula over the outer tables of the outer join that rejects rows with
+ null values for can be extracted from the WHERE condition
+
+ - t is an outer table of a non-top outer join operation and a conjunctive
+ formula over the outer tables of the outer join that rejects rows with
+ null values for f can be extracted from the ON expression of the
+ embedding outer join
+
+ - the joined table is an inner table of a outer join operation and
+ a conjunctive formula over inner tables of the outer join that rejects
+ rows with null values for f can be extracted from the ON expression of
+ the outer join operation.
+
+ It is assumed above that all inner join nests have been eliminated and
+ that all possible conversions of outer joins into inner joins have been
+ already done.
+*/
+
+void JOIN::make_notnull_conds_for_range_scans()
+{
+ DBUG_ENTER("JOIN::make_notnull_conds_for_range_scans");
+
+
+ if (impossible_where ||
+ !optimizer_flag(thd, OPTIMIZER_SWITCH_NOT_NULL_RANGE_SCAN))
+ {
+ /* Complementary range analysis is not needed */
+ DBUG_VOID_RETURN;
+ }
+
+ if (conds && build_notnull_conds_for_range_scans(this, conds,
+ conds->used_tables()))
+ {
+ Item *false_cond= new (thd->mem_root) Item_int(thd, (longlong) 0, 1);
+ if (false_cond)
+ {
+ /*
+ Found a IS NULL conjunctive predicate for a null-rejected field
+ in the WHERE clause
+ */
+ conds= false_cond;
+ cond_equal= 0;
+ impossible_where= true;
+ }
+ DBUG_VOID_RETURN;
+ }
+
+ List_iterator<TABLE_LIST> li(*join_list);
+ TABLE_LIST *tbl;
+ while ((tbl= li++))
+ {
+ if (tbl->on_expr)
+ {
+ if (tbl->nested_join)
+ {
+ build_notnull_conds_for_inner_nest_of_outer_join(this, tbl);
+ }
+ else if (build_notnull_conds_for_range_scans(this, tbl->on_expr,
+ tbl->table->map))
+ {
+ /*
+ Found a IS NULL conjunctive predicate for a null-rejected field
+ of the inner table of an outer join with ON expression tbl->on_expr
+ */
+ Item *false_cond= new (thd->mem_root) Item_int(thd, (longlong) 0, 1);
+ if (false_cond)
+ tbl->on_expr= false_cond;
+ }
+ }
+ }
+ DBUG_VOID_RETURN;
+}
+
+
+/**
+ @brief
+ Build not null conditions for range scans of given join tables
+
+ @param join the join for whose tables not null conditions are to be built
+ @param cond the condition from which not null predicates are to be inferred
+ @param allowed the bit map of join tables to be taken into account
+
+ @details
+ For each join table t from the 'allowed' set of tables the function finds
+ all fields whose null-rejectedness can be inferred from null-rejectedness
+ of the condition cond. For each found field f from table t such that it
+ participates at least in one index on table t a NOT NULL predicate is
+ constructed and a conjunction of all such predicates is attached to t.
+ If when looking for null-rejecting fields of t it is discovered one of its
+ fields has to be null-rejected and there is IS NULL conjunctive top level
+ predicate for this field then the function immediately returns true.
+ The function uses the bitmap TABLE::tmp_set to mark found null-rejected
+ fields of table t.
+
+ @note
+ Currently only top level conjuncts without disjunctive sub-formulas are
+ are taken into account when looking for null-rejected fields.
+
+ @retval
+ true if a contradiction is inferred
+ false otherwise
+*/
+
+static
+bool build_notnull_conds_for_range_scans(JOIN *join, Item *cond,
+ table_map allowed)
+{
+ THD *thd= join->thd;
+
+ DBUG_ENTER("build_notnull_conds_for_range_scans");
+
+ for (JOIN_TAB *s= join->join_tab + join->const_tables ;
+ s < join->join_tab + join->table_count ; s++)
+ {
+ /* Clear all needed bitmaps to mark found fields */
+ if (allowed & s->table->map)
+ bitmap_clear_all(&s->table->tmp_set);
+ }
+
+ /*
+ Find all null-rejected fields assuming that cond is null-rejected and
+ only formulas over tables from 'allowed' are to be taken into account
+ */
+ if (cond->find_not_null_fields(allowed))
+ DBUG_RETURN(true);
+
+ /*
+ For each table t from 'allowed' build a conjunction of NOT NULL predicates
+ constructed for all found fields if they are included in some indexes.
+ If the construction of the conjunction succeeds attach the formula to
+ t->table->notnull_cond. The condition will be used to look for complementary
+ range scans.
+ */
+ for (JOIN_TAB *s= join->join_tab + join->const_tables ;
+ s < join->join_tab + join->table_count ; s++)
+ {
+ TABLE *tab= s->table;
+ List<Item> notnull_list;
+ Item *notnull_cond= 0;
+
+ if (!(allowed & tab->map))
+ continue;
+
+ for (Field** field_ptr= tab->field; *field_ptr; field_ptr++)
+ {
+ Field *field= *field_ptr;
+ if (field->part_of_key.is_clear_all())
+ continue;
+ if (!bitmap_is_set(&tab->tmp_set, field->field_index))
+ continue;
+ Item_field *field_item= new (thd->mem_root) Item_field(thd, field);
+ if (!field_item)
+ continue;
+ Item *isnotnull_item=
+ new (thd->mem_root) Item_func_isnotnull(thd, field_item);
+ if (!isnotnull_item)
+ continue;
+ if (notnull_list.push_back(isnotnull_item, thd->mem_root))
+ continue;
+ s->const_keys.merge(field->part_of_key);
+ }
+
+ switch (notnull_list.elements) {
+ case 0:
+ break;
+ case 1:
+ notnull_cond= notnull_list.head();
+ break;
+ default:
+ notnull_cond=
+ new (thd->mem_root) Item_cond_and(thd, notnull_list);
+ }
+ if (notnull_cond && !notnull_cond->fix_fields(thd, 0))
+ {
+ tab->notnull_cond= notnull_cond;
+ }
+ }
+ DBUG_RETURN(false);
+}
+
+
+/**
+ @brief
+ Build not null conditions for inner nest tables of an outer join
+
+ @param join the join for whose table nest not null conditions are to be built
+ @param nest_tbl the nest of the inner tables of an outer join
+
+ @details
+ The function assumes that nest_tbl is the nest of the inner tables of an
+ outer join and so an ON expression for this outer join is attached to
+ nest_tbl.
+ The function selects the tables of the nest_tbl that are not inner tables of
+ embedded outer joins and then it calls build_notnull_conds_for_range_scans()
+ for nest_tbl->on_expr and the bitmap for the selected tables. This call
+ finds all fields belonging to the selected tables whose null-rejectedness
+ can be inferred from the null-rejectedness of nest_tbl->on_expr. After this
+ the function recursively finds all null_rejected fields for the remaining
+ tables from the nest of nest_tbl.
+*/
+
+static
+void build_notnull_conds_for_inner_nest_of_outer_join(JOIN *join,
+ TABLE_LIST *nest_tbl)
+{
+ TABLE_LIST *tbl;
+ table_map used_tables= 0;
+ THD *thd= join->thd;
+ List_iterator<TABLE_LIST> li(nest_tbl->nested_join->join_list);
+
+ while ((tbl= li++))
+ {
+ if (!tbl->on_expr)
+ used_tables|= tbl->table->map;
+ }
+ if (used_tables &&
+ build_notnull_conds_for_range_scans(join, nest_tbl->on_expr, used_tables))
+ {
+ Item *false_cond= new (thd->mem_root) Item_int(thd, (longlong) 0, 1);
+ if (false_cond)
+ nest_tbl->on_expr= false_cond;
+ }
+
+ li.rewind();
+ while ((tbl= li++))
+ {
+ if (tbl->on_expr)
+ {
+ if (tbl->nested_join)
+ {
+ build_notnull_conds_for_inner_nest_of_outer_join(join, tbl);
+ }
+ else if (build_notnull_conds_for_range_scans(join, tbl->on_expr,
+ tbl->table->map))
+ {
+ Item *false_cond= new (thd->mem_root) Item_int(thd, (longlong) 0, 1);
+ if (false_cond)
+ tbl->on_expr= false_cond;
+ }
+ }
+ }
+}
+
+
/**
@} (end of group Query_Optimizer)
*/
diff --git a/sql/sql_select.h b/sql/sql_select.h
index dd823a7d7df..2b40a414d57 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1749,6 +1749,7 @@ public:
void add_keyuses_for_splitting();
bool inject_best_splitting_cond(table_map remaining_tables);
bool fix_all_splittings_in_plan();
+ void make_notnull_conds_for_range_scans();
bool transform_in_predicates_into_in_subq(THD *thd);
private:
@@ -2343,7 +2344,7 @@ Item_equal *find_item_equal(COND_EQUAL *cond_equal, Field *field,
extern bool test_if_ref(Item *,
Item_field *left_item,Item *right_item);
-inline bool optimizer_flag(THD *thd, uint flag)
+inline bool optimizer_flag(THD *thd, ulonglong flag)
{
return (thd->variables.optimizer_switch & flag);
}
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index b50f697b489..58b6da53e6c 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -2544,6 +2544,7 @@ export const char *optimizer_switch_names[]=
"orderby_uses_equalities",
"condition_pushdown_for_derived",
"split_materialized",
+ "not_null_range_scan",
"default",
NullS
};
diff --git a/sql/table.cc b/sql/table.cc
index 4805017972c..34fb6d5feac 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -4658,6 +4658,8 @@ void TABLE::init(THD *thd, TABLE_LIST *tl)
(*f_ptr)->cond_selectivity= 1.0;
}
+ notnull_cond= 0;
+
DBUG_ASSERT(!file->keyread_enabled());
restore_record(this, s->default_values);
diff --git a/sql/table.h b/sql/table.h
index fa6cb707efb..47096aacb67 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1344,6 +1344,13 @@ public:
SplM_opt_info *spl_opt_info;
key_map keys_usable_for_splitting;
+ /*
+ Conjunction of the predicates of the form IS NOT NULL(f) where f refers to
+ a column of this TABLE such that they can be inferred from the condition
+ of the WHERE clause or from some ON expression of the processed select
+ and can be useful for range optimizer.
+ */
+ Item *notnull_cond;
inline void reset() { bzero((void*)this, sizeof(*this)); }
void init(THD *thd, TABLE_LIST *tl);