Contradictory behavior of array_agg(distinct) aggregate.

Поиск
Список
Период
Сортировка
От Konstantin Knizhnik
Тема Contradictory behavior of array_agg(distinct) aggregate.
Дата
Msg-id 7d3268df-abc2-4eb3-8e2b-59df084b579a@garret.ru
обсуждение исходный текст
Ответы Re: Contradictory behavior of array_agg(distinct) aggregate.
Re: Contradictory behavior of array_agg(distinct) aggregate.
Список pgsql-hackers
Hi hackers!


Is it only me who consider that current behavior of array_agg(distinct) 
contradicts to interpretation of nulls in other cases ("null" is 
something like "unknown" which means that we can not say weather two 
nulls are the same or not). This is why it is allowed to insert multiple 
nulls in the unique column.

postgres=# create table t(x integer unique);
CREATE TABLE
postgres=# insert into t values (null),(null);
INSERT 0 2
postgres=# select count(distinct x) from t;
  count
-------
      0
(1 row)

postgres=# select array_agg(distinct x) from t;
  array_agg
-----------
  {NULL}
(1 row)

postgres=# select array_agg(x) from t;
   array_agg
-------------
  {NULL,NULL}
(1 row)


So what is the number of distinct "x" values in this case? I think that 
according to SQL model - 0 (as count(distinct) returns).
Why in this case array_agg(distinct x) returns non-empty array?

Yes, unlike most other aggregates, `array_agg` is not ignoring null values.
But is it correct to treat two null values as the same (non-distinct)?
IMHO correct result in this case should be either {} or NULL, either 
{NULL,NULL}.






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