Bug 17354

Summary: Group By/Order By SQL mismatch in issues_stats.pl
Product: Koha Reporter: Joel Coehoorn <jcoehoorn>
Component: ReportsAssignee: Galen Charlton <gmcharlt>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low    
Version: 16.05   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on:    
Bug Blocks: 17258    

Description Joel Coehoorn 2016-09-26 19:08:21 UTC
I'm looking in intranet/cgi-bin/reports/issues_stats.pl. I'm not sure of the exact line number, since I may have changed some white space earlier in the file on my system while tracking this down, but near line 350 in that file you'll see this:

$colorder = ($colfield =~ /dayname/) ? "weekday($column)" :
            ($colfield =~ /^month/ ) ? "  month($column)" : $colfield;

The $colorder value will be used in the ORDER BY clause for the final SQL statement for the report. Contrast that with this code from earlier in the file, which will be used for the GROUP BY and SELECT clauses:

$colfield = ($dsp == 1) ? "  dayname($column)" :
            ($dsp == 2) ? "monthname($column)" :

There's a clear mismatch here. The earlier snippet should be using "monthname()" instead of "month()", to match the GROUP BY syntax.

I found this while playing with Ubunutu 16.04/MySql 5.7. I have turned strict mode off in MySql 5.7, but even with strict mode off the Circulation Report listed under "Statistics Wizards" was broken when I tried to group by month. I suspect grouping by day will be broken as well. The report works as expected for grouping by month after changing to use the monthname() function.