3771 Jorgen Loland 2012-03-14
BUG#13701206: WHERE A>=B DOES NOT GIVE SAME EXECUTION PLAN
AS WHERE B<=A (RANGE OPTIMIZER)
One would expect the optimizer to create the same QEP from
for these two queries:
1) SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE t1.a=t2.b;
2) SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE t2.b=t1.a;
However, the range optimizer did not treat (a OP b) equal to
(b OP a). Because of this, dynamic range ("Range
checked for each record") was not a possible a access method
for query 1).
The fix is to treat "a OP b" equal to "b OP a" in the range
optimizer.
@ mysql-test/r/range_all.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. The next test in this file is actually
with reverse operands and the same QEP as this result. This
confirms that the change is OK.
@ mysql-test/r/range_icp.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. The next test in this file is actually
with reverse operands and the same QEP as this result. This
confirms that the change is OK.
@ mysql-test/r/range_icp_mrr.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. The next test in this file is actually
with reverse operands and the same QEP as this result. This
confirms that the change is OK.
@ mysql-test/r/range_mrr.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. The next test in this file is actually
with reverse operands and the same QEP as this result. This
confirms that the change is OK.
@ mysql-test/r/range_mrr_cost.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. The next test in this file is actually
with reverse operands and the same QEP as this result. This
confirms that the change is OK.
@ mysql-test/r/range_none.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. The next test in this file is actually
with reverse operands and the same QEP as this result. This
confirms that the change is OK.
@ mysql-test/r/select_all.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. Confirmed that these results are good:
the same QEP is achieved without the bugfix if the operands
are reversed.
@ mysql-test/r/select_all_bka.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. Confirmed that these results are good:
the same QEP is achieved without the bugfix if the operands
are reversed.
@ mysql-test/r/select_all_bka_nixbnl.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. Confirmed that these results are good:
the same QEP is achieved without the bugfix if the operands
are reversed.
@ mysql-test/r/select_icp_mrr.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. Confirmed that these results are good:
the same QEP is achieved without the bugfix if the operands
are reversed.
@ mysql-test/r/select_icp_mrr_bka.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. Confirmed that these results are good:
the same QEP is achieved without the bugfix if the operands
are reversed.
@ mysql-test/r/select_icp_mrr_bka_nixbnl.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. Confirmed that these results are good:
the same QEP is achieved without the bugfix if the operands
are reversed.
@ mysql-test/r/select_none.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. Confirmed that these results are good:
the same QEP is achieved without the bugfix if the operands
are reversed.
@ mysql-test/r/select_none_bka.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. Confirmed that these results are good:
the same QEP is achieved without the bugfix if the operands
are reversed.
@ mysql-test/r/select_none_bka_nixbnl.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. Confirmed that these results are good:
the same QEP is achieved without the bugfix if the operands
are reversed.
@ mysql-test/r/type_time.result
QEP changes as a result of treating "a OP b" equal to "b OP a"
in the range optimizer. Confirmed that these results are good:
the same QEP is achieved without the bugfix if the operands
are reversed.
@ sql/opt_range.cc
Treat "a OP b" equal to "b OP a" in the range optimizer.
modified:
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
mysql-test/r/select_all.result
mysql-test/r/select_all_bka.result
mysql-test/r/select_all_bka_nixbnl.result
mysql-test/r/select_icp_mrr.result
mysql-test/r/select_icp_mrr_bka.result
mysql-test/r/select_icp_mrr_bka_nixbnl.result
mysql-test/r/select_none.result
mysql-test/r/select_none_bka.result
mysql-test/r/select_none_bka_nixbnl.result
mysql-test/r/type_time.result
sql/opt_range.cc
3770 Tor Didriksen 2012-03-14
Fix broken build: uint != size_t !!!
modified:
sql/sql_table.cc
sql/sql_table.h
=== modified file 'mysql-test/r/range_all.result'
--- a/mysql-test/r/range_all.result 2012-03-09 08:50:17 +0000
+++ b/mysql-test/r/range_all.result 2012-03-14 13:10:55 +0000
@@ -2425,7 +2425,7 @@ b
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)
+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
=== modified file 'mysql-test/r/range_icp.result'
--- a/mysql-test/r/range_icp.result 2012-03-09 08:50:17 +0000
+++ b/mysql-test/r/range_icp.result 2012-03-14 13:10:55 +0000
@@ -2425,7 +2425,7 @@ b
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)
+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
=== modified file 'mysql-test/r/range_icp_mrr.result'
--- a/mysql-test/r/range_icp_mrr.result 2012-03-09 08:50:17 +0000
+++ b/mysql-test/r/range_icp_mrr.result 2012-03-14 13:10:55 +0000
@@ -2425,7 +2425,7 @@ b
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)
+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
=== modified file 'mysql-test/r/range_mrr.result'
--- a/mysql-test/r/range_mrr.result 2012-03-09 08:50:17 +0000
+++ b/mysql-test/r/range_mrr.result 2012-03-14 13:10:55 +0000
@@ -2425,7 +2425,7 @@ b
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)
+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
=== modified file 'mysql-test/r/range_mrr_cost.result'
--- a/mysql-test/r/range_mrr_cost.result 2012-03-09 08:50:17 +0000
+++ b/mysql-test/r/range_mrr_cost.result 2012-03-14 13:10:55 +0000
@@ -2425,7 +2425,7 @@ b
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)
+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
=== modified file 'mysql-test/r/range_none.result'
--- a/mysql-test/r/range_none.result 2012-03-09 08:50:17 +0000
+++ b/mysql-test/r/range_none.result 2012-03-14 13:10:55 +0000
@@ -2424,7 +2424,7 @@ b
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)
+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
=== modified file 'mysql-test/r/select_all.result'
--- a/mysql-test/r/select_all.result 2012-03-06 14:29:42 +0000
+++ b/mysql-test/r/select_all.result 2012-03-14 13:10:55 +0000
@@ -4402,7 +4402,7 @@ INSERT INTO t1 VALUES
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
1
1
@@ -4412,7 +4412,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
1
1
=== modified file 'mysql-test/r/select_all_bka.result'
--- a/mysql-test/r/select_all_bka.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/select_all_bka.result 2012-03-14 13:10:55 +0000
@@ -4403,7 +4403,7 @@ INSERT INTO t1 VALUES
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
1
1
@@ -4413,7 +4413,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
1
1
=== modified file 'mysql-test/r/select_all_bka_nixbnl.result'
--- a/mysql-test/r/select_all_bka_nixbnl.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/select_all_bka_nixbnl.result 2012-03-14 13:10:55 +0000
@@ -4403,7 +4403,7 @@ INSERT INTO t1 VALUES
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
1
1
@@ -4413,7 +4413,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
1
1
=== modified file 'mysql-test/r/select_icp_mrr.result'
--- a/mysql-test/r/select_icp_mrr.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/select_icp_mrr.result 2012-03-14 13:10:55 +0000
@@ -4402,7 +4402,7 @@ INSERT INTO t1 VALUES
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
1
1
@@ -4412,7 +4412,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
1
1
=== modified file 'mysql-test/r/select_icp_mrr_bka.result'
--- a/mysql-test/r/select_icp_mrr_bka.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/select_icp_mrr_bka.result 2012-03-14 13:10:55 +0000
@@ -4403,7 +4403,7 @@ INSERT INTO t1 VALUES
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
1
1
@@ -4413,7 +4413,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
1
1
=== modified file 'mysql-test/r/select_icp_mrr_bka_nixbnl.result'
--- a/mysql-test/r/select_icp_mrr_bka_nixbnl.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/select_icp_mrr_bka_nixbnl.result 2012-03-14 13:10:55 +0000
@@ -4403,7 +4403,7 @@ INSERT INTO t1 VALUES
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
1
1
@@ -4413,7 +4413,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
1
1
=== modified file 'mysql-test/r/select_none.result'
--- a/mysql-test/r/select_none.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/select_none.result 2012-03-14 13:10:55 +0000
@@ -4401,7 +4401,7 @@ INSERT INTO t1 VALUES
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
1
1
@@ -4411,7 +4411,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
1
1
=== modified file 'mysql-test/r/select_none_bka.result'
--- a/mysql-test/r/select_none_bka.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/select_none_bka.result 2012-03-14 13:10:55 +0000
@@ -4402,7 +4402,7 @@ INSERT INTO t1 VALUES
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
1
1
@@ -4412,7 +4412,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
1
1
=== modified file 'mysql-test/r/select_none_bka_nixbnl.result'
--- a/mysql-test/r/select_none_bka_nixbnl.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/select_none_bka_nixbnl.result 2012-03-14 13:10:55 +0000
@@ -4402,7 +4402,7 @@ INSERT INTO t1 VALUES
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
1
1
@@ -4412,7 +4412,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS
EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL
-1 SIMPLE t2 ALL a NULL NULL NULL 2 Using where
+1 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1)
SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
1
1
=== modified file 'mysql-test/r/type_time.result'
--- a/mysql-test/r/type_time.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/type_time.result 2012-03-14 13:10:55 +0000
@@ -684,7 +684,7 @@ t1 force INDEX (col_time_key)
WHERE col_datetime_key = col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 NULL
-1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Warning 1713 Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key'
Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
@@ -770,7 +770,7 @@ t1 force INDEX (col_time_key)
WHERE col_datetime_key = col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
-1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Warning 1713 Cannot use ref access on index 'col_time_key' due to type or collation conversion on field 'col_time_key'
Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
@@ -854,7 +854,7 @@ t2 force INDEX (col_datetime_key)
WHERE col_time_key >= col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 NULL
-1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`)
SELECT * FROM
@@ -978,7 +978,7 @@ t2 force INDEX (col_datetime_key)
WHERE col_time_key >= col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index
-1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`)
SELECT * FROM
@@ -1133,7 +1133,7 @@ t1 force INDEX (col_time_key)
WHERE col_datetime_key >= col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 NULL
-1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`)
SELECT * FROM
@@ -1257,7 +1257,7 @@ t1 force INDEX (col_time_key)
WHERE col_datetime_key >= col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
-1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`)
SELECT * FROM
@@ -1340,7 +1340,7 @@ t2 force INDEX (col_datetime_key)
WHERE col_time_key > col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 NULL
-1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
SELECT * FROM
@@ -1444,7 +1444,7 @@ t2 force INDEX (col_datetime_key)
WHERE col_time_key > col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index
-1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
SELECT * FROM
@@ -1574,7 +1574,7 @@ t1 force INDEX (col_time_key)
WHERE col_datetime_key > col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 NULL
-1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`)
SELECT * FROM
@@ -1678,7 +1678,7 @@ t1 force INDEX (col_time_key)
WHERE col_datetime_key > col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
-1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`)
SELECT * FROM
@@ -1766,7 +1766,7 @@ t2 force INDEX (col_datetime_key)
WHERE col_time_key <= col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 NULL
-1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`)
SELECT * FROM
@@ -1890,7 +1890,7 @@ t2 force INDEX (col_datetime_key)
WHERE col_time_key <= col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index
-1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`)
SELECT * FROM
@@ -2045,7 +2045,7 @@ t1 force INDEX (col_time_key)
WHERE col_datetime_key <= col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 NULL
-1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`)
SELECT * FROM
@@ -2169,7 +2169,7 @@ t1 force INDEX (col_time_key)
WHERE col_datetime_key <= col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
-1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`)
SELECT * FROM
@@ -2252,7 +2252,7 @@ t2 force INDEX (col_datetime_key)
WHERE col_time_key < col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 NULL
-1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`)
SELECT * FROM
@@ -2356,7 +2356,7 @@ t2 force INDEX (col_datetime_key)
WHERE col_time_key < col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using index
-1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t2 ALL col_datetime_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`)
SELECT * FROM
@@ -2486,7 +2486,7 @@ t1 force INDEX (col_time_key)
WHERE col_datetime_key < col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 NULL
-1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` IGNORE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`)
SELECT * FROM
@@ -2590,7 +2590,7 @@ t1 force INDEX (col_time_key)
WHERE col_datetime_key < col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
-1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`)
SELECT * FROM
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2012-03-09 14:50:40 +0000
+++ b/sql/opt_range.cc 2012-03-14 13:10:55 +0000
@@ -5977,7 +5977,8 @@ static SEL_TREE *get_func_mm_tree(RANGE_
param PARAM from SQL_SELECT::test_quick_select
cond_func item for the predicate
field_item field in the predicate
- value constant in the predicate
+ value constant in the predicate (or a field already read from
+ a table in the case of dynamic range access)
(for BETWEEN it contains the number of the field argument,
for IN it's always 0)
inv TRUE <> NOT cond_func is considered
@@ -6253,21 +6254,37 @@ static SEL_TREE *get_mm_tree(RANGE_OPT_P
DBUG_RETURN(ftree);
}
default:
+
+ DBUG_ASSERT (!ftree);
if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM)
{
field_item= (Item_field*) (cond_func->arguments()[0]->real_item());
- value= cond_func->arg_count > 1 ? cond_func->arguments()[1] : 0;
+ value= cond_func->arg_count > 1 ? cond_func->arguments()[1] : NULL;
+ ftree= get_full_func_mm_tree(param, cond_func, field_item, value, inv);
}
- else if (cond_func->have_rev_func() &&
- cond_func->arguments()[1]->real_item()->type() ==
- Item::FIELD_ITEM)
+ /*
+ Even if get_full_func_mm_tree() was executed above and did not
+ return a range predicate it may still be possible to create one
+ by reversing the order of the operands. Note that this only
+ applies to predicates where both operands are fields. Example: A
+ query of the form
+
+ WHERE t1.a OP t2.b
+
+ In this case, arguments()[0] == t1.a and arguments()[1] == t2.b.
+ When creating range predicates for t2, get_full_func_mm_tree()
+ above will return NULL because 'field' belongs to t1 and only
+ predicates that applies to t2 are of interest. In this case a
+ call to get_full_func_mm_tree() with reversed operands (see
+ below) may succeed.
+ */
+ if (!ftree && cond_func->have_rev_func() &&
+ cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM)
{
field_item= (Item_field*) (cond_func->arguments()[1]->real_item());
value= cond_func->arguments()[0];
+ ftree= get_full_func_mm_tree(param, cond_func, field_item, value, inv);
}
- else
- DBUG_RETURN(0);
- ftree= get_full_func_mm_tree(param, cond_func, field_item, value, inv);
}
DBUG_RETURN(ftree);
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:3770 to 3771) Bug#13701206 | Jorgen Loland | 14 Mar |
