From: Date: July 12 2006 9:57am Subject: bk commit into 4.1 tree (gkodinov:1.2525) BUG#17212 List-Archive: http://lists.mysql.com/commits/9079 X-Bug: 17212 Message-Id: <20060712075751.CEB7A14AC51@macbook.gmz> Below is the list of changes that have just been committed into a local 4.1 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-07-12 10:57:38+03:00, gkodinov@stripped +3 -0 Bug #17212 results not sorted correctly by ORDER BY when using index * don't use join cache when the incoming data set is already ordered for ORDER BY This choice must be made because join cache will effectively reverse the join order and the results will be sorted by the index of the table that uses join cache. mysql-test/r/innodb_mysql.result@stripped, 2006-07-12 10:57:30+03:00, gkodinov@stripped +29 -0 Bug #17212 results not sorted correctly by ORDER BY when using index * Test suite for the bug mysql-test/t/innodb_mysql.test@stripped, 2006-07-12 10:57:31+03:00, gkodinov@stripped +33 -0 Bug #17212 results not sorted correctly by ORDER BY when using index * Test suite for the bug sql/sql_select.cc@stripped, 2006-07-12 10:57:32+03:00, gkodinov@stripped +19 -1 Bug #17212 results not sorted correctly by ORDER BY when using index * don't use join cache when the incoming data set is already sorted # 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.gmz # Root: /Users/kgeorge/mysql/work/B17212-4.1-opt --- 1.459/sql/sql_select.cc 2006-07-12 10:57:51 +03:00 +++ 1.460/sql/sql_select.cc 2006-07-12 10:57:51 +03:00 @@ -3845,6 +3845,7 @@ { uint i; bool statistics= test(!(join->select_options & SELECT_DESCRIBE)); + bool ordered_set= 0; DBUG_ENTER("make_join_readinfo"); for (i=join->const_tables ; i < join->tables ; i++) @@ -3854,6 +3855,22 @@ tab->read_record.table= table; tab->read_record.file=table->file; tab->next_select=sub_select; /* normal select */ + + /* + Determine if the set is already ordered for ORDER BY, so it can + disable join cache because it will change the ordering of the results. + Code handles sort table that is at any location (not only first after + the const tables) despite the fact that it's currently prohibited. + */ + if (!ordered_set && + (table == join->sort_by_table && + (!join->order || join->skip_sort_order || + test_if_skip_sort_order(tab, join->order, join->select_limit, + 1)) + ) || + (join->sort_by_table == (TABLE *) 1 && i != join->const_tables)) + ordered_set= 1; + switch (tab->type) { case JT_SYSTEM: // Only happens with left join table->status=STATUS_NO_RECORD; @@ -3924,10 +3941,11 @@ case JT_ALL: /* If previous table use cache + If the incoming data set is already sorted don't use cache. */ table->status=STATUS_NO_RECORD; if (i != join->const_tables && !(options & SELECT_NO_JOIN_CACHE) && - tab->use_quick != 2 && !tab->on_expr) + tab->use_quick != 2 && !tab->on_expr && !ordered_set) { if ((options & SELECT_DESCRIBE) || !join_init_cache(join->thd,join->join_tab+join->const_tables, --- 1.3/mysql-test/r/innodb_mysql.result 2006-07-12 10:57:51 +03:00 +++ 1.4/mysql-test/r/innodb_mysql.result 2006-07-12 10:57:51 +03:00 @@ -54,3 +54,32 @@ slai_id 12 drop table t1, t2; +CREATE TABLE t1 (a int, b int, KEY b (b)) Engine=InnoDB; +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b)) Engine=InnoDB; +CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a), +UNIQUE KEY b (b,c), KEY a (a,b,c)) Engine=InnoDB; +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t1 SELECT a + 1, b + 1 FROM t1; +INSERT INTO t1 SELECT a + 2, b + 2 FROM t1; +INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); +INSERT INTO t2 SELECT a + 1, b FROM t2; +DELETE FROM t2 WHERE a = 1 AND b < 2; +INSERT INTO t3 VALUES (1,1,1),(2,1,2); +INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3; +INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3; +SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE +t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) +ORDER BY t1.b LIMIT 2; +b a +1 1 +2 2 +SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE +t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) +ORDER BY t1.b LIMIT 5; +b a +1 1 +2 2 +2 2 +3 3 +3 3 +DROP TABLE t1, t2, t3; --- 1.3/mysql-test/t/innodb_mysql.test 2006-07-12 10:57:51 +03:00 +++ 1.4/mysql-test/t/innodb_mysql.test 2006-07-12 10:57:51 +03:00 @@ -57,3 +57,36 @@ c.c_id = 218 and expiredate is null; drop table t1, t2; + +# +# Bug#17212: results not sorted correctly by ORDER BY when using index +# (repeatable only w/innodb because of index props) +# +CREATE TABLE t1 (a int, b int, KEY b (b)) Engine=InnoDB; +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b)) Engine=InnoDB; +CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a), + UNIQUE KEY b (b,c), KEY a (a,b,c)) Engine=InnoDB; + +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t1 SELECT a + 1, b + 1 FROM t1; +INSERT INTO t1 SELECT a + 2, b + 2 FROM t1; + +INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); +INSERT INTO t2 SELECT a + 1, b FROM t2; +DELETE FROM t2 WHERE a = 1 AND b < 2; + +INSERT INTO t3 VALUES (1,1,1),(2,1,2); +INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3; +INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3; + +# demonstrate a problem when a must-use-sort table flag +# (sort_by_table=1) is being neglected. +SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE + t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) + ORDER BY t1.b LIMIT 2; + +# demonstrate the problem described in the bug report +SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE + t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) + ORDER BY t1.b LIMIT 5; +DROP TABLE t1, t2, t3;