After adding one FAQ node and I browse to the FAQ page I receive the following error:

PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: function if(boolean, unknown, smallint) does not exist LINE 1: ...ight, n.sticky AS nsticky, n.created AS ncreated, if((w.weig... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.: SELECT n.nid AS nid, weight AS weight, n.sticky AS nsticky, n.created AS ncreated, if((w.weight IS NULL), :default_weight, w.weight) AS weight FROM {node} n LEFT OUTER JOIN {faq_weights} w ON w.nid=n.nid WHERE (n.type = :db_condition_placeholder_0) AND (n.status = :db_condition_placeholder_1) AND( (w.tid = :db_condition_placeholder_2) OR (w.tid IS NULL ) ) ORDER BY weight DESC, n.sticky DESC, n.created DESC; Array ( [:db_condition_placeholder_0] => faq [:db_condition_placeholder_1] => 1 [:db_condition_placeholder_2] => 0 [:default_weight] => 0 ) in faq_page() (line 494 of /srv/http/test/sites/all/modules/faq/faq.module).

This line (640) in faq.module seems to be the culprit, commenting it out removes the Postgres error, but I assume functionality is broken:

$query->addExpression('if((w.weight IS NULL), :default_weight, w.weight)', 'weight', array(':default_weight' => $default_weight));

CommentFileSizeAuthor
#7 faq.module-replace-if-with-coalesce-r2-6.x-1.x.patch10.55 KBCapBBeard
#4 faq.module-replace-if-with-coalesce.patch3.18 KBCapBBeard
#5 faq.replace-if-with-coalesce-r2.patch5.1 KBCapBBeard
#1 faq.module-replace-if-with-coalesce.patch3.18 KBPeter Bex

Comments

Peter Bex’s picture

Peter Bex commented
StatusFileSize
new faq.module-replace-if-with-coalesce.patch3.18 KB

A better solution is to drop the reliance on the nonstandard MySQL-specific IF function and use COALESCE instead, which also works in MySQL. Here's a patch that does that. It also renames the column because it makes the "order by" ambiguous; it may refer either to the newly named output column or w.weight. I have no idea what MySQL makes of that but Postgres complains noisily.

yrro’s picture

yrro commented
Status: Active » Needs review
CapBBeard’s picture

CapBBeard commented

I've also just encountered this bug using a SQL Server backend, and came to the exact same conclusion as Peter Bex in regards to dropping IF in favour of COALESCE.

CapBBeard’s picture

CapBBeard commented
StatusFileSize
new faq.module-replace-if-with-coalesce.patch3.18 KB

It appears this issue is also present in faq.admin.inc but is not immediately obvious until you click on the 'Order' link on your FAQ page.

I've attached an updated patch which adds to the work done by Peter Bex to also make these changes to faq.admin.inc as well as faq.module.

EDIT: Sorry, this patch is the same one as above, please see next post for correct one.

CapBBeard’s picture

CapBBeard commented
StatusFileSize
new faq.replace-if-with-coalesce-r2.patch5.1 KB

Oops, apologies all, I've selected the wrong patch file in my post above (it seems I cant change attachments after the fact?). Here is the correct one.

hshana’s picture

hshana commented

I'm guessing this patch doesn't work with the 6.x branch?...

CapBBeard’s picture

CapBBeard commented
StatusFileSize
new faq.module-replace-if-with-coalesce-r2-6.x-1.x.patch10.55 KB

No it wont I'm afraid.

I've attached another patch specifically for the 6.x branch, but I'm not in a position to test it properly at the moment. It is the same changes as above though, just applied to 6.x.

stella’s picture

stella commented
Status: Needs review » Fixed

Fixed in both D6 and D7 branches, thanks!

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.