Заявление о ситуации в T-SQL странное поведение с newid() как источник случайности

Я использую SQL Server 2012.

Если я сделаю следующее, чтобы получить список случайных чисел в диапазоне [1,3], он работает нормально:

SELECT TOP 100 
 ABS(CHECKSUM(NEWID()))%3 + 1 [value_of_rand]
FROM sys.objects

и я получаю такие приятные вещи (все между 1 и 3).

3
2
2
2
1
....etc.

Но если я затем помещаю вывод той же функции с цепным случайным значением в оператор CASE, он, по-видимому, не производит только значения 1,2,3.

SELECT TOP 100 
 CASE (ABS(CHECKSUM(NEWID()))%3 + 1)
 WHEN 1
 THEN 'one'
 WHEN 2
 THEN 'two'
 WHEN 3
 THEN 'three'
 ELSE
 'that is strange'
 END [value_of_case]
FROM sys.objects

Он выводит:

three
that is strange
that is strange
one
two
...etc

Что я здесь делаю неправильно?

3 ответа

Ваш

SELECT TOP 100 
 CASE (ABS(CHECKSUM(NEWID()))%3 + 1)
 WHEN 1
 THEN 'one'
 WHEN 2
 THEN 'two'
 WHEN 3
 THEN 'three'
 ELSE
 'that is strange'
 END [value_of_case]
FROM sys.objects

Выполняется фактически:

SELECT TOP 100 
 CASE 
 WHEN (ABS(CHECKSUM(NEWID()))%3 + 1) = 1 THEN 'one'
 WHEN (ABS(CHECKSUM(NEWID()))%3 + 1) = 2 THEN 'two'
 WHEN (ABS(CHECKSUM(NEWID()))%3 + 1) = 3 THEN 'three'
 ELSE 'that is strange'
 END [value_of_case]
FROM sys.objects;

В основном ваше выражение не является детерминированным, и каждый раз оценивается, поэтому вы можете получить ELSE clause. Таким образом, нет ошибки или улова, просто вы используете ее с переменным выражением, и это абсолютно нормальное поведение.

Это тот же класс, что и COALESCE syntactic-sugar

Выражение COALESCE является синтаксическим ярлыком для CASE выражение. То есть код COALESCE (выражение1,... n) переписан оптимизатором запросов в качестве следующего выражения CASE:

CASE

КОГДА (выражение1 НЕ НУЛЬНО) ТОГДА выражение1

КОГДА (выражение2 не является NULL) THEN expression2

...

ELSE выражениеN

КОНЕЦ

Это означает, что входные значения (выражение1, выражение2, выражениеN и т.д.) будут оцениваться несколько раз. Также в соответствие стандарту SQL, выражение значения, которое содержит подзапрос считается недетерминированным и подзапрос оценивается дважды. В любом случае разные результаты могут быть возвращены между первая оценка и последующие оценки.

EDIT:

Решение: SqlFiddle

SELECT TOP 100 
 CASE t.col
 WHEN 1 THEN 'one'
 WHEN 2 THEN 'two'
 WHEN 3 THEN 'three'
 ELSE 'that is strange'
 END [value_of_case]
FROM sys.objects
CROSS APPLY ( SELECT ABS(CHECKSUM(NEWID()))%3 + 1 ) AS t(col)


Я думаю, что проблема, с которой вы столкнулись здесь, заключается в том, что (ABS(CHECKSUM(NEWID()))%3 + 1) не является значением, это выражение, и SQL имеет возможность повторно оценивать его, когда захочет. Вы можете попробовать различные синтаксические вещи, такие как удаление дополнительной круглой скобки или CTE. Это может заставить его уйти (пока), но это может не произойти, поскольку логически это похоже на тот же запрос оптимизатору.

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


Я не могу сказать, почему, это действительно странно, но я могу дать вам обходной путь. Выберите случайные значения в cte перед тем, как попытаться их использовать.

;with rndsrc(value_of_rand) as
(
SELECT TOP 100 
 ABS(CHECKSUM(NEWID()))%3 + 1
FROM sys.objects
)
SELECT TOP 100 
CASE value_of_rand
 WHEN 1
 THEN 'one'
 WHEN 2
 THEN 'two'
 WHEN 3
 THEN 'three'
 ELSE
 'that is strange'
END [value_of_case]
from rndsrc

Нет ничего странного "

licensed under cc by-sa 3.0 with attribution.