Wrong aggregate result when sorting by a NULL value - Mailing list pgsql-bugs

Dear PostgreSQLers,

the following seems as a bug to me on Postgres 11.0:


CREATE FUNCTION first_arg(ANYELEMENT, ANYELEMENT) RETURNS ANYELEMENT
AS $function$
     SELECT $1
$function$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE AGGREGATE first(ANYELEMENT) (
     SFUNC = first_arg,
     STYPE = ANYELEMENT
);

CREATE TABLE t (
     x TEXT,
     y INT,
     z DATE
);
INSERT INTO t (x, y, z) VALUES ('val', 42, NULL);

SELECT first(x ORDER BY y) FROM t; -- returns 'val', as expected
SELECT first(x ORDER BY y, z) FROM t; -- returns NULL, which seems wrong


I would expect both the SELECT statements to return 'val'. Additional 
order by "z" should make no difference as there is just one row in the 
table.

More interestingly, if "z" is not NULL, the result is correct:

UPDATE t SET z = CURRENT_DATE;

SELECT first(x ORDER BY y) FROM t; -- returns 'val'
SELECT first(x ORDER BY y, z) FROM t; -- returns 'val'


The documentation [https://www.postgresql.org/docs/11/static/xaggr.html] 
says that if the state function is STRICT, the first non-NULL value is 
automatically used as the initial state. The ORDER BY option is not 
documented to have any effect on this - the documentation just says that 
"[DISTINCT and ORDER BY] options are implemented behind the scenes and 
are not the concern of the aggregate's support functions."

Do I miss something, or is it really a bug?

Best regards,
Ondrej Bouda