Типичные причины картезианского произведения при JOIN, как диагностировать и исправить, и примеры тестов.
Причины
- Отсутствует условие соединения (пропущено ON/USING) — например: `SELECT ... FROM A JOIN B` без `ON`.
- Использована запятая вместо JOIN: `FROM A, B` без WHERE/ON.
- Неправильное условие в ON (опечатка в имени столбца, сравнение всегда истинно/ложно, например `ON 1=1` или `ON A.x B.x` при ожидании равенства).
- Условие в WHERE вместо ON при OUTER JOIN (превращает LEFT/RIGHT JOIN во внутреннее).
- Типы столбцов не совпадают (implicit cast может приводить к неточностям или отсутствию соответствия).
- NULL-значения: обычное `=` не совпадает с NULL, из‑за этого иногда добавляют неверные обходы.
- Логика OR/функций в ON, делающая условие слишком широким (включает много строк).
- Ожидание «1‑к‑1», а на самом деле «1‑ко‑множеству» — множительность таблиц (дубликаты ключей).
- Некорректные псевдонимы/неуточнённые имена столбцов — условие не применяется к нужным столбцам.
Как диагностировать
1. Быстрая проверка подсчётов:
- Получите размеры таблиц: `SELECT COUNT(*) FROM A;` и `SELECT COUNT(*) FROM B;` — обозначим их $n_A$ и $n_B$.
- Считайте результат JOIN без фильтров: `SELECT COUNT(*) FROM A JOIN B ON ;` — если это близко к $n_A\times n_B$, значит условие отсутствует/не работает.
- Формула контрольной границы: ожидаемый размер при отсутствии условия — $n_A\times n_B$.
2. Проверка наличия ON/USING и правильных колонок — прочитать SQL: есть ли `ON A.id = B.a_id` или использованы неверные имена?
3. EXPLAIN / EXPLAIN ANALYZE — покажет использование CROSS JOIN или отсутствие индексов, подсказки про последовательности сканов.
4. Простые выборки для диагностики:
- Посмотреть первые несколько строк объединения: `SELECT A.*, B.* FROM A JOIN B ON LIMIT 10;` — видно ожидаемые/неожиданные пары.
5. Проверка дубликатов/мультипликативности:
- `SELECT A.id, COUNT(B.*) AS cnt FROM A LEFT JOIN B ON A.id = B.a_id GROUP BY A.id ORDER BY cnt DESC LIMIT 10;` — если `cnt` > $1$ для многих строк, мультипликативность.
6. Проверка типов и NULL:
- `SELECT column_name, data_type FROM information_schema.columns WHERE table_name IN ('a','b');`
- Проверить NULL: `SELECT COUNT(*) FROM A JOIN B ON A.x = B.x WHERE A.x IS NULL OR B.x IS NULL;`
7. Локализация ошибки: упростить запрос до минимального JOIN с целью воспроизведения проблемы.
Как исправить
- Добавить или поправить условие соединения:
- Правильно: `SELECT ... FROM A JOIN B ON A.id = B.a_id;`
- Если нужен OUTER JOIN, поместить фильтры на столбцы второй таблицы в ON, а не в WHERE, или использовать `WHERE (B.col IS NULL OR B.col = ...)` осторожно.
- Убедиться в совпадении типов: привести типы явно: `ON CAST(A.id AS bigint) = B.a_id`.
- Обрабатывать NULL: либо `ON A.x IS NOT DISTINCT FROM B.x` (Postgres) либо `ON COALESCE(A.x,'') = COALESCE(B.x,'')` если допустимо.
- Избежать `FROM A, B` — заменить на явный JOIN с ON.
- Исправить логические ошибки в ON (заменить `OR` на нужные условия, избегать `1=1`).
- При многократном совпадении — либо агрегировать, либо уточнить условие, либо добавить уникальность (индексы/ограничения) если предполагается 1‑к‑1.
- Ввести явную проверку целостности (FK), чтобы предотвратить неожиданные комбинации.
Примеры тестов (быстро выполнить в БД)
1. Базовая проверка картезиана:
- Узнать размеры:
- `SELECT COUNT(*) FROM A;` → $n_A$ - `SELECT COUNT(*) FROM B;` → $n_B$ - Результат JOIN:
- `SELECT COUNT(*) FROM A JOIN B ON A.id = B.a_id;` → $n_{join}$ - Тест: если условие отсутствует, то ожидаем $n_{join}\approx n_A\times n_B$. Иначе $n_{join}\ll n_A\times n_B$.
2. Тест множительности (ищем неожиданную множественность):
- `SELECT COUNT(*) FROM (SELECT A.id, COUNT(B.*) AS cnt FROM A LEFT JOIN B ON A.id = B.a_id GROUP BY A.id HAVING COUNT(B.*) > 1) t;`
- Если результат > $0$, есть строки A, которые соединяются с несколькими B — проверьте, это ожидаемо.
3. Тест выборочных строк:
- Для конкретного ключа `x0`: `SELECT * FROM A JOIN B ON A.id = B.a_id WHERE A.id = x0;` — убедитесь, что вывод соответствует ожиданию (количество строк и значения).
4. EXPLAIN:
- `EXPLAIN SELECT ... FROM A JOIN B ON ;` — надеемся увидеть не `Cross Join` и адекватную стратегию.
5. Тест на NULL-совпадение (если ожидается совпадение NULLs):
- `SELECT COUNT(*) FROM A JOIN B ON A.x IS NOT DISTINCT FROM B.x;`
6. Интеграционный тест (ожидаем ровно $m$ строк):
- Если ожидаете ровно $M$ соответствий по бизнес-логике, выполнить:
- `SELECT COUNT(*) FROM ...JOIN... WHERE ;` — сравнить с ожидаемым $M$.
Короткий пример «было/стало»
- Неправильно (картезианский):
- `SELECT * FROM orders o JOIN customers c;` — без `ON`
- Правильно:
- `SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;`
Итог: основная причина — отсутствие/ошибка в условии соединения. Диагностика — сравнение счётчиков $n_A$, $n_B$, $n_{join}$ (и проверка равенства $n_{join}=n_A\times n_B$), EXPLAIN, выборочные строки и группировка по ключу. Исправление — добавить корректный ON/CAST/обработку NULL/ограничение дубликатов.