List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:March 9 2012 8:50am
Subject:bzr push into mysql-trunk branch (jorgen.loland:3739 to 3740) Bug#13803810
View as plain text  
 3740 Jorgen Loland	2012-03-09
      Bug#13803810: TOO FEW ROWS RETURNED FOR RANGE ACCESS 
                    IN VARCHAR INDEX USING DATETIME VALUE
      
      There are many allowed ways to represent DATE and TIME in MySQL,
      e.g the default "2001-01-01 11:22:33" but also variations like
      "01#01#2001 11,22,33" and "1.1.2001". If a VARCHAR column is 
      used to store temporal data, we don't know which format is 
      used and it may not even be consistent from row to row. Due to 
      this, a lookup key cannot be created for temporal data into a 
      VARCHAR index.
      
      'ref' access of temporal values in VARCHAR columns was 
      already disallowed, but 'range' access was not. The fix is to
      also disallow 'range' access.
     @ mysql-test/include/range.inc
        Add test for BUG#13803810
     @ mysql-test/r/gis.result
        Recorded result file after bugfix of BUG#13803810. The test
        case used to do range access with no qualifying rows in the
        ranges. However, range access cannot be used for string indexes
        when the value is DATETIME, so after the bugfix a table scan 
        must be performed instead. The rows are now evaluated
        wrt the predicates and this emits warnings.
     @ mysql-test/r/range_all.result
        Add test for BUG#13803810
     @ mysql-test/r/range_icp.result
        Add test for BUG#13803810
     @ mysql-test/r/range_icp_mrr.result
        Add test for BUG#13803810
     @ mysql-test/r/range_mrr.result
        Add test for BUG#13803810
     @ mysql-test/r/range_mrr_cost.result
        Add test for BUG#13803810
     @ mysql-test/r/range_none.result
        Add test for BUG#13803810
     @ sql/opt_range.cc
        Don't do range access if the indexed column is a VARCHAR and
        the value of the predicate is temporal (DATE, DATETIME etc).

    modified:
      mysql-test/include/range.inc
      mysql-test/r/gis.result
      mysql-test/r/range_all.result
      mysql-test/r/range_icp.result
      mysql-test/r/range_icp_mrr.result
      mysql-test/r/range_mrr.result
      mysql-test/r/range_mrr_cost.result
      mysql-test/r/range_none.result
      sql/opt_range.cc
 3739 Sunny Bains	2012-03-09
      Bug #13817703 - auto_increment_offset != 1 + innodb_autoinc_lock_mode=1 => bulk inserts fail
      
      Follow up to fix: If offset > max_value then we return max_value.

    modified:
      storage/innobase/handler/ha_innodb.cc
=== modified file 'mysql-test/include/range.inc'
--- a/mysql-test/include/range.inc	2012-03-08 09:44:21 +0000
+++ b/mysql-test/include/range.inc	2012-03-09 08:50:17 +0000
@@ -1749,3 +1749,41 @@ 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;
+

