GREATEST function does not behave equally in all database engines

This worked for me to remove the warnings in referenced POST:

ALTER FUNCTION [dbo].[GREATEST](@op1 sql_variant, @op2 sql_variant) RETURNS sql_variant AS
BEGIN
DECLARE @result sql_variant
SET @result = CASE WHEN @op1 >= @op2 THEN @op1 ELSE @op2 END
RETURN @result
END

I changed INSTALL.inc to create the funcition in the new way.

The patch is not GIT ready, I'm still struggling to learn GIT and am on a project deadline right now.

I think there is no way to accomplish that except for using CLR.

In SQL Server a UDF cannot have optional parameters (it does have optional parameter values, wich is not the same thing).

Anyways, if you come up with something, let us know!

Thx 4 the patch!

br, thorsten

DANGER!!! The use of this patch breaks (silently) Views Module when using the content field handler "Node Last Update or Comment" wich ironically is used in the Views used by Advanced Forum Module (the motivation of the patch was to solve warnings in the Forum Module).

I am attaching the modified handlers in Views Module if anyone comes across this crazy issue that took me ages to solve. What happens is that, whenever a View is using the affected field type, it will silently show no results at all.

thx 4 the update @david_garcia_garcia

br, thorsten

I am starting to think that to solve all compatibility issues with SQL Server Driver we need 100% compatible CLR implementation of functions such as GREATEST, WS_CONCAT, CONCAT, GROUP_CONCAT, but I am not sure of making the effort because it looks as though CLR functions will never make it into the module due to security reasons.

Ported to D8.

--

Hi,

Is there a fix or workaround for this? I have followed several threads regarding this and can't seem to get advanced forum views comment working with MSSQL.

Any suggestions are greatly appreciated.

EDIT to add that I found fix here: http://cgit.drupalcode.org/drupal/commit/?id=b4858c7

I am getting the previously mentioned date_time errors, but I am happy that I am able to use the Advanced Forums module now. Thanks for working on this...

Is there a fix or workaround for this?

There is: review, propose patches and/or donate to projects.

This issue needs a follow-up with something like "Remove all uses of GREATEST in core".

I've rerolled the D8 patch, but if no one reviews, this will never make it into D8 (and into D7 because the backport policy states that for something to get into D7, first must be solved in D8).

Not that bad, there is only one additional place in core where greatest is used:

\core\modules\comment\src\Plugin\views\field\NodeNewComments.php (1 hit)
Line 136: AND c.changed > GREATEST(COALESCE(h.timestamp, :timestamp1), :timestamp2) AND c.status = :status GROUP BY n.nid", array(

SQLite also does not support GREATEST. In the SQLite driver there is a file called core/lib/Drupal/Core/Database/Driver/sqlite/Connection.php and in that file there is a helper function called sqlFunctionGreatest that fixes the problem for SQLite.

Unluckily that workaround will not do the job for MSSQL.

The thing is that core shoud be as database agnostic as possible and CASE WHEN is a more universally equaly implemented SQL specification.

Looking good, but as per #21, there is still one instance to go?

Is this still an issue

I asked about this issue in #bugsmash, and pinged daffie. daffie responded saying that MySQL/MariaDB and PostgreSQL having a different opinions on how to handle NULL values in combination with the "GREATEST" functionality. The SQL Server has yet another opinion how to do the functionality And finished by saying that this something we cannot fix.

I am not sure if this should be documented somewhere and I left a question about that in #bugsmash.

As for this issue, we can close this as won't fix.

I just heard from @daffie in slack who told me they updated the documentation to explain GREATEST and LEAST, etc. I have added that to the Issue Summary.

Based on that I am changing this to a Task and marking it Fixed, and updating credit.

Thanks!

Cleaning up tags.