Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 баг или фича? ORDER BY  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
Всем привет!
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4290.0 (X64) 
Aug 5 2013 12:40:54
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

+ Скрипт таблицы
CREATE TABLE [dbo].[td_depo_docs](
	[id] [numeric](18, 0) NOT NULL,
	[in_no] [varchar](50) NULL,
	[in_date] [datetime] NULL,
	[depo_doc_type] [int] NULL
    ---- + ещё 33 столбца
) ON [PRIMARY]

GO

CREATE UNIQUE CLUSTERED INDEX [td_depo_docs_pk] ON [dbo].[td_depo_docs] 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_dd_InNo_InDate] ON [dbo].[td_depo_docs] 
(
	[in_no] ASC,
	[in_date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
USE [msd_sd_happy]
GO

CREATE NONCLUSTERED INDEX [IX_td_depo_docs_in_date_depo_doc_type] ON [dbo].[td_depo_docs] 
(
	[in_date] ASC,
	[depo_doc_type] ASC
)
INCLUDE ( [id],
[in_no]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Запрос возвращает данные отсортированные не по дате, а по строковому представлению даты
SELECT CONVERT(VARCHAR(10), [d].[in_date], 104) AS [in_date] FROM [dbo].[td_depo_docs] [d] ORDER BY [d].[in_date] DESC

  |--Sort(ORDER BY:([Expr1002] DESC))
|--Compute Scalar(DEFINE:([Expr1002]=CONVERT(varchar(10),[msd_sd_happy].[dbo].[td_depo_docs].[in_date] as [d].[in_date],104)))
|--Index Scan(OBJECT:([msd_sd_happy].[dbo].[td_depo_docs].[IX_dd_InNo_InDate] AS [d]))SELECT CONVERT(VARCHAR(10), [d].[in_date], 104) AS [in_date_d] FROM [dbo].[td_depo_docs] [d] ORDER BY [d].[in_date] DESC
Не, ну ладно, ошибся сервер индексом, с кем не бывает. Но сортировать то всё равно надо по столбцу, а не по только что вычисленному выражению! Что за халтура?

Лечится любым из вариантов
SELECT CONVERT(VARCHAR(10), [d].[in_date], 104) AS [in_date_d] FROM [dbo].[td_depo_docs] [d] ORDER BY [d].[in_date] DESC
SELECT CONVERT(VARCHAR(10), [d].[in_date], 104) AS [in_date] FROM [dbo].[td_depo_docs] [d] ORDER BY [d].[in_date]+1 DESC
SELECT CONVERT(VARCHAR(10), [d].[in_date], 104) AS [in_date] FROM [dbo].[td_depo_docs] [d] ORDER BY CAST([d].[in_date] AS DATETIME) DESC

Во всех этих случаях план одинаковый
  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT(varchar(10),[msd_sd_happy].[dbo].[td_depo_docs].[in_date] as [d].[in_date],104)))
|--Index Scan(OBJECT:([msd_sd_happy].[dbo].[td_depo_docs].[IX_td_depo_docs_in_date_depo_doc_type] AS [d]), ORDERED BACKWARD)

У кого какие мысли?

К сообщению приложен файл (order_by.sqlplan - 8Kb) cкачать
23 ноя 16, 07:22    [19923396]     Ответить | Цитировать Сообщить модератору
 Re: баг или фича? ORDER BY  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
Расходимся, это - фича.
Уровень совместимости базы - 80
https://msdn.microsoft.com/en-US/library/ms178653(SQL.90).aspx
WHEN binding the column references in the ORDER BY list to the columns defined in the SELECT list, column ambiguities are ignored and column prefixes are sometimes ignored. This can cause the result set to return in an unexpected order.

For example, an ORDER BY clause with a single two-part column (<table_alias>.<column>) that is used as a reference to a column in a SELECT list is accepted, but the table alias is ignored. Consider the following query.

SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1

When executed, the column prefix is ignored in the ORDER BY. The sort operation does not occur on the specified source column (x.c1) as expected; instead it occurs on the derived c1 column that is defined in the query. The execution plan for this query shows that the values for the derived column are computed first and then the computed values are sorted.
23 ноя 16, 07:42    [19923412]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить