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
|
Список | 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 по дате отправления: