summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/null.result4
-rw-r--r--mysql-test/r/type_float.result11
-rw-r--r--mysql-test/r/type_int.result17
-rw-r--r--mysql-test/r/type_newdecimal.result11
-rw-r--r--mysql-test/r/type_year.result44
-rw-r--r--mysql-test/t/type_float.test9
-rw-r--r--mysql-test/t/type_int.test16
-rw-r--r--mysql-test/t/type_newdecimal.test7
-rw-r--r--mysql-test/t/type_year.test22
-rw-r--r--sql/field.cc19
-rw-r--r--sql/field.h31
-rw-r--r--sql/item.cc2
12 files changed, 157 insertions, 36 deletions
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index f5c8b5c68de..debb3c05681 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -1369,7 +1369,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and 1)
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2010)
SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL;
a
2010
@@ -1377,7 +1377,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL EL
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and 1)
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2010)
DROP TABLE t1;
# Two warnings expected
CREATE TABLE t1 AS SELECT
diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result
index 76465d77b50..21734b30c8c 100644
--- a/mysql-test/r/type_float.result
+++ b/mysql-test/r/type_float.result
@@ -625,5 +625,16 @@ Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 1.10e0) and (<cache>(length(1.100)) <> rand()))
DROP TABLE t1;
#
+# MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010
+#
+CREATE TABLE t1 (a DOUBLE ZEROFILL);
+INSERT INTO t1 VALUES (2010),(2020);
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010e0 AND a>=2010e0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2010e0)
+DROP TABLE t1;
+#
# End of 10.1 tests
#
diff --git a/mysql-test/r/type_int.result b/mysql-test/r/type_int.result
new file mode 100644
index 00000000000..b52c26b6851
--- /dev/null
+++ b/mysql-test/r/type_int.result
@@ -0,0 +1,17 @@
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010
+#
+CREATE TABLE t1 (a INT ZEROFILL);
+INSERT INTO t1 VALUES (2010),(2020);
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=2010;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2010)
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index 0e5f16bd8c0..b498910f13c 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -2145,5 +2145,16 @@ Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 1.10) and (<cache>(length(1.100)) <> rand()))
DROP TABLE t1;
#
+# MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010
+#
+CREATE TABLE t1 (a DECIMAL(10,1) ZEROFILL);
+INSERT INTO t1 VALUES (2010),(2020);
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010.0 AND a>=2010.0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2010.0)
+DROP TABLE t1;
+#
# End of 10.1 tests
#
diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result
index 38bddd42cfa..842a16e3b4a 100644
--- a/mysql-test/r/type_year.result
+++ b/mysql-test/r/type_year.result
@@ -394,3 +394,47 @@ select a from t1 where a=b;
a
drop table t1;
drop function y2k;
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010
+#
+CREATE TABLE t1 (a YEAR);
+INSERT INTO t1 VALUES (2010),(2020);
+SELECT * FROM t1 WHERE a=2010 AND a>=2010;
+a
+2010
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=2010;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2010)
+SELECT * FROM t1 WHERE a=2010 AND a>=10;
+a
+2010
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=10;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2010)
+SELECT * FROM t1 WHERE a=10 AND a>=2010;
+a
+2010
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10 AND a>=2010;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2010)
+SELECT * FROM t1 WHERE a=10 AND a>=10;
+a
+2010
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10 AND a>=10;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2010)
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test
index 9b4b6fa21f7..3717dc028ba 100644
--- a/mysql-test/t/type_float.test
+++ b/mysql-test/t/type_float.test
@@ -447,5 +447,14 @@ DROP TABLE t1;
--echo #
+--echo # MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010
+--echo #
+CREATE TABLE t1 (a DOUBLE ZEROFILL);
+INSERT INTO t1 VALUES (2010),(2020);
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010e0 AND a>=2010e0;
+DROP TABLE t1;
+
+
+--echo #
--echo # End of 10.1 tests
--echo #
diff --git a/mysql-test/t/type_int.test b/mysql-test/t/type_int.test
new file mode 100644
index 00000000000..9c67f27d9b2
--- /dev/null
+++ b/mysql-test/t/type_int.test
@@ -0,0 +1,16 @@
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010
+--echo #
+
+CREATE TABLE t1 (a INT ZEROFILL);
+INSERT INTO t1 VALUES (2010),(2020);
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=2010;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test
index d01e37c5262..c31aefdb71c 100644
--- a/mysql-test/t/type_newdecimal.test
+++ b/mysql-test/t/type_newdecimal.test
@@ -1667,6 +1667,13 @@ EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)!=rand() AND a=1.10;
DROP TABLE t1;
+--echo #
+--echo # MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010
+--echo #
+CREATE TABLE t1 (a DECIMAL(10,1) ZEROFILL);
+INSERT INTO t1 VALUES (2010),(2020);
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010.0 AND a>=2010.0;
+DROP TABLE t1;
--echo #
--echo # End of 10.1 tests
diff --git a/mysql-test/t/type_year.test b/mysql-test/t/type_year.test
index 685587fe3c5..d9fa2af1eb4 100644
--- a/mysql-test/t/type_year.test
+++ b/mysql-test/t/type_year.test
@@ -188,3 +188,25 @@ drop table t1;
drop function y2k;
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010
+--echo #
+CREATE TABLE t1 (a YEAR);
+INSERT INTO t1 VALUES (2010),(2020);
+SELECT * FROM t1 WHERE a=2010 AND a>=2010;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=2010;
+SELECT * FROM t1 WHERE a=2010 AND a>=10;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=10;
+SELECT * FROM t1 WHERE a=10 AND a>=2010;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10 AND a>=2010;
+SELECT * FROM t1 WHERE a=10 AND a>=10;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10 AND a>=10;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
diff --git a/sql/field.cc b/sql/field.cc
index f040891141d..dcbea63f5c0 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -1403,7 +1403,6 @@ Item *Field_num::convert_zerofill_number_to_string(THD *thd, Item *item) const
Item *Field_num::get_equal_zerofill_const_item(THD *thd, const Context &ctx,
- Item_field *field_item,
Item *const_item)
{
switch (ctx.subst_constraint()) {
@@ -1414,7 +1413,7 @@ Item *Field_num::get_equal_zerofill_const_item(THD *thd, const Context &ctx,
}
DBUG_ASSERT(const_item->const_item());
DBUG_ASSERT(ctx.compare_type() != STRING_RESULT);
- return field_item;
+ return const_item;
}
@@ -3292,12 +3291,10 @@ Field_new_decimal::unpack(uchar* to, const uchar *from, const uchar *from_end,
Item *Field_new_decimal::get_equal_const_item(THD *thd, const Context &ctx,
- Item_field *field_item,
Item *const_item)
{
if (flags & ZEROFILL_FLAG)
- return Field_num::get_equal_zerofill_const_item(thd, ctx,
- field_item, const_item);
+ return Field_num::get_equal_zerofill_const_item(thd, ctx, const_item);
switch (ctx.subst_constraint()) {
case IDENTITY_SUBST:
if (const_item->field_type() != MYSQL_TYPE_NEWDECIMAL ||
@@ -4644,12 +4641,10 @@ bool Field_real::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
Item *Field_real::get_equal_const_item(THD *thd, const Context &ctx,
- Item_field *field_item,
Item *const_item)
{
if (flags & ZEROFILL_FLAG)
- return Field_num::get_equal_zerofill_const_item(thd, ctx,
- field_item, const_item);
+ return Field_num::get_equal_zerofill_const_item(thd, ctx, const_item);
switch (ctx.subst_constraint()) {
case IDENTITY_SUBST:
if (const_item->decimal_scale() != Field_real::decimals())
@@ -5519,9 +5514,9 @@ bool Field_temporal::can_optimize_group_min_max(const Item_bool_func *cond,
}
-Item *Field_temporal::get_equal_const_item_datetime(THD *thd, const Context &ctx,
- Item_field *field_item,
- Item *const_item)
+Item *Field_temporal::get_equal_const_item_datetime(THD *thd,
+ const Context &ctx,
+ Item *const_item)
{
switch (ctx.subst_constraint()) {
case IDENTITY_SUBST:
@@ -5843,7 +5838,6 @@ int Field_time::store_decimal(const my_decimal *d)
Item *Field_time::get_equal_const_item(THD *thd, const Context &ctx,
- Item_field *field_item,
Item *const_item)
{
switch (ctx.subst_constraint()) {
@@ -6311,7 +6305,6 @@ void Field_newdate::sql_type(String &res) const
Item *Field_newdate::get_equal_const_item(THD *thd, const Context &ctx,
- Item_field *field_item,
Item *const_item)
{
switch (ctx.subst_constraint()) {
diff --git a/sql/field.h b/sql/field.h
index 1c438c25589..868854ff156 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -1103,7 +1103,7 @@ public:
virtual bool can_be_substituted_to_equal_item(const Context &ctx,
const Item_equal *item);
virtual Item *get_equal_const_item(THD *thd, const Context &ctx,
- Item_field *field_item, Item *const_item)
+ Item *const_item)
{
return const_item;
}
@@ -1209,7 +1209,6 @@ class Field_num :public Field {
protected:
Item *convert_zerofill_number_to_string(THD *thd, Item *item) const;
Item *get_equal_zerofill_const_item(THD *thd, const Context &ctx,
- Item_field *field_item,
Item *const_item);
public:
const uint8 dec;
@@ -1223,11 +1222,10 @@ public:
uint repertoire(void) const { return MY_REPERTOIRE_NUMERIC; }
CHARSET_INFO *charset(void) const { return &my_charset_numeric; }
void prepend_zeros(String *value) const;
- Item *get_equal_const_item(THD *thd, const Context &ctx,
- Item_field *field_item, Item *const_item)
+ Item *get_equal_const_item(THD *thd, const Context &ctx, Item *const_item)
{
return (flags & ZEROFILL_FLAG) ?
- get_equal_zerofill_const_item(thd, ctx, field_item, const_item) :
+ get_equal_zerofill_const_item(thd, ctx, const_item) :
const_item;
}
void add_zerofill_and_unsigned(String &res) const;
@@ -1363,8 +1361,7 @@ public:
my_decimal *val_decimal(my_decimal *);
uint32 max_display_length() { return field_length; }
uint size_of() const { return sizeof(*this); }
- Item *get_equal_const_item(THD *thd, const Context &ctx,
- Item_field *field_item, Item *const_item);
+ Item *get_equal_const_item(THD *thd, const Context &ctx, Item *const_item);
};
@@ -1450,8 +1447,7 @@ public:
uint is_equal(Create_field *new_field);
virtual const uchar *unpack(uchar* to, const uchar *from, const uchar *from_end, uint param_data);
static Field *create_from_item(MEM_ROOT *root, Item *);
- Item *get_equal_const_item(THD *thd, const Context &ctx,
- Item_field *field_item, Item *const_item);
+ Item *get_equal_const_item(THD *thd, const Context &ctx, Item *const_item);
};
@@ -1790,7 +1786,6 @@ public:
class Field_temporal: public Field {
protected:
Item *get_equal_const_item_datetime(THD *thd, const Context &ctx,
- Item_field *field_item,
Item *const_item);
public:
Field_temporal(uchar *ptr_arg,uint32 len_arg, uchar *null_ptr_arg,
@@ -1937,10 +1932,9 @@ public:
return unpack_int32(to, from, from_end);
}
bool validate_value_in_record(THD *thd, const uchar *record) const;
- Item *get_equal_const_item(THD *thd, const Context &ctx,
- Item_field *field_item, Item *const_item)
+ Item *get_equal_const_item(THD *thd, const Context &ctx, Item *const_item)
{
- return get_equal_const_item_datetime(thd, ctx, field_item, const_item);
+ return get_equal_const_item_datetime(thd, ctx, const_item);
}
uint size_of() const { return sizeof(*this); }
};
@@ -2130,8 +2124,7 @@ public:
bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate)
{ return Field_newdate::get_TIME(ltime, ptr, fuzzydate); }
uint size_of() const { return sizeof(*this); }
- Item *get_equal_const_item(THD *thd, const Context &ctx,
- Item_field *field_item, Item *const_item);
+ Item *get_equal_const_item(THD *thd, const Context &ctx, Item *const_item);
};
@@ -2175,8 +2168,7 @@ public:
Field *new_key_field(MEM_ROOT *root, TABLE *new_table,
uchar *new_ptr, uint32 length,
uchar *new_null_ptr, uint new_null_bit);
- Item *get_equal_const_item(THD *thd, const Context &ctx,
- Item_field *field_item, Item *const_item);
+ Item *get_equal_const_item(THD *thd, const Context &ctx, Item *const_item);
};
@@ -2335,10 +2327,9 @@ public:
{
return unpack_int64(to, from, from_end);
}
- Item *get_equal_const_item(THD *thd, const Context &ctx,
- Item_field *field_item, Item *const_item)
+ Item *get_equal_const_item(THD *thd, const Context &ctx, Item *const_item)
{
- return get_equal_const_item_datetime(thd, ctx, field_item, const_item);
+ return get_equal_const_item_datetime(thd, ctx, const_item);
}
uint size_of() const { return sizeof(*this); }
};
diff --git a/sql/item.cc b/sql/item.cc
index 075480ba33c..074d3966ecc 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5416,7 +5416,7 @@ Item *Item_field::propagate_equal_fields(THD *thd,
*/
return this;
}
- if (!(item= field->get_equal_const_item(thd, ctx, this, item)))
+ if (!(item= field->get_equal_const_item(thd, ctx, item)))
{
/*
Could not do safe conversion from the original constant item
View Lorry Controller Status