From: Date: April 20 2007 8:42am Subject: bk commit into 5.0 tree (gkodinov:1.2457) BUG#27786 List-Archive: http://lists.mysql.com/commits/24970 X-Bug: 27786 Message-Id: <200704200642.l3K6gxlF025275@magare.gmz> 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, 2007-04-20 09:42:52+03:00, gkodinov@stripped +4 -0 Bug #27786: When merging views into the enclosing statement the ORDER BY clause of the view is merged to the parent's ORDER BY clause. However when the VIEW is merged into an UNION branch the ORDER BY should be ignored. Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces unordered set of rows. Fixed by ignoring the ORDER BY clause from the merge view when expanded in an UNION branch. mysql-test/r/view.result@stripped, 2007-04-20 09:42:51+03:00, gkodinov@stripped +6 -0 Bug #27786: test case mysql-test/t/view.test@stripped, 2007-04-20 09:42:51+03:00, gkodinov@stripped +11 -0 Bug #27786: test case sql/sql_lex.cc@stripped, 2007-04-20 09:42:51+03:00, gkodinov@stripped +2 -1 Bug #27786: fixed misleading comment sql/sql_view.cc@stripped, 2007-04-20 09:42:51+03:00, gkodinov@stripped +7 -2 Bug #27786: ignore ORDER BY in mergeable views when in UNION context # 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: magare.gmz # Root: /home/kgeorge/mysql/work/B27786-5.0-opt --- 1.218/sql/sql_lex.cc 2007-04-12 21:17:23 +03:00 +++ 1.219/sql/sql_lex.cc 2007-04-20 09:42:51 +03:00 @@ -1766,9 +1766,10 @@ st_lex::st_lex() DESCRIPTION We can apply merge algorithm if it is single SELECT view with subqueries only in WHERE clause (we do not count SELECTs of underlying - views, and second level subqueries) and we have not grpouping, ordering, + views, and second level subqueries) and we have not grpouping, HAVING clause, aggregate functions, DISTINCT clause, LIMIT clause and several underlying tables. + Ordering is allowed. RETURN FALSE - only temporary table algorithm can be used --- 1.198/mysql-test/r/view.result 2007-04-12 21:20:46 +03:00 +++ 1.199/mysql-test/r/view.result 2007-04-20 09:42:51 +03:00 @@ -3319,4 +3319,10 @@ lgid clid 2 YES DROP VIEW v1; DROP table t1,t2; +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a; +SELECT * FROM t1 UNION SELECT * FROM v1; +a +DROP VIEW v1; +DROP TABLE t1; End of 5.0 tests. --- 1.181/mysql-test/t/view.test 2007-04-12 21:19:34 +03:00 +++ 1.182/mysql-test/t/view.test 2007-04-20 09:42:51 +03:00 @@ -3205,4 +3205,15 @@ SELECT * FROM v1; DROP VIEW v1; DROP table t1,t2; +# +# Bug#27786: Inconsistent Operation Performing UNION On View With ORDER BY +# +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a; + +SELECT * FROM t1 UNION SELECT * FROM v1; + +DROP VIEW v1; +DROP TABLE t1; + --echo End of 5.0 tests. --- 1.107/sql/sql_view.cc 2007-03-22 22:12:41 +02:00 +++ 1.108/sql/sql_view.cc 2007-04-20 09:42:51 +03:00 @@ -1263,13 +1263,18 @@ bool mysql_make_view(THD *thd, File_pars unit->slave= save_slave; // fix include_down initialisation } + /* + We can safely ignore the VIEW's ORDER BY if we merge into union + branch, as order is not important there. + */ + if (table->select_lex->linkage != UNION_TYPE) + table->select_lex->order_list.push_back(&lex->select_lex.order_list); /* This SELECT_LEX will be linked in global SELECT_LEX list to make it processed by mysql_handle_derived(), but it will not be included to SELECT_LEX tree, because it will not be executed - */ - table->select_lex->order_list.push_back(&lex->select_lex.order_list); + */ goto ok; }