Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 SQL: баг или фича? Order by + case  [new]
OrderByCase
Guest
Коллеги, нашел интересное поведение движка SQL.


Если создать покрывающий индекс по полю, который будет использоваться в order by и затем вызвать два запроса:

1. с order by по этому полю
2. с order by по этому полю, но в order by указать case when

То первый запрос отработает со Scan Index и без сортировки
А второй еще раз отсортирует уже отсортированные данные в индексе.

Это баг, фича? Как обойти, не переписывая запрос (без кучи if - else в коде)?


Код для воспроизведения:
      if object_id(N'tempdb..#t') is not null
      drop table #t

	create table #t(
			id bigint,
			processed_date datetime,
			comment nvarchar(256)
	)

	insert into #t(id, processed_date, comment)
	select object_id, modify_date, name from sys.objects 

   create unique clustered index tmp_id on #t (id)

    create nonclustered index tmp_processed_date on #t (processed_date desc)
    include(id, comment)


	declare @type tinyint = 1

	select * from #t b
	order by b.processed_date desc

	select * from #t b
   order by case when @type in (1, 2) then b.processed_date
         end desc

      if object_id(N'tempdb..#t') is not null
      drop table #t



Версия сервера:
Microsoft SQL Server 2014 - 12.0.2546.0 (X64)
Jun 6 2015 00:57:37
25 апр 16, 10:38    [19101601]     Ответить | Цитировать Сообщить модератору
 Re: SQL: баг или фича? Order by + case  [new]
OrderByCase
Guest
Планы:

|--Sort(ORDER BY:([b].[processed_date] DESC))
|--Table Scan(OBJECT:([tempdb].[dbo].[#t] AS [b]))

|--Sort(ORDER BY:([Expr1002] DESC))
|--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [@type]=(2) OR [@type]=(1) THEN [tempdb].[dbo].[#t].[processed_date] as [b].[processed_date] ELSE NULL END))
|--Table Scan(OBJECT:([tempdb].[dbo].[#t] AS [b]))

К сообщению приложен файл. Размер - 68Kb
25 апр 16, 10:41    [19101620]     Ответить | Цитировать Сообщить модератору
 Re: SQL: баг или фича? Order by + case  [new]
iljy
Member

Откуда:
Сообщений: 8711
OrderByCase,

и что именно вас удивляет? что когда вы заставляете сервер сортировать по выражению - он сортирует по этому выражению вместо использования индекса не по этому выражению? Сервер далеко не всегда может сопоставить порядок аргументов и результата. Если сделаете ORED BY b.processed_date + 1, то сортировки не будет.
25 апр 16, 10:46    [19101645]     Ответить | Цитировать Сообщить модератору
 Re: SQL: баг или фича? Order by + case  [new]
Glory
Member

Откуда:
Сообщений: 104760
OrderByCase
Это баг, фича? Как обойти, не переписывая запрос (без кучи if - else в коде)?

В смысле, как заставить сервер понять, что case нет, хотя он есть в тексте запроса ?
25 апр 16, 10:47    [19101655]     Ответить | Цитировать Сообщить модератору
 Re: SQL: баг или фича? Order by + case  [new]
iljy
Member

Откуда:
Сообщений: 8711
OrderByCase,

кстати, конкретно в вашем случае поможет указание для запроса option (recompile)
25 апр 16, 10:49    [19101666]     Ответить | Цитировать Сообщить модератору
 Re: SQL: баг или фича? Order by + case  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
OrderByCase,
Сервер параметр не может прослушать, а для неизвестного в момент выполнения параметра он не может не вычислить выражение для сортировки
25 апр 16, 10:50    [19101676]     Ответить | Цитировать Сообщить модератору
 Re: SQL: баг или фича? Order by + case  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
OPTION(RECOMPILE) Вам в помощь, хотя сакрального смысла "баг фича" не увидел.
25 апр 16, 10:51    [19101683]     Ответить | Цитировать Сообщить модератору
 Re: SQL: баг или фича? Order by + case  [new]
OrderByCase
Guest
iljy,AlanDenton

Да, option recompile помог, спасибо.
25 апр 16, 11:08    [19101790]     Ответить | Цитировать Сообщить модератору
 Re: SQL: баг или фича? Order by + case  [new]
iljy
Member

Откуда:
Сообщений: 8711
OrderByCase
iljy,AlanDenton

Да, option recompile помог, спасибо.


Да не за что, непонятно только, нафига это нужно. Разные сортировки одного отчета?
25 апр 16, 11:10    [19101797]     Ответить | Цитировать Сообщить модератору
 Re: SQL: баг или фича? Order by + case  [new]
OrderByCase
Guest
iljy
OrderByCase
iljy,AlanDenton

Да, option recompile помог, спасибо.


Да не за что, непонятно только, нафига это нужно. Разные сортировки одного отчета?


Да, именно так.
Видимо все-таки придется переписывать на if-ы некоторые вещи.
25 апр 16, 11:55    [19102184]     Ответить | Цитировать Сообщить модератору
 Re: SQL: баг или фича? Order by + case  [new]
iljy
Member

Откуда:
Сообщений: 8711
OrderByCase,

сортировка является частью плана, план строится при компиляции, так что если хотите, чтобы он зависел от значения параметров, придется компилировать его в момент, когда значения всех параметров известны.
25 апр 16, 11:59    [19102222]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить