Wrong aggregate result when sorting by a NULL value

Поиск
Список
Период
Сортировка
От Ondřej Bouda
Тема Wrong aggregate result when sorting by a NULL value
Дата
Msg-id 2a505161-2727-2473-7c46-591ed108ac52@email.cz
обсуждение исходный текст
Ответы Re: Wrong aggregate result when sorting by a NULL value  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Etsuro Fujita
Дата:
Сообщение: Re: BUG #15449: file_fdw using program cause exit code error whenusing LIMIT
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Wrong aggregate result when sorting by a NULL value