Wrong aggregate result when sorting by a NULL value

From: Ondřej Bouda <obouda(at)email(dot)cz>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Wrong aggregate result when sorting by a NULL value
Date: 2018-11-02 13:29:53
Message-ID: 2a505161-2727-2473-7c46-591ed108ac52@email.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2018-11-02 15:07:31 Re: Wrong aggregate result when sorting by a NULL value
Previous Message Etsuro Fujita 2018-11-02 13:05:36 Re: BUG #15449: file_fdw using program cause exit code error when using LIMIT