sqlansi-sqldatabase-agnostic

Why is NULL handled differently in aggregate and scalar functions?

Let's take the following two queries:

select greatest(1, val) from unnest([null]) as val
# null

And:

select sum(val) from unnest([1, null]) as val
# 1

My question is why is null handled differently between aggregate functions and normal scalar functions? Of course, the second query is more useful when null values are not counted. But I would think the first one would be more useful as well if it were to return 1 instead of null. If null is an 'unknown value', then wouldn't both functions have a supposedly unknown answer (i.e., null), not just the latter?

If there is a historical reason for this it would be great to know as well. Hopefully someone can shine some light on why there's a difference between the two.

Solution

  • The aggregate functions are supposed to skip null values. Here is an excerpt from SQL-92 standard:

    <set function specification>

    [...]

    Otherwise, let TX be the single-column table that is the result of applying the <value expression> to each row of T and eliminating null values. If one or more null values are eliminated, then a completion condition is raised: warning- null value eliminated in set function.

    The greatest function on the other hand should return null if any argument is null¹.

    My question is why is null handled differently between aggregate functions and normal scalar functions?

    Generally speaking, scalar functions return null if any of their arguments is null². Here is Ask Tom's take on this exact same issue, I personally agree with everything that is said in the answer:

    and Tom said...

    pretty much all single row functions return NULL if any of their inputs to compare are null.

    ops$tkyte%ORA10GR2> select round( 1.2, null ) from dual;

ROUND(1.2,NULL)
---------------
NULL

    when you ask "what is the greatest of 1, NULL, 2 - the answer is "we don't know, because NULL is unknown"

    Aggregates are defined to "skip nulls" (ANSI says so)

    But functions in general that take a set of inputs, will return NULL when a deciding input is NULL.

    http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#i88893

    If you call a SQL function with a null argument, then the SQL function automatically returns null

    ¹ Different implementations of the greatest function existed long before it was standardized. MySQL and Oracle return null if any argument is null; while PostgreSQL and SQL Server ignore null values.

    ² Conversely, vendor specific scalar functions exist that skip null values. One example is concat_ws (MySQL, PostgreSQL, SQL Server) function that is designed to simplify concatenation of (possibly null) strings using a separator.