Actions
Defect #32046
closed
SQL to get counts when using created_on in MySQL to group issues does not work correctly
Start date:
Due date:
% Done:
0%
Estimated time:
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
Updated by Mizuki ISHIKAWA about 5 years 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
Updated by Go MAEDA about 5 years ago
- Related to Feature #13803: Implement grouping issues by date (start, due, creation, update, closing dates) added
Updated by Go MAEDA about 5 years ago
- Assignee set to Marius BĂLTEANU
- 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.
Updated by Marius BĂLTEANU about 5 years ago
- Assignee changed from Marius BĂLTEANU 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.
Updated by Go MAEDA about 5 years 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.
Actions