Backported the test case for bug 43618 from mysql code line.

4 files changed, 149 insertions, 0 deletions

diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc
index af52e7f30d4..4b28faa8e41 100644
--- a/mysql-test/include/icp_tests.inc
+++ b/mysql-test/include/icp_tests.inc
@@ -139,6 +139,47 @@ ORDER BY c2 DESC LIMIT 2;
 DROP TABLE t1;
 
 --echo #
+--echo # BUG#43618: MyISAM&Maria returns wrong results with 'between' 
+--echo #            on timestamp
+--echo #
+
+CREATE TABLE t1(
+   ts TIMESTAMP NOT NULL, 
+   c char NULL,
+   PRIMARY KEY(ts)
+);
+
+INSERT INTO t1 VALUES
+   ('1971-01-01','a'),
+   ('2007-05-25','b'),
+   ('2008-01-01','c'),
+   ('2038-01-09','d');
+
+--disable_warnings
+
+--echo
+--echo # Execute select with invalid timestamp, desc ordering
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2; 
+
+--echo
+--echo # Should use index condition
+EXPLAIN
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2; 
+--echo
+
+--enable_warnings
+
+DROP TABLE t1;
+
+--echo #
 --echo # BUG#49906: Assertion failed - Field_varstring::val_str in field.cc
 --echo # (Note: Fixed by patch for LP BUG#625841)
 --echo #
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index 729ad81fa25..42d53f5cfde 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -133,6 +133,42 @@ c1	c2	c3	c4
 
 DROP TABLE t1;
 #
+# BUG#43618: MyISAM&Maria returns wrong results with 'between' 
+#            on timestamp
+#
+CREATE TABLE t1(
+ts TIMESTAMP NOT NULL, 
+c char NULL,
+PRIMARY KEY(ts)
+);
+INSERT INTO t1 VALUES
+('1971-01-01','a'),
+('2007-05-25','b'),
+('2008-01-01','c'),
+('2038-01-09','d');
+
+# Execute select with invalid timestamp, desc ordering
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2;
+ts	c
+2008-01-01 00:00:00	c
+2007-05-25 00:00:00	b
+
+# Should use index condition
+EXPLAIN
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition
+
+DROP TABLE t1;
+#
 # BUG#49906: Assertion failed - Field_varstring::val_str in field.cc
 # (Note: Fixed by patch for LP BUG#625841)
 #
diff --git a/mysql-test/r/maria_icp.result b/mysql-test/r/maria_icp.result
index 14a450d13ca..39edaf67029 100644
--- a/mysql-test/r/maria_icp.result
+++ b/mysql-test/r/maria_icp.result
@@ -133,6 +133,42 @@ c1	c2	c3	c4
 
 DROP TABLE t1;
 #
+# BUG#43618: MyISAM&Maria returns wrong results with 'between' 
+#            on timestamp
+#
+CREATE TABLE t1(
+ts TIMESTAMP NOT NULL, 
+c char NULL,
+PRIMARY KEY(ts)
+);
+INSERT INTO t1 VALUES
+('1971-01-01','a'),
+('2007-05-25','b'),
+('2008-01-01','c'),
+('2038-01-09','d');
+
+# Execute select with invalid timestamp, desc ordering
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2;
+ts	c
+2008-01-01 00:00:00	c
+2007-05-25 00:00:00	b
+
+# Should use index condition
+EXPLAIN
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using index condition
+
+DROP TABLE t1;
+#
 # BUG#49906: Assertion failed - Field_varstring::val_str in field.cc
 # (Note: Fixed by patch for LP BUG#625841)
 #
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index 41f0d411be4..68f18da3f50 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -131,6 +131,42 @@ c1	c2	c3	c4
 
 DROP TABLE t1;
 #
+# BUG#43618: MyISAM&Maria returns wrong results with 'between' 
+#            on timestamp
+#
+CREATE TABLE t1(
+ts TIMESTAMP NOT NULL, 
+c char NULL,
+PRIMARY KEY(ts)
+);
+INSERT INTO t1 VALUES
+('1971-01-01','a'),
+('2007-05-25','b'),
+('2008-01-01','c'),
+('2038-01-09','d');
+
+# Execute select with invalid timestamp, desc ordering
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2;
+ts	c
+2008-01-01 00:00:00	c
+2007-05-25 00:00:00	b
+
+# Should use index condition
+EXPLAIN
+SELECT *
+FROM t1 
+WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' 
+ORDER BY ts DESC
+LIMIT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using index condition
+
+DROP TABLE t1;
+#
 # BUG#49906: Assertion failed - Field_varstring::val_str in field.cc
 # (Note: Fixed by patch for LP BUG#625841)
 #