From: Date: October 9 2006 5:51pm Subject: bk commit into 5.0 tree (gkodinov:1.2293) BUG#22781 List-Archive: http://lists.mysql.com/commits/13340 X-Bug: 22781 Message-Id: <20061009155159.C300C6128CC@macbook.local> Below is the list of changes that have just been committed into a local 5.0 repository of kgeorge. When kgeorge 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, 2006-10-09 19:51:41+04:00, gkodinov@stripped +8 -0 Bug #22781: SQL_BIG_RESULT fails to influence sort plan Currently SQL_BIG_RESULT is checked only at compile time. However, additional optimizations may take place after this check that change the sort method from 'filesort' to sorting via index. As a result the actual plan executed is not the one specified by the SQL_BIG_RESULT hint. Similarly, there is no such test when executing EXPLAIN, resulting in incorrect output. The patch corrects the problem by testing for SQL_BIG_RESULT both during the explain and execution phases. mysql-test/r/bdb.result@stripped, 2006-10-09 19:51:31+04:00, gkodinov@stripped +36 -36 Bug #22781: SQL_BIG_RESULT fails to influence sort plan - updated sql_big_result testcase mysql-test/r/group_by.result@stripped, 2006-10-09 19:51:32+04:00, gkodinov@stripped +65 -2 Bug #22781: SQL_BIG_RESULT fails to influence sort plan - test case with MyISAM mysql-test/r/innodb.result@stripped, 2006-10-09 19:51:32+04:00, gkodinov@stripped +8 -8 Bug #22781: SQL_BIG_RESULT fails to influence sort plan - updated sql_big_result testcase mysql-test/r/innodb_mysql.result@stripped, 2006-10-09 19:51:33+04:00, gkodinov@stripped +16 -0 Bug #22781: SQL_BIG_RESULT fails to influence sort plan - test case with InnoDB mysql-test/r/myisam.result@stripped, 2006-10-09 19:51:34+04:00, gkodinov@stripped +8 -8 Bug #22781: SQL_BIG_RESULT fails to influence sort plan - updated sql_big_result testcase mysql-test/t/group_by.test@stripped, 2006-10-09 19:51:34+04:00, gkodinov@stripped +22 -0 Bug #22781: SQL_BIG_RESULT fails to influence sort plan - test case with MyISAM mysql-test/t/innodb_mysql.test@stripped, 2006-10-09 19:51:35+04:00, gkodinov@stripped +18 -0 Bug #22781: SQL_BIG_RESULT fails to influence sort plan - test case with InnoDB sql/sql_select.cc@stripped, 2006-10-09 19:51:36+04:00, gkodinov@stripped +10 -3 Bug #22781: SQL_BIG_RESULT fails to influence sort plan - When SQL_BIG_RESULT is specified, disable the optimization performed at execution/explain time that decides to use an index instead of filesort. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: gkodinov # Host: macbook.local # Root: /Users/kgeorge/mysql/work/B22781-5.0-opt --- 1.458/sql/sql_select.cc 2006-10-09 19:51:58 +04:00 +++ 1.459/sql/sql_select.cc 2006-10-09 19:51:58 +04:00 @@ -1399,7 +1399,8 @@ JOIN::exec() simple_order= simple_group; skip_sort_order= 0; } - if (order && + if (order && + (order != group_list || !(select_options & SELECT_BIG_RESULT)) && (const_tables == tables || ((simple_order || skip_sort_order) && test_if_skip_sort_order(&join_tab[const_tables], order, @@ -11995,11 +11996,17 @@ create_sort_index(THD *thd, JOIN *join, table= tab->table; select= tab->select; - if (test_if_skip_sort_order(tab,order,select_limit,0)) + /* + When there is SQL_BIG_RESULT do not sort using index for GROUP BY, + and thus force sorting on disk. + */ + if ((order != join->group_list || + !(join->select_options & SELECT_BIG_RESULT)) && + test_if_skip_sort_order(tab,order,select_limit,0)) DBUG_RETURN(0); if (!(sortorder=make_unireg_sortorder(order,&length))) goto err; /* purecov: inspected */ - /* It's not fatal if the following alloc fails */ + table->sort.io_cache=(IO_CACHE*) my_malloc(sizeof(IO_CACHE), MYF(MY_WME | MY_ZEROFILL)); table->status=0; // May be wrong if quick_select --- 1.9/mysql-test/r/innodb_mysql.result 2006-10-09 19:51:58 +04:00 +++ 1.10/mysql-test/r/innodb_mysql.result 2006-10-09 19:51:58 +04:00 @@ -337,3 +337,19 @@ id select_type table type possible_keys 1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where DROP TABLE t1,t2; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB; +INSERT INTO t1 VALUES ( 1 , 1 , 1); +INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1; +INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1; +INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1; +INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1; +INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1; +INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1; +INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1; +EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL b 5 NULL 128 +EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort +DROP TABLE t1; --- 1.8/mysql-test/t/innodb_mysql.test 2006-10-09 19:51:58 +04:00 +++ 1.9/mysql-test/t/innodb_mysql.test 2006-10-09 19:51:58 +04:00 @@ -302,3 +302,21 @@ SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON WHERE t1.name LIKE 'A%' OR FALSE; DROP TABLE t1,t2; + +# +# Bug#22781: SQL_BIG_RESULT fails to influence sort plan +# +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB; + +INSERT INTO t1 VALUES ( 1 , 1 , 1); +INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1; +INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1; +INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1; +INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1; +INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1; +INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1; +INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1; + +EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b; +EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b; +DROP TABLE t1; --- 1.72/mysql-test/r/group_by.result 2006-10-09 19:51:58 +04:00 +++ 1.73/mysql-test/r/group_by.result 2006-10-09 19:51:58 +04:00 @@ -303,10 +303,10 @@ spid sum(userid) 1 1 explain select sql_big_result score,count(*) from t1 group by score desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL score 3 NULL 8 Using index +1 SIMPLE t1 index NULL score 3 NULL 8 Using index; Using filesort explain select sql_big_result score,count(*) from t1 group by score desc order by null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL score 3 NULL 8 Using index +1 SIMPLE t1 index NULL score 3 NULL 8 Using index; Using filesort select sql_big_result score,count(*) from t1 group by score desc; score count(*) 3 5 @@ -821,3 +821,66 @@ a b real_b 68 France France DROP VIEW v1; DROP TABLE t1,t2; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b)); +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1; +INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1; +INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1; +INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1; +INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1; +INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1; +INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1; +SELECT MIN(b), MAX(b) from t1; +MIN(b) MAX(b) +0 19 +EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL b 5 NULL 128 Using index +EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL b 5 NULL 128 Using index; Using filesort +SELECT b, sum(1) FROM t1 GROUP BY b; +b sum(1) +0 6 +1 7 +2 7 +3 7 +4 7 +5 7 +6 7 +7 7 +8 7 +9 6 +10 6 +11 6 +12 6 +13 6 +14 6 +15 6 +16 6 +17 6 +18 6 +19 6 +SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b; +b sum(1) +0 6 +1 7 +2 7 +3 7 +4 7 +5 7 +6 7 +7 7 +8 7 +9 6 +10 6 +11 6 +12 6 +13 6 +14 6 +15 6 +16 6 +17 6 +18 6 +19 6 +DROP TABLE t1; --- 1.162/mysql-test/r/innodb.result 2006-10-09 19:51:59 +04:00 +++ 1.163/mysql-test/r/innodb.result 2006-10-09 19:51:59 +04:00 @@ -2070,15 +2070,15 @@ i 10 select sql_big_result v,count(c) from t1 group by v limit 10; v count(c) a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 h 10 -i 10 +i 10 select c,count(*) from t1 group by c limit 10; c count(*) a 1 --- 1.59/mysql-test/t/group_by.test 2006-10-09 19:51:59 +04:00 +++ 1.60/mysql-test/t/group_by.test 2006-10-09 19:51:59 +04:00 @@ -655,3 +655,25 @@ where t2.b=v1.a GROUP BY t2.b; DROP VIEW v1; DROP TABLE t1,t2; + +# +# Bug#22781: SQL_BIG_RESULT fails to influence sort plan +# +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b)); + +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1; +INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1; +INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1; +INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1; +INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1; +INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1; +INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1; + +SELECT MIN(b), MAX(b) from t1; + +EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b; +EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b; +SELECT b, sum(1) FROM t1 GROUP BY b; +SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b; +DROP TABLE t1; --- 1.85/mysql-test/r/myisam.result 2006-10-09 19:51:59 +04:00 +++ 1.86/mysql-test/r/myisam.result 2006-10-09 19:51:59 +04:00 @@ -1002,15 +1002,15 @@ i 10 select sql_big_result v,count(c) from t1 group by v limit 10; v count(c) a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 h 10 -i 10 +i 10 select c,count(*) from t1 group by c limit 10; c count(*) a 1 --- 1.51/mysql-test/r/bdb.result 2006-10-09 19:51:59 +04:00 +++ 1.52/mysql-test/r/bdb.result 2006-10-09 19:51:59 +04:00 @@ -1509,27 +1509,27 @@ i 10 select sql_big_result v,count(t) from t1 group by v limit 10; v count(t) a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 select sql_big_result v,count(c) from t1 group by v limit 10; v count(c) a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 select c,count(*) from t1 group by c limit 10; c count(*) a 1 @@ -1673,15 +1673,15 @@ i 10 select sql_big_result v,count(t) from t1 group by v limit 10; v count(t) a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 alter table t1 drop key v, add key v (v(30)); show create table t1; Table Create Table @@ -1800,15 +1800,15 @@ i 10 select sql_big_result v,count(t) from t1 group by v limit 10; v count(t) a 1 -a 10 -b 10 -c 10 -d 10 -e 10 -f 10 -g 10 -h 10 -i 10 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 drop table t1; create table t1 (a char(10), unique (a)); insert into t1 values ('a ');