http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14704 Bug ID: 14704 Summary: members/statistics.pl crashes because the SQL 'return'-keyword might be reserved at 'return FROM (' Change sponsored?: --- Product: Koha Version: unspecified Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Patrons Assignee: koha-bugs@lists.koha-community.org Reporter: olli-antti.kivilahti@jns.fi QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com, kyle.m.hall@gmail.com At Ubuntu 14.04 using MariaDB, the following SQL doesn't work: SELECT COUNT(*) as count_precedent_state,issues,onloan,return FROM ( SELECT it.* FROM issues i, items it WHERE i.borrowernumber = 1234 AND i.itemnumber = it.itemnumber AND DATE(i.issuedate) < CAST(now() AS date) UNION SELECT it.* FROM old_issues oi, items it WHERE oi.borrowernumber = 1234 AND oi.itemnumber = it.itemnumber AND DATE(oi.issuedate) < CAST(now() AS date) AND DATE(oi.returndate) = CAST(now() AS date) ) tmp GROUP BY issues,onloan,return; We get "Syntax error near 'return FROM ('" This patch changes the SQL to prefix the selected columns with a table name prefix, so we get: SELECT COUNT(*) as count_precedent_state,tmp.issues,tmp.onloan,tmp.return FROM ( SELECT it.* FROM issues i, items it WHERE i.borrowernumber = 1234 AND i.itemnumber = it.itemnumber AND DATE(i.issuedate) < CAST(now() AS date) UNION SELECT it.* FROM old_issues oi, items it WHERE oi.borrowernumber = 1234 AND oi.itemnumber = it.itemnumber AND DATE(oi.issuedate) < CAST(now() AS date) AND DATE(oi.returndate) = CAST(now() AS date) ) tmp GROUP BY tmp.issues,tmp.onloan,tmp.return; Which is more formal and less implicit. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.