Обсуждение: query_is_distinct_for does not take into account set returning functions

Поиск
Список
Период
Сортировка

query_is_distinct_for does not take into account set returning functions

От
David Rowley
Дата:
Over here -> http://www.postgresql.org/message-id/6351.1404663344@sss.pgh.pa.us Tom noted that create_unique_path did not check for set returning functions.

Tom Wrote:
> I notice that create_unique_path is not paying attention to the question
> of whether the subselect's tlist contains SRFs or volatile functions.
> It's possible that that's a pre-existing bug.

I looked at this a bit and I can confirm that it does not behave as it should do. Take the following as an example:

create table x (id int primary key);
create table y (n int not null);

insert into x values(1);
insert into y values(1);

select * from x where (id,id) in(select n,generate_series(1,2) / 10 + 1 g from y);
 id
----
  1
(1 row)

select * from x where (id,id) in(select n,generate_series(1,2) / 10 + 1 g from y group by n);
 id
----
  1
  1
(2 rows)

The 2nd query does group by n, so query_is_distinct_for returns true, therefore the outer query think's it's ok to perform an INNER JOIN rather than a SEMI join, which is this case produces an extra record.

I think we should probably include the logic to test for set returning functions into query_is_distinct_for.

The attached fixes the problem.

Regards

David Rowley
Вложения

Re: query_is_distinct_for does not take into account set returning functions

От
Tom Lane
Дата:
David Rowley <dgrowleyml@gmail.com> writes:
> I think we should probably include the logic to test for set returning
> functions into query_is_distinct_for.

It strikes me that there's only a problem if the SRF is in a tlist entry
that is not one of the DISTINCT or GROUP BY columns, respectively.  It
may not be worth the extra complexity to figure that out, though.
        regards, tom lane

Сайт использует файлы cookie для корректной работы и повышения удобства. Нажимая кнопку «Принять» или продолжая пользоваться сайтом, вы соглашаетесь на их использование в соответствии с Политикой в отношении обработки cookie ООО «ППГ», в том числе на передачу данных из файлов cookie сторонним статистическим и рекламным службам. Вы можете управлять настройками cookie через параметры вашего браузера