Re: Cannot do time-aligned listing with FULL JOIN, get ERROR: FULL JOIN is only supported with merge-joinable join conditions

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Cannot do time-aligned listing with FULL JOIN, get ERROR: FULL JOIN is only supported with merge-joinable join conditions
Дата
Msg-id 3595.1257459280@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Cannot do time-aligned listing with FULL JOIN, get ERROR: FULL JOIN is only supported with merge-joinable join conditions  ("John Koerber" <johnk@musicreports.com>)
Список pgsql-novice
"John Koerber" <johnk@musicreports.com> writes:
> SELECT *
>   FROM public.events_a A
>   FULL JOIN public.events_b B
>     ON (start_a, end_a) OVERLAPS (start_b, end_b)
>  ORDER BY coalesce(start_a, start_b), start_b  ;
>  [ doesn't work ]

> In practice, is there:
> * A different structuring the time spans of the 'events' such that a
> merge-joinable condition can be found?
> * A possibility of teaching Hash Join to do the FULL JOIN on
> non-merge-joinable conditions?

Even if we could do full joins by hashing, that wouldn't help you since
OVERLAPS is no more hashable than it is mergeable.  The only possible
join plan would be nestloop, with a work table the size of the inner
input to keep track of which inner rows hadn't been joined to anything
:-(

            regards, tom lane

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

Предыдущее
От: "John Koerber"
Дата:
Сообщение: Cannot do time-aligned listing with FULL JOIN, get ERROR: FULL JOIN is only supported with merge-joinable join conditions
Следующее
От: Brian Modra
Дата:
Сообщение: Re: SQL Server