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 _______________________________________________ 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