Использовались ли два LEFT JOIN для создания декартова произведения?
Я читаю книгу SQL-антипаттерн от Билл Карвин. В главе 18 он обсуждает плохой сложный запрос на следующем примере:
SELECT p.product_id,
COUNT(f.bug_id) AS count_fixed,
COUNT(o.bug_id) as count_open
FROM BugsProducts p
LEFT Outer JOIN (BugsProducts bpf JOIN Bugs f Using (bug_id))
ON (p.bug_id = f.bug_id AND f.status = 'FIXED')
LEFT OUTER JOIN (BugsProducts bpo JOIN Bugs o Using (bug_id))
ON (p.bug_id = o.bug_id AND o.status = 'OPEN')
WHERE p.product_id = 1
GROUP BY p.product_id
Он утверждает следующее:
You happen to know that in reality there are eleven fixed bugs and seven open bugs for the given product. So the Result of the query is puzzeling:
product_id | count_fixed | count_open 1 | 77 | 77
Затем он объяснил, что это происходит потому, что результатом является декартово произведение между исправленными и открытыми ошибками.
Я не понимал, почему это должно происходить, и перестроил этот запрос с помощью MySQL 5.7.25.
Результат был на удивление
product_id | count_fixed | count_open
1 | 11 | 7
Также можно просто заменить (BugsProducts bpf JOIN Bugs f Using (bug_id)) на Bugs f и (BugsProducts bpf JOIN Bugs o Using (bug_id)) на Bugs o.
Почему утверждается, что запрос должен производить декартово произведение? Возвращает ли запрос результат 11/7 только из-за какой-то особенности MySQL, которая не будет работать в других БД?
Мои 3 пенса: 1. Здесь не должно быть декартова произведения, потому что таблицы выглядит должным образом соединяются на их PK/FK. 2. это сочетание синтаксиса ON и USING ужасно (но, возможно, в этом и был смысл, поскольку в этой книге говорится о плохая сложность). 3. Нет необходимости объединять таблицы ошибок вообще (внутри соединений), потому что из них не извлекаются данные, а конечной целью является только подсчет NOT NULL bug_id в обеих объединенных таблицах. Интересно, что скажет автор книги, если ответит на звонок выше...
@ThomasG Я закончил главу, и автор также обращается к 2. и 3.. Это было частью плохого примера, который он хотел исправить. Однако все еще не уверен насчет 1.
Ответы 1
Два левых соединения могут иногда давать декартово произведение.
В этом случае запрос вообще не имеет смысла. Скорее всего, это ошибка.
Попробуйте удалить GROUP BY p.product_id и изменить предложение select следующим образом:
SELECT p.product_id,
f.bug_id AS bug1Id,
o.bug_id as bug2Id
Таким образом, более очевидно, что является результирующим набором.
Я предполагаю, что у нас есть следующие таблицы:
- Продукты (идентификатор_продукта)
- Ошибки(bug_id, статус)
- BugsProducts(bug_id, product_id)
где BugsProducts — таблица соединения между продуктами и ошибками.
С помощью запроса он пытается
- получить все строки BugsProducts (p) для product_id = 1
- присоединился 1-й с (BugsProducts - внутреннее соединение ошибок) с тем же bug_id, что и (p), поэтому присоединение каждой строки BugsProducts к себе, а также к таблице ошибок если статус = "ИСПРАВЛЕНО", поэтому, чтобы обобщить, если строка ошибки имеет статус = «ИСПРАВЛЕНО», она присоединяется к p(который относится к самому себе), в противном случае строки не соединяются (вы видите NULL для bug1Id)
- то же самое со вторым соединением, но со статусом условия = 'OPEN'
Во всяком случае, я считаю, что автор хотел продемонстрировать что-то вроде:
SELECT p.product_id,
COUNT(f.bug_id) AS count_fixed,
COUNT(o.bug_id) as count_open
FROM Products p
LEFT Outer JOIN (BugsProducts bpf JOIN Bugs f Using (bug_id))
ON (bpf.product_id = p.product_id AND f.status = 'FIXED')
LEFT OUTER JOIN (BugsProducts bpo JOIN Bugs o Using (bug_id))
ON (bpo.product_id = p.product_id AND o.status = 'OPEN')
WHERE p.product_id = 1
Group by p.product_id
что приводит к декартовому произведению.
@billkarwin Есть мысли?