=== modified file 'mysql-test/r/gis.result'
--- a/mysql-test/r/gis.result	2012-03-06 14:29:42 +0000
+++ b/mysql-test/r/gis.result	2012-03-09 08:50:17 +0000
@@ -1089,7 +1089,9 @@ FORCE INDEX(i) WHERE a = date_sub(now(),
 ;
 1
 Warnings:
+Warning	1292	Truncated incorrect datetime value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\xF0?\x00\x00\x00\x00\x00\x00\xF0?'
 Warning	1441	Datetime function: datetime field overflow
+Warning	1292	Truncated incorrect datetime value: '\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\xF0?\x00\x00\x00\x00\x00\x00\x00@'
 DROP TABLE g1;
 #
 # Bug#13013970 MORE CRASHES IN FIELD_BLOB::GET_KEY_IMAGE

=== modified file 'mysql-test/r/range_all.result'
--- a/mysql-test/r/range_all.result	2012-03-08 09:44:21 +0000
+++ b/mysql-test/r/range_all.result	2012-03-09 08:50:17 +0000
@@ -2393,4 +2393,58 @@ pk
 3
 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	index	b	b	67	NULL	5	Using where; Using index; Using join buffer (Block Nested Loop)
+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;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_icp.result'
--- a/mysql-test/r/range_icp.result	2012-03-07 07:16:42 +0000
+++ b/mysql-test/r/range_icp.result	2012-03-09 08:50:17 +0000
@@ -2393,4 +2393,58 @@ pk
 3
 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	index	b	b	67	NULL	5	Using where; Using index; Using join buffer (Block Nested Loop)
+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;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_icp_mrr.result'
--- a/mysql-test/r/range_icp_mrr.result	2012-03-07 07:16:42 +0000
+++ b/mysql-test/r/range_icp_mrr.result	2012-03-09 08:50:17 +0000
@@ -2393,4 +2393,58 @@ pk
 3
 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	index	b	b	67	NULL	5	Using where; Using index; Using join buffer (Block Nested Loop)
+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;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_mrr.result'
--- a/mysql-test/r/range_mrr.result	2012-03-07 07:16:42 +0000
+++ b/mysql-test/r/range_mrr.result	2012-03-09 08:50:17 +0000
@@ -2393,4 +2393,58 @@ pk
 3
 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	index	b	b	67	NULL	5	Using where; Using index; Using join buffer (Block Nested Loop)
+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;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_mrr_cost.result'
--- a/mysql-test/r/range_mrr_cost.result	2012-03-07 07:16:42 +0000
+++ b/mysql-test/r/range_mrr_cost.result	2012-03-09 08:50:17 +0000
@@ -2393,4 +2393,58 @@ pk
 3
 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	index	b	b	67	NULL	5	Using where; Using index; Using join buffer (Block Nested Loop)
+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;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_none.result'
--- a/mysql-test/r/range_none.result	2012-03-07 07:16:42 +0000
+++ b/mysql-test/r/range_none.result	2012-03-09 08:50:17 +0000
@@ -2392,4 +2392,58 @@ pk
 3
 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	index	b	b	67	NULL	5	Using where; Using index; Using join buffer (Block Nested Loop)
+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;
 set optimizer_switch=default;

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2012-03-08 09:44:21 +0000
+++ b/sql/opt_range.cc	2012-03-09 08:50:17 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -5666,6 +5666,33 @@ QUICK_SELECT_I *TRP_ROR_UNION::make_quic
 }
 
 
+/**
+   If EXPLAIN EXTENDED, add a warning that the index cannot be
+   used for range access due to either type conversion or different
+   collations on the field used for comparison
+
+   @param param              PARAM from SQL_SELECT::test_quick_select
+   @param key_num            Key number
+   @param field              Field in the predicate
+ */
+static void 
+if_extended_explain_warn_index_not_applicable(const RANGE_OPT_PARAM *param,
+                                              const uint key_num,
+                                              const Field *field)
+{
+  if (param->using_real_indexes &&
+      param->thd->lex->describe & DESCRIBE_EXTENDED)
+    push_warning_printf(
+            param->thd,
+            Sql_condition::WARN_LEVEL_WARN, 
+            ER_WARN_INDEX_NOT_APPLICABLE,
+            ER(ER_WARN_INDEX_NOT_APPLICABLE),
+            "range",
+            field->table->key_info[param->real_keynr[key_num]].name,
+            field->field_name);
+}
+
+
 /*
   Build a SEL_TREE for <> or NOT BETWEEN predicate
  
@@ -5682,7 +5709,6 @@ QUICK_SELECT_I *TRP_ROR_UNION::make_quic
     #  Pointer to tree built tree
     0  on error
 */
