MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:mhansson Date:September 25 2007 3:47pm
Subject:bk commit into 5.1 tree (mhansson:1.2577) BUG#30665
View as plain text  
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-25 17:47:27+02:00, mhansson@stripped +5 -0
  Bug#30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY}
  
  The optimizer takes different execution paths during EXPLAIN than SELECT,
  this fix relates only to EXPLAIN, hence no behavior changes.
  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 apparently different EXPLAINs; the latter 
  erroneously claimed to do filesort. The second inconsistency 
  is that the test of sort keys is called twice, finding a sort key the first
  time but not the second time, 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. 

  mysql-test/r/group_by.result@stripped, 2007-09-25 17:47:23+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-25 17:47:23+02:00, mhansson@stripped +285 -0
    Bug#30665: Test result

  mysql-test/t/group_by.test@stripped, 2007-09-25 17:47:23+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-25 17:47:23+02:00, mhansson@stripped +57 -0
    Bug#30665: Test case

  sql/sql_select.cc@stripped, 2007-09-25 17:47:24+02:00, mhansson@stripped +3 -1
    Bug#30665: 
    - the fix: Give test_if_skip_sort_order all keys not the subset of non-disabled keys.
    - Added comment to test_if_skip_sort_order

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-25 17:47:23 +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-25 17:47:23 +02:00
@@ -1131,3 +1131,288 @@ 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 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 @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 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 @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-25 17:47:23 +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-25 17:47:23 +02:00
@@ -779,3 +779,60 @@ 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;
+
+--disable_query_log
+--let $q = `show status like 'Created_tmp_tables';`
+eval set @tmp_tables_before = 
+  CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED);
+--enable_query_log
+
+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.
+
+--disable_query_log
+--let $q = `show status like 'Created_tmp_tables';`
+eval set @tmp_tables_after = 
+  CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED);
+--enable_query_log
+
+SELECT @tmp_tables_after = @tmp_tables_before ;
+
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
+
+--disable_query_log
+--let $q = `show status like 'Created_tmp_tables';`
+eval set @tmp_tables_before = 
+  CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED);
+--enable_query_log
+
+SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
+
+--disable_query_log
+--let $q = `show status like 'Created_tmp_tables';`
+eval set @tmp_tables_after = 
+  CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED);
+--enable_query_log
+
+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-25 17:47: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
Thread
bk commit into 5.1 tree (mhansson:1.2577) BUG#30665mhansson25 Sep