Koha uses IFNULL() in 5 places in it's SQL; this is not standard SQL, and isn't compatible with all databases. The COALESCE() function is supported by: DB2: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.sqlref/fcoal.htm Microsoft SQL Server: http://msdn.microsoft.com/en-us/library/aa258244.aspx MySQL: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull Oracle: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions023.htm PostgreSQL: http://www.postgresql.org/docs/8.1/static/functions-conditional.html#AEN12656 and SQLite: http://www.sqlite.org/lang_corefunc.html And probably others (from http://www.nomadjourney.com/2009/04/database-independent-django-queries-coalesce/)
Created attachment 11791 [details] [review] Bug 8164: Replace IFNULL with COALESCE mysql> SELECT IFNULL(0, 123); +----------------+ | IFNULL(0, 123) | +----------------+ | 0 | +----------------+ 1 row in set (0.00 sec) mysql> SELECT IFNULL(1, 123); +----------------+ | IFNULL(1, 123) | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) mysql> SELECT IFNULL(NULL, 123); +-------------------+ | IFNULL(NULL, 123) | +-------------------+ | 123 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT COALESCE(0, 123); +------------------+ | COALESCE(0, 123) | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT COALESCE(1, 123); +------------------+ | COALESCE(1, 123) | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT COALESCE(NULL, 123); +---------------------+ | COALESCE(NULL, 123) | +---------------------+ | 123 | +---------------------+ 1 row in set (0.00 sec)
Created attachment 11811 [details] [review] Bug 8164: Replace IFNULL with COALESCE mysql> SELECT IFNULL(0, 123); +----------------+ | IFNULL(0, 123) | +----------------+ | 0 | +----------------+ 1 row in set (0.00 sec) mysql> SELECT IFNULL(1, 123); +----------------+ | IFNULL(1, 123) | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) mysql> SELECT IFNULL(NULL, 123); +-------------------+ | IFNULL(NULL, 123) | +-------------------+ | 123 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT COALESCE(0, 123); +------------------+ | COALESCE(0, 123) | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT COALESCE(1, 123); +------------------+ | COALESCE(1, 123) | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT COALESCE(NULL, 123); +---------------------+ | COALESCE(NULL, 123) | +---------------------+ | 123 | +---------------------+ 1 row in set (0.00 sec) Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
Changing target version so this bug report will show up in the list of 3.12-targeted features and doesn't get lost in the shuffle during feature freeze.
Tested Acquisition Late Orders and Lists.
Created attachment 13676 [details] [review] Signed patch
QA Comment: Works. Small patch improving code. Looks good to me. Passed QA
This patch has been pushed to master.