Обнаружен скалярный подзапрос SQL, проверяющий строку

Введение

Иногда вместо соединения вы можете намеренно использовать скалярный подзапрос, чтобы проверить, что найдено не более одной строки. Например, у вас может быть этот запрос для поиска национальности в некоторых person строках.

select p.name, c.iso from person p join person_country_map pcm on p.id = pcm.person join country c on pcm.country = c.id where p.id in (1, 2, 3)

Теперь предположим, что person_country_map не является функциональным отображением. Данный человек может отображаться в более чем одной стране, поэтому объединение может найти более одной строки. Или, действительно, человек может вообще не быть в таблице сопоставления, по крайней мере, в том, что касается каких-либо ограничений базы данных.

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

Добавление проверки безопасности не более одной строки

Чтобы проверить наличие более одной строки, вы можете переписать соединение как скалярный подзапрос:

select p.name, ( select c.iso from person_country_map pcm join country c on pc.country = c.id where pcm.person = p.id ) as iso from person p where p.id in (1, 2, 3)

Теперь СУБД выдает ошибку, если человек запрашивал карты двух или более стран. Он не вернет несколько записей для одного и того же человека, как при простом соединении. Так что я могу спать немного легче, зная, что этот случай ошибки проверяется еще до того, как какие-либо строки будут возвращены в приложение. Как осторожный программист, я, конечно, тоже могу проверить приложение.

Можно ли проверить безопасность не строки?

Но что делать, если в person_country_map для человека нет строки? В этом случае скалярный подзапрос вернет null, что делает его примерно эквивалентным левому соединению.

(В качестве аргумента предположим, что внешний ключ от person_country_map.country к country.id и уникальный индекс для country.id, так что это конкретное соединение всегда будет успешным и найдет ровно одну строку страны.)

Мой вопрос

Есть ли способ выразить в SQL, что мне нужен один и только один результат? Обычный скалярный подзапрос - «ноль или единица». Я хотел бы иметь возможность сказать

select 42, (select exactly one x from t where id = 55)

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

Я использую MSSQL 2008 R2, и на самом деле этот код находится в хранимой процедуре, поэтому при необходимости я могу использовать TSQL. (Очевидно, что предпочтительнее использовать обычный декларативный SQL, так как его можно использовать и в определениях представлений.) Конечно, я могу выполнить exists проверку или выбрать значение в переменной TSQL, а затем явно проверить его на наличие null и т. Д. Я мог бы даже выделить результаты во временную таблицу, а затем построить уникальные индексы для этой таблицы в качестве проверки. Но нет ли более удобочитаемого и элегантного способа отметить мое предположение, что подзапрос возвращает ровно одну строку, и проверить это предположение СУБД?


tsql sql-server-2008 join cardinality scalar-subquery
person Ed Avis    schedule 05.03.2015    source источник
comment
Рассматривали ли вы пользовательскую функцию, которая выдает исключение, если нет точно одного результата?   -  person HABO    schedule 05.03.2015
comment
@HABO, вы имеете в виду общую функцию check_not_null (x), которая возвращает x, если x не равно нулю, и barfs (например, запускает деление на ноль) в противном случае?   -  person Ed Avis    schedule 06.03.2015
comment
Я думал о GetCountryForUser( UserId ). Он либо возвращает уникальный CountryId для указанного пользователя, либо жалуется. К сожалению, RaIsError и Throw не разрешены в UDF, поэтому остается что-то вроде this.   -  person HABO    schedule 06.03.2015

Ответы (2)


arrow_upward
0
arrow_downward

Вы делаете это сложнее, чем должно быть

Конечно, вам нужны отношения FK для person.id и person_country_map.person.

У вас либо есть уникальное ограничение для person_country_map.person, либо его нет?
Если у вас нет уникального ограничения, то да, вы можете иметь несколько записей для одного и того же person_country_map.person.

Если вы хотите узнать, есть ли у вас дубликаты, тогда

select pcm.person 
from person_country_map pcm
group by  pcm.person  
having count(*) > 1

Если их несколько, вам просто нужно определить, какой из них

select p.name,
       min(c.iso)
from person p
join person_country_map pcm
  on p.id = pcm.person
join country c
  on pcm.country = c.id 
where p.id in (1, 2, 3)
group by p.name
person paparazzo    schedule 05.03.2015
comment
Для person_country_map.person нет уникальных ограничений, потому что в целом человек может принадлежать более чем одной стране. Но для этого конкретного приложения я знаю, что опрашиваю только тех, кто принадлежит к одному из них. Пример схемы и запроса придуманы, чтобы проиллюстрировать общий смысл желания проверить, что скалярный подзапрос возвращает ровно одну строку, потому что ограничения базы данных не гарантируют этого, хотя я, как программист, считаю, что так будет всегда. - person Ed Avis; 06.03.2015
comment
Это не ответ на мой вопрос о том, как создать скалярный подзапрос SQL, который проверяет, был возвращен только один результат. Пример схемы person - это всего лишь то, что я придумал, чтобы проиллюстрировать этот вопрос. Запрос, который возвращает min(c.iso), - это одна из техник, которые я использовал в прошлом, но в случае обнаружения более одной строки я не хочу определять, какую из них; Я хочу, чтобы запрос завершился ошибкой с большой жирной ошибкой, чтобы я мог выяснить, что не так с моими предположениями. - person Ed Avis; 06.03.2015
comment
Извините, я должен был сделать это более явным, что схема придумана только для иллюстрации общей мысли. Я отредактировал текст вопроса. - person Ed Avis; 06.03.2015

arrow_upward
0
arrow_downward

В MSSQL оказывается, что isnull оценивает свой второй аргумент только в том случае, если первый равен нулю. Так что в общем можно сказать

select isnull(x, 0/0)

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

select 42, isnull((select x from t where id = 55), 0/0)

гарантирует, что подзапрос select x найдет ровно одну строку. Если их несколько, сама СУБД выдаст ошибку; если нет строки, запускается деление на ноль.

Применение этого к исходному примеру приводит к коду

select p.name, -- Get the unique country code of this person. -- Although the database constraints do not guarantee it in general, -- for this particular query we expect exactly one row. Check that. -- isnull(( select c.iso from person_country_map pcm join country c on pc.country = c.id where pcm.person = p.id ), 0/0) as iso from person p where p.id in (1, 2, 3)

Для лучшего сообщения об ошибке вы можете использовать ошибку преобразования вместо деления на ноль:

select 42, isnull((select x from t where id = 55), convert(int, 'No row found'))

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

person Ed Avis    schedule 06.03.2015