Почему PostgreSQL не может сделать этот простой ПОЛНЫЙ JOIN?

Вопрос:Здесь минимальная настройка с двумя таблицами a и b, каждая с тремя строками: CREATE TABLE a ( id SERIAL PRIMARY KEY, value TEXT ); CREATE INDEX ON a (value); CREATE TABLE b ( id SERIAL PRIMARY KEY, value TEXT ); CREATE INDEX ON b (value); INSERT INTO a (value) VALUES ('x'), ('y'), (NULL); INSERT INTO

Вопрос:

Здесь минимальная настройка с двумя таблицами a и b, каждая с тремя строками:

CREATE TABLE a ( id SERIAL PRIMARY KEY, value TEXT ); CREATE INDEX ON a (value); CREATE TABLE b ( id SERIAL PRIMARY KEY, value TEXT ); CREATE INDEX ON b (value); INSERT INTO a (value) VALUES (‘x’), (‘y’), (NULL); INSERT INTO b (value) VALUES (‘y’), (‘z’), (NULL);

Вот LEFT JOIN, который работает нормально, как ожидалось:

SELECT * FROM a LEFT JOIN b ON a.value IS NOT DISTINCT FROM b.value;

с выходом:

id | value | id | value —-+——-+—-+——- 1 | x | | 2 | y | 1 | y 3 | | 3 | (3 rows)

Изменение “LEFT JOIN” на “FULL JOIN” дает ошибку:

SELECT * FROM a FULL JOIN b ON a.value IS NOT DISTINCT FROM b.value;

ОШИБКА: FULL JOIN поддерживается только с условиями объединения слияния или хеширования.

Может кто-нибудь ответить:

Что такое “условие объединения слиянием или хеш-соединяемым соединением” и почему объединение в a.value IS NOT DISTINCT FROM b.value не выполняет это условие, но a.value = b.value отлично подходит?

Кажется, что единственная разница в том, как обрабатываются значения NULL. Поскольку столбец value индексируется в обеих таблицах, запуск EXPLAIN в поиске NULL так же эффективен, как поиск значений, которые не являются NULL:

EXPLAIN SELECT * FROM a WHERE value = ‘x’; QUERY PLAN ————————————————————————— Bitmap Heap Scan on a (cost=4.20..13.67 rows=6 width=36) Recheck Cond: (value = ‘x’::text) -> Bitmap Index Scan on a_value_idx (cost=0.00..4.20 rows=6 width=0) Index Cond: (value = ‘x’::text) EXPLAIN SELECT * FROM a WHERE value ISNULL; QUERY PLAN ————————————————————————— Bitmap Heap Scan on a (cost=4.20..13.65 rows=6 width=36) Recheck Cond: (value IS NULL) -> Bitmap Index Scan on a_value_idx (cost=0.00..4.20 rows=6 width=0) Index Cond: (value IS NULL)

Это было протестировано с помощью PostgreSQL 9.6.3 и 10beta1.

Был обсуждение этой проблемы, но он напрямую не отвечает на указанный выше вопрос.

Лучший ответ:

PostgreSQL реализует FULL OUTER JOIN либо с хешем, либо с объединением.

Чтобы иметь право на такое объединение, условие соединения должно иметь форму

<expression using only left table> <operator> <expression using only right table>

Теперь ваше условие соединения выглядит так, но PostgreSQL не имеет специального оператора IS NOT DISTINCT FROM, поэтому он анализирует ваше состояние на:

(NOT ($1 IS DISTINCT FROM $2))

И такое выражение не может использоваться для хеш-соединений или объединений, поэтому сообщение об ошибке.

Я могу придумать способ обойти это:

SELECT a_id, NULLIF(a_value, ‘<null>’), b_id, NULLIF(b_value, ‘<null>’) FROM (SELECT id AS a_id, COALESCE(value, ‘<null>’) AS a_value FROM a ) x FULL JOIN (SELECT id AS b_id, COALESCE(value, ‘<null>’) AS b_value FROM b ) y ON x.a_value = y.b_value;

Это работает, если <null> не отображается нигде в столбцах value.

Ответ №1

Я просто решил такой случай, заменив условие ON на “TRUE” и переместив исходное условие “ON” в предложение WHERE. Я не знаю, как это повлияло на производительность.

Оцените статью
Добавить комментарий