[Koha-bugs] [Bug 14704] New: members/statistics.pl crashes because the SQL 'return'-keyword might be reserved at 'return FROM ('
bugzilla-daemon at bugs.koha-community.org
bugzilla-daemon at bugs.koha-community.org
Fri Aug 21 12:42:10 CEST 2015
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 at lists.koha-community.org
Reporter: olli-antti.kivilahti at jns.fi
QA Contact: testopia at bugs.koha-community.org
CC: gmcharlt at gmail.com, kyle.m.hall at 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.
More information about the Koha-bugs
mailing list