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#13803810 | Jorgen Loland | 9 Mar |