-
 static SEL_TREE *get_ne_mm_tree(RANGE_OPT_PARAM *param, Item_func *cond_func, 
                                 Field *field,
                                 Item *lt_value, Item *gt_value,
@@ -6352,10 +6378,6 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
        WHERE latin1_swedish_ci_column = 'a' COLLATE lati1_bin;
 
        WHERE latin1_swedish_ci_colimn = BINARY 'a '
-
-    3. Grep for IndexedTimeComparedToDate. If 'value' is a DATETIME part,
-       using the index on the TIME column would retain only the TIME part of
-       'value', giving false comparison results.
   */
   if ((field->result_type() == STRING_RESULT &&
        field->match_collation_to_optimize_range() &&
@@ -6363,19 +6385,39 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
        key_part->image_type == Field::itRAW &&
        field->charset() != conf_func->compare_collation() &&
        !(conf_func->compare_collation()->state & MY_CS_BINSORT &&
-         (type == Item_func::EQUAL_FUNC || type == Item_func::EQ_FUNC))) ||
-      field_time_cmp_date(field, value))
+         (type == Item_func::EQUAL_FUNC || type == Item_func::EQ_FUNC))))
   {
-    if (param->using_real_indexes &&
-        param->thd->lex->describe & DESCRIBE_EXTENDED)
-      push_warning_printf(
-              param->thd,
-              Sql_condition::WARN_LEVEL_WARN, 
-              ER_WARN_INDEX_NOT_APPLICABLE,
-              ER(ER_WARN_INDEX_NOT_APPLICABLE),
-              "range",
-              field->table->key_info[param->real_keynr[key_part->key]].name,
-              field->field_name);
+    if_extended_explain_warn_index_not_applicable(param, key_part->key, field);
+    goto end;
+  }
+
+  /*
+    Temporal values: Cannot use range access if:
+      1) 'temporal_value = indexed_varchar_column' because there are
+         many ways to represent the same date as a string. A few
+         examples: "01-01-2001", "1-1-2001", "2001-01-01",
+         "2001#01#01". The same problem applies to time. Thus, we
+         cannot create a usefull range predicate for temporal values
+         into VARCHAR column indexes. @see add_key_field()
+      2) 'temporal_value_with_date_part = indexed_time' because: 
+         - without index, a TIME column with value '48:00:00' is 
+           equal to a DATETIME column with value 
+           'CURDATE() + 2 days' 
+         - with range access into the TIME column, CURDATE() + 2 
+           days becomes "00:00:00" (Field_timef::store_internal() 
+           simply extracts the time part from the datetime) which 
+           is a lookup key which does not match "48:00:00"; so 
+           ref access is not be able to give the same result as 
+           On the other hand, we can do ref access for
+           IndexedDatetimeComparedToTime because
+           Field_temporal_with_date::store_time() will convert
+           48:00:00 to CURDATE() + 2 days which is the correct
+           lookup key.
+   */
+  if ((!field->is_temporal() && value->is_temporal()) ||   // 1)
+      field_time_cmp_date(field, value))                   // 2)
+  {
+    if_extended_explain_warn_index_not_applicable(param, key_part->key, field);
     goto end;
   }
 
@@ -6499,18 +6541,10 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item
       value->result_type() != STRING_RESULT &&
       field->cmp_type() != value->result_type())
   {
-    if (param->using_real_indexes &&
-        param->thd->lex->describe & DESCRIBE_EXTENDED)
-      push_warning_printf(
-              param->thd,
-              Sql_condition::WARN_LEVEL_WARN, 
-              ER_WARN_INDEX_NOT_APPLICABLE,
-              ER(ER_WARN_INDEX_NOT_APPLICABLE),
-              "range",
-              field->table->key_info[param->real_keynr[key_part->key]].name,
-              field->field_name);
+    if_extended_explain_warn_index_not_applicable(param, key_part->key, field);
     goto end;
   }
+
   /* For comparison purposes allow invalid dates like 2000-01-32 */
   orig_sql_mode= field->table->in_use->variables.sql_mode;
   if (value->real_item()->type() == Item::STRING_ITEM &&

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (jorgen.loland:3739 to 3740) Bug#13803810Jorgen Loland9 Mar