Below is the list of changes that have just been committed into a local
5.1 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-09-04 11:49:29+02:00, mhansson@stripped +6 -0
Bug#30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY}
The test of sort keys for ORDER BY was prohibited from considering keys
that were mentioned in IGNORE KEYS FOR ORDER BY. This led to two
inconsistencies: One was that IGNORE INDEX FOR GROUP BY and
IGNORE INDEX FOR ORDER BY gave different plans; the latter had an
unneccesary filesort. The second inconsistency is that the test of sort
keys finds no usable sort key, but one is used anyway, leading to the
mentioned filesort.
Fixed by making the test of sort keys consider all enabled keys on the table.
This test rejects keys that are not covering, and for covering keys the
hint should be ignored anyway.
BitKeeper/etc/ignore@stripped, 2007-09-04 11:48:40+02:00, mhansson@stripped +3 -0
Added cscope.in.out cscope.out cscope.po.out to the ignore list
mysql-test/r/group_by.result@stripped, 2007-09-04 11:48:40+02:00, mhansson@stripped +147 -1
Bug#30665: Changed test result. The plan gets more efficient here.
The output is included in order to show that it is still correct.
mysql-test/r/order_by.result@stripped, 2007-09-04 11:48:40+02:00, mhansson@stripped +297 -0
Bug#30665: Test result
mysql-test/t/group_by.test@stripped, 2007-09-04 11:48:41+02:00, mhansson@stripped +1 -0
Bug#30665: Changed test case to show correctness of changed plan
mysql-test/t/order_by.test@stripped, 2007-09-04 11:49:23+02:00, mhansson@stripped +51 -0
Bug#30665: Test case
sql/sql_select.cc@stripped, 2007-09-04 11:49:24+02:00, mhansson@stripped +3 -1
Bug#30665:
- the fix
- Added comment to test_if_skip_sort_order
diff -Nrup a/BitKeeper/etc/ignore b/BitKeeper/etc/ignore
--- a/BitKeeper/etc/ignore 2007-08-04 03:44:42 +02:00
+++ b/BitKeeper/etc/ignore 2007-09-04 11:48:40 +02:00
@@ -3001,3 +3001,6 @@ win/vs71cache.txt
win/vs8cache.txt
zlib/*.ds?
zlib/*.vcproj
+cscope.in.out
+cscope.out
+cscope.po.out
diff -Nrup a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
--- a/mysql-test/r/group_by.result 2007-08-03 17:45:44 +02:00
+++ b/mysql-test/r/group_by.result 2007-09-04 11:48:40 +02:00
@@ -1096,7 +1096,153 @@ id select_type table type possible_keys
1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
+SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+a
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+20
+21
+22
+23
+24
+25
+26
+27
+28
+29
+30
+31
+32
+33
+34
+35
+36
+37
+38
+39
+40
+41
+42
+43
+44
+45
+46
+47
+48
+49
+50
+51
+52
+53
+54
+55
+56
+57
+58
+59
+60
+61
+62
+63
+64
+65
+66
+67
+68
+69
+70
+71
+72
+73
+74
+75
+76
+77
+78
+79
+80
+81
+82
+83
+84
+85
+86
+87
+88
+89
+90
+91
+92
+93
+94
+95
+96
+97
+98
+99
+100
+101
+102
+103
+104
+105
+106
+107
+108
+109
+110
+111
+112
+113
+114
+115
+116
+117
+118
+119
+120
+121
+122
+123
+124
+125
+126
+127
+128
+129
+130
+131
+132
+133
+134
+135
+136
+137
+138
+139
+140
+141
+142
+143
+144
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
diff -Nrup a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
--- a/mysql-test/r/order_by.result 2007-08-05 02:15:24 +02:00
+++ b/mysql-test/r/order_by.result 2007-09-04 11:48:40 +02:00
@@ -1131,3 +1131,300 @@ id c3
186 14
196 14
DROP TABLE t1,t2;
+CREATE TABLE t1 (
+a INT,
+b INT,
+PRIMARY KEY (a),
+KEY ab(a, b)
+);
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4);
+INSERT INTO t1 SELECT a + 4, b + 4 FROM t1;
+INSERT INTO t1 SELECT a + 8, b + 8 FROM t1;
+INSERT INTO t1 SELECT a +16, b +16 FROM t1;
+INSERT INTO t1 SELECT a +32, b +32 FROM t1;
+INSERT INTO t1 SELECT a +64, b +64 FROM t1;
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range NULL ab 4 NULL 10 Using index for group-by
+SELECT variable_value INTO @tmp_tables_before
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
+a
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+20
+21
+22
+23
+24
+25
+26
+27
+28
+29
+30
+31
+32
+33
+34
+35
+36
+37
+38
+39
+40
+41
+42
+43
+44
+45
+46
+47
+48
+49
+50
+51
+52
+53
+54
+55
+56
+57
+58
+59
+60
+61
+62
+63
+64
+65
+66
+67
+68
+69
+70
+71
+72
+73
+74
+75
+76
+77
+78
+79
+80
+81
+82
+83
+84
+85
+86
+87
+88
+89
+90
+91
+92
+93
+94
+95
+96
+97
+98
+99
+100
+101
+102
+103
+104
+105
+106
+107
+108
+109
+110
+111
+112
+113
+114
+115
+116
+117
+118
+119
+120
+121
+122
+123
+124
+125
+126
+127
+128
+SELECT variable_value - 1 INTO @tmp_tables_after
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+SELECT @tmp_tables_after = @tmp_tables_before ;
+@tmp_tables_after = @tmp_tables_before
+1
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using index
+SELECT variable_value INTO @tmp_tables_before
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
+a
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+20
+21
+22
+23
+24
+25
+26
+27
+28
+29
+30
+31
+32
+33
+34
+35
+36
+37
+38
+39
+40
+41
+42
+43
+44
+45
+46
+47
+48
+49
+50
+51
+52
+53
+54
+55
+56
+57
+58
+59
+60
+61
+62
+63
+64
+65
+66
+67
+68
+69
+70
+71
+72
+73
+74
+75
+76
+77
+78
+79
+80
+81
+82
+83
+84
+85
+86
+87
+88
+89
+90
+91
+92
+93
+94
+95
+96
+97
+98
+99
+100
+101
+102
+103
+104
+105
+106
+107
+108
+109
+110
+111
+112
+113
+114
+115
+116
+117
+118
+119
+120
+121
+122
+123
+124
+125
+126
+127
+128
+SELECT variable_value - 1 INTO @tmp_tables_after
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+SELECT @tmp_tables_after = @tmp_tables_before;
+@tmp_tables_after = @tmp_tables_before
+1
+DROP TABLE t1;
diff -Nrup a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
--- a/mysql-test/t/group_by.test 2007-07-31 08:12:19 +02:00
+++ b/mysql-test/t/group_by.test 2007-09-04 11:48:41 +02:00
@@ -811,6 +811,7 @@ EXPLAIN SELECT a FROM t1 IGNORE INDEX (P
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
diff -Nrup a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
--- a/mysql-test/t/order_by.test 2007-08-05 02:15:24 +02:00
+++ b/mysql-test/t/order_by.test 2007-09-04 11:49:23 +02:00
@@ -779,3 +779,54 @@ EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BE
SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20;
DROP TABLE t1,t2;
+
+#
+# Bug #30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY}
+#
+
+CREATE TABLE t1 (
+ a INT,
+ b INT,
+ PRIMARY KEY (a),
+ KEY ab(a, b)
+);
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4);
+INSERT INTO t1 SELECT a + 4, b + 4 FROM t1;
+INSERT INTO t1 SELECT a + 8, b + 8 FROM t1;
+INSERT INTO t1 SELECT a +16, b +16 FROM t1;
+INSERT INTO t1 SELECT a +32, b +32 FROM t1;
+INSERT INTO t1 SELECT a +64, b +64 FROM t1;
+
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
+
+SELECT variable_value INTO @tmp_tables_before
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+
+SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
+
+# this query creates one temporary table in itself, which we are not
+# interested in.
+SELECT variable_value - 1 INTO @tmp_tables_after
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+
+SELECT @tmp_tables_after = @tmp_tables_before ;
+
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
+
+SELECT variable_value INTO @tmp_tables_before
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+
+SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
+
+# this query creates one temporary table in itself, which we are not
+# interested in.
+SELECT variable_value - 1 INTO @tmp_tables_after
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+
+SELECT @tmp_tables_after = @tmp_tables_before;
+
+DROP TABLE t1;
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2007-08-26 16:49:45 +02:00
+++ b/sql/sql_select.cc 2007-09-04 11:49:24 +02:00
@@ -1681,7 +1681,7 @@ JOIN::exec()
test_if_skip_sort_order(&join_tab[const_tables], order,
select_limit, 0,
&join_tab[const_tables].table->
- keys_in_use_for_order_by))))
+ keys_in_use_for_query))))
order=0;
having= tmp_having;
select_describe(this, need_tmp,
@@ -12589,6 +12589,8 @@ find_field_in_item_list (Field *field, v
If we can use an index, the JOIN_TAB / tab->select struct
is changed to use the index.
+
+ The index must cover all fields in <order>, or it will not be considered.
Return:
0 We have to use filesort to do the sorting
