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:
  Bug #27786: test case
mysql-test/t/view.test:
  Bug #27786: test case
sql/sql_lex.h:
  Bug #27786: add a is_union() inlined function
  Returns true if the unit represents an UNION.
sql/sql_view.cc:
  Bug #27786: ignore ORDER BY in mergeable views when in UNION context

2 files changed, 15 insertions, 2 deletions

diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index de7de0d46e9..2ff29d684d1 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -468,6 +468,7 @@ public:
   bool change_result(select_subselect *result, select_subselect *old_result);
   void set_limit(st_select_lex *values);
   void set_thd(THD *thd_arg) { thd= thd_arg; }
+  inline bool is_union (); 
 
   friend void lex_start(THD *thd, uchar *buf, uint length);
   friend int subselect_union_engine::exec();
@@ -700,6 +701,13 @@ public:
 };
 typedef class st_select_lex SELECT_LEX;
 
+
+inline bool st_select_lex_unit::is_union ()
+{ 
+  return first_select()->next_select() && 
+    first_select()->next_select()->linkage == UNION_TYPE;
+}
+
 #define ALTER_ADD_COLUMN	1
 #define ALTER_DROP_COLUMN	2
 #define ALTER_CHANGE_COLUMN	4
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 83beec3d1be..95dec40450f 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -1263,13 +1263,18 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table,
         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->master_unit()->is_union())
+        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;
     }