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

Нашёл ли я bug в SQL-сервере или я чего-то не понимаю?

Для начала:

SELECT @@Version:

> Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Запрос:

select *
from (
SELECT s1.itemid, s1.inventlocationid, s1.restq, s1.resta,
ROW_NUMBER() OVER (PARTITION BY s1.inventlocationid, s1.itemid ORDER BY s1.RDate DESC) Rnk
FROM dbo.TTR_RestA_Copy1 AS s1 (nolock)
where s1.RDate<='2012-10-31'
) as one
WHERE Rnk = 1 and (restq<>0 or resta<>0)
and exists (select 1 from #InvLocs as il (nolock) where one.inventlocationid=il.inventlocationid)
and exists (select 1 from #Items as it (nolock) where one.itemid=it.itemid)

Возвращает 8966 записей.

А запросы, по идее, эквивалентные ему:

select * into #tmp
from (
SELECT s1.itemid, s1.inventlocationid, s1.restq, s1.resta,
ROW_NUMBER() OVER (PARTITION BY s1.inventlocationid, s1.itemid ORDER BY s1.RDate DESC) Rnk
FROM dbo.TTR_RestA_Copy1 AS s1 (nolock)
where s1.RDate<='2012-10-31'
) as one

select *
from #tmp as one
WHERE Rnk = 1 and (restq<>0 or resta<>0)
and exists (select 1 from #InvLocs as il (nolock) where one.inventlocationid=il.inventlocationid)
and exists (select 1 from #Items as it (nolock) where one.itemid=it.itemid)
order by itemid

Возвращают (2-ой запрос) 31145 записей.
30 ноя 12, 15:20    [13556538]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
Добрый Э - Эх
Guest
Не ленись пользоваться тегом SRC
MSSQLBug
Добрый день.

Нашёл ли я bug в SQL-сервере или я чего-то не понимаю?

Для начала:

SELECT @@Version:

> Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)   Dec 10 2010 10:56:29   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 

Запрос:

select *
from (
SELECT s1.itemid, s1.inventlocationid, s1.restq, s1.resta, 
       ROW_NUMBER() OVER (PARTITION BY s1.inventlocationid, s1.itemid ORDER BY s1.RDate DESC) Rnk
FROM dbo.TTR_RestA_Copy1 AS s1 (nolock)
where s1.RDate<='2012-10-31'
) as one
WHERE Rnk = 1 and (restq<>0 or resta<>0)
  and exists (select 1 from #InvLocs as il (nolock) where one.inventlocationid=il.inventlocationid)
  and exists (select 1 from #Items as it (nolock) where one.itemid=it.itemid)

Возвращает 8966 записей.

А запросы, по идее, эквивалентные ему:

select * into #tmp
from (
SELECT s1.itemid, s1.inventlocationid, s1.restq, s1.resta, 
       ROW_NUMBER() OVER (PARTITION BY s1.inventlocationid, s1.itemid ORDER BY s1.RDate DESC) Rnk
FROM dbo.TTR_RestA_Copy1 AS s1 (nolock)
where s1.RDate<='2012-10-31'
) as one

select *
from #tmp as one 
WHERE Rnk = 1 and (restq<>0 or resta<>0)
  and exists (select 1 from #InvLocs as il (nolock) where one.inventlocationid=il.inventlocationid)
  and exists (select 1 from #Items as it (nolock) where one.itemid=it.itemid)
order by itemid


Возвращают (2-ой запрос) 31145 записей.
30 ноя 12, 15:26    [13556595]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
super-code
Member

Откуда:
Сообщений: 244
MSSQLBug, помоему не один из этих запросов не возвращает записи, только копирует в другую таблицу...
30 ноя 12, 15:53    [13556893]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
MSSQLBug
Guest
super-code,
Нет, возвращают оба. ;)
30 ноя 12, 15:56    [13556913]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
Добрый Э - Эх
Guest
MSSQLBug
Bug с оконными функциями в 2005-ом?
Ты хочешь сказать, что если убрать ROW_NUMBER, то результаты запросов идентичны, а с ROW_NUMBER-ом - различаются?
30 ноя 12, 15:57    [13556920]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
MSSQLBug
Guest
Добрый Э - Эх,

В смысле? Я хочу сказать, что при сохранение и несохранении промежуточных результатов в таблицу конечный результат отличается.

Я уже убедился, что это точно Bug, т.к.:

select *
from (
SELECT s1.itemid, s1.inventlocationid, s1.restq, s1.resta, 
       ROW_NUMBER() OVER (PARTITION BY s1.inventlocationid, s1.itemid ORDER BY s1.RDate DESC) Rnk
FROM dbo.TTR_RestA_Copy1 AS s1 (nolock)
where s1.RDate<='2012-10-31'
) as one
WHERE Rnk = 1 and (restq<>0 or resta<>0)
  and exists (select 1 from #InvLocs as il (nolock) where one.inventlocationid=il.inventlocationid)
  and exists (select 1 from #Items as it (nolock) where one.itemid=it.itemid)

(8966 row(s) affected)

А с HINT-ом:

select *
from (
SELECT s1.itemid, s1.inventlocationid, s1.restq, s1.resta, 
       ROW_NUMBER() OVER (PARTITION BY s1.inventlocationid, s1.itemid ORDER BY s1.RDate DESC) Rnk
FROM dbo.TTR_RestA_Copy1 AS s1 (nolock)
where s1.RDate<='2012-10-31'
) as one
WHERE Rnk = 1 and (restq<>0 or resta<>0)
  and exists (select 1 from #InvLocs as il (nolock) where one.inventlocationid=il.inventlocationid)
  and exists (select 1 from #Items as it (nolock) where one.itemid=it.itemid)
OPTION (HASH JOIN)

(31145 row(s) affected)

Такие дела. Куда и, главное, как его report-ить?
30 ноя 12, 16:25    [13557187]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
MSSQLBug,

1. Репортить можете как только предоставите репро, воспроизводящее проблему не только у вас. Ну и как минимум, для начала выполните dbcc checkdb на своей БД.
2. Если мне не изменяет память, поддержка 2005-го прекращена.
30 ноя 12, 16:50    [13557390]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
MSSQLBug
Guest
invm,

А как бы мне для запроса:

select one.*
from (
SELECT s1.itemid, s1.inventlocationid, s1.restq,
       ROW_NUMBER() OVER (PARTITION BY s1.inventlocationid, s1.itemid ORDER BY s1.RDate DESC) Rnk
FROM TTR_RestA_Copy1 AS s1 (nolock)
where s1.RDate<='2012-10-31'
) as one 
INNER JOIN #Items3 as it (nolock) ON one.itemid=it.itemid
INNER JOIN #InvLocs2 as il (nolock) ON one.inventlocationid=il.inventlocationid 
WHERE Rnk = 1 and restq<>0
order by one.itemid


Принудительно задать вот такой план (а иначе bug не проявляется, когда данных мало)?

  |--Sort(ORDER BY:([s1].[Itemid] ASC))
       |--Hash Match(Inner Join, HASH:([it].[itemid])=([s1].[Itemid]), RESIDUAL:([AxOlap].[dbo].[TTR_RestA_Copy1].[Itemid] as [s1].[Itemid]=[tempdb].[dbo].[#Items3].[itemid] as [it].[itemid]))
            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Items3] AS [it]))
            |--Merge Join(Inner Join, MERGE:([il].[inventlocationid])=([s1].[Inventlocationid]), RESIDUAL:([AxOlap].[dbo].[TTR_RestA_Copy1].[Inventlocationid] as [s1].[Inventlocationid]=[tempdb].[dbo].[#InvLocs2].[inventlocationid] as [il].[inventlocationid]))
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#InvLocs2] AS [il]), ORDERED FORWARD)
                 |--Filter(WHERE:([Expr1002]=(1) AND [AxOlap].[dbo].[TTR_RestA_Copy1].[RestQ] as [s1].[RestQ]<>(0.0000)))
                      |--Sequence Project(DEFINE:([Expr1002]=row_number))
                           |--Compute Scalar(DEFINE:([Expr1008]=(1)))
                                |--Segment
                                     |--Clustered Index Scan(OBJECT:([AxOlap].[dbo].[TTR_RestA_Copy1].[idx_main] AS [s1]), WHERE:([AxOlap].[dbo].[TTR_RestA_Copy1].[RDate] as [s1].[RDate]<='2012-10-31 00:00:00.000'))
30 ноя 12, 17:56    [13557926]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
Добрый Э - Эх
Guest
MSSQLBug
В смысле? Я хочу сказать, что при сохранение и несохранении промежуточных результатов в таблицу конечный результат отличается.
У тебЯ тема называется " Bug с оконными функциями в 2005-ом". ПРИЧЕМ ЗДЕСЬ СОХРАНЕНИЕ И НЕСОХРАНЕНИЕ ДАННЫХ? Вот я у тебя и спрашиваю - с ROW_NUMBER и без - результат одинаков? Если нет, то при чем здесь оконные функция? Либо объясни, что есть в твоем понимание оконная функция.
30 ноя 12, 18:02    [13557977]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
MSSQLBug
А как бы мне для запроса:
Принудительно задать вот такой план

OPTION(USE PLAN N'<тут большая портянка с XML-планом>')
30 ноя 12, 18:14    [13558054]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
MSSQLBug
Guest
Добрый Э - Эх, не понял, какого запроса? Как результат разных по смыслу запросов (с ROW_NUMBER и без) может быть одинаков? Я же привожу ЭКВИВАЛЕНТНЫЕ запросы, а из моего сообщения про HINT-ы видно, что это просто баг. А оконные функции здесь могут быть и не виноваты, просто без них мне его воспроизвести пока не удалось. В моём понимании здесь оконная функция --- это ROW_NUMBER.
30 ноя 12, 21:40    [13559154]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
хм. интересно! а сделайте планы первых "эквивалентных" запросов. данные которых расходятся! было подозрение, что ранжируются разные наборы данных, но "оказалось" не так - и на 2005 и на 2008 сначала выполняется ранжирование и только после применяются остальные фильтры
30 ноя 12, 22:42    [13559490]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
Добрый Э - Эх
Guest
MSSQLBug,

твоя сортировка по "s1.RDate DESC" внутри ROW_NUMBER-а - она детерминирована? Если нет, то неудивительно, что результаты различаются: просто в зависимости от способа доступа к данным, методов и порядка соединения таблиц может меняться порядок следования строк в недетерминированной сортировке и, как следствие, - меняться набор строк, получающих в качестве Rnk значение 1.

В качестве примера:
with t (dt, num) as 
(select * from (values
               ('2012-12-01', 1),
               ('2012-12-01', 2),
               ('2012-12-01', 3),
               ('2012-12-01', 4),
               ('2012-12-01', 5),
               ('2012-12-01', 6),
               ('2012-12-01', 7),
               ('2012-12-01', 8),
               ('2012-12-01', 9)
               )v(dt,num))

select t.* , row_number() over(order by dt desc) as rnk
  from t
он-лайн проверка на sqlfiddle.com

with t (dt, num) as 
(select * from (values
               ('2012-12-01', 4),
               ('2012-12-01', 5),
               ('2012-12-01', 3),
               ('2012-12-01', 1),
               ('2012-12-01', 2),
               ('2012-12-01', 6),
               ('2012-12-01', 9),
               ('2012-12-01', 8),
               ('2012-12-01', 7)
               )v(dt,num))

select t.* , row_number() over(order by dt desc) as rnk
  from t
он-лайн проверка на sqlfiddle.com

В обоих случаях используется (по сути) один и тот же набор данных. НО!!!! Так как сортировка недетерминирована, MS SQL Server возвращает разный (в плане упорядоченности) результат. Как следствие - в первом случае rnk=1 получит запись с num=1, а во втором случае - с num=4.
И вот теперь, если накладывать дополнительные условия на выборку в виде where rnk = 1 and num = 1, то первый запрос вернет строку, а второй - нет. И это не баг. И к оконным функциям ЭТО отношения не имеет. Соль проблемы - недетерминированность сортировки.
1 дек 12, 05:13    [13560843]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
MSSQLBug
Guest
Добрый Э - Эх, нет, по-моему, соль проблемы --- в баге в MSSQL Server 2005. ;) Предположение интересное, но дело в том, что сортировка детерминирована, так как на TTR_RestA_Copy1 есть уникальный кластерный индекс:
CREATE UNIQUE CLUSTERED INDEX idx_main ON TTR_RestA_Copy1(inventlocationid, itemid, RDate DESC)

Хотелось бы сделать из этого что-то воспроизводимое, чтобы можно было проверить на других версиях MSSQL. Возможно, в понедельник я этим займусь.
1 дек 12, 22:52    [13562892]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
Crimean
Member

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

а вы планчики пока покажите таки
1 дек 12, 23:01    [13562919]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
MSSQLBug
Guest
Crimean, подождите до понедельника, на работу приду и тогда выложу.
1 дек 12, 23:14    [13562941]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
MSSQLBug
Guest
Crimean, вот запросы и планы:
select *
from (
SELECT itemid, inventlocationid, restq,
       ROW_NUMBER() OVER (PARTITION BY inventlocationid, itemid ORDER BY RDate DESC) Rnk
FROM TTR_RestA_Copy1 (nolock)
where RDate<='2012-10-31'
) as one
WHERE Rnk = 1 and restq<>0
  and exists (select 1 from InvLocs as il (nolock) where one.inventlocationid=il.inventlocationid)
  and exists (select 1 from Items as it (nolock) where one.itemid=it.itemid)
order by itemid


  |--Sort(ORDER BY:([AxOlap].[dbo].[TTR_RestA_Copy1].[Itemid] ASC))
       |--Hash Match(Inner Join, HASH:([it].[itemid])=([AxOlap].[dbo].[TTR_RestA_Copy1].[Itemid]), RESIDUAL:([AxOlap].[dbo].[TTR_RestA_Copy1].[Itemid]=[AxOlap].[dbo].[Items].[itemid] as [it].[itemid]))
            |--Clustered Index Scan(OBJECT:([AxOlap].[dbo].[Items].[idx1] AS [it]))
            |--Merge Join(Inner Join, MERGE:([il].[inventlocationid])=([AxOlap].[dbo].[TTR_RestA_Copy1].[Inventlocationid]), RESIDUAL:([AxOlap].[dbo].[TTR_RestA_Copy1].[Inventlocationid]=[AxOlap].[dbo].[InvLocs].[inventlocationid] as [il].[inventlocationid]))
                 |--Clustered Index Scan(OBJECT:([AxOlap].[dbo].[InvLocs].[idx1] AS [il]), ORDERED FORWARD)
                 |--Filter(WHERE:([Expr1003]=(1) AND [AxOlap].[dbo].[TTR_RestA_Copy1].[RestQ]<>(0.0000)))
                      |--Sequence Project(DEFINE:([Expr1003]=row_number))
                           |--Compute Scalar(DEFINE:([Expr1011]=(1)))
                                |--Segment
                                     |--Clustered Index Scan(OBJECT:([AxOlap].[dbo].[TTR_RestA_Copy1].[idx_main]), WHERE:([AxOlap].[dbo].[TTR_RestA_Copy1].[RDate]<='2012-10-31 00:00:00.000'))


select *
from (
SELECT itemid, inventlocationid, restq,
       ROW_NUMBER() OVER (PARTITION BY inventlocationid, itemid ORDER BY RDate DESC) Rnk
FROM TTR_RestA_Copy1 (nolock)
where RDate<='2012-10-31'
) as one
WHERE Rnk = 1 and restq<>0
  and exists (select 1 from InvLocs as il (nolock) where one.inventlocationid=il.inventlocationid)
  and exists (select 1 from Items as it (nolock) where one.itemid=it.itemid)
order by itemid
OPTION (MERGE JOIN)


  |--Sort(ORDER BY:([AxOlap].[dbo].[TTR_RestA_Copy1].[Itemid] ASC))
       |--Merge Join(Inner Join, MERGE:([il].[inventlocationid])=([AxOlap].[dbo].[TTR_RestA_Copy1].[Inventlocationid]), RESIDUAL:([AxOlap].[dbo].[TTR_RestA_Copy1].[Inventlocationid]=[AxOlap].[dbo].[InvLocs].[inventlocationid] as [il].[inventlocationid]))
            |--Clustered Index Scan(OBJECT:([AxOlap].[dbo].[InvLocs].[idx1] AS [il]), ORDERED FORWARD)
            |--Sort(ORDER BY:([AxOlap].[dbo].[TTR_RestA_Copy1].[Inventlocationid] ASC))
                 |--Merge Join(Inner Join, MERGE:([it].[itemid])=([AxOlap].[dbo].[TTR_RestA_Copy1].[Itemid]), RESIDUAL:([AxOlap].[dbo].[TTR_RestA_Copy1].[Itemid]=[AxOlap].[dbo].[Items].[itemid] as [it].[itemid]))
                      |--Clustered Index Scan(OBJECT:([AxOlap].[dbo].[Items].[idx1] AS [it]), ORDERED FORWARD)
                      |--Sort(ORDER BY:([AxOlap].[dbo].[TTR_RestA_Copy1].[Itemid] ASC))
                           |--Filter(WHERE:([Expr1003]=(1) AND [AxOlap].[dbo].[TTR_RestA_Copy1].[RestQ]<>(0.0000)))
                                |--Sequence Project(DEFINE:([Expr1003]=row_number))
                                     |--Compute Scalar(DEFINE:([Expr1011]=(1)))
                                          |--Segment
                                               |--Clustered Index Scan(OBJECT:([AxOlap].[dbo].[TTR_RestA_Copy1].[idx_main]),  WHERE:([AxOlap].[dbo].[TTR_RestA_Copy1].[RDate]<='2012-10-31 00:00:00.000') ORDERED FORWARD)


А насчёт воспроизведения --- есть проблема с использованием USE PLAN, при попытке применения со вторым планом всё нормально, а именно с ПЕРВЫМ планом выдаёт:
Msg 8698, Level 16, State 0, Line 1
Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. 
Remove or replace USE PLAN hint. 
For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.


Опять-таки вопрос: как воспроизвести этот bug? Потому что в TTR_RestA_Copy1 29 085 459 записей, а если пытаюсь сильно обрезать её, план получается другой.
3 дек 12, 11:02    [13567093]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
Читатель неместный
Guest
не все прочитал)
но проблема думаю в том что сочетание полей inventlocationid, itemid, RDate неуникальны
в этом случае
ROW_NUMBER() OVER (PARTITION BY inventlocationid, itemid ORDER BY RDate DESC)
будет нумеровать в случайном порядке.

В обеих случаях возвращаются правильные (возможно разные) данные
3 дек 12, 11:51    [13567444]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
MSSQLBug
Guest
Проверил на MS SQL 2008, тоже воспроизводится.
SELECT @@version

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)   Apr  2 2010 15:53:02   Copyright (c) Microsoft Corporation  Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) 
3 дек 12, 11:51    [13567445]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
MSSQLBug
Guest
Читатель неместный, нет, см. 13562919.
3 дек 12, 11:53    [13567462]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
Добрый Э - Эх
Guest
Читатель неместный,

такое предположение я уже делал.
Автор утверждает, что сортировка вполне себе детерминированна, так как:
MSSQLBug
Добрый Э - Эх, нет, по-моему, соль проблемы --- в баге в MSSQL Server 2005. ;) Предположение интересное, но дело в том, что сортировка детерминирована, так как на TTR_RestA_Copy1 есть уникальный кластерный индекс:
CREATE UNIQUE CLUSTERED INDEX idx_main ON TTR_RestA_Copy1(inventlocationid, itemid, RDate DESC)
3 дек 12, 11:54    [13567467]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
Добрый Э - Эх
Guest
MSSQLBug
Читатель неместный, нет, см. 13562919.
ты промахнулся с собственным цитированием ;)
3 дек 12, 11:55    [13567480]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
MSSQLBug
Guest
Читатель неместный, ошибся ссылкой, 13562892. ;)
3 дек 12, 11:55    [13567484]     Ответить | Цитировать Сообщить модератору
 Re: Bug с оконными функциями в 2005-ом?  [new]
Читатель неместный
Guest
MSSQLBug
Читатель неместный, ошибся ссылкой, 13562892. ;)
ну теперь то я дочитал))
3 дек 12, 12:01    [13567532]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить