One question: what are SEL_ARG_LE/GE/GT classes? How do they relate to the base SEL_ARG ? If I have "10 < t.key <= 20" which class it would be ? If you are adding several new classes and an inheritance hierarchy, it would be nice to have a comment describing the overall picture. On Tue, Apr 03, 2018 at 12:57:22PM +0400, Alexander Barkov wrote:

Hello Vicentiu,

Please review my patch for MDEV-15758.

It also fixes this bug:

MDEV-15759 Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant

Thanks!

diff --git a/mysql-test/main/type_bit.result b/mysql-test/main/type_bit.result index 30cd94c..c9796c8 100644 --- a/mysql-test/main/type_bit.result +++ b/mysql-test/main/type_bit.result @@ -830,3 +830,21 @@ def COALESCE(val, 1) 246 2 1 Y 32896 0 63 COALESCE(val, 1) 0 DROP TABLE t1; +# +# Start of 10.3 tests +# +# +# MDEV-15759 Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant +# +CREATE TABLE t1 (a BIT(7), KEY(a)); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5); +EXPLAIN SELECT * FROM t1 WHERE a=200; +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 +EXPLAIN SELECT * FROM t1 WHERE a<=>200; +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 +DROP TABLE t1; +# +# End of 10.3 tests +# diff --git a/mysql-test/main/type_bit.test b/mysql-test/main/type_bit.test index bb282fc..f663542 100644 --- a/mysql-test/main/type_bit.test +++ b/mysql-test/main/type_bit.test @@ -458,3 +458,23 @@ DROP TABLE t2; SELECT COALESCE(val, 1) FROM t1; --disable_metadata DROP TABLE t1; + + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-15759 Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant +--echo # + +CREATE TABLE t1 (a BIT(7), KEY(a)); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5); +EXPLAIN SELECT * FROM t1 WHERE a=200; +EXPLAIN SELECT * FROM t1 WHERE a<=>200; +DROP TABLE t1; + + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/main/type_int.result b/mysql-test/main/type_int.result index 39e2e91..2125680 100644 --- a/mysql-test/main/type_int.result +++ b/mysql-test/main/type_int.result @@ -93,3 +93,21 @@ DROP TABLE t1; # # End of 10.2 tests # +# +# Start of 10.3 tests +# +# +# MDEV-15759 Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant +# +CREATE TABLE t1 (a TINYINT, KEY(a)); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5); +EXPLAIN SELECT * FROM t1 WHERE a=200; +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 +EXPLAIN SELECT * FROM t1 WHERE a<=>200; +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 +DROP TABLE t1; +# +# End of 10.3 tests +# diff --git a/mysql-test/main/type_int.test b/mysql-test/main/type_int.test index 271b4d5..899af44 100644 --- a/mysql-test/main/type_int.test +++ b/mysql-test/main/type_int.test @@ -76,3 +76,22 @@ DROP TABLE t1; --echo # --echo # End of 10.2 tests --echo # + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-15759 Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant +--echo # + +CREATE TABLE t1 (a TINYINT, KEY(a)); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5); +EXPLAIN SELECT * FROM t1 WHERE a=200; +EXPLAIN SELECT * FROM t1 WHERE a<=>200; +DROP TABLE t1; + + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/main/update.result b/mysql-test/main/update.result index 73ebb73..ccf2f44 100644 --- a/mysql-test/main/update.result +++ b/mysql-test/main/update.result @@ -447,7 +447,7 @@ UPDATE t1 SET user_id=null WHERE request_id=9999999999999; show status like '%Handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 3 +Handler_read_key 2 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 @@ -459,7 +459,7 @@ UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999; show status like '%Handler_read%'; Variable_name Value Handler_read_first 0 -Handler_read_key 3 +Handler_read_key 2 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 diff --git a/sql/field.h b/sql/field.h index f9eb283..cc67611 100644 --- a/sql/field.h +++ b/sql/field.h @@ -48,6 +48,9 @@ class Item_equal; class Virtual_tmp_table; class Qualified_column_ident; class Table_ident; +class SEL_ARG; +class RANGE_OPT_PARAM; +struct KEY_PART;

