Window functions: make "ORDER BY window_func" work

- When window functions are present, JOIN::simple_order should be set
  to FALSE. (Otherwise, the optimizer may attempt to do a "pre-sorting"
  on the first join_tab. Which can work in some cases, but generally
  isn't)

- filesort tries to only read table fields that it requires. Window
  function requires its temp.table field.  In order to pass this info
  to filesort, added an implementation of Item_window_func::
  register_field_in_read_map.

4 files changed, 82 insertions, 0 deletions

diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index 3fb99a1d2bb..54c0efb4fe8 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -1767,3 +1767,48 @@ rank() over (order by a)
 10
 set big_tables=@tmp;
 drop table t1;
+#
+# Check if "ORDER BY window_func" works
+#
+create table t1 (s1 int, s2 char(5));
+insert into t1 values (1,'a');
+insert into t1 values (null,null);
+insert into t1 values (1,null);
+insert into t1 values (null,'a');
+insert into t1 values (2,'b');
+insert into t1 values (-1,'');
+explain format=json
+select *, row_number() over (order by s1) as X from t1 order by X desc;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "filesort": {
+      "sort_key": "X",
+      "window_functions_computation": {
+        "sorts": {
+          "filesort": {
+            "sort_key": "t1.s1"
+          }
+        },
+        "temporary_table": {
+          "table": {
+            "table_name": "t1",
+            "access_type": "ALL",
+            "rows": 6,
+            "filtered": 100
+          }
+        }
+      }
+    }
+  }
+}
+select *, row_number() over (order by s1) as X from t1 order by X desc;
+s1	s2	X
+2	b	6
+1	a	5
+1	NULL	4
+-1		3
+NULL	NULL	2
+NULL	a	1
+drop table t1;
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index 37a447f0a5c..3011db87481 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -1079,3 +1079,20 @@ select rank() over (order by a) from t1;
 set big_tables=@tmp;
 drop table t1;
 
+--echo #
+--echo # Check if "ORDER BY window_func" works
+--echo #
+
+create table t1 (s1 int, s2 char(5));
+insert into t1 values (1,'a');
+insert into t1 values (null,null);
+insert into t1 values (1,null);
+insert into t1 values (null,'a');
+insert into t1 values (2,'b');
+insert into t1 values (-1,'');
+
+explain format=json
+select *, row_number() over (order by s1) as X from t1 order by X desc;
+select *, row_number() over (order by s1) as X from t1 order by X desc;
+drop table t1;
+
diff --git a/sql/item_windowfunc.h b/sql/item_windowfunc.h
index f239f7f2ccf..ad72bd7040c 100644
--- a/sql/item_windowfunc.h
+++ b/sql/item_windowfunc.h
@@ -511,6 +511,25 @@ public:
 
   void update_used_tables();
 
+  /*
+    This is used by filesort to mark the columns it needs to read (because they
+    participate in the sort criteria and/or row retrieval. Window functions can
+    only be used in sort criteria).
+
+    Sorting by window function value is only done after the window functions
+    have been computed. In that case, window function will need to read its
+    temp.table field. In order to allow that, mark that field in the read_set.
+  */
+  bool register_field_in_read_map(uchar *arg)
+  {
+    TABLE *table= (TABLE*) arg;
+    if (result_field && (result_field->table == table || !table))
+    {
+      bitmap_set_bit(result_field->table->read_set, result_field->field_index);
+    }
+    return 0;
+  }
+
   bool is_frame_prohibited() const
   {
     switch (window_func()->sum_func()) {
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 024c8cf0428..6a5715761e5 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1846,6 +1846,7 @@ JOIN::optimize_inner()
   //TODO this could probably go in test_if_need_tmp_table.
   if (this->select_lex->window_specs.elements > 0) {
     need_tmp= TRUE;
+    simple_order= FALSE;
   }
 
   /*