Defect #32046

SQL to get counts when using created_on in MySQL to group issues does not work correctly

Added by Mizuki ISHIKAWA 8 days ago. Updated 6 days ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Go MAEDA% Done:

0%

Category:Issues filter
Target version:-
Resolution:Fixed Affected version:

Description

Reproduction procedure
  • Use MySQL
  • Time zone data is loaded(If it is not loaded, time zone conversion will fail.)
  • Change user language from UTC to something else
  • Group by created_on at issues/index

query.result_count_by_group executed in QueriesHelper#grouped_query_results returns the following result.

Actual result:

{2006-07-18 14:00:00 +0000=>2,
 2019-08-13 14:00:00 +0000=>1,
 2019-08-18 14:00:00 +0000=>1,
 2019-08-23 14:00:00 +0000=>2,
 2019-08-25 14:00:00 +0000=>1,
 2019-08-28 14:00:00 +0000=>3}

Expected result:

 {Wed, 19 Jul 2006=>2,
 Tue, 13 Aug 2019=>1,
 Sun, 18 Aug 2019=>1,
 Fri, 23 Aug 2019=>2,
 Sun, 25 Aug 2019=>1,
 Wed, 28 Aug 2019=>3}

The number of records cannot be retrieved properly because the keys format is different than expected.

This issue is related to #13803.


Related issues

Related to Redmine - Feature #13803: Implement grouping issues by date (start, due, creation, ... Closed

Associated revisions

Revision 18460
Added by Go MAEDA 6 days ago

Fix that date grouping for MySQL is not working (#32046, #13803).

Patch by Mizuki ISHIKAWA.

History

#1 Updated by Mizuki ISHIKAWA 8 days ago

You have to make changes like the diff below.

 diff --git a/lib/redmine/database.rb b/lib/redmine/database.rb
index 979363329..0f0876157 100644
--- a/lib/redmine/database.rb
+++ b/lib/redmine/database.rb
@@ -80,7 +80,7 @@ module Redmine
           if time_zone
             user_identifier = ActiveSupport::TimeZone.find_tzinfo(time_zone.name).identifier
             local_identifier = ActiveSupport::TimeZone.find_tzinfo(Time.zone.name).identifier
-            "CONVERT_TZ(DATE(#{column}),'#{local_identifier}', '#{user_identifier}')" 
+            "DATE(CONVERT_TZ(#{column},'#{local_identifier}', '#{user_identifier}'))" 
           else
             "DATE(#{column})" 
           end

#2 Updated by Go MAEDA 8 days ago

  • Related to Feature #13803: Implement grouping issues by date (start, due, creation, update, closing dates) added

#3 Updated by Go MAEDA 8 days ago

  • Assignee set to Marius BALTEANU
  • Target version set to 4.1.0

Marius, could you review this patch? It updates MySQL support for the date grouping filter option committed in r17745.

#4 Updated by Marius BALTEANU 6 days ago

  • Assignee changed from Marius BALTEANU to Go MAEDA

Go MAEDA wrote:

Marius, could you review this patch? It updates MySQL support for the date grouping filter option committed in r17745.

The fix works well, thanks for catching and fixing this issue.

#5 Updated by Go MAEDA 6 days ago

  • Status changed from New to Closed
  • Target version deleted (4.1.0)
  • Resolution set to Fixed

Committed the fix as a part of #13803. Thank you all for working on fixing the issue.

Also available in: Atom PDF