1 files changed, 73 insertions, 0 deletions
diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test
index bb7a784553e..3717dc028ba 100644
--- a/mysql-test/t/type_float.test
+++ b/mysql-test/t/type_float.test
@@ -385,3 +385,76 @@ DROP TABLE t1,t2;
 --echo #
 --echo # End of 10.0 tests
 --echo #
+
+
+--echo #
+--echo # MDEV-8704 Wrong result for SELECT..WHERE LENGTH(double_column)!=6 AND double_column=100e0
+--echo #
+
+# The original test case from the bug report
+CREATE TABLE t1 (a DOUBLE(9,2));
+INSERT INTO t1 VALUES (100),(110);
+SELECT * FROM t1 WHERE LENGTH(a)!=6;
+SELECT * FROM t1 WHERE LENGTH(a)!=6 AND a=100e0;
+DROP TABLE t1;
+
+# DOUBLE with no specific precision
+CREATE TABLE t1 (a DOUBLE);
+INSERT INTO t1 VALUES (100),(110);
+SELECT * FROM t1 WHERE LENGTH(a)!=6;
+SELECT * FROM t1 WHERE LENGTH(a)!=6 AND a=100e0;
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE LENGTH(a)!=6 AND a=100e0;
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE LENGTH(a)!=RAND() AND a=100e0;
+DROP TABLE t1;
+
+# The constant scale is bigger than the field scale
+CREATE TABLE t1 (a DOUBLE(10,1));
+INSERT INTO t1 VALUES (1.1),(1.2),(1.3);
+SELECT * FROM t1 WHERE LENGTH(a)!=3;
+SELECT * FROM t1 WHERE LENGTH(a)!=3 AND a=1.10e0;
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE LENGTH(a)!=3 AND a=1.10e0;
+--echo # Notice 1.1 instead of 1.10 in the final WHERE condition
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE LENGTH(a)!=RAND() AND a=1.10e0;
+DROP TABLE t1;
+
+# The constant scale is equal to the field scale
+CREATE TABLE t1 (a DOUBLE(10,2));
+INSERT INTO t1 VALUES (1.1),(1.2),(1.3);
+SELECT * FROM t1 WHERE LENGTH(a)!=4;
+SELECT * FROM t1 WHERE LENGTH(a)!=4 AND a=1.10e0;
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE LENGTH(a)!=4 AND a=1.10e0;
+--echo # Notice 1.10 in the final WHERE condition
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE LENGTH(a)!=RAND() AND a=1.10e0;
+DROP TABLE t1;
+
+# The constant scale is smaller than the field scale
+CREATE TABLE t1 (a DOUBLE(10,3));
+INSERT INTO t1 VALUES (1.1),(1.2),(1.3);
+SELECT * FROM t1 WHERE LENGTH(a)!=5;
+SELECT * FROM t1 WHERE LENGTH(a)!=5 AND a=1.10e0;
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE LENGTH(a)!=5 AND a=1.10e0;
+--echo # Notice 1.100 rather than 1.10 in the final WHERE condition
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE LENGTH(a)!=RAND() AND a=1.10e0;
+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 #