enum enum_check_fields { @@ -865,6 +868,10 @@ class Field: public Value_source to be quoted when used in constructing an SQL query. */ virtual bool str_needs_quotes() { return FALSE; } + const Type_handler *type_handler_for_comparison() const + { + return type_handler()->type_handler_for_comparison(); + } Item_result result_type () const { return type_handler()->result_type(); @@ -1378,6 +1385,59 @@ class Field: public Value_source } int warn_if_overflow(int op_result); Copy_func *get_identical_copy_func() const; + bool can_optimize_scalar_range(const RANGE_OPT_PARAM *param, + const KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, + const Item *value) const; + uchar *make_key_image(MEM_ROOT *mem_root, const KEY_PART *key_part); + SEL_ARG *get_mm_leaf_int(RANGE_OPT_PARAM *param, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value, + bool unsigned_field); + /* + Make a leaf tree for the cases when the value was stored + to the field exactly, without any truncation, rounding or adjustments. + For example, if we stored an INT value into an INT column, + and value->save_in_field_no_warnings() returned 0, + we know that the value was stored exactly. + */ + SEL_ARG *stored_field_make_mm_leaf_exact(RANGE_OPT_PARAM *param, + KEY_PART *key_part, + scalar_comparison_op op, + Item *value); + /* + Make a leaf tree for the cases when we don't know if + the value was stored to the field without any data loss, + or was modified to a smaller or a greater value. + Used for the data types whose methods Field::store*() + silently adjust the value. This is the most typical case. + */ + SEL_ARG *stored_field_make_mm_leaf(RANGE_OPT_PARAM *param, + KEY_PART *key_part, + scalar_comparison_op op, Item *value); + /* + Make a leaf tree when an INT value was stored into a field of INT type, + and some truncation happened. Tries to adjust the range search condition + when possible, e.g. "tinytint < 300" -> "tinyint <= 127". + Can also return SEL_ARG_IMPOSSIBLE(), and NULL (not sargable). + */ + SEL_ARG *stored_field_make_mm_leaf_bounded_int(RANGE_OPT_PARAM *param, + KEY_PART *key_part, + scalar_comparison_op op, + Item *value, + bool unsigned_field); + /* + Make a leaf tree when some truncation happened during + value->save_in_field_no_warning(this), and we cannot yet adjust the range + search condition for the current combination of the field and the value + data types. + Returns SEL_ARG_IMPOSSIBLE() for "=" and "<=>". + Returns NULL (not sargable) for other comparison operations. + */ + SEL_ARG *stored_field_make_mm_leaf_truncated(RANGE_OPT_PARAM *prm, + scalar_comparison_op, + Item *value); public: void set_table_name(String *alias) { @@ -1531,6 +1591,10 @@ class Field: public Value_source const Item *item, bool is_eq_func) const;

+ virtual SEL_ARG *get_mm_leaf(RANGE_OPT_PARAM *param, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value)= 0; + bool can_optimize_outer_join_table_elimination(const Item_bool_func *cond, const Item *item) const { @@ -1690,6 +1754,9 @@ class Field_num :public Field { { return pos_in_interval_val_real(min, max); } + SEL_ARG *get_mm_leaf(RANGE_OPT_PARAM *param, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value); };

@@ -1739,6 +1806,9 @@ class Field_str :public Field { return pos_in_interval_val_str(min, max, length_size()); } bool test_if_equality_guarantees_uniqueness(const Item *const_item) const; + SEL_ARG *get_mm_leaf(RANGE_OPT_PARAM *param, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value); };

/* base class for Field_string, Field_varstring and Field_blob */ @@ -1966,6 +2036,12 @@ class Field_int :public Field_num bool val_bool() { return val_int() != 0; } int store_time_dec(const MYSQL_TIME *ltime, uint dec); bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate); + SEL_ARG *get_mm_leaf(RANGE_OPT_PARAM *param, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value) + { + return get_mm_leaf_int(param, key_part, cond, op, value, unsigned_flag); + } };

@@ -2430,6 +2506,9 @@ class Field_temporal: public Field { { return true; } + SEL_ARG *get_mm_leaf(RANGE_OPT_PARAM *param, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value); };

@@ -2705,14 +2784,31 @@ class Field_year :public Field_tiny { };

