Order by greatest (1, If(a,0,b)) desc

[tenants]

I'm using the following order stament for one of my plugins:

Order by GREATEST (1, IF(sft.king_pinned_bid IS NULL, 0, sft.king_pinned_bid)) DESC

A plugin user found that their database returned the following error:
"Mysqli prepare error: FUNCTION scenemar_xenforo.GREATEST does not exist"

This to me implies their database doesn't support the GREATEST function, or using it in this way

Is GREATEST, or using GREATEST in this way not supported by all versions of MySQL?

 

[Paul B]

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_greatest

That implies it works in all versions of 5.x, and as the minimum required version for XenForo is 5.0, then it should be fine.

There is a change in operation from 5.0.13 though:
Before MySQL 5.0.13, GREATEST() returns NULL only if all arguments are NULL. As of 5.0.13, it returns NULL if any argument is NULL.

 

[tenants]

Yup, I was just looking at that at the same time "Before MySQL 5.0.13, GREATEST() returns NULL only if all arguments are NULL"
The thing is, the way I'm using it here, it will always be

Order by GREATEST (1, 0) // ( if king_pinned_bid IS NULL)
or
Order by GREATEST (1, sft.king_pinned_bid) // ( if king_pinned_bid IS not NULL)

So this shouldnt be an issue anyway

 

[Pepelac]

Just remove space between GREATEST and (

 

[tenants]

Woah, that could be it!
I'll get them to give it a go, if it works then I will update the plugin (it is a bit strange having a space there, but I didnt see any issues on 2 databases that I tried)