MDEV-8699 Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x'

1 files changed, 83 insertions, 0 deletions

diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index 90d66ba6d98..f14b37ff390 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -513,3 +513,86 @@ DROP TABLE t1;
 #
 # End of 10.1 tests
 #
+#
+# Start of 10.1 tests
+#
+#
+#     MDEV-8699 Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x'
+#
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02');
+SELECT * FROM t1 WHERE a='2001-01-01x';
+a
+2001-01-01
+Warnings:
+Warning	1292	Truncated incorrect date value: '2001-01-01x'
+SELECT * FROM t1 WHERE HEX(a)!='323030312D30312D3031';
+a
+2001-01-02
+SELECT * FROM t1 WHERE HEX(a)!='323030312D30312D3031' AND a='2001-01-01x';
+a
+Warnings:
+Warning	1292	Truncated incorrect date value: '2001-01-01x'
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE HEX(a)!='323030312D30312D3031' AND a='2001-01-01x';
+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
+Warnings:
+Warning	1292	Truncated incorrect date value: '2001-01-01x'
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE HEX(a)!=CONCAT('xx',RAND()) AND a='2001-01-01x';
+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:
+Warning	1292	Truncated incorrect date value: '2001-01-01x'
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = '2001-01-01x') and (<cache>(hex(DATE'2001-01-01')) <> concat('xx',rand())))
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02');
+SELECT * FROM t1 WHERE LENGTH(a)=11;
+a
+SELECT * FROM t1 WHERE LENGTH(a)=11 AND a=' 2001-01-01';
+a
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE LENGTH(a)=11 AND a=' 2001-01-01';
+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
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE LENGTH(a)=11+RAND() AND a=' 2001-01-01';
+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` = ' 2001-01-01') and (<cache>(length(DATE'2001-01-01')) = (11 + rand())))
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE LENGTH(a)=11+RAND() AND a=' garbage ';
+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:
+Warning	1292	Incorrect datetime value: ' garbage '
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = ' garbage ') and (length(`test`.`t1`.`a`) = (11 + rand())))
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-01');
+SELECT * FROM t1 WHERE LENGTH(a)=8;
+a
+SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='20010101';
+a
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='20010101';
+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
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE LENGTH(a)=8+RAND() AND a='20010101';
+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` = '20010101') and (<cache>(length(DATE'2001-01-01')) = (8 + rand())))
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#