На главную страницу
Характерные ошибки при решении упражнений.
Задача 46
Моисеенко С.И.
Укажите сражения, в которых участвовало, по меньшей мере, три корабля одной и той
же страны.
Ранее на сайте проходил заведомо неправильное решение:
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, что и делается в следующем (внутреннем) соединении. Предикат этого
соединения
(1)
ON o.ship = s.name OR c.class = o.ship
сформирует строку, в столбце ship которой будет находиться имя корабля,
принимавшего участие в сражениях, если его имя совпадает с именем корабля
известного класса в таблице Ships ИЛИ если его имя совпадает с именем класса
(головной корабль). Если корабль не принимал участия в сражении, то в столбце
ship будет NULL. Затем выполняется группировка по паре атрибутов {battle,
country} с предложением HAVING COUNT(ship) >= 3, что позволяет отобрать
только те страны, которые участвовали в битве более чем двумя кораблями.
Заметим, что функция COUNT корректно обработает NULL-значения в столбце ship.
О разнице в использовании COUNT(*) и COUNT(<имя столбца>) можно почитать в
справке на сайте.
В этом "ИЛИ" предиката (1) и заключается основная ошибка этого запроса. Если
один и тот же головной корабль имеется в таблице Outcomes и в таблице Ships, то
он будет учтен дважды для одной и той же битвы. Это можно увидеть из запроса,
который любезно предложил автор неправильного решения (BSV1969):
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
Я привожу здесь только одну неправильную строку результата:
Battle |
country
|
ship
|
qty |
Surigao Strait |
USA |
Tennessee |
2 |
Явная ошибка, т.к. один и тот же корабль не может дважды упоминаться для одной и
той же битвы (Surigao Strait), что запрещено первичным ключом на таблице
Outcomes.
Отметим, что рассматриваемый запрос содержит еще одну ошибку, описанную в FAQ.
Поскольку группировка выполняется по паре атрибутов {battle, country}, то битва
будет выводиться неоднократно, если в ней принимало участие по 3 и более
корабля от каждой страны.
Остается один вопрос. Почему же при трех отмеченных ошибках (>3 вместо
>=3, ошибочное соединение и возможное появление дубликатов) запрос был
принят системой?
Попробуем разобраться. В основной базе не было ни одной битвы, для которой бы
выполнялось условие задачи. В правильном решении выводился пустой набор
записей. Поэтому ошибочное увеличение числа кораблей не работало с правильным
критерием (>=3), т.к. запрос выдавал битву Surigao Strait, хотя в ней
реально принимало участие 2 корабля из USA. А вот условие >3 опять давало
пустой набор.
В проверочной базе для блокировки решения с неисключенными дубликатами для одной
битвы было два набора по 3 корабля разных стран. При этом в одном наборе
головной корабль присутствовал в обеих таблицах (Outcomes и Ships). Для этого
набора рассматриваемым запросом ошибочно считалось 4 корабля, а для второго
правильно - 3. Поэтому условие в предикате HAVING - > 3 и давало только одну
битву, разрешая самым неожиданным образом проблему с дубликатами.
Приведенные здесь примеры можно выполнить непосредственно на сайте, установив
флажок "Без проверки" на странице с упражнениями
на SELECT.
Перейти к
решению задачи #46
На главную страницу