1 files changed, 68 insertions, 0 deletions
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index e93eff1b1af..adff404a0c5 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1732,3 +1732,71 @@ SELECT * FROM t1 WHERE a > 5;
 SELECT * FROM t1 WHERE a > 5;
 set @@optimizer_switch=@save_optimizer_switch;
 drop table t1;
+
+--echo #
+--echo # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE
+--echo #
+
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(3),(5);
+SELECT * FROM t1 WHERE pk <> 3 OR pk < 4;
+DROP TABLE t1;
+
+--echo #
+--echo # BUG#13803810: TOO FEW ROWS RETURNED FOR RANGE ACCESS IN 
+--echo #               VARCHAR INDEX USING DATETIME VALUE
+--echo
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00');
+INSERT INTO t1 VALUES ('2001-01-01 11:22:33');
+
+CREATE TABLE t2 (b VARCHAR(64), KEY (b));
+INSERT INTO t2 VALUES ('2001-01-01');
+INSERT INTO t2 VALUES ('2001.01.01');
+INSERT INTO t2 VALUES ('2001#01#01');
+INSERT INTO t2 VALUES ('2001-01-01 00:00:00');
+INSERT INTO t2 VALUES ('2001-01-01 11:22:33');
+
+
+--echo
+--echo # range/ref access cannot be used for this query
+--echo
+EXPLAIN SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE);
+SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE);
+
+let $query_ab=SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b;
+let $query_ba=SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b;
+
+--echo
+--echo # range/ref access cannot be used for any of the queries below.
+--echo # See BUG#13814468 about 'Range checked for each record'
+--echo
+eval EXPLAIN $query_ab;
+eval $query_ab;
+--echo
+eval EXPLAIN $query_ba;
+eval $query_ba;
+
+--echo
+DROP TABLE t1,t2;
+
+--echo #
+--echo #  MDEV-5606: range optimizer: "x < y" is sargable, while "y > x" is not
+--echo #
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2(a int);
+insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C;
+alter table t2 add key(a);
+--echo # Should have "range checked for each table" for second table:
+explain select * from t1, t2 where t2.a < t1.a;
+--echo # Should have "range checked for each table" for second table:
+explain select * from t1, t2 where t1.a > t2.a;
+
+create table t3 (a int primary key, b int);
+insert into t3 select a,a from t1;
+--echo # The second table should use 'range':
+explain select * from t3, t2 where t2.a < t3.b  and t3.a=1;
+--echo # The second table should use 'range':
+explain select * from t3, t2 where t3.b > t2.a   and t3.a=1;
+drop table t1,t2,t3;