Wrong aggregate result when sorting by a NULL value
Ondřej Bouda <obouda@email.cz>
From: Ondřej Bouda <obouda@email.cz>
To: pgsql-bugs@postgresql.org
Date: 2018-11-02T13:29:53Z
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
Commits
-
Fix unused-variable warning.
- 6f4e01c7d289 11.1 landed
- 3e0b05a75673 12.0 landed
-
Prevent generating EEOP_AGG_STRICT_INPUT_CHECK operations when nargs == 0.
- 6eb31cedb316 11.1 landed
- 793beab37e8f 12.0 landed
-
Fix STRICT check for strict aggregates with NULL ORDER BY columns.
- fd59b29c8739 11.1 landed
- 4c640f4f38d5 12.0 landed
-
Expression evaluation based aggregate transition invocation.
- 69c3936a1499 11.0 cited