Проверяет ли SQL Server всю функцию COALESCE, даже если первый аргумент не равен NULL?
Я использую функцию T-SQL COALESCE
, где первый аргумент не будет равен нулю примерно в 95% случаев, когда он выполняется. Если первый аргумент NULL
, второй аргумент - довольно длительный процесс:
SELECT COALESCE (c.FirstName
, (SELECT TOP 1 b.FirstName
FROM TableA a
JOIN TableB b ON .....)
)
Если, например, c.FirstName = 'John'
, будет ли SQL Server выполнять подзапрос?
Я знаю с помощью функции VB.NET IIF ()
, если второй аргумент True, код все еще читает третий аргумент (даже если он не будет использоваться).
7 ответов
Неа . Вот простой тест:
SELECT COALESCE (1, (SELECT 1/0)) - отлично работает
SELECT COALESCE (NULL, (SELECT 1/0)) - выдает ошибку
Если второе условие оценивается, генерируется исключение для деления на ноль.
В документации MSDN это связано с тем, как COALESCE
просматривается интерпретатором - это просто простой способ написать оператор CASE
.
Известно, что CASE
является одной из единственных функций SQL Server, которая (в основном) надежно коротких замыканий.
Есть несколько исключений при сравнении со скалярными переменными и агрегациями, как показано Aaron Bertrand в другом ответе здесь (и это применимо как к CASE
, так и к COALESCE
):
DECLARE @i INT = 1;
ВЫБЕРИТЕ СЛУЧАЙ, КОГДА @i = 1 ТОГДА 1 ВЫШЕ МИН (1/0) КОНЕЦ;
будет генерировать деление на нулевую ошибку.
Это должно считаться ошибкой, и, как правило, COALESCE
будет анализировать слева направо.
Как насчет этого - как сообщил мне Ицик Бен-Ган, который был рассказал об этом Хайме Лафаргу ?
DECLARE @i INT = 1;
ВЫБЕРИТЕ СЛУЧАЙ, КОГДА @i = 1 ТОГДА 1 ВЫШЕ МИН (1/0) КОНЕЦ;
Результат:
Msg 8134, уровень 16, состояние 1, строка 2
Разделение по нулевой ошибке.
Конечно, есть тривиальные обходные пути, но дело в том, что CASE
не всегда гарантирует оценку слева направо /короткое замыкание. Я сообщил об ошибке здесь и был закрыт как« по дизайну ». Пол Уайт впоследствии подал this Connect item , и он был закрыт как Fixed. Не потому, что это было само по себе, а потому, что они обновили Books Online с более точным описанием сценария, в котором агрегаты могут изменять порядок оценки выражения CASE
. Я недавно добавил в блог больше об этом здесь .
ИЗМЕНИТЬ только добавление, в то время как я согласен, что это случаи краев, что большую часть времени вы можете положиться на оценку слева и справа от короткого замыкания , и что это ошибки, которые противоречат документации и, вероятно, в конечном итоге будут исправлены (это не определенно - см. последующий разговор на сообщение в блоге Барта Дункана , чтобы понять, почему), я должен не согласитесь, когда люди говорят, что что-то всегда верно, даже если есть один краевой случай, который опровергает его. Если Ицик и другие могут найти такие одиночные ошибки, это делает его по крайней мере в том, что есть другие ошибки. И поскольку мы не знаем остальной части запроса OP, мы не можем точно сказать, что он будет полагаться на это короткое замыкание, но в конечном итоге его укусил. Поэтому для меня более безопасный ответ:
Если вы можете обычно полагаться на CASE
для оценки слева направо и короткого замыкания, как описано в документации, неточно сказать, что вы всегда может это сделать. На этой странице есть два показанных примера, где это неверно, и ни одна ошибка не была исправлена в любой общедоступной версии SQL Server.
EDIT вот еще один случай (мне нужно прекратить это делать), где выражение CASE
не оценивается в том порядке, который вы ожидаете, даже если не задействованы агрегаты.
Мой взгляд на это заключается в том, что документация делает достаточно понятным, что намерение заключается в том, что CASE должен быть короткозамкнутым. Как упоминает Аарон, было несколько случаев (ха!), Где это, как было показано, не всегда верно.
До сих пор все они были признаны ошибками и исправлены - хотя и не обязательно в версии SQL Server, которую вы можете купить и запланировать сегодня (ошибка с постоянным сгибанием еще не дошла до накопительного обновления AFAIK). Новейшая потенциальная ошибка - первоначально сообщаемая Ициком Бен-Ганом - еще предстоит исследовать (либо Аарон, либо я добавим его в Connect в ближайшее время).
В связи с исходным вопросом существуют другие проблемы с CASE (и, следовательно, COALESCE), в которых используются побочные функции или подзапросы. Рассмотрим:
SELECT COALESCE ((ВЫБОР СЛУЧАЙ, КОГДА RAND () <= 0,5 THEN 999 END), 999);
SELECT ISNULL ((ВЫБОР СЛУЧАЙ, КОГДА RAND () <= 0,5 THEN 999 END), 999);
Форма COALESCE часто возвращает NULL, более подробную информацию можно найти на https : //connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null
Продемонстрированные проблемы с преобразованиями оптимизаторов и отслеживанием общего выражения означают, что невозможно гарантировать, что CASE будет иметь короткое замыкание при любых обстоятельствах. Я могу представить себе случаи, когда, возможно, даже невозможно предсказать поведение, проверив вывод публичного шоу, хотя сегодня у меня нет репродуцирования.
В целом, я думаю, вы можете быть достаточно уверенны в том, что CASE будет короткозамкнуто в целом (особенно если опытный специалист проверяет план выполнения и что план выполнения «принужден» с помощью руководства или подсказок плана), но если вам нужна абсолютная гарантия, вам нужно написать SQL, который вообще не включает выражение.
Не очень удовлетворительное состояние дел, я думаю.
Я столкнулся с другим случаем, когда CASE
/COALESCE
не замыкается. Следующий TVF поднимет PK-нарушение, если в качестве параметра передан 1
.
CREATE FUNCTION F (@P INT)
RETURNS @T TABLE (
C INT ПЕРВИЧНЫЙ КЛЮЧ)
В ВИДЕ
НАЧАТЬ
INSERT INTO @T
ЦЕННОСТИ (1),
(@П)
ВЕРНУТЬ
КОНЕЦ
Если вызывается следующим образом
DECLARE @Number INT = 1
SELECT COALESCE (@Number, (номер SELECT
FROM master..spt_values
WHERE type = 'P'
AND number = @Number),
(SELECT TOP (1) C
FROM F (@Number)))
Или как
DECLARE @Number INT = 1
ВЫБЕРИТЕ СЛУЧАЙ
КОГДА @Number = 1 THEN @Number
ELSE (SELECT TOP (1) C
FROM F (@Number))
КОНЕЦ
Оба дают результат
Нарушение ограничения PRIMARY KEY 'PK__F__3BD019A800551192'. Не могу вставьте дубликат ключа в объект 'dbo. @ T'. Значение повторяющегося ключа (1).
, показывающий, что SELECT
(или, по крайней мере, совокупность переменных таблицы) все еще выполняется и вызывает ошибку, даже если эта ветвь оператора никогда не будет достигнута. Ниже приведена версия для COALESCE
.
Эта запись запроса появляется, чтобы избежать проблемы
SELECT COALESCE (Число, (номер SELECT
FROM master..spt_values
WHERE type = 'P'
AND number = Number),
(SELECT TOP (1) C
FROM F (Number)))
FROM (VALUES (1)) V (Число)
Что дает план
Я просто хотел упомянуть стратегию, которую вы, возможно, не рассматривали. Возможно, это не совпадение, но иногда это пригодится. Посмотрите, дает ли эта модификация лучшую производительность:
SELECT COALESCE (c.FirstName
, (SELECT TOP 1 b.FirstName
FROM TableA a
JOIN TableB b ON .....
WHERE C.FirstName IS NULL) - это измененная часть
)
Другой способ сделать это может быть таким (в основном эквивалентным, но при необходимости позволяет получить доступ к большему количеству столбцов из другого запроса):
SELECT COALESCE (c.FirstName, x.FirstName)
ИЗ
Таблица C c
ВНЕШНЕЕ ПРИМЕНЕНИЕ (
SELECT TOP 1 b.FirstName
ИЗ
Таблица A a
JOIN TableB b ON ...
ГДЕ
c.FirstName IS NULL - важная часть
) Икс
В основном это метод «жестких» соединений таблиц, но включает условие, когда любые строки должны быть объединены. По моему опыту это действительно помогало выполнению планов в разы.
Другой пример
CREATE TABLE T1 (C INT PRIMARY KEY)
CREATE TABLE T2 (C INT PRIMARY KEY)
ВСТАВИТЬ В Т1
OUTPUT вставлен. * INTO T2
ЦЕННОСТИ (1), (2), (3);
Запрос
SET STATISTICS IO ON;
SELECT T1.C,
COALESCE (T1.C, CASE ПРИ СУЩЕСТВУЮЩИХ (ВЫБОР * ОТ T2 ГДЕ T2.C = T1.C) THEN -1 END)
FROM T1
ВАРИАНТ (СОЕДИНЕНИЕ LOOP)
Показывает отсутствие чтения с T2
.
Поиск T2
находится под прохождением предиката, и оператор никогда не выполняется. Но
SELECT T1.C,
COALESCE (T1.C, CASE ПРИ СУЩЕСТВУЮЩИХ (ВЫБОР * ОТ T2 ГДЕ T2.C = T1.C) THEN -1 END)
FROM T1
ВАРИАНТ (СОЕДИНЕНИЕ СМЕШАНЬ)
Указывает , что читается T2
. Даже если значение T2
вообще не требуется.
Конечно, это не удивительно, но я думал, что стоит добавить в репозиторий-пример счетчика, хотя бы потому, что он поднимает вопрос о том, что короткое замыкание означает даже в объявлении на основе набора.
<забастовка>
Нет, это не так. Он будет работать только тогда, когда c.FirstName
является NULL
.
Однако, вы должны попробовать это самостоятельно. Эксперимент. Вы сказали, что ваш подзапрос длительный. Benchmark. Сделай свои выводы на этом.
@ Ответ Аарона на выполняемый подзапрос завершен.
Тем не менее, я все еще думаю, что вы должны переработать свой запрос и использовать LEFT JOIN
. В большинстве случаев подзапросы можно удалить, переработав ваш запрос, чтобы использовать LEFT JOIN
s.
Проблема с использованием подзапросов заключается в том, что ваш общий оператор будет работать медленнее, потому что подзапрос выполняется для каждой строки в результирующем наборе основного запроса.