#At file:///data0/martin/bzr/bug46454/5.1bt-gca/ based on revid:davi.arnaut@stripped
3062 Martin Hansson 2009-08-05
Bug#46454: MySQL wrong index optimisation leads to incorrect result & crashes
When the 'Using index' optimization is used, the optimizer may still - after
cost-based optimization - decide to use another index in order to avoid using
a temporary table. But when this happens, the flag to the storage engine to
read index only (not table) was still set. Fixed by resetting the flag in the
storage engine and TABLE structure in the above scenario, unless the new index
allows for the same optimization.
@ mysql-test/r/order_by.result
Bug#46454: Test result.
@ mysql-test/t/order_by.test
Bug#46454: Test case.
@ sql/sql_select.cc
Bug#46454: Fix. Removed code which would a duplicate.
@ sql/table.h
Bug#46454: Added comment to field.
modified:
mysql-test/r/order_by.result
mysql-test/t/order_by.test
sql/sql_select.cc
sql/table.h
=== modified file 'mysql-test/r/order_by.result'
--- a/mysql-test/r/order_by.result 2008-10-16 18:04:31 +0000
+++ b/mysql-test/r/order_by.result 2009-08-05 14:44:24 +0000
@@ -1500,3 +1500,39 @@ id1
15
16
DROP TABLE t1;
+CREATE TABLE t1 (a INT, b INT, c char(100), KEY (b, c), KEY (b, a, c))
+DEFAULT CHARSET = utf8;
+INSERT INTO t1 VALUES
+(1, 1, 1),
+(2, 2, 2),
+(3, 3, 3),
+(4, 4, 4),
+(5, 5, 5),
+(6, 6, 6),
+(7, 7, 7),
+(8, 8, 8),
+(9, 9, 9);
+INSERT INTO t1 SELECT a + 10, b, c FROM t1;
+INSERT INTO t1 SELECT a + 20, b, c FROM t1;
+INSERT INTO t1 SELECT a + 40, b, c FROM t1;
+INSERT INTO t1 SELECT a + 80, b, c FROM t1;
+INSERT INTO t1 SELECT a + 160, b, c FROM t1;
+INSERT INTO t1 SELECT a + 320, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+EXPLAIN
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range b,b_2 b 5 NULL 226 Using where
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+a
+1911
+1901
+1891
+1881
+1871
+1861
+1851
+1841
+1831
+DROP TABLE t1;
=== modified file 'mysql-test/t/order_by.test'
--- a/mysql-test/t/order_by.test 2008-10-16 18:04:31 +0000
+++ b/mysql-test/t/order_by.test 2009-08-05 14:44:24 +0000
@@ -1361,3 +1361,34 @@ DROP TABLE t1;
+#
+# Bug#46454: MySQL wrong index optimisation leads to incorrect result & crashes
+#
+CREATE TABLE t1 (a INT, b INT, c char(100), KEY (b, c), KEY (b, a, c))
+DEFAULT CHARSET = utf8;
+
+INSERT INTO t1 VALUES
+(1, 1, 1),
+(2, 2, 2),
+(3, 3, 3),
+(4, 4, 4),
+(5, 5, 5),
+(6, 6, 6),
+(7, 7, 7),
+(8, 8, 8),
+(9, 9, 9);
+
+INSERT INTO t1 SELECT a + 10, b, c FROM t1;
+INSERT INTO t1 SELECT a + 20, b, c FROM t1;
+INSERT INTO t1 SELECT a + 40, b, c FROM t1;
+INSERT INTO t1 SELECT a + 80, b, c FROM t1;
+INSERT INTO t1 SELECT a + 160, b, c FROM t1;
+INSERT INTO t1 SELECT a + 320, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+
+EXPLAIN
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+
+DROP TABLE t1;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-07-24 12:15:56 +0000
+++ b/sql/sql_select.cc 2009-08-05 14:44:24 +0000
@@ -13277,6 +13277,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
HA_POS_ERROR :
join->unit->select_limit_cnt,
0) > 0;
+ /*
+ If ref_key used index tree reading only ('Using index' in EXPLAIN),
+ and best_key doesn't, then revert this decision.
+ */
+ if (!table->covering_keys.is_set(best_key))
+ {
+ table->key_read= 0;
+ table->file->extra(HA_EXTRA_NO_KEYREAD);
+ }
}
if (!no_changes)
{
@@ -13296,16 +13305,6 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
table->key_read=1;
table->file->extra(HA_EXTRA_KEYREAD);
}
- else if (table->key_read)
- {
- /*
- Clear the covering key read flags that might have been
- previously set for some key other than the current best_key.
- */
- table->key_read= 0;
- table->file->extra(HA_EXTRA_NO_KEYREAD);
- }
-
table->file->ha_index_or_rnd_end();
if (join->select_options & SELECT_DESCRIBE)
{
=== modified file 'sql/table.h'
--- a/sql/table.h 2009-07-29 08:54:20 +0000
+++ b/sql/table.h 2009-08-05 14:44:24 +0000
@@ -755,7 +755,13 @@ struct st_table {
*/
my_bool force_index;
my_bool distinct,const_table,no_rows;
- my_bool key_read, no_keyread;
+
+ /**
+ If set, the optimizer has found that row retrieval should access index
+ tree only.
+ */
+ my_bool key_read;
+ my_bool no_keyread;
/*
Placeholder for an open table which prevents other connections
from taking name-locks on this table. Typically used with
Attachment: [text/bzr-bundle] bzr/martin.hansson@sun.com-20090805144424-h0y0mp0ein26hj7q.bundle
