maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #11373
Re: Please review a patch for MDEV-15758 Split Item_bool_func::get_mm_leaf() into virtual methods in Field and Type_handler
Hi Vicențiu,
Sending the patch adjusted to the latest 10.4.
Thanks.
On 04/03/2018 12:57 PM, 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 eeedc50..12fe302 100644
--- a/mysql-test/main/type_bit.result
+++ b/mysql-test/main/type_bit.result
@@ -849,3 +849,21 @@ DROP TABLE IF EXISTS t1;
#
# End of 10.2 tests
#
+#
+# Start of 10.4 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.4 tests
+#
diff --git a/mysql-test/main/type_bit.test b/mysql-test/main/type_bit.test
index 04db151..c6d5a1f 100644
--- a/mysql-test/main/type_bit.test
+++ b/mysql-test/main/type_bit.test
@@ -483,3 +483,21 @@ DROP TABLE IF EXISTS t1;
--echo # End of 10.2 tests
--echo #
+--echo #
+--echo # Start of 10.4 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.4 tests
+--echo #
diff --git a/mysql-test/main/type_int.result b/mysql-test/main/type_int.result
index 9fe0451..a3a7026 100644
--- a/mysql-test/main/type_int.result
+++ b/mysql-test/main/type_int.result
@@ -268,5 +268,17 @@ Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
DROP TABLE t1;
#
+# 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.4 tests
#
diff --git a/mysql-test/main/type_int.test b/mysql-test/main/type_int.test
index b3fe250..ba240d7 100644
--- a/mysql-test/main/type_int.test
+++ b/mysql-test/main/type_int.test
@@ -197,5 +197,15 @@ EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1+a<=>?+a' USING 1;
DROP TABLE t1;
--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;
+
+
+--echo #
--echo # End of 10.4 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 a7335d3..597480d 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
{
@@ -847,6 +850,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();
@@ -1375,6 +1382,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)
{
@@ -1547,6 +1607,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
{
@@ -1708,6 +1772,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);
};
@@ -1764,6 +1831,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 */
@@ -2061,6 +2131,12 @@ class Field_int :public Field_num
uint32 prec= type_limits_int()->precision();
return Information_schema_numeric_attributes(prec, 0);
}
+ 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);
+ }
};
@@ -2543,6 +2619,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);
};
@@ -2823,14 +2902,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; }
@@ -2858,14 +2954,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; }
@@ -4180,6 +4277,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 df11b9c..fddae45 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -6507,7 +6507,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;
}
@@ -9941,73 +9941,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_func.h b/sql/item_func.h
index 083b49b..a257b15 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -78,6 +78,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 d49ee33..a7f7547 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)
{
@@ -8015,52 +8093,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
@@ -8080,76 +8218,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 &&
- !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.
@@ -8162,66 +8310,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) &&
- !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 3607e6d..b6b5484 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -6532,3 +6532,89 @@ bool Type_handler_decimal_result::Item_eq_value(THD *thd,
}
/***************************************************************************/
+
+
+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 f0ec0ba..1f0c985 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -77,6 +77,17 @@ struct SORT_FIELD_ATTR;
class Vers_history_point;
+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.
@@ -1180,6 +1191,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,
@@ -1555,6 +1568,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
{
@@ -1881,6 +1899,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,
@@ -1958,6 +1977,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;
@@ -2167,6 +2187,7 @@ class Type_handler_int_result: public Type_handler_numeric
bool is_limit_clause_valid_type() const { return true; }
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;
@@ -2230,6 +2251,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;
+
};
@@ -2324,6 +2346,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;
@@ -2878,6 +2901,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
@@ -2976,6 +3000,7 @@ class Type_handler_temporal_with_date: public Type_handler_temporal_result
virtual ~Type_handler_temporal_with_date() {}
bool Item_eq_value(THD *thd, const Type_cmp_attributes *attr,
Item *a, Item *b) const;
+ 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
{
Follow ups
References
