GREATEST and LEAST aren't fully compliant with the SQL Standard

[gabrielsky]

the function "least()" have diffent behavior
myqsl : If any argument is NULL, the result is NULL. No comparison is needed.
h2 : all value must be null return null

example:
least(null,123)
in mysql return null，but h2 return 123

we use h2 as junit test db,so hope they have same behavior

h2 version:1.4.200

[katzyn]

This function is vendor-specific, its implementations are different in Oracle, MySQL, and H2.

Standard-compliant CASE WHEN A < B OR A IS NULL THEN A ELSE B END should return the same results in all these DBMS.

[katzyn]

I don't know where this function came from and why it is implemented in that way in H2. It looks like something very useful when there are more than two arguments, but its behavior in presence of NULL values looks strange, but this behavior is documented.

Some functions have optional clauses, for example LEAD and LAG have RESPECT NULLS and IGNORE NULLS; JSON functions have NULL ON NULL and ABSENT ON NULL. Maybe non-standard LEAST and GREATEST need some clauses like these, with default that depends on compatibility mode.

[gabrielsky]

@katzyn thanks for reply , I'll pay attention to test diffent database non-standard function behavior before use it

This function is vendor-specific, its implementations are different in Oracle, MySQL, and H2.

Standard-compliant CASE WHEN A < B OR A IS NULL THEN A ELSE B END should return the same results in all these DBMS.

I don't know where this function came from and why it is implemented in that way in H2. It looks like something very useful when there are more than two arguments, but its behavior in presence of NULL values looks strange, but this behavior is documented.

Some functions have optional clauses, for example LEAD and LAG have RESPECT NULLS and IGNORE NULLS; JSON functions have NULL ON NULL and ABSENT ON NULL. Maybe non-standard LEAST and GREATEST need some clauses like these, with default that depends on compatibility mode.