-class Field_date :public Field_temporal_with_date { +class Field_date_common: public Field_temporal_with_date +{ +public: + Field_date_common(uchar *ptr_arg, uchar *null_ptr_arg, uchar null_bit_arg, + enum utype unireg_check_arg, + const LEX_CSTRING *field_name_arg) + :Field_temporal_with_date(ptr_arg, MAX_DATE_WIDTH, + null_ptr_arg, null_bit_arg, + unireg_check_arg, field_name_arg) + {} + SEL_ARG *get_mm_leaf(RANGE_OPT_PARAM *param, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value); +}; + + +class Field_date :public Field_date_common +{ void store_TIME(MYSQL_TIME *ltime); bool get_TIME(MYSQL_TIME *ltime, const uchar *pos, ulonglong fuzzydate) const; public: Field_date(uchar *ptr_arg, uchar *null_ptr_arg, uchar null_bit_arg, enum utype unireg_check_arg, const LEX_CSTRING *field_name_arg) - :Field_temporal_with_date(ptr_arg, MAX_DATE_WIDTH, null_ptr_arg, null_bit_arg, - unireg_check_arg, field_name_arg) {} + :Field_date_common(ptr_arg, null_ptr_arg, null_bit_arg, + unireg_check_arg, field_name_arg) {} const Type_handler *type_handler() const { return &type_handler_date; } enum ha_base_keytype key_type() const { return HA_KEYTYPE_ULONG_INT; } int reset(void) { ptr[0]=ptr[1]=ptr[2]=ptr[3]=0; return 0; } @@ -2740,14 +2836,15 @@ class Field_date :public Field_temporal_with_date { };

-class Field_newdate :public Field_temporal_with_date { +class Field_newdate :public Field_date_common +{ void store_TIME(MYSQL_TIME *ltime); bool get_TIME(MYSQL_TIME *ltime, const uchar *pos, ulonglong fuzzydate) const; public: Field_newdate(uchar *ptr_arg, uchar *null_ptr_arg, uchar null_bit_arg, enum utype unireg_check_arg, const LEX_CSTRING *field_name_arg) - :Field_temporal_with_date(ptr_arg, MAX_DATE_WIDTH, null_ptr_arg, null_bit_arg, - unireg_check_arg, field_name_arg) + :Field_date_common(ptr_arg, null_ptr_arg, null_bit_arg, + unireg_check_arg, field_name_arg) {} const Type_handler *type_handler() const { return &type_handler_newdate; } enum ha_base_keytype key_type() const { return HA_KEYTYPE_UINT24; } @@ -4028,6 +4125,12 @@ class Field_bit :public Field { } void hash(ulong *nr, ulong *nr2);

+ SEL_ARG *get_mm_leaf(RANGE_OPT_PARAM *param, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value) + { + return get_mm_leaf_int(param, key_part, cond, op, value, true); + } private: virtual size_t do_last_null_byte() const; int save_field_metadata(uchar *first_byte); diff --git a/sql/item.cc b/sql/item.cc index b32967e..4f88cb8 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -6601,7 +6601,7 @@ Item *Item_field::replace_equal_field(THD *thd, uchar *arg) comparison context, and it's safe to replace it to the constant from item_equal. */ - DBUG_ASSERT(type_handler()->type_handler_for_comparison()->cmp_type() == + DBUG_ASSERT(type_handler_for_comparison()->cmp_type() == item_equal->compare_type_handler()->cmp_type()); return const_item2; } @@ -9826,73 +9826,14 @@ void resolve_const_item(THD *thd, Item **ref, Item *comp_item)

int stored_field_cmp_to_item(THD *thd, Field *field, Item *item) { - Item_result res_type=item_cmp_type(field->result_type(), - item->result_type()); - /* - We have to check field->cmp_type() instead of res_type, - as result_type() - and thus res_type - can never be TIME_RESULT (yet). - */ - if (field->cmp_type() == TIME_RESULT) - { - MYSQL_TIME field_time, item_time, item_time2, *item_time_cmp= &item_time; - if (field->type() == MYSQL_TYPE_TIME) - { - field->get_time(&field_time); - item->get_time(&item_time); - } - else - { - field->get_date(&field_time, TIME_INVALID_DATES); - item->get_date(&item_time, TIME_INVALID_DATES); - if (item_time.time_type == MYSQL_TIMESTAMP_TIME) - if (time_to_datetime(thd, &item_time, item_time_cmp= &item_time2)) - return 1; - } - return my_time_compare(&field_time, item_time_cmp); - } - if (res_type == STRING_RESULT) + Type_handler_hybrid_field_type cmp(field->type_handler_for_comparison()); + if (cmp.aggregate_for_comparison(item->type_handler_for_comparison())) { - char item_buff[MAX_FIELD_WIDTH]; - char field_buff[MAX_FIELD_WIDTH]; - - String item_tmp(item_buff,sizeof(item_buff),&my_charset_bin); - String field_tmp(field_buff,sizeof(field_buff),&my_charset_bin); - String *item_result= item->val_str(&item_tmp); - /* - Some implementations of Item::val_str(String*) actually modify - the field Item::null_value, hence we can't check it earlier. - */ - if (item->null_value) - return 0; - String *field_result= field->val_str(&field_tmp); - return sortcmp(field_result, item_result, field->charset()); - } - if (res_type == INT_RESULT) - return 0; // Both are of type int - if (res_type == DECIMAL_RESULT) - { - my_decimal item_buf, *item_val, - field_buf, *field_val; - item_val= item->val_decimal(&item_buf); - if (item->null_value) - return 0; - field_val= field->val_decimal(&field_buf); - return my_decimal_cmp(field_val, item_val); - } - /* - The patch for Bug#13463415 started using this function for comparing - BIGINTs. That uncovered a bug in Visual Studio 32bit optimized mode. - Prefixing the auto variables with volatile fixes the problem.... - */ - volatile double result= item->val_real(); - if (item->null_value) + // At fix_fields() time we checked that "field" and "item" are comparable + DBUG_ASSERT(0); return 0; - volatile double field_result= field->val_real(); - if (field_result < result) - return -1; - else if (field_result > result) - return 1; - return 0; + } + return cmp.type_handler()->stored_field_cmp_to_item(thd, field, item); }

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index ba503f1..4246606 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -132,8 +132,7 @@ Type_handler_hybrid_field_type::aggregate_for_comparison(const char *funcname, unsigned_count+= items[i]->unsigned_flag; if (!m_vers_trx_id) m_vers_trx_id= items[i]->vers_trx_id(); - if (aggregate_for_comparison(items[i]->type_handler()-> - type_handler_for_comparison())) + if (aggregate_for_comparison(items[i]->type_handler_for_comparison())) { /* For more precise error messages if aggregation failed on the first pair diff --git a/sql/item_func.h b/sql/item_func.h index f33b936..9990d5e 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -76,6 +76,20 @@ class Item_func :public Item_func_or_sum EXTRACT_FUNC, CHAR_TYPECAST_FUNC, FUNC_SP, UDF_FUNC, NEG_FUNC, GSYSVAR_FUNC, IN_OPTIMIZER_FUNC, DYNCOL_FUNC, JSON_EXTRACT_FUNC }; + static scalar_comparison_op get_scalar_comparison_op(Functype type) + { + switch (type) { + case EQ_FUNC: return SCALAR_CMP_EQ; + case EQUAL_FUNC: return SCALAR_CMP_EQUAL; + case LT_FUNC: return SCALAR_CMP_LT; + case LE_FUNC: return SCALAR_CMP_LE; + case GE_FUNC: return SCALAR_CMP_GE; + case GT_FUNC: return SCALAR_CMP_GT; + default: break; + } + DBUG_ASSERT(0); + return SCALAR_CMP_EQ; + } enum Type type() const { return FUNC_ITEM; } virtual enum Functype functype() const { return UNKNOWN_FUNC; } Item_func(THD *thd): Item_func_or_sum(thd) diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 38dbed9..1f51ea6 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -1892,6 +1892,84 @@ SEL_ARG::SEL_ARG(Field *field_,uint8 part_, left=right= &null_element; }

+ +class SEL_ARG_LE: public SEL_ARG +{ +public: + SEL_ARG_LE(const uchar *key, Field *field) + :SEL_ARG(field, key, key) + { + if (!field->real_maybe_null()) + min_flag= NO_MIN_RANGE; // From start + else + { + min_value= is_null_string; + min_flag= NEAR_MIN; // > NULL + } + } +}; + + +class SEL_ARG_LT: public SEL_ARG_LE +{ +public: + SEL_ARG_LT(THD *thd, const uchar *key, Field *field, Item *value) + :SEL_ARG_LE(key, field) + { + if (stored_field_cmp_to_item(thd, field, value) == 0) + max_flag= NEAR_MAX; + } + SEL_ARG_LT(const uchar *key, Field *field) + :SEL_ARG_LE(key, field) + { max_flag= NEAR_MAX; } +}; + + +class SEL_ARG_GT: public SEL_ARG +{ +public: + SEL_ARG_GT(THD *thd, const uchar *key, + const KEY_PART *key_part, Field *field, Item *value) + :SEL_ARG(field, key, key) + { + /* Don't use open ranges for partial key_segments */ + if ((!(key_part->flag & HA_PART_KEY_SEG)) && + (stored_field_cmp_to_item(thd, field, value) <= 0)) + min_flag= NEAR_MIN; + max_flag= NO_MAX_RANGE; + } + SEL_ARG_GT(const uchar *key, const KEY_PART *key_part, Field *field) + :SEL_ARG(field, key, key) + { + /* Don't use open ranges for partial key_segments */ + if (!(key_part->flag & HA_PART_KEY_SEG)) + min_flag= NEAR_MIN; + max_flag= NO_MAX_RANGE; + } +}; + + +class SEL_ARG_GE: public SEL_ARG +{ +public: + SEL_ARG_GE(THD *thd, const uchar *key, + const KEY_PART *key_part, Field *field, Item *value) + :SEL_ARG(field, key, key) + { + /* Don't use open ranges for partial key_segments */ + if ((!(key_part->flag & HA_PART_KEY_SEG)) && + (stored_field_cmp_to_item(thd, field, value) < 0)) + min_flag= NEAR_MIN; + max_flag= NO_MAX_RANGE; + } + SEL_ARG_GE(const uchar *key, Field *field) + :SEL_ARG(field, key, key) + { + max_flag= NO_MAX_RANGE; + } +}; + + SEL_ARG *SEL_ARG::clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent, SEL_ARG **next_arg) { @@ -8013,52 +8091,112 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM *param, SEL_ARG * Item_bool_func::get_mm_leaf(RANGE_OPT_PARAM *param, Field *field, KEY_PART *key_part, - Item_func::Functype type, Item *value) + Item_func::Functype functype, Item *value) { - uint maybe_null=(uint) field->real_maybe_null(); - SEL_ARG *tree= 0; - MEM_ROOT *alloc= param->mem_root; - uchar *str; - int err; DBUG_ENTER("Item_bool_func::get_mm_leaf"); - DBUG_ASSERT(value); // IS NULL and IS NOT NULL are handled separately - if (key_part->image_type != Field::itRAW) DBUG_RETURN(0); // e.g. SPATIAL index + DBUG_RETURN(field->get_mm_leaf(param, key_part, this, + Item_func::get_scalar_comparison_op(functype), + value)); +}

- if (param->using_real_indexes && - !field->optimize_range(param->real_keynr[key_part->key], - key_part->part) && - type != EQ_FUNC && - type != EQUAL_FUNC) - goto end; // Can't optimize this

- if (!field->can_optimize_range(this, value, - type == EQUAL_FUNC || type == EQ_FUNC)) - goto end; +bool Field::can_optimize_scalar_range(const RANGE_OPT_PARAM *param, + const KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, + const Item *value) const +{ + bool is_eq_func= op == SCALAR_CMP_EQ || op == SCALAR_CMP_EQUAL; + if ((param->using_real_indexes && + !optimize_range(param->real_keynr[key_part->key], + key_part->part) && !is_eq_func) || + !can_optimize_range(cond, value, is_eq_func)) + return false; + return true; +}

- err= value->save_in_field_no_warnings(field, 1); + +uchar *Field::make_key_image(MEM_ROOT *mem_root, const KEY_PART *key_part) +{ + DBUG_ENTER("Field::make_key_image"); + uint maybe_null= (uint) real_maybe_null(); + uchar *str; + if (!(str= (uchar*) alloc_root(mem_root, key_part->store_length + 1))) + DBUG_RETURN(0); + if (maybe_null) + *str= (uchar) is_real_null(); // Set to 1 if null + get_key_image(str + maybe_null, key_part->length, key_part->image_type); + DBUG_RETURN(str); +} + + +SEL_ARG *Field::stored_field_make_mm_leaf_truncated(RANGE_OPT_PARAM *param, + scalar_comparison_op op, + Item *value) +{ + DBUG_ENTER("Field::stored_field_make_mm_leaf_truncated"); + if ((op == SCALAR_CMP_EQ || op == SCALAR_CMP_EQUAL) && + value->result_type() == item_cmp_type(result_type(), + value->result_type())) + DBUG_RETURN(new (param->mem_root) SEL_ARG_IMPOSSIBLE(this)); + /* + TODO: We should return trees of the type SEL_ARG::IMPOSSIBLE + for the cases like int_field > 999999999999999999999999 as well. + */ + DBUG_RETURN(0); +} + + +SEL_ARG *Field_num::get_mm_leaf(RANGE_OPT_PARAM *prm, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value) +{ + DBUG_ENTER("Field_num::get_mm_leaf"); + if (!can_optimize_scalar_range(prm, key_part, cond, op, value)) + DBUG_RETURN(0); + int err= value->save_in_field_no_warnings(this, 1); + if ((op != SCALAR_CMP_EQUAL && is_real_null()) || err < 0) + DBUG_RETURN(&null_element); + if (err > 0 && cmp_type() != value->result_type()) + DBUG_RETURN(stored_field_make_mm_leaf_truncated(prm, op, value)); + DBUG_RETURN(stored_field_make_mm_leaf(prm, key_part, op, value)); +} + + +SEL_ARG *Field_temporal::get_mm_leaf(RANGE_OPT_PARAM *prm, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value) +{ + DBUG_ENTER("Field_temporal::get_mm_leaf"); + if (!can_optimize_scalar_range(prm, key_part, cond, op, value)) + DBUG_RETURN(0); + int err= value->save_in_field_no_warnings(this, 1); + if ((op != SCALAR_CMP_EQUAL && is_real_null()) || err < 0) + DBUG_RETURN(&null_element); if (err > 0) - { - if (field->type_handler() == &type_handler_enum || - field->type_handler() == &type_handler_set) - { - if (type == EQ_FUNC || type == EQUAL_FUNC) - tree= new (alloc) SEL_ARG_IMPOSSIBLE(field); - goto end; - } + DBUG_RETURN(stored_field_make_mm_leaf_truncated(prm, op, value)); + DBUG_RETURN(stored_field_make_mm_leaf(prm, key_part, op, value)); +}

- if (err == 2 && field->cmp_type() == STRING_RESULT) - { - if (type == EQ_FUNC || type == EQUAL_FUNC) - tree= new (alloc) SEL_ARG_IMPOSSIBLE(field); - else - tree= NULL; /* Cannot infer anything */ - goto end; - }

- if (err == 3 && field->type() == FIELD_TYPE_DATE) +SEL_ARG *Field_date_common::get_mm_leaf(RANGE_OPT_PARAM *prm, + KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, + Item *value) +{ + DBUG_ENTER("Field_date_common::get_mm_leaf"); + if (!can_optimize_scalar_range(prm, key_part, cond, op, value)) + DBUG_RETURN(0); + int err= value->save_in_field_no_warnings(this, 1); + if ((op != SCALAR_CMP_EQUAL && is_real_null()) || err < 0) + DBUG_RETURN(&null_element); + if (err > 0) + { + if (err == 3) { /* We were saving DATETIME into a DATE column, the conversion went ok @@ -8078,76 +8216,86 @@ Item_bool_func::get_mm_leaf(RANGE_OPT_PARAM *param, be done together with other types at the end of this function (grep for stored_field_cmp_to_item) */ - if (type == EQ_FUNC || type == EQUAL_FUNC) - { - tree= new (alloc) SEL_ARG_IMPOSSIBLE(field); - goto end; - } - // Continue with processing non-equality ranges - } - else if (field->cmp_type() != value->result_type()) - { - if ((type == EQ_FUNC || type == EQUAL_FUNC) && - value->result_type() == item_cmp_type(field->result_type(), - value->result_type())) - { - tree= new (alloc) SEL_ARG_IMPOSSIBLE(field); - goto end; - } - else - { - /* - TODO: We should return trees of the type SEL_ARG::IMPOSSIBLE - for the cases like int_field > 999999999999999999999999 as well. - */ - tree= 0; - goto end; - } - } - - /* - guaranteed at this point: err > 0; field and const of same type - If an integer got bounded (e.g. to within 0..255 / -128..127) - for < or >, set flags as for <= or >= (no NEAR_MAX / NEAR_MIN) - */ - else if (err == 1 && field->result_type() == INT_RESULT) - { - if (type == LT_FUNC && (value->val_int() > 0)) - type= LE_FUNC; - else if (type == GT_FUNC && - (field->type() != FIELD_TYPE_BIT) && - !((Field_num*)field)->unsigned_flag && - !((Item_int*)value)->unsigned_flag && - (value->val_int() < 0)) - type= GE_FUNC; + if (op == SCALAR_CMP_EQ || op == SCALAR_CMP_EQUAL) + DBUG_RETURN(new (prm->mem_root) SEL_ARG_IMPOSSIBLE(this)); + DBUG_RETURN(stored_field_make_mm_leaf(prm, key_part, op, value)); } + DBUG_RETURN(stored_field_make_mm_leaf_truncated(prm, op, value)); } - else if (err < 0) + DBUG_RETURN(stored_field_make_mm_leaf(prm, key_part, op, value)); +} + + +SEL_ARG *Field_str::get_mm_leaf(RANGE_OPT_PARAM *prm, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value) +{ + DBUG_ENTER("Field_str::get_mm_leaf"); + if (!can_optimize_scalar_range(prm, key_part, cond, op, value)) + DBUG_RETURN(0); + int err= value->save_in_field_no_warnings(this, 1); + if ((op != SCALAR_CMP_EQUAL && is_real_null()) || err < 0) + DBUG_RETURN(&null_element); + if (err > 0) { - /* This happens when we try to insert a NULL field in a not null column */ - tree= &null_element; // cmp with NULL is never TRUE - goto end; + if (op == SCALAR_CMP_EQ || op == SCALAR_CMP_EQUAL) + DBUG_RETURN(new (prm->mem_root) SEL_ARG_IMPOSSIBLE(this)); + DBUG_RETURN(NULL); /* Cannot infer anything */ } + DBUG_RETURN(stored_field_make_mm_leaf(prm, key_part, op, value)); +}

- /* - Any sargable predicate except "<=>" involving NULL as a constant is always - FALSE - */ - if (type != EQUAL_FUNC && field->is_real_null()) + +SEL_ARG *Field::get_mm_leaf_int(RANGE_OPT_PARAM *prm, KEY_PART *key_part, + const Item_bool_func *cond, + scalar_comparison_op op, Item *value, + bool unsigned_field) +{ + DBUG_ENTER("Field::get_mm_leaf_int"); + if (!can_optimize_scalar_range(prm, key_part, cond, op, value)) + DBUG_RETURN(0); + int err= value->save_in_field_no_warnings(this, 1); + if ((op != SCALAR_CMP_EQUAL && is_real_null()) || err < 0) + DBUG_RETURN(&null_element); + if (err > 0) { - tree= &null_element; - goto end; + if (value->result_type() != INT_RESULT) + DBUG_RETURN(stored_field_make_mm_leaf_truncated(prm, op, value)); + else + DBUG_RETURN(stored_field_make_mm_leaf_bounded_int(prm, key_part, + op, value, + unsigned_field)); } - - str= (uchar*) alloc_root(alloc, key_part->store_length+1); - if (!str) - goto end; - if (maybe_null) - *str= (uchar) field->is_real_null(); // Set to 1 if null - field->get_key_image(str+maybe_null, key_part->length, - key_part->image_type); - if (!(tree= new (alloc) SEL_ARG(field, str, str))) - goto end; // out of memory + if (value->result_type() != INT_RESULT) + DBUG_RETURN(stored_field_make_mm_leaf(prm, key_part, op, value)); + DBUG_RETURN(stored_field_make_mm_leaf_exact(prm, key_part, op, value)); +} + + +/* + This method is called when: + - value->save_in_field_no_warnings() returned err > 0 + - and both field and "value" are of integer data types + If an integer got bounded (e.g. to within 0..255 / -128..127) + for < or >, set flags as for <= or >= (no NEAR_MAX / NEAR_MIN) +*/ + +SEL_ARG *Field::stored_field_make_mm_leaf_bounded_int(RANGE_OPT_PARAM *param, + KEY_PART *key_part, + scalar_comparison_op op, + Item *value, + bool unsigned_field) +{ + DBUG_ENTER("Field::make_mm_leaf_bounded_int"); + if (op == SCALAR_CMP_EQ || op == SCALAR_CMP_EQUAL) // e.g. tinyint = 200 + DBUG_RETURN(new (param->mem_root) SEL_ARG_IMPOSSIBLE(this)); + longlong item_val= value->val_int(); + + if (op == SCALAR_CMP_LT && item_val > 0) + op= SCALAR_CMP_LE; // e.g. rewrite (tinyint < 200) to (tinyint <= 127) + else if (op == SCALAR_CMP_GT && !unsigned_field && + !value->unsigned_flag && item_val < 0) + op= SCALAR_CMP_GE; // e.g. rewrite (tinyint > -200) to (tinyint >= -128)

/* Check if we are comparing an UNSIGNED integer with a negative constant. @@ -8160,66 +8308,74 @@ Item_bool_func::get_mm_leaf(RANGE_OPT_PARAM *param, negative integers (which otherwise fails because at query execution time negative integers are cast to unsigned if compared with unsigned). */ - if (field->result_type() == INT_RESULT && - value->result_type() == INT_RESULT && - ((field->type() == FIELD_TYPE_BIT || - ((Field_num *) field)->unsigned_flag) && - !((Item_int*) value)->unsigned_flag)) + if (unsigned_field && !value->unsigned_flag && item_val < 0) { - longlong item_val= value->val_int(); - if (item_val < 0) - { - if (type == LT_FUNC || type == LE_FUNC) - { - tree->type= SEL_ARG::IMPOSSIBLE; - goto end; - } - if (type == GT_FUNC || type == GE_FUNC) - { - tree= 0; - goto end; - } - } + if (op == SCALAR_CMP_LT || op == SCALAR_CMP_LE) // e.g. uint < -1 + DBUG_RETURN(new (param->mem_root) SEL_ARG_IMPOSSIBLE(this)); + if (op == SCALAR_CMP_GT || op == SCALAR_CMP_GE) // e.g. uint > -1 + DBUG_RETURN(0); } + DBUG_RETURN(stored_field_make_mm_leaf_exact(param, key_part, op, value)); +}

- switch (type) { - case LT_FUNC: - if (stored_field_cmp_to_item(param->thd, field, value) == 0) - tree->max_flag=NEAR_MAX; - /* fall through */ - case LE_FUNC: - if (!maybe_null) - tree->min_flag=NO_MIN_RANGE; /* From start */ - else - { // > NULL - tree->min_value=is_null_string; - tree->min_flag=NEAR_MIN; - } - break; - case GT_FUNC: - /* Don't use open ranges for partial key_segments */ - if ((!(key_part->flag & HA_PART_KEY_SEG)) && - (stored_field_cmp_to_item(param->thd, field, value) <= 0)) - tree->min_flag=NEAR_MIN; - tree->max_flag= NO_MAX_RANGE; - break; - case GE_FUNC: - /* Don't use open ranges for partial key_segments */ - if ((!(key_part->flag & HA_PART_KEY_SEG)) && - (stored_field_cmp_to_item(param->thd, field, value) < 0)) - tree->min_flag= NEAR_MIN; - tree->max_flag=NO_MAX_RANGE; - break; - case EQ_FUNC: - case EQUAL_FUNC: - break; - default: - DBUG_ASSERT(0); + +SEL_ARG *Field::stored_field_make_mm_leaf(RANGE_OPT_PARAM *param, + KEY_PART *key_part, + scalar_comparison_op op, + Item *value) +{ + DBUG_ENTER("Field::stored_field_make_mm_leaf"); + THD *thd= param->thd; + MEM_ROOT *mem_root= param->mem_root; + uchar *str; + if (!(str= make_key_image(param->mem_root, key_part))) + DBUG_RETURN(0); + + switch (op) { + case SCALAR_CMP_LE: + DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this)); + case SCALAR_CMP_LT: + DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, value)); + case SCALAR_CMP_GT: + DBUG_RETURN(new (mem_root) SEL_ARG_GT(thd, str, key_part, this, value)); + case SCALAR_CMP_GE: + DBUG_RETURN(new (mem_root) SEL_ARG_GE(thd, str, key_part, this, value)); + case SCALAR_CMP_EQ: + case SCALAR_CMP_EQUAL: + DBUG_RETURN(new (mem_root) SEL_ARG(this, str, str)); break; } + DBUG_ASSERT(0); + DBUG_RETURN(NULL); +}

-end: - DBUG_RETURN(tree); + +SEL_ARG *Field::stored_field_make_mm_leaf_exact(RANGE_OPT_PARAM *param, + KEY_PART *key_part, + scalar_comparison_op op, + Item *value) +{ + DBUG_ENTER("Field::stored_field_make_mm_leaf_int"); + uchar *str; + if (!(str= make_key_image(param->mem_root, key_part))) + DBUG_RETURN(0); + + switch (op) { + case SCALAR_CMP_LE: + DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this)); + case SCALAR_CMP_LT: + DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this)); + case SCALAR_CMP_GT: + DBUG_RETURN(new (param->mem_root) SEL_ARG_GT(str, key_part, this)); + case SCALAR_CMP_GE: + DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this)); + case SCALAR_CMP_EQ: + case SCALAR_CMP_EQUAL: + DBUG_RETURN(new (param->mem_root) SEL_ARG(this, str, str)); + break; + } + DBUG_ASSERT(0); + DBUG_RETURN(NULL); }

diff --git a/sql/sql_type.cc b/sql/sql_type.cc index 09daca0..bf0df58 100644 --- a/sql/sql_type.cc +++ b/sql/sql_type.cc @@ -5802,3 +5802,91 @@ void Type_handler_geometry::Item_param_set_param_func(Item_param *param, #endif

/***************************************************************************/ + +int Type_handler_temporal_with_date::stored_field_cmp_to_item(THD *thd, + Field *field, + Item *item) const +{ + MYSQL_TIME field_time, item_time, item_time2, *item_time_cmp= &item_time; + field->get_date(&field_time, TIME_INVALID_DATES); + item->get_date(&item_time, TIME_INVALID_DATES); + if (item_time.time_type == MYSQL_TIMESTAMP_TIME && + time_to_datetime(thd, &item_time, item_time_cmp= &item_time2)) + return 1; + return my_time_compare(&field_time, item_time_cmp); +} + + +int Type_handler_time_common::stored_field_cmp_to_item(THD *thd, + Field *field, + Item *item) const +{ + MYSQL_TIME field_time, item_time; + field->get_time(&field_time); + item->get_time(&item_time); + return my_time_compare(&field_time, &item_time); +} + + +int Type_handler_string_result::stored_field_cmp_to_item(THD *thd, + Field *field, + Item *item) const +{ + StringBuffer<MAX_FIELD_WIDTH> item_tmp; + StringBuffer<MAX_FIELD_WIDTH> field_tmp; + String *item_result= item->val_str(&item_tmp); + /* + Some implementations of Item::val_str(String*) actually modify + the field Item::null_value, hence we can't check it earlier. + */ + if (item->null_value) + return 0; + String *field_result= field->val_str(&field_tmp); + return sortcmp(field_result, item_result, field->charset()); +} + + +int Type_handler_int_result::stored_field_cmp_to_item(THD *thd, + Field *field, + Item *item) const +{ + DBUG_ASSERT(0); // Not used yet + return 0; +} + + +int Type_handler_decimal_result::stored_field_cmp_to_item(THD *thd, + Field *field, + Item *item) const +{ + my_decimal item_buf, *item_val, field_buf, *field_val; + item_val= item->val_decimal(&item_buf); + if (item->null_value) + return 0; + field_val= field->val_decimal(&field_buf); + return my_decimal_cmp(field_val, item_val); +} + + +int Type_handler_real_result::stored_field_cmp_to_item(THD *thd, + Field *field, + Item *item) const +{ + /* + The patch for Bug#13463415 started using this function for comparing + BIGINTs. That uncovered a bug in Visual Studio 32bit optimized mode. + Prefixing the auto variables with volatile fixes the problem.... + */ + volatile double result= item->val_real(); + if (item->null_value) + return 0; + volatile double field_result= field->val_real(); + if (field_result < result) + return -1; + else if (field_result > result) + return 1; + return 0; +} + + +/***************************************************************************/ diff --git a/sql/sql_type.h b/sql/sql_type.h index db03b77..e8b365d 100644 --- a/sql/sql_type.h +++ b/sql/sql_type.h @@ -74,6 +74,17 @@ struct TABLE; struct SORT_FIELD_ATTR;

+enum scalar_comparison_op +{ + SCALAR_CMP_EQ, + SCALAR_CMP_EQUAL, + SCALAR_CMP_LT, + SCALAR_CMP_LE, + SCALAR_CMP_GE, + SCALAR_CMP_GT +}; + + /** Class Time is designed to store valid TIME values.

@@ -1029,6 +1040,8 @@ class Type_handler { return this; } + virtual int + stored_field_cmp_to_item(THD *thd, Field *field, Item *item) const= 0; virtual CHARSET_INFO *charset_for_protocol(const Item *item) const; virtual const Type_handler* type_handler_adjusted_to_max_octet_length(uint max_octet_length, @@ -1376,6 +1389,11 @@ class Type_handler_row: public Type_handler return ROW_RESULT; } const Type_handler *type_handler_for_comparison() const; + int stored_field_cmp_to_item(THD *thd, Field *field, Item *item) const + { + DBUG_ASSERT(0); + return 0; + } bool subquery_type_allows_materialization(const Item *inner, const Item *outer) const { @@ -1693,6 +1711,7 @@ class Type_handler_real_result: public Type_handler_numeric Item_result cmp_type() const { return REAL_RESULT; } virtual ~Type_handler_real_result() {} const Type_handler *type_handler_for_comparison() const; + int stored_field_cmp_to_item(THD *thd, Field *field, Item *item) const; bool subquery_type_allows_materialization(const Item *inner, const Item *outer) const; void make_sort_key(uchar *to, Item *item, const SORT_FIELD_ATTR *sort_field, @@ -1766,6 +1785,7 @@ class Type_handler_decimal_result: public Type_handler_numeric Item_result cmp_type() const { return DECIMAL_RESULT; } virtual ~Type_handler_decimal_result() {}; const Type_handler *type_handler_for_comparison() const; + int stored_field_cmp_to_item(THD *thd, Field *field, Item *item) const; bool subquery_type_allows_materialization(const Item *inner, const Item *outer) const; Field *make_num_distinct_aggregator_field(MEM_ROOT *, const Item *) const; @@ -1844,6 +1864,7 @@ class Type_handler_int_result: public Type_handler_numeric Item_result cmp_type() const { return INT_RESULT; } virtual ~Type_handler_int_result() {} const Type_handler *type_handler_for_comparison() const; + int stored_field_cmp_to_item(THD *thd, Field *field, Item *item) const; bool subquery_type_allows_materialization(const Item *inner, const Item *outer) const; Field *make_num_distinct_aggregator_field(MEM_ROOT *, const Item *) const; @@ -1903,6 +1924,7 @@ class Type_handler_int_result: public Type_handler_numeric bool Item_func_mul_fix_length_and_dec(Item_func_mul *) const; bool Item_func_div_fix_length_and_dec(Item_func_div *) const; bool Item_func_mod_fix_length_and_dec(Item_func_mod *) const; + };

@@ -1991,6 +2013,7 @@ class Type_handler_string_result: public Type_handler CHARSET_INFO *charset_for_protocol(const Item *item) const; virtual ~Type_handler_string_result() {} const Type_handler *type_handler_for_comparison() const; + int stored_field_cmp_to_item(THD *thd, Field *field, Item *item) const; const Type_handler * type_handler_adjusted_to_max_octet_length(uint max_octet_length, CHARSET_INFO *cs) const; @@ -2442,6 +2465,7 @@ class Type_handler_time_common: public Type_handler_temporal_result return Item_divisor_precision_increment_with_seconds(item); } const Type_handler *type_handler_for_comparison() const; + int stored_field_cmp_to_item(THD *thd, Field *field, Item *item) const; bool Column_definition_fix_attributes(Column_definition *c) const; bool Item_save_in_value(Item *item, st_value *value) const; bool Item_send(Item *item, Protocol *protocol, st_value *buf) const @@ -2523,6 +2547,7 @@ class Type_handler_temporal_with_date: public Type_handler_temporal_result { public: virtual ~Type_handler_temporal_with_date() {} + int stored_field_cmp_to_item(THD *thd, Field *field, Item *item) const; bool Item_save_in_value(Item *item, st_value *value) const; bool Item_send(Item *item, Protocol *protocol, st_value *buf) const {

_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

-- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog