[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (igor:2779)
#At lp:maria based on revid:knielsen@knielsen-hq.org-20091130132430-edrwle5zh6udx9rp 2779 Igor Babaev 2010-06-28 Optimization that checks for expressions whether they are always null. modified: mysql-test/r/func_in.result sql/item.h sql/item_cmpfunc.cc sql/item_cmpfunc.h sql/item_func.cc sql/item_func.h sql/item_sum.h sql/sql_select.cc sql/sql_udf.h === modified file 'mysql-test/r/func_in.result' --- a/mysql-test/r/func_in.result 2009-10-05 05:27:36 +0000 +++ b/mysql-test/r/func_in.result 2010-06-29 00:24:26 +0000 @@ -642,10 +642,10 @@ id select_type table type possible_keys 1 SIMPLE t1 range c_int c_int 4 NULL 3 Using where EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using where @@ -654,10 +654,10 @@ id select_type table type possible_keys 1 SIMPLE t1 range c_decimal c_decimal 3 NULL 3 Using where EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_float IN (1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c_float c_float 4 NULL 3 Using where @@ -666,10 +666,10 @@ id select_type table type possible_keys 1 SIMPLE t1 range c_float c_float 4 NULL 3 Using where EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_bit IN (1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c_bit c_bit 2 NULL 3 Using where @@ -678,10 +678,10 @@ id select_type table type possible_keys 1 SIMPLE t1 range c_bit c_bit 2 NULL 3 Using where EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_date IN ('2009-09-01', '2009-09-02', '2009-09-03'); id select_type table type possible_keys key key_len ref rows Extra @@ -692,10 +692,10 @@ id select_type table type possible_keys 1 SIMPLE t1 range c_date c_date 3 NULL 3 Using where EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL, NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_datetime IN ('2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01'); id select_type table type possible_keys key key_len ref rows Extra @@ -706,10 +706,10 @@ id select_type table type possible_keys 1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using where EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL, NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN ('2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); id select_type table type possible_keys key key_len ref rows Extra @@ -720,10 +720,10 @@ id select_type table type possible_keys 1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using where EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL, NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_year IN (1, 2, 3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c_year c_year 1 NULL 3 Using where @@ -732,10 +732,10 @@ id select_type table type possible_keys 1 SIMPLE t1 range c_year c_year 1 NULL 3 Using where EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_char IN ('1', '2', '3'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c_char c_char 10 NULL 3 Using where @@ -744,10 +744,10 @@ id select_type table type possible_keys 1 SIMPLE t1 range c_char c_char 10 NULL 3 Using where EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, NULL); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE DROP TABLE t1; # End of 5.1 tests === modified file 'sql/item.h' --- a/sql/item.h 2009-11-16 20:49:51 +0000 +++ b/sql/item.h 2010-06-29 00:24:26 +0000 @@ -774,6 +774,7 @@ public: will not change until next fix_fields) and its value is known. */ virtual bool const_item() const { return used_tables() == 0; } + virtual bool is_always_null() const { return 0; } /* Returns true if this is constant but its value may be not known yet. (Can be used for parameters of prep. stmts or of stored procedures.) @@ -1563,6 +1564,7 @@ public: enum Item_result result_type () const { return STRING_RESULT; } enum_field_types field_type() const { return MYSQL_TYPE_NULL; } bool basic_const_item() const { return 1; } + bool is_always_null() const { return 1; } Item *clone_item() { return new Item_null(name); } bool is_null() { return 1; } === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2009-11-16 20:49:51 +0000 +++ b/sql/item_cmpfunc.cc 2010-06-29 00:24:26 +0000 @@ -1983,6 +1983,19 @@ bool Item_func_between::fix_fields(THD * (args[1]->not_null_tables() & args[2]->not_null_tables())); + if (negated) + { + always_null_cache= 1; + if (!args[0]->is_always_null()) + always_null_cache= args[1]->is_always_null() && + args[2]->is_always_null(); + } + else + { + always_null_cache= args[0]->is_always_null() || + args[1]->is_always_null() || + args[2]->is_always_null(); + } return 0; } @@ -3545,6 +3558,23 @@ Item_func_in::fix_fields(THD *thd, Item for (arg= args + 1, arg_end= args + arg_count; arg != arg_end; arg++) not_null_tables_cache&= (*arg)->not_null_tables(); not_null_tables_cache|= (*args)->not_null_tables(); + if (negated) + { + always_null_cache= 0; + for (arg= args, arg_end= args + arg_count; + !always_null_cache && arg != arg_end; arg++) + always_null_cache= (*arg)->is_always_null(); + } + else + { + always_null_cache= 1; + if (!(*args)->is_always_null()) + { + for (arg= args + 1, arg_end= args + arg_count; + always_null_cache && arg != arg_end; arg++) + always_null_cache= (*arg)->is_always_null(); + } + } return 0; } === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2009-11-16 20:49:51 +0000 +++ b/sql/item_cmpfunc.h 2010-06-29 00:24:26 +0000 @@ -239,6 +239,7 @@ public: longlong val_int(); void cleanup(); const char *func_name() const { return "<in_optimizer>"; } + bool is_null_preserving() const { return 0; } Item_cache **get_cache() { return &cache; } void keep_top_level_cache(); }; @@ -438,6 +439,7 @@ public: longlong val_int(); enum Functype functype() const { return NOT_ALL_FUNC; } const char *func_name() const { return "<not>"; } + bool is_null_preserving() const { return 0; } virtual void print(String *str, enum_query_type query_type); void set_sum_test(Item_sum_hybrid *item) { test_sum_item= item; }; void set_sub_test(Item_maxmin_subselect *item) { test_sub_item= item; }; @@ -453,6 +455,7 @@ public: Item_func_nop_all(Item *a) :Item_func_not_all(a) {} longlong val_int(); const char *func_name() const { return "<nop>"; } + bool is_null_preserving() const { return 0; } Item *neg_transformer(THD *thd); }; @@ -480,6 +483,7 @@ public: enum Functype rev_functype() const { return EQUAL_FUNC; } cond_result eq_cmp_result() const { return COND_TRUE; } const char *func_name() const { return "<=>"; } + bool is_null_preserving() const { return 0; } Item *neg_transformer(THD *thd) { return 0; } }; @@ -597,6 +601,7 @@ public: optimize_type select_optimize() const { return OPTIMIZE_KEY; } enum Functype functype() const { return BETWEEN; } const char *func_name() const { return "between"; } + bool is_null_preserving() const { return !negated; } bool fix_fields(THD *, Item **); void fix_length_and_dec(); virtual void print(String *str, enum_query_type query_type); @@ -663,6 +668,7 @@ public: const char *func_name() const { return "coalesce"; } table_map not_null_tables() const { return 0; } enum_field_types field_type() const { return cached_field_type; } + bool is_null_preserving() const { return 0; } }; @@ -720,6 +726,7 @@ public: void fix_length_and_dec(); uint decimal_precision() const { return args[0]->decimal_precision(); } const char *func_name() const { return "nullif"; } + bool is_null_preserving() const { return 0; } virtual inline void print(String *str, enum_query_type query_type) { @@ -1152,6 +1159,7 @@ public: void fix_length_and_dec(); uint decimal_precision() const; table_map not_null_tables() const { return 0; } + bool is_null_preserving() const { return 0; } enum Item_result result_type () const { return cached_result_type; } enum_field_types field_type() const { return cached_field_type; } const char *func_name() const { return "case"; } @@ -1225,6 +1233,7 @@ public: virtual void print(String *str, enum_query_type query_type); enum Functype functype() const { return IN_FUNC; } const char *func_name() const { return " IN "; } + bool is_null_preserving() { return arg_count == 2 || negated; } bool nulls_in_row(); bool is_bool_func() { return 1; } CHARSET_INFO *compare_collation() { return cmp_collation.collation; } @@ -1275,6 +1284,7 @@ public: update_used_tables(); } const char *func_name() const { return "isnull"; } + bool is_null_preserving() const { return 0; } /* Optimize case of not_null_column IS NULL */ virtual void update_used_tables() { @@ -1340,6 +1350,7 @@ public: decimals=0; max_length=1; maybe_null=0; } const char *func_name() const { return "isnotnull"; } + bool is_null_preserving() const { return 0; } optimize_type select_optimize() const { return OPTIMIZE_NULL; } table_map not_null_tables() const { return abort_on_null ? not_null_tables_cache : 0; } @@ -1465,6 +1476,7 @@ public: enum Type type() const { return COND_ITEM; } List<Item>* argument_list() { return &list; } + bool is_null_preserving() const { return 0; } table_map used_tables() const; void update_used_tables(); virtual void print(String *str, enum_query_type query_type); === modified file 'sql/item_func.cc' --- a/sql/item_func.cc 2009-11-16 20:49:51 +0000 +++ b/sql/item_func.cc 2010-06-29 00:24:26 +0000 @@ -156,6 +156,8 @@ Item_func::fix_fields(THD *thd, Item **r used_tables_cache= not_null_tables_cache= 0; const_item_cache=1; + always_null_cache= 0; + bool maybe_always_null= is_null_preserving(); if (check_stack_overrun(thd, STACK_MIN_SIZE, buff)) return TRUE; // Fatal error if flag is set! @@ -193,6 +195,8 @@ Item_func::fix_fields(THD *thd, Item **r not_null_tables_cache|= item->not_null_tables(); const_item_cache&= item->const_item(); with_subselect|= item->with_subselect; + if (maybe_always_null && !always_null_cache) + always_null_cache= item->is_always_null(); } } fix_length_and_dec(); @@ -202,7 +206,7 @@ Item_func::fix_fields(THD *thd, Item **r return FALSE; } - + bool Item_func::walk(Item_processor processor, bool walk_subquery, uchar *argument) { @@ -2863,6 +2867,7 @@ udf_handler::fix_fields(THD *thd, Item_r func->maybe_null=0; used_tables_cache=0; const_item_cache=1; + always_null_cache= 0; if ((f_args.arg_count=arg_count)) { === modified file 'sql/item_func.h' --- a/sql/item_func.h 2009-11-16 20:49:51 +0000 +++ b/sql/item_func.h 2010-06-29 00:24:26 +0000 @@ -40,6 +40,7 @@ public: uint arg_count; table_map used_tables_cache, not_null_tables_cache; bool const_item_cache; + bool always_null_cache; enum Functype { UNKNOWN_FUNC,EQ_FUNC,EQUAL_FUNC,NE_FUNC,LT_FUNC,LE_FUNC, GE_FUNC,GT_FUNC,FT_FUNC, LIKE_FUNC,ISNULL_FUNC,ISNOTNULL_FUNC, @@ -135,7 +136,9 @@ public: instead. */ virtual const char *func_name() const= 0; + virtual bool is_null_preserving() const { return TRUE; } virtual bool const_item() const { return const_item_cache; } + bool is_always_null() const { return always_null_cache; } inline Item **arguments() const { return args; } void set_arguments(List<Item> &list); inline uint argument_count() const { return arg_count; } @@ -983,6 +986,7 @@ public: Item_func_last_insert_id(Item *a) :Item_int_func(a) {} longlong val_int(); const char *func_name() const { return "last_insert_id"; } + bool is_null_preserving() const { return 0; } void fix_length_and_dec() { if (arg_count) @@ -1034,6 +1038,7 @@ public: Item_udf_func(udf_func *udf_arg, List<Item> &list) :Item_func(list), udf(udf_arg) {} const char *func_name() const { return udf.name(); } + bool is_null_preserving() const { return 0; } enum Functype functype() const { return UDF_FUNC; } bool fix_fields(THD *thd, Item **ref) { @@ -1041,6 +1046,7 @@ public: bool res= udf.fix_fields(thd, this, arg_count, args); used_tables_cache= udf.used_tables_cache; const_item_cache= udf.const_item_cache; + always_null_cache= udf.always_null_cache; fixed= 1; return res; } @@ -1352,6 +1358,7 @@ public: virtual void print(String *str, enum_query_type query_type); void print_as_stmt(String *str, enum_query_type query_type); const char *func_name() const { return "set_user_var"; } + bool is_null_preserving() const { return 0; } int save_in_field(Field *field, bool no_conversions, bool can_use_result_field); int save_in_field(Field *field, bool no_conversions) @@ -1468,6 +1475,7 @@ public: String* val_str(String*); /* TODO: fix to support views */ const char *func_name() const { return "get_system_var"; } + bool is_null_preserving() const { return 0; } /** Indicates whether this system variable is written to the binlog or not. @@ -1628,6 +1636,8 @@ public: const char *func_name() const; + bool is_null_preserving() const { return 0; } + enum enum_field_types field_type() const; Field *tmp_table_field(TABLE *t_arg); === modified file 'sql/item_sum.h' --- a/sql/item_sum.h 2009-09-15 10:46:35 +0000 +++ b/sql/item_sum.h 2010-06-29 00:24:26 +0000 @@ -287,6 +287,7 @@ public: Item_sum(THD *thd, Item_sum *item); enum Type type() const { return SUM_FUNC_ITEM; } virtual enum Sumfunctype sum_func () const=0; + bool is_null_preserving() const { return 0; } /* This method is similar to add(), but it is called when the current === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2009-11-27 13:20:59 +0000 +++ b/sql/sql_select.cc 2010-06-29 00:24:26 +0000 @@ -7886,6 +7886,10 @@ static COND *build_equal_items_for_cond( } else if (cond->type() == Item::FUNC_ITEM) { + Item *new_item; + if ((Item_func *)cond->is_always_null() && (new_item= new Item_null())) + return new_item; + List<Item> eq_list; /* If an equality predicate forms the whole and level, === modified file 'sql/sql_udf.h' --- a/sql/sql_udf.h 2007-07-06 12:18:49 +0000 +++ b/sql/sql_udf.h 2010-06-29 00:24:26 +0000 @@ -63,6 +63,7 @@ class udf_handler :public Sql_alloc public: table_map used_tables_cache; bool const_item_cache; + bool always_null_cache; bool not_original; udf_handler(udf_func *udf_arg) :u_d(udf_arg), buffers(0), error(0), is_null(0), initialized(0), not_original(0)
participants (1)
-
Igor Babaev