[Commits] e793e4e260b: MDEV-18373: DENSE_RANK is not calculated correctly

Varun varunraiko1803 at gmail.com
Sat May 4 01:02:52 EEST 2019


revision-id: e793e4e260b6faa4666d4ff439a6a7023daedceb (mariadb-10.2.23-110-ge793e4e260b)
parent(s): 386a759efe552519fb6a4f9acc6f630b2872316e
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-05-04 03:21:42 +0530
message:

MDEV-18373: DENSE_RANK is not calculated correctly

Need to call split_sum_func if an aggregate function is part of order by
or partition by clause so that we have the required fields inside the temporary
table, as all the fields inside the partition by and order by clause of the
window function needs to be there in the temp table used for window function
computation.

---
 mysql-test/r/win.result | 26 ++++++++++++++++++++++++++
 mysql-test/t/win.test   | 15 +++++++++++++++
 sql/sql_select.cc       |  8 ++++++++
 3 files changed, 49 insertions(+)

diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index 849da8668f1..5da8a7f8a3c 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -3581,5 +3581,31 @@ x	b	c
 8	64	248
 drop table t1;
 #
+# MDEV-18373: DENSE_RANK is not calculated correctly
+#
+create table t1 (a int, b int);
+insert into t1 values (60, 1515),(60, 2000),(70, 2000),(55, 1600);
+select b, dense_rank() over (order by sum(a)) from t1 group by  b;
+b	dense_rank() over (order by sum(a))
+1515	2
+1600	1
+2000	3
+select b, dense_rank() over (order by sum(a)+1) from t1 group by  b;
+b	dense_rank() over (order by sum(a)+1)
+1515	2
+1600	1
+2000	3
+select b, row_number() over (partition by sum(a)) from t1 group by b;
+b	row_number() over (partition by sum(a))
+1515	1
+1600	1
+2000	1
+select b, row_number() over (partition by sum(a)+1) from t1 group by b;
+b	row_number() over (partition by sum(a)+1)
+1515	1
+1600	1
+2000	1
+drop table t1;
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index 270af3833c9..40da44c0426 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -2310,6 +2310,21 @@ SELECT max(t1.a) over (partition by c) as x, b, c from t1 order by max(t1.a) ove
 
 drop table t1;
 
+--echo #
+--echo # MDEV-18373: DENSE_RANK is not calculated correctly
+--echo #
+
+create table t1 (a int, b int);
+insert into t1 values (60, 1515),(60, 2000),(70, 2000),(55, 1600);
+
+select b, dense_rank() over (order by sum(a)) from t1 group by  b;
+select b, dense_rank() over (order by sum(a)+1) from t1 group by  b;
+ 
+select b, row_number() over (partition by sum(a)) from t1 group by b;
+select b, row_number() over (partition by sum(a)+1) from t1 group by b;
+
+drop table t1;
+
 --echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 7665fedd0cf..521cd863cfc 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -22584,6 +22584,10 @@ int setup_order(THD *thd, Ref_ptr_array ref_pointer_array, TABLE_LIST *tables,
       my_error(ER_WINDOW_FUNCTION_IN_WINDOW_SPEC, MYF(0));
       return 1;
     }
+    if (from_window_spec && (*order->item)->with_sum_func &&
+        (*order->item)->type() != Item::SUM_FUNC_ITEM)
+      (*order->item)->split_sum_func(thd, ref_pointer_array,
+                                     all_fields, SPLIT_SUM_SELECT);
   }
   return 0;
 }
@@ -22651,6 +22655,10 @@ setup_group(THD *thd, Ref_ptr_array ref_pointer_array, TABLE_LIST *tables,
         my_error(ER_WINDOW_FUNCTION_IN_WINDOW_SPEC, MYF(0));
       return 1;
     }
+    if (from_window_spec && (*ord->item)->with_sum_func &&
+        (*ord->item)->type() != Item::SUM_FUNC_ITEM)
+      (*ord->item)->split_sum_func(thd, ref_pointer_array,
+                                   all_fields, SPLIT_SUM_SELECT);
   }
   if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY &&
       context_analysis_place == IN_GROUP_BY)


More information about the commits mailing list