summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/compare.result2
-rw-r--r--mysql-test/r/func_encrypt.result18
-rw-r--r--mysql-test/r/func_str.result27
-rw-r--r--mysql-test/r/func_weight_string.result48
-rw-r--r--mysql-test/r/null.result10
-rw-r--r--mysql-test/r/type_int.result21
-rw-r--r--mysql-test/t/func_encrypt.test18
-rw-r--r--mysql-test/t/func_str.test21
-rw-r--r--mysql-test/t/func_weight_string.test28
-rw-r--r--mysql-test/t/type_int.test12
-rw-r--r--sql/field.cc31
-rw-r--r--sql/field.h3
12 files changed, 201 insertions, 38 deletions
diff --git a/mysql-test/r/compare.result b/mysql-test/r/compare.result
index cdd234f1478..a5654fb3160 100644
--- a/mysql-test/r/compare.result
+++ b/mysql-test/r/compare.result
@@ -64,7 +64,7 @@ EXPLAIN EXTENDED SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`b` = 1) and (concat('01',`test`.`t1`.`c`) = '0101'))
+Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`b` = 1) and (concat(`test`.`t1`.`b`,`test`.`t1`.`c`) = '0101'))
SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101';
b c
01 01
diff --git a/mysql-test/r/func_encrypt.result b/mysql-test/r/func_encrypt.result
index 91ff4e218fb..f5e03a90327 100644
--- a/mysql-test/r/func_encrypt.result
+++ b/mysql-test/r/func_encrypt.result
@@ -190,3 +190,21 @@ insert into t1 values (null,'contraction\'s');
insert into t1 values (-15818,'requirement\'s');
select encrypt(f1,f2) as a from t1,(select encrypt(f1,f2) as b from t1) a;
drop table t1;
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field
+#
+CREATE TABLE t1 (a INT(6) ZEROFILL);
+INSERT INTO t1 VALUES (1),(2);
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=1 AND DES_ENCRYPT('test',a)=_latin1 'abc' COLLATE latin1_bin;
+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` = 1) and (des_encrypt('test',`test`.`t1`.`a`) = 'abc'))
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 3ebb5f2d3eb..c37fa7a654a 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -4535,3 +4535,30 @@ set global max_allowed_packet=default;
#
# End of 5.6 tests
#
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field
+#
+CREATE TABLE t1 (a BIGINT(20) ZEROFILL);
+INSERT INTO t1 VALUES (18446744073709551615),(0);
+SELECT * FROM t1 WHERE a=18446744073709551615;
+a
+18446744073709551615
+SELECT * FROM t1 WHERE FORMAT(a,0)='18,446,744,073,709,551,615';
+a
+18446744073709551615
+SELECT * FROM t1 WHERE a=18446744073709551615 AND FORMAT(a,0)='18,446,744,073,709,551,615';
+a
+18446744073709551615
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=18446744073709551615 AND FORMAT(a,0)='18,446,744,073,709,551,615';
+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` = 18446744073709551615) and (format(`test`.`t1`.`a`,0) = '18,446,744,073,709,551,615'))
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/func_weight_string.result b/mysql-test/r/func_weight_string.result
index f28e5451af5..e63ee4267ea 100644
--- a/mysql-test/r/func_weight_string.result
+++ b/mysql-test/r/func_weight_string.result
@@ -92,3 +92,51 @@ Warnings:
Warning 1301 Result of cast_as_binary() was larger than max_allowed_packet (1048576) - truncated
Warning 1301 Result of weight_string() was larger than max_allowed_packet (1048576) - truncated
set global max_allowed_packet=default;
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field
+#
+CREATE TABLE t1 (a INT(6) ZEROFILL);
+INSERT INTO t1 VALUES (1),(2);
+SELECT * FROM t1 WHERE a=1;
+a
+000001
+SELECT * FROM t1 WHERE WEIGHT_STRING(a) IS NULL;
+a
+000001
+000002
+SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(a) IS NULL;
+a
+000001
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(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` = 1) and isnull(weight_string(`test`.`t1`.`a`)))
+ALTER TABLE t1 MODIFY a DOUBLE ZEROFILL;
+SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(a) IS NULL;
+a
+0000000000000000000001
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(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` = 1) and isnull(weight_string(`test`.`t1`.`a`)))
+ALTER TABLE t1 MODIFY a DECIMAL(10,1) ZEROFILL;
+SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(a) IS NULL;
+a
+000000001.0
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(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` = 1) and isnull(weight_string(`test`.`t1`.`a`)))
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index 04044602a68..b4cebac661f 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -1427,15 +1427,15 @@ a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011';
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+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 0
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and ((case when 2010 = 2011 then NULL else `test`.`t1`.`a` end) = '2011'))
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)=CONCAT('2011',RAND());
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 (<cache>((case when 2010 = 2011 then NULL else '2010' end)) = concat('2011',rand())))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and ((case when 2010 = 2011 then NULL else `test`.`t1`.`a` end) = concat('2011',rand())))
DROP TABLE t1;
#
# MDEV-8754 Wrong result for SELECT..WHERE year_field=2020 AND NULLIF(year_field,2010)='2020'
@@ -1456,13 +1456,13 @@ SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)='2020';
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` = 2020)
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2020) and ((case when 2020 = 2010 then NULL else `test`.`t1`.`a` end) = '2020'))
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)=CONCAT('2020',RAND());
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` = 2020) and (<cache>((case when 2020 = 2010 then NULL else '2020' end)) = concat('2020',rand())))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2020) and ((case when 2020 = 2010 then NULL else `test`.`t1`.`a` end) = concat('2020',rand())))
DROP TABLE t1;
#
# End of 10.1 tests
diff --git a/mysql-test/r/type_int.result b/mysql-test/r/type_int.result
index b52c26b6851..4e7b928ac07 100644
--- a/mysql-test/r/type_int.result
+++ b/mysql-test/r/type_int.result
@@ -13,5 +13,26 @@ Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2010)
DROP TABLE t1;
#
+# MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field
+#
+CREATE TABLE t1 (a INT ZEROFILL);
+INSERT INTO t1 VALUES (128),(129);
+SELECT * FROM t1 WHERE a=128;
+a
+0000000128
+SELECT * FROM t1 WHERE hex(a)='80';
+a
+0000000128
+SELECT * FROM t1 WHERE a=128 AND hex(a)='80';
+a
+0000000128
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=128 AND hex(a)='80';
+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` = 128) and (hex(`test`.`t1`.`a`) = '80'))
+DROP TABLE t1;
+#
# End of 10.1 tests
#
diff --git a/mysql-test/t/func_encrypt.test b/mysql-test/t/func_encrypt.test
index 18fb072966b..4eab0ac87e9 100644
--- a/mysql-test/t/func_encrypt.test
+++ b/mysql-test/t/func_encrypt.test
@@ -103,3 +103,21 @@ insert into t1 values (-15818,'requirement\'s');
select encrypt(f1,f2) as a from t1,(select encrypt(f1,f2) as b from t1) a;
--enable_result_log
drop table t1;
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field
+--echo #
+CREATE TABLE t1 (a INT(6) ZEROFILL);
+INSERT INTO t1 VALUES (1),(2);
+# This should not propagate a=1 into DES_ENCRYPT
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=1 AND DES_ENCRYPT('test',a)=_latin1 'abc' COLLATE latin1_bin;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index 726397bc2a7..14af10b0320 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -1750,3 +1750,24 @@ set global max_allowed_packet=default;
--echo #
--echo # End of 5.6 tests
--echo #
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field
+--echo #
+CREATE TABLE t1 (a BIGINT(20) ZEROFILL);
+INSERT INTO t1 VALUES (18446744073709551615),(0);
+SELECT * FROM t1 WHERE a=18446744073709551615;
+SELECT * FROM t1 WHERE FORMAT(a,0)='18,446,744,073,709,551,615';
+SELECT * FROM t1 WHERE a=18446744073709551615 AND FORMAT(a,0)='18,446,744,073,709,551,615';
+# This should not propagate the equality into FORMAT()
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=18446744073709551615 AND FORMAT(a,0)='18,446,744,073,709,551,615';
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
diff --git a/mysql-test/t/func_weight_string.test b/mysql-test/t/func_weight_string.test
index cf6524e9753..b8fdfd8b250 100644
--- a/mysql-test/t/func_weight_string.test
+++ b/mysql-test/t/func_weight_string.test
@@ -119,3 +119,31 @@ SELECT HEX(WEIGHT_STRING('ab' AS BINARY(1000000000000000000)));
disconnect conn1;
connection default;
set global max_allowed_packet=default;
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field
+--echo #
+CREATE TABLE t1 (a INT(6) ZEROFILL);
+INSERT INTO t1 VALUES (1),(2);
+SELECT * FROM t1 WHERE a=1;
+SELECT * FROM t1 WHERE WEIGHT_STRING(a) IS NULL;
+SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(a) IS NULL;
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(a) IS NULL;
+ALTER TABLE t1 MODIFY a DOUBLE ZEROFILL;
+SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(a) IS NULL;
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(a) IS NULL;
+ALTER TABLE t1 MODIFY a DECIMAL(10,1) ZEROFILL;
+SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(a) IS NULL;
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=1 AND WEIGHT_STRING(a) IS NULL;
+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
index 9c67f27d9b2..e8b9b2cffcd 100644
--- a/mysql-test/t/type_int.test
+++ b/mysql-test/t/type_int.test
@@ -12,5 +12,17 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=2010;
DROP TABLE t1;
--echo #
+--echo # MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field
+--echo #
+CREATE TABLE t1 (a INT ZEROFILL);
+INSERT INTO t1 VALUES (128),(129);
+SELECT * FROM t1 WHERE a=128;
+SELECT * FROM t1 WHERE hex(a)='80';
+SELECT * FROM t1 WHERE a=128 AND hex(a)='80';
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=128 AND hex(a)='80';
+DROP TABLE t1;
+
+--echo #
--echo # End of 10.1 tests
--echo #
diff --git a/sql/field.cc b/sql/field.cc
index e9c20d1d92a..99e38feb71b 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -1373,41 +1373,12 @@ void Field_num::prepend_zeros(String *value) const
}
-/**
- Convert a numeric value to a zero-filled string
-
- @param[in] thd current thread
- @param[in] item the item to convert
-
- This function converts a numeric value to a string. In this conversion
- the zero-fill flag of the field is taken into account.
- This is required so the resulting string value can be used instead of
- the field reference when propagating equalities.
-*/
-
-Item *Field_num::convert_zerofill_number_to_string(THD *thd, Item *item) const
-{
- char buff[MAX_FIELD_WIDTH],*pos;
- String tmp(buff,sizeof(buff),Field_num::charset()), *res;
-
- res= item->val_str(&tmp);
- if (item->is_null())
- return new (thd->mem_root) Item_null(thd);
- else
- {
- prepend_zeros(res);
- pos= (char *) sql_strmake (res->ptr(), res->length());
- return new (thd->mem_root) Item_string(thd, pos, res->length(), Field_num::charset());
- }
-}
-
-
Item *Field_num::get_equal_zerofill_const_item(THD *thd, const Context &ctx,
Item *const_item)
{
switch (ctx.subst_constraint()) {
case IDENTITY_SUBST:
- return convert_zerofill_number_to_string(thd, const_item);
+ return NULL; // Not safe to propagate if not in comparison. See MDEV-8369.
case ANY_SUBST:
break;
}
diff --git a/sql/field.h b/sql/field.h
index 868854ff156..cd6c476671b 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -1207,7 +1207,7 @@ protected:
class Field_num :public Field {
protected:
- Item *convert_zerofill_number_to_string(THD *thd, Item *item) const;
+ void prepend_zeros(String *value) const;
Item *get_equal_zerofill_const_item(THD *thd, const Context &ctx,
Item *const_item);
public:
@@ -1221,7 +1221,6 @@ public:
enum Derivation derivation(void) const { return DERIVATION_NUMERIC; }
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 *const_item)
{
return (flags & ZEROFILL_FLAG) ?
View Lorry Controller Status