[Bug 23982] New: Count/pagination broken for reports with duplicated column names
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Bug ID: 23982 Summary: Count/pagination broken for reports with duplicated column names Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Reports Assignee: koha-bugs@lists.koha-community.org Reporter: nick@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org bug 23624 simplified how we get the count of rows in a report, however, it breaks under certain circumstances. A report like: SELECT * FROM issues JOIN borrowers USING (borrowernumber) will have two borrowernumber columns - SQL will give us there rsults, but if we try to wrap them in a SELECT COUNT(*) FROM (report) it throws a duplicated column error. I don't know an easy fix except to state that one must name the columns they want to avoid duplication -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@bugs.koha-c | |ommunity.org, | |kelly@bywatersolutions.com, | |martin.renvoize@ptfs-europe | |.com, tomascohen@gmail.com -- You are receiving this mail because: You are watching all bug changes.
From https://bugs.mysql.com/bug.php?id=6709: """ This is in agreement with the SQL Standard, which says: [...]
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 --- Comment #1 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- It seems that we should revert commit bca4453c50d8b5b1d3c7029ded2b522a9a2bf868 Bug 23624: (QA follow-up) Optimize even more 11) No <column name> shall be specified more than once in a <derived column> list. """ -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |rel_19_11_candidate Severity|normal |major -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |23624 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23624 [Bug 23624] Count rows in report without (potentially) consuming all memory -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 --- Comment #2 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- This is horrible.. I really don't like the idea of reverting that followup as it fixes an important performance hole. Feels like MySQL is getting it wrong to me.. grr Contemplating if we have any other options. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 --- Comment #3 from Nick Clemens <nick@bywatersolutions.com> --- I think if we can make a sensible error back to the user about deduplicating the names we would be okay. Ideally we would also make basic pagination work, just assumign 10 pages, and again having a friendly error if they page out of bounds -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 --- Comment #4 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 95479 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=95479&action=edit Bug 23982: (bug 23624 follow-up) Handle SQL error caused by derived table Caused by commit bca4453c50d8b5b1d3c7029ded2b522a9a2bf868 Bug 23624: (QA follow-up) Optimize even more A report like: SELECT * FROM issues JOIN borrowers USING (borrowernumber) will have two borrowernumber columns - SQL will give us there rsults, but if we try to wrap them in a SELECT COUNT(*) FROM (report) it throws a duplicated column error. This patch suggests to execute the query the old way if the derived table optimization failed. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |jonathan.druart@bugs.koha-c |ity.org |ommunity.org -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 --- Comment #5 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- This impacts stable releases and will cause reports to fail. I think it's a critical one. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #95479|0 |1 is obsolete| | --- Comment #6 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 95545 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=95545&action=edit Bug 23982: (bug 23624 follow-up) Handle SQL error caused by derived table Caused by commit bca4453c50d8b5b1d3c7029ded2b522a9a2bf868 Bug 23624: (QA follow-up) Optimize even more A report like: SELECT * FROM issues JOIN borrowers USING (borrowernumber) will have two borrowernumber columns - SQL will give us there rsults, but if we try to wrap them in a SELECT COUNT(*) FROM (report) it throws a duplicated column error. This patch suggests to execute the query the old way if the derived table optimization failed. Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off --- Comment #7 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Thanks for jumping on this one Jonathan, looks reasonable and in a quick test it works for me.. Signing off. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #95545|0 |1 is obsolete| | --- Comment #8 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 95548 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=95548&action=edit Bug 23982: (bug 23624 follow-up) Handle SQL error caused by derived table Caused by commit bca4453c50d8b5b1d3c7029ded2b522a9a2bf868 Bug 23624: (QA follow-up) Optimize even more A report like: SELECT * FROM issues JOIN borrowers USING (borrowernumber) will have two borrowernumber columns - SQL will give us there rsults, but if we try to wrap them in a SELECT COUNT(*) FROM (report) it throws a duplicated column error. This patch suggests to execute the query the old way if the derived table optimization failed. Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Nick Clemens <nick@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |19.11.00 released in| | Status|Passed QA |Pushed to master -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 --- Comment #9 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Nice work! Pushed to master for 19.11.00 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|rel_19_11_candidate | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 --- Comment #10 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 95593 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=95593&action=edit Bug 23982: Fix the failing test # Failed test 'nb_rows returns 0 on bad queries' # at t/db_dependent/Reports/Guided.t line 441. # got: undef # expected: '0' Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |additional_work_needed -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|additional_work_needed | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 --- Comment #11 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Test Fix pushed to master -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 madamyk@ckls.org changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |madamyk@ckls.org -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23982 Michael Adamyk <madamyk@ckls.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC|madamyk@ckls.org | -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org