[Commits] Rev 4034: BUG#13803810: TOO FEW ROWS RETURNED FOR RANGE ACCESS IN VARCHAR INDEX USING DATETIME VALUE in file:///home/psergey/dev2/10.0/

Sergey Petrunya psergey at askmonty.org
Fri Mar 7 14:00:21 EET 2014


At file:///home/psergey/dev2/10.0/

------------------------------------------------------------
revno: 4034
revision-id: psergey at askmonty.org-20140307120020-mtoqihi39x8aj0nn
parent: psergey at askmonty.org-20140307114940-fdrrc83h0ib7kz0r
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 10.0
timestamp: Fri 2014-03-07 13:00:20 +0100
message:
  BUG#13803810: TOO FEW ROWS RETURNED FOR RANGE ACCESS IN VARCHAR INDEX USING DATETIME VALUE
  - Backport the testcase from mysql-5.6
=== modified file 'mysql-test/r/range.result'
--- a/mysql-test/r/range.result	2014-03-07 11:49:40 +0000
+++ b/mysql-test/r/range.result	2014-03-07 12:00:20 +0000
@@ -2058,6 +2058,60 @@ pk
 5
 DROP TABLE t1;
 #
+# BUG#13803810: TOO FEW ROWS RETURNED FOR RANGE ACCESS IN 
+#               VARCHAR INDEX USING DATETIME VALUE
+
+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');
+
+# range/ref access cannot be used for this query
+
+EXPLAIN SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	b	b	67	NULL	5	Using where; Using index
+SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE);
+b
+2001#01#01
+2001-01-01
+2001-01-01 00:00:00
+2001.01.01
+
+# range/ref access cannot be used for any of the queries below.
+# See BUG#13814468 about 'Range checked for each record'
+
+EXPLAIN SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	5	Range checked for each record (index map: 0x1)
+SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b;
+a	b
+2001-01-01 00:00:00	2001#01#01
+2001-01-01 00:00:00	2001-01-01
+2001-01-01 00:00:00	2001-01-01 00:00:00
+2001-01-01 00:00:00	2001.01.01
+2001-01-01 11:22:33	2001-01-01 11:22:33
+
+EXPLAIN SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	5	Range checked for each record (index map: 0x1)
+SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b;
+a	b
+2001-01-01 00:00:00	2001#01#01
+2001-01-01 00:00:00	2001-01-01
+2001-01-01 00:00:00	2001-01-01 00:00:00
+2001-01-01 00:00:00	2001.01.01
+2001-01-01 11:22:33	2001-01-01 11:22:33
+
+DROP TABLE t1,t2;
+#
 #  MDEV-5606: range optimizer: "x < y" is sargable, while "y > x" is not
 #
 create table t1(a int);

=== modified file 'mysql-test/r/range_mrr_icp.result'
--- a/mysql-test/r/range_mrr_icp.result	2014-03-07 11:49:40 +0000
+++ b/mysql-test/r/range_mrr_icp.result	2014-03-07 12:00:20 +0000
@@ -2060,6 +2060,60 @@ pk
 5
 DROP TABLE t1;
 #
+# BUG#13803810: TOO FEW ROWS RETURNED FOR RANGE ACCESS IN 
+#               VARCHAR INDEX USING DATETIME VALUE
+
+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');
+
+# range/ref access cannot be used for this query
+
+EXPLAIN SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	b	b	67	NULL	5	Using where; Using index
+SELECT * FROM t2 WHERE b=CAST('2001-01-01' AS DATE);
+b
+2001#01#01
+2001-01-01
+2001-01-01 00:00:00
+2001.01.01
+
+# range/ref access cannot be used for any of the queries below.
+# See BUG#13814468 about 'Range checked for each record'
+
+EXPLAIN SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	5	Range checked for each record (index map: 0x1)
+SELECT * FROM t1, t2 WHERE a=b ORDER BY BINARY a, BINARY b;
+a	b
+2001-01-01 00:00:00	2001#01#01
+2001-01-01 00:00:00	2001-01-01
+2001-01-01 00:00:00	2001-01-01 00:00:00
+2001-01-01 00:00:00	2001.01.01
+2001-01-01 11:22:33	2001-01-01 11:22:33
+
+EXPLAIN SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	5	Range checked for each record (index map: 0x1)
+SELECT * FROM t1, t2 WHERE b=a ORDER BY BINARY a, BINARY b;
+a	b
+2001-01-01 00:00:00	2001#01#01
+2001-01-01 00:00:00	2001-01-01
+2001-01-01 00:00:00	2001-01-01 00:00:00
+2001-01-01 00:00:00	2001.01.01
+2001-01-01 11:22:33	2001-01-01 11:22:33
+
+DROP TABLE t1,t2;
+#
 #  MDEV-5606: range optimizer: "x < y" is sargable, while "y > x" is not
 #
 create table t1(a int);

=== modified file 'mysql-test/t/range.test'
--- a/mysql-test/t/range.test	2014-03-07 11:49:40 +0000
+++ b/mysql-test/t/range.test	2014-03-07 12:00:20 +0000
@@ -1649,6 +1649,44 @@ 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);



More information about the commits mailing list