maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #03413
bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (igor:2779)
#At lp:maria based on revid:knielsen@xxxxxxxxxxxxxxx-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)
