[SQL Server] Головоломки (Часть 3)
От: Olaf Россия  
Дата: 19.01.17 07:25
Оценка: 113 (6) +1
Добрый день, коллеги!

Подготовлена третья часть головоломок. Предыдущие выпуски можно посмотреть здесь:
[SQL Server] Головоломки (Часть 1)
[SQL Server] Головоломки (Часть 2)

1. Использование переменных в инструкции UPDATE
Дано: Таблица с двумя колонками и одной записью.
create table #Table1
(
    A int,
    B int
)

insert into #Table1(A, B)
select 10, 10

Вопрос: Что содержится в таблице (колонках A и B) и чему равно значение переменных (@A и @B) после выполнения запроса на обновление ?
declare @A int = 0
declare @B int = 0

update #Table1
set @A = A = A + 5
    ,B = @B + 5
    ,@B = B

select @A, t.A, @B, T.B
from #Table1 t

Выберите один вариант ответа:
a) 0, 15, 5, 0
б) 15, 15, 5, 0
в) 15, 0, 15, 15
г) 15, 0, 5, 0
д) 0, 15, 15, 15
е) 15, 15, 10, 15
ё) 0, 15, 0, 5
ж) 15, 0, 0, 5
з) Возникнет ошибка
  Ответ

е) 15, 15, 10, 15
Подсказка:
Правильный ответ содержится в документации к инструкции UPDATE

SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

Вычисления выполняются в следующем порядке:
1. @A = A = A + 5 <=> @A = A = 10 + 5 = 15, значения переменной @A и A совпадают и равны 15
2. @B = B <=> @B = 10, значение переменной @B равно 10
3. B = @B + 5 <=> B = 10 + 5, значение колонки B равно 15



2. CASE и недетерминированная функция
Дано: Выражение floor(1 + rand() * 3), которое гарантированно генерирует числа от 1 до 3.
Вопрос: Какие возможные значения могут быть получены при использовании обозначенного выше выражения в инструкции case ?
select 
    case floor(1 + rand() * 3)
        when 1 then 'один'
        when 2 then 'два'
        when 3 then 'три'
    end

Выберите один вариант ответа:
а) один, два, три
б) один, два, три, 0
в) один, два, три, NULL
г) NULL
д) 0
  Ответ

в) один, два, три, NULL
Подсказка:
Несмотря на то, что в запросе используется синтаксис простого выражения CASE, проверка на равенство с вычислением входного выражения выполняется для каждого предложения WHEN. Если в качестве выражения используется переменная или колонка таблицы, то этот эффект не заметен. Однако в случае использования недетерминированной функции (floor(1 + rand() * 3)), возможны ситуации, когда ни одна из операций сравнения не выдаст TRUE, именно поэтому инструкция CASE вернет NULL. Запрос фактически будет выполнен следующим образом:

select 
    case 
        when floor(1 + rand() * 3) = 1 then 'один'
        when floor(1 + rand() * 3) = 2 then 'два'
        when floor(1 + rand() * 3) = 3 then 'три'
        else null
    end



3. CASE и агрегирующая функция
Дано: Три запроса, в каждом из которых переменная @i проверяется на равенство значению 1 через выражение case.
declare @i int = 1
/* 1 */ select case when @i = 1 then 1 else 1/0 end
/* 2 */ select case when @i = 1 then 1 else min(1/0) end
/* 3 */ select case when @i = 1 then 1 else (select min(1/0)) end

Вопрос: Какие из трех запросов выполнятся успешно, т.е. без ошибок ?
Выберите один вариант ответа:
а) 1 ,2, 3
б) 1, 3
в) 1, 2
г) 2, 3
  Ответ

б) 1, 3
Подсказка:
В документации для инструкции CASE сказано, что она последовательно оценивает свои условия и останавливается, когда находит первое удовлетворяющее. В некоторых ситуациях выражение оценивается до того, как инструкция CASE получает результаты выражения в качестве входных данных. При оценке этих выражений возможны ошибки. Агрегатные выражения в аргументах WHEN или THEN(!) оцениваются вначале, после чего передаются инструкции CASE.
Получается, что первый запрос /* 1 */ выполнится успешно, на втором /* 2 */ возникнет ошибка деления на 0, т.к. там используется агрегирующая функция MIN. А вот третий запрос вопреки документации выполнится успешно. Возможно, использование подзапроса возвращающего скалярное значение с агрегирующей функцией меняет порядок обработки, и до этого места выполнение не доходит.
Официальных комментариев от производителя на этот счет не было, тем не менее, описанное выше поведение инструкции CASE с агрегирующими функциями задокументировано относительно недавно после создания обращений на сайте Connect. Одно из обращений находится со статусом Closed as Fixed #691535, другое #690017 Closed as By Design



4. newid() и неравенство
Дано: Табличная переменная @Test с одной записью
declare @Test table
(
    Id int primary key,
    Uid uniqueidentifier 
)

insert into @Test select 1, null

Вопрос: На ваш взгляд запрос следующего вида всегда возвращает одну запись ?
declare @uid uniqueidentifier = newid()

select * 
from @Test t
where t.Id = 1 and isnull(t.uid, newid()) <> @uid

Выберите один вариант ответа:
а) Да, всегда
б) Нет, не всегда
  Ответ

б) Нет, не всегда
Подсказка:
Выполняя многократно запрос на выборку данных можно получать разное количество записей от 0 до 1. Если посмотреть план запроса, то можно увидеть, что оператор фильтрации разворачивает условие isnull(t.uid, newid()) <> @uid в предикат isnull(@Test.[Uid] as [t].[Uid],newid())<[@uid] OR isnull(@Test.[Uid] as [t].[Uid],newid())>[@uid]. Вместо одного запуска недетерминированной функции newid(), мы получаем два, поэтому существуют моменты времени, когда условие используемое в предикате не выполняется и возвращается 0 записей. Данное поведение замечено на табличных переменных.



5. Строки с концевыми пробелами и сравнение
Дано: Таблица с заполненными данными
create table #Test
(
    Name varchar(100)
)

insert into #Test select 'abc'
insert into #Test select 'abc '
insert into #Test select 'abc  '
insert into #Test select 'abc   '

Вопрос: Какое количество строк вернет запрос ?
select distinct Name
from #Test

Выберите один вариант ответа:
а) 1
б) 2
в) 3
г) 4
  Ответ

а) 1
Подсказка:
В соответствии со стандартом ANSI SQL-92, перед сравнением строки с концевыми пробелами выравниваются до одинаковой длины. Поэтому с точки зрения сравнения все записи представленные в таблице #Test эквивалентны.

T-SQL SQL SQL Server Головоломки Puzzles
 
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.