Характерные ошибки при решении задач на написание запросов на выборку (SELECT)

База данных «Корабли»

Решение 3.7.3

Консоль

Выполнить

SELECT battle 
FROM Classes c LEFT JOIN 
 Ships s ON c.class = s.class INNER JOIN 
 Outcomes o ON o.ship = s.name OR 
 c.class = o.ship
GROUP BY battle, country 
HAVING COUNT(ship) > 3;

SELECT battle FROM Classes c LEFT JOIN Ships s ON c.class = s.class INNER JOIN Outcomes o ON o.ship = s.name OR c.class = o.ship GROUP BY battle, country HAVING COUNT(ship) > 3;

    Схема

Обратите внимание на HAVING COUNT(ship) > 3. Использование правильного предиката с условием >=3 делало запрос неверным, каким он и является. Подгонка решения позволила обнаружить огрех в проверке, который и был устранен.

Итак, запрос соединяет классы с кораблями из таблицы Ships, чтобы определить страну корабля. Левое соединение (LEFT JOIN) используется для того, чтобы не потерять класс, если кораблей этого класса нет в таблице Ships. Такой (и не только) класс понадобится для того, чтобы учесть головные корабли из таблицы Outcomes, что и делается в следующем (внутреннем) соединении. Предикат этого соединения

ON o.ship = s.name OR c.class = o.ship

сформирует строку, в столбце ship которой будет находиться имя корабля, принимавшего участие в сражениях, если его имя совпадает с именем корабля известного класса в таблице Ships или если его имя совпадает с именем класса (головной корабль). Если корабль не принимал участия в сражении, то значением в столбце ship будет NULL. Затем выполняется группировка по паре атрибутов {battle, country} с предложением HAVING COUNT(ship) >= 3, что позволяет отобрать только те страны, которые участвовали в битве более чем двумя кораблями. Заметим, что функция COUNT корректно обработает NULL-значения в столбце ship.

В этом «или» предиката (1) и заключается основная ошибка этого запроса. Если один и тот же головной корабль имеется и в таблице Outcomes, и в таблице Ships, то он будет учтен дважды для одной и той же битвы. Это можно увидеть из следующего запроса:

Консоль

Выполнить

SELECT battle, country, ship, COUNT(*) qty 
FROM Classes c LEFT JOIN 
 Ships s ON c.class = s.class INNER JOIN 
 Outcomes o ON o.ship = s.name OR 
 c.class = o.ship
GROUP BY battle, country, ship;

SELECT battle, country, ship, COUNT(*) qty FROM Classes c LEFT JOIN Ships s ON c.class = s.class INNER JOIN Outcomes o ON o.ship = s.name OR c.class = o.ship GROUP BY battle, country, ship;

    Схема

Приведем здесь только одну неправильную строку результата:

Явная ошибка, так как один и тот же корабль не может дважды упоминаться для одной и той же битвы (Surigao Strait), что запрещено первичным ключом на таблице Outcomes.

Отметим, что рассматриваемый запрос, как и решения 3.7.2 и 3.7.1, содержит еще одну ошибку, встречающуюся настолько часто, что она даже описана в FAQ на сайте. Эта ошибка заключается в том, что поскольку группировка выполняется по паре атрибутов {battle, country}, то битва будет выводиться неоднократно, если в ней принимало участие минимум по 3 корабля от каждой участвовавшей в битве страны.

Остается один вопрос. Почему же при трех отмеченных ошибках (>3 вместо >=3, ошибочное соединение и возможное появление дубликатов) запрос принимался системой?

Попробуем разобраться. В основной базе не было ни одной битвы, для которой бы выполнялось условие задачи. Правильное решение показывало пустой набор записей. Поэтому ошибочное увеличение числа кораблей не работало с правильным критерием (>=3), так как запрос выдавал битву Surigao Strait, хотя в ней реально принимало участие 2 корабля из USA. А вот условие >3 опять давало пустой набор.

В проверочной базе для блокировки решения с неисключенными дубликатами для одной битвы было два набора по 3 и более корабля разных стран. При этом в одном наборе головной корабль присутствовал в обеих таблицах (Outcomes и Ships). Для этого набора рассматриваемым запросом ошибочно считалось 4 корабля, а для второго правильно — 3. Поэтому условие в предикате HAVING – > 3 и давало только одну битву, разрешая самым неожиданным образом проблему с дубликатами.

Мир полон неожиданностей; чем больше делается ошибок, тем больше вероятность совпадения результатов.

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