From d6734e62397fff3b806b67d521b1080f6fab03b1 Mon Sep 17 00:00:00 2001
From: Jean-Philippe Lang <jp_lang@yahoo.fr>
Date: Sun, 5 Apr 2020 09:43:03 +0000
Subject: [PATCH] Fix sort does not work with group by datetime columns
 (#33110).

Patch by Marius BALTEANU.

git-svn-id: http://svn.redmine.org/redmine/trunk@19655 e93f8b46-1217-0410-a6f0-8f06a7374b81
---
 app/models/query.rb     |  7 ++++++-
 test/unit/query_test.rb | 28 ++++++++++++++++++++++++++++
 2 files changed, 34 insertions(+), 1 deletion(-)

diff --git a/app/models/query.rb b/app/models/query.rb
index cefe1a0ef..100728cff 100644
--- a/app/models/query.rb
+++ b/app/models/query.rb
@@ -850,7 +850,12 @@ class Query < ActiveRecord::Base
   def group_by_sort_order
     if column = group_by_column
       order = (sort_criteria.order_for(column.name) || column.default_order || 'asc').try(:upcase)
-      Array(column.sortable).map {|s| Arel.sql("#{s} #{order}")}
+
+      column_sortable = column.sortable
+      if column.is_a?(TimestampQueryColumn)
+        column_sortable = Redmine::Database.timestamp_to_date(column.sortable, User.current.time_zone)
+      end
+      Array(column_sortable).map {|s| Arel.sql("#{s} #{order}")}
     end
   end
 
diff --git a/test/unit/query_test.rb b/test/unit/query_test.rb
index 0759a447b..02cb25afd 100644
--- a/test/unit/query_test.rb
+++ b/test/unit/query_test.rb
@@ -1705,6 +1705,34 @@ class QueryTest < ActiveSupport::TestCase
     assert_equal values.sort, values
   end
 
+  def test_sort_with_group_by_timestamp_query_column_should_sort_after_date_value
+    User.current = User.find(1)
+
+    # Touch Issue#10 in order to be the last updated issue
+    Issue.find(10).update_attribute(:updated_on, Issue.find(10).updated_on + 1)
+
+    q = IssueQuery.new(
+      :name => '_',
+      :filters => { 'updated_on' => {:operator => 't', :values => ['']} },
+      :group_by => 'updated_on',
+      :sort_criteria => [['subject', 'asc']]
+    )
+
+    # The following 3 issues are updated today (ordered by updated_on):
+    #   Issue#10: Issue Doing the Blocking
+    #   Issue#9: Blocked Issue
+    #   Issue#6: Issue of a private subproject
+
+    # When we group by a timestamp query column, all the issues in the group have the same date value (today)
+    # and the time of the value should not be taken into consideration when sorting
+    #
+    # For the same issues after subject ascending should return the following:
+    # Issue#9: Blocked Issue
+    # Issue#10: Issue Doing the Blocking
+    # Issue#6: Issue of a private subproject
+    assert_equal [9, 10, 6], q.issues.map(&:id)
+  end
+
   def test_sort_by_total_for_estimated_hours
     # Prepare issues
     parent = issues(:issues_001)
-- 
2.39.5