Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
o-o
Guest |
навеяно вот этой темой: баг или фича?
не хочу здесь разводить голосование по поводу, считать ли "неоднозначность поведения сервера" багом или оспаривать очевидное
но ведь дело все же не в the variable is assigned the last value that is returned, так? я вообще в своем примере не буду использовать значения, хранящиеся в таблице. просто предлагаю обсудить то, что всего 1 раз опубликовал MS, и даже не считают нужным обновлять версии серверов, к к-ым это применимо (а ведь применимо же не только к 2005). я молчу, что их "объяснение" -- это просто тыканье носом, на что обратить внимание (order by). т.е. по факту объяснения нет. репро: declare @s int = 0; select @s = @s + 1 from dbo.nums; select @s; --- --1000000 vs declare @s int = 0; select @s = @s + 1 from dbo.nums -- order by n; -- no sort at all [PK_nums_n] order by n * 2 + 3; -- sort!!! select @s; --- --1 картинка: К сообщению приложен файл. Размер - 74Kb |
||||||||||
9 апр 15, 10:45 [17492480] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
единственное объяснение, что мне удалось отловить, оказалось от участника нашего же форума, непонятно почему только оно лежит на совсем другом форуме. захотелось поделиться в явном виде (не просто ссылкой) и услышать мнение SomewhereSomehow по этому поводу. ну и, возможно, поставить точку в еще одном "misterious behaivior" :) оригинал сообщения тут К сообщению приложен файл. Размер - 38Kb |
9 апр 15, 10:49 [17492502] Ответить | Цитировать Сообщить модератору |
мимо
Guest |
Да вроде всё понятно, сервер строит некий план выполнения запроса и выполняет его. В БОле тоже расписано, что так агрегировать нельзя (одно значение присваивается) . Другое дело: надо ли генерить ошибку, если возвращается более одного значения? Чтоб избежать неоднозначностей. if OBJECT_ID('tempdb..#t') is not null drop table #t; select top 100 number into #t from spt_values as s where s.type = 'P'; go declare @s bigint = 0; select @s = @s + 1 from #t; select @s; go declare @s bigint = 0; select @s = @s + 1 from #t -- order by n; -- no sort at all [PK_nums_n] order by number * 2 + 3; -- sort!!! select @s; go declare @s bigint = 0; select @s = @s + 1 from ( select top 100000 * from #t ) z -- order by n; -- no sort at all [PK_nums_n] order by number * 2 + 3; select @s; go declare @s bigint = 0; select @s = @s + 1 from ( select top 100000 * from #t ) z order by number * 2 + 3 option (force order); select @s; go if OBJECT_ID('tempdb..#t') is not null drop table #t; |
9 апр 15, 11:28 [17492715] Ответить | Цитировать Сообщить модератору |
0-0
Guest |
o-o, пример у вас искусственный уж очень Но интересно услышать мнение SomewhereSomehow |
9 апр 15, 11:38 [17492781] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
1. выполните мой первый кусок кода (без ) и убедитесь в неверности утверждения "одно значение присваивается" 2. покажите, где в БОЛ пишут "так агрегировать нельзя" еще раз уточню, если может это не было явно озвучено: SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned. относится к конструкции select @s = n from tab -- где n -- имя колонки таблицы tab а про select @s = @s +... -- @s = local variableвообще ничего не написано, или я плохо ищу? в связи с чем еще раз процитирую:
|
||||
9 апр 15, 11:42 [17492815] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
0-0, именно что пример ни при чем, а при чем только SORT и SomewhereSomehow по возможности :) мне никаким боком не сдалось жерезж-ое агрегирование, о чем сказано в первом посте. я хочу разобраться, сделал ли погоду именно SORT |
9 апр 15, 11:45 [17492841] Ответить | Цитировать Сообщить модератору |
SomewhereSomehow Member Откуда: Moscow Сообщений: 2480 Блог |
Ребят, тему читаю, дебаггер подключаю, картинки для поста нарезаю - немного потерпите, ведь я еще и на работе =) |
9 апр 15, 11:59 [17492922] Ответить | Цитировать Сообщить модератору |
мимо
Guest |
o-o, план надо смотреть. Все ж пишут, скл - декларативный язык. Процесс скрыт - главное результат. Вот если результат не "бьет" - тогда бага. А есть ордер, нет ордера - какая разница, важно куда он(оптимизатор) его запихнет. Ещё раз: if OBJECT_ID('tempdb..#t') is not null drop table #t; select top 100 number into #t from spt_values as s where s.type = 'P'; go declare @s bigint = 0; select top 10000 @s = @s + 1 from #t order by number * 2 + 3 ; select @s; if OBJECT_ID('tempdb..#t') is not null drop table #t; Насчет того, что нельзя так агрегировать: программисты - они ж не юристы, у них что задокументировано - то можно, а что нет - то значит нельзя. Т.е. что не запрещено, то разрешено - тут не катит. |
9 апр 15, 12:09 [17492997] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
вот же блин, глаза у меня есть, план я вижу, а вы??? на картинке что, не видно, что в первом случае СОРТА ПРОСТО НЕТ??? и в ответе, о чудо, то, что и ожидалось. у меня закомментирован даже order by n, к-ый не приведет к появлению сорта в плане, (у меня и без того кластерный по n) ладно, все, я жду ответ SomewhereSomehow |
||
9 апр 15, 12:18 [17493046] Ответить | Цитировать Сообщить модератору |
Заинтересовался
Guest |
Пример достаточно искусственный, учитывая странный вид выражения в order by. Но действительно показывает, что подобный метод агрегирования может приводить к ошибкам. Можно ли провести пример (если он есть) с аналогичной ошибкой для конкатенации строк (где это метод реально часто используют)? |
9 апр 15, 13:02 [17493316] Ответить | Цитировать Сообщить модератору |
мимо
Guest |
Чё кипятиться? Я к тому, что важно, где будет компьютскаляр. Если в конце , т.е. перед селектом, то посчитает сумму(в независимости от наличия или отсутствия ордера, о примере надо подумать). Ежели нет - то как фишка ляжет. А так да, все ждем SomewhereSomehow. |
||||
9 апр 15, 13:44 [17493673] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
да конечно давайте спокойно. вы считаете, что важна позиция COMPUTE SCALAR vs SELECT, я считаю, что важна позиция COMPUTE SCALAR vs SORT. нету сорта -- нет проблемы или приведите пример того, с чем еще можно переставить COMPUTE SCALAR, чтобы получился неверный результат. у меня не зря пример выбран такой, что его план проще нЕкуда. ну не с чем переставлять COMPUTE SCALAR в отсутствии SORT. мне казалось, это очевидно... |
||
9 апр 15, 13:53 [17493728] Ответить | Цитировать Сообщить модератору |
мимо
Guest |
o-o, Про расчет скаляра, несколько не в тему. Со скаляром, малость, не всё понятно |
9 апр 15, 14:12 [17493822] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
мимо, спсб, но в том плане куча итераторов, так что действительно, есть возможность вычислить выражение когда угодно, раз свобода выбора предоставлена, начиная с 2005. на deffered scalar evaluation как раз SomewhereSomehow недавно мне глаза и открыл: 17471054 меня этим уже и не удивишь :) но в данном-то случае еще раз "внимание на экран" (см. мою картинку): разница лишь в SORT-е попробуйте еще чем-то вызвать этот эффект, не добавляя в запрос ничего, что в план сортировку добавит. у меня не вышло, а я на слово не верю ни за что и никогда :) |
9 апр 15, 14:50 [17494045] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9633 |
Еще раз: 1. Для каждой строки результирующего набора построчно а) вычисляется @s + 1; б) результат из а присваивается @s. В противном случае никакого агрегирования бы не было. 2. Если между а и б будет любой блокирующий оператор (в данном случае это sort), выражение @s + 1 будет вычислено для всех строк набора с одним и тем же значением @s до какого-либо присваивания в @s. |
9 апр 15, 15:06 [17494166] Ответить | Цитировать Сообщить модератору |
Заинтересовался
Guest |
Можно привести пример неправильной работы агрегации через переменную без наличия order by? |
||
9 апр 15, 15:24 [17494287] Ответить | Цитировать Сообщить модератору |
SomewhereSomehow Member Откуда: Moscow Сообщений: 2480 Блог |
Я честно говоря как-то пропустил тему, а про другой форум вообще не знаю, но invm, абсолютно правильно объяснил физику процесса. Кому лень читать дальше – можно остановиться тут. Но насколько я понял, в ответе на вопрос требуется тяжелая артиллерия, не в виде меня конечно, а в виде дебаггера, чтобы народ знал, что так оно и есть на самом деле (в данной версии сервера – я привожу результаты для 2014 CU1). Предлагаю, для начала, упростить репро до нескольких строк (это тот случай, где можно это сделать). Кроме того, сделаем репро интересней и загадочней =)
Как видно из результатов, комбинации самые разные (ниже будут даны объяснения каждого из примеров). Давайте сформулируем вопросы. 1. Влияет ли оператор сортировки на результат 2. Если влияет, то как 3. Влияет ли предложение ORDER BY на появления оператора сортировки 1. Из планов, можно заметить, что само по себе наличие сортировки в плане не обязательно влияет на результат (Запросы 4 и 5), но влияет последовательность операторов. В планах, где агрегация происходит, вычисление скаляра идет строго после сортировки, в планах где нет агрегации, сначала (читаем план справа налево или снизу вверх) идет вычисление скаляра, потом сортировка. Ответ на этот вопрос – сама по себе сортировка не обязательно приведет к однозначному результату, влияет последовательность действий при выполнении. Может ли быть так, что вычисление скаляра идет после сортировки, но результат не агрегирован. Иными словами, может ли план номер 4 выдать другой результат. Может из-за того, что для скалярных выражений место их выполнения не определено в плане и в более сложном плане – это возможно.
Вот еще пример, почему не стоит полагаться на положение Compute Scalar. declare @s int; set @s = 0; select @s = @s + 1 from dbo.nums order by n * 2 + 3; select @s; set @s = 0; select top(10) @s = @s + 1 from dbo.nums order by n * 2 + 3; select @s; В первом случае результат 1, во втором 4. Т.к. во втором случае, сервер решил сначала вычислить выражение n*2+3 (поскольку мы же запросили top с сортировкой, т.е. он заранее должен вычислить выражение, по которому отбирает top), потом отсортировать и потом заняться переменной. Можно еще понапридумывать разных примеров, где результат будет неожиданным. 2. Как и почему влияет положение сортировки. Как уже было сказано invm, Sort блокирующий итератор, но что это значит. Чтобы это продемонстрировать, давайте возьмем дебаггер. Поставим точку останова в дебаггере на Clustered Index Scan и конкретно метод, получающий строку (это, в данном случае (есть еще несколько), метод sqlmin!CQScanTableScanNew::GetRow). Выполним два запроса, первый и второй. Первый ![]() Запрашивающий итератор CQueryScan – спрашивает строку (GetRow), это напрямую транслируется в итератор CQScanTableScanNew, который также запрашивает строку, далее, Storage Engine делает еще много интересных вещей (блокировки, латчи и т.д.), но нас это не интересует. Мы видим поток: ![]() Второй: ![]() Видите, у сортировки нет пока GetRow, вызов метода будет позже, когда сортировка прочитает все данные из нижестоящего оператора, у которого она сама вызывает GetRow. Это и есть понятие «блокирующий», т.е. если кто-то спрашивает сортировку – «дай строку», то она спрашивает нижестоящие операторы, сортирует, но на время сортировки блокирует того, кто ее вызывал. Графически представить можно так: ![]() Где же Compute Scalar в дебаггере, можно спросить, и ответ будет как раз – то самое отложенное выполнение. Он может выполнится в любом операторе (который это поддерживает), и это (выполнение) не обязательно будет отражено в плане. 3. Предложение ORDER BY влияет на требование сортировки. Без него, сортировка не гарантирована. Но что важно в данном случае, что требование сортировки заставляет оптимизатор искать наиболее дешевый способ выполнения сортировки. А нет более дешевого способа, чем просканировать индекс в порядке ключа индекса и получить отсортированный набор. Если мы используем подсказку with(index(0)) – не многие знают – что даже если есть кластерный индекс, форсируется сканирование по цепочкам IAM, а значит после этого требуется сортировка. Но в случае 4, она идет перед вычислением скаляра, т.е. строки сначала читаются из индекса, потом сортируются, а потом идет накопление в переменную. TF 9259 – почему если включен этот trace flag, то не происходит накопления? Это влияние упрощения запроса. Начиная с 2005 сервера в упрощении запроса появляется такая стадия как нормализация проекций (проекция – проектирование из множества (таблицы/индекса) в результат). Этот флаг отключает фазу нормализации. И скаляр остается там, где он был согласно дереву запроса, не нормализуется и не перемещается, вычисляется перед сортировкой и не является накапливающим. Резюмируя 1. Влияет ли оператор сортировки на результат Да, влияет. Но влияет его положение. При этом, может быть так, что даже при условно «правильной» последовательности итераторов – результат будет отличный от ожидаемого из-за отложенного выполнения скалярных операторов. 2. Если влияет, то как Сортировка заставляет сервер сначала прочитать все строки и отсортировать их, в том числе, выполнив присвоение переменной и только потом начать отдавать строки. 3. Влияет ли предложение ORDER BY на появления оператора сортировки Влияет, но могут сервер может выбирать как сделать сортировку, сканируя ли индекс, либо предоставляя явный оператор сортировки. Какие еще могут быть неожиданные случаи без ORDER BY, где нельзя предугадать результат: - Nested Loop Join – Optimized property – неявная Batch сортировка - Оконные функции – могут сортировать как удобно без явного order by во внешнем запросе - Index Spool – сортирует в порядке ключа индекса, абсолютно неважно наличие ORDER BY – является внутренней оптимизацией - Параллельные планы, множество вариантов, начиная от Repartitioning Hash и заканчивая сортирующим Gather Streams. - Локальная-глобальная агрегация – то же что и с параллельными планами - Выбор индекса – определяющий факто стоимость, покрывающий индекс, но требующий сортировки, может быть выгоднее не покрывающего без нее - Уникальность индекса - Предложение TOP, EXISTS, FAST N - Миллион причин о которых я забыл или еще не знаю. Holly war Ввязываться в холивар про "документировано" или нет – не хочу. С одной стороны, вроде есть KB который описывает это поведение, с другой, читает ли их юзер, так ли их легко найти? Сами себе ответьте – пока не столкнетесь с проблемой – будете читать все KB? Конечно кто-то читает (например, я и многие другие тут на форуме, причем читаем по каждому CU или SP), ну а кто не так вовлечен в сиквел? Их это вина, или вина слабой документации. Кто использует поиск по интернет документации в BOL? Я лично – ничего так не нахожу – точнее нахожу массу нерелевантных статей. Для меня лучший способ это гугл, спасибо MS что все индексируется. Тема для холивара – конечно. Но участвовать и комментировать не хочу. Просто не пользуйтесь этим способом =) |
||||||||||||||||||||||||||||
9 апр 15, 15:41 [17494394] Ответить | Цитировать Сообщить модератору |
SomewhereSomehow Member Откуда: Moscow Сообщений: 2480 Блог |
SomewhereSomehow, Пятый пример не репрезентативный, в данном репро не влияет, но вообще - влиять может. Надо экспериментировать с вычисляемыми колонками. |
9 апр 15, 15:58 [17494501] Ответить | Цитировать Сообщить модератору |
Гадя Петрович Member Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали Сообщений: 52912 |
SomewhereSomehow, шикарно! Почти готовая статья :) |
9 апр 15, 15:59 [17494511] Ответить | Цитировать Сообщить модератору |
SomewhereSomehow Member Откуда: Moscow Сообщений: 2480 Блог |
Гадя Петрович, Да, только куча опечаток =) Ну и c TF 9259 получилось не совсем то, что хотел сказать. Что-то мне подумалось, что на простом примере можно показать, что может быть так: ![]() Сейчас понимаю, что надо репро посложнее, но не переписывать же весь пост =) Просто помним, что скаляры могут перемещаться и упрощаться и такое может быть - лишний повод - не пользоваться таким способом. |
9 апр 15, 16:10 [17494560] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
лучше по делу, но с опечатками, чем литературный трактат ни о чем, это же очевидно :) так что не надо переживать по этому поводу. огромное спасибо за потраченное время. и invm спасибо, хоть и не нам адресовалось. если проблема в отдельных хамящих личностях, то я буду на место ставить, не надо нас вашими объяснениями обделять, наш форум все равно лучше :) |
||
9 апр 15, 16:22 [17494635] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
пример без искусственного order by и без хинта, просящего насильно выбрать такой-то индекс: просто нужно добавить индекс гораздо уже того, что сейчас используется в запросе. т.е. если у нас кластерный индекс в нужном нам порядке, но таблица широченная, появление узкого индекса с нужными полями, но с "неподходящей" сортировкой, приведет к смене плана и неверному результату (в плане появится сорт между select и compute scalar). пример взят из книги T-SQL Querying поле filler олицетворяет кучу не интересующих нас полей, ну и заполняю таблицу для улучшения воспроизводимости не через dbo.GetNums(), а через row_number() USE tempdb; IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL DROP TABLE dbo.T1; CREATE TABLE dbo.T1 ( col1 INT NOT NULL IDENTITY, col2 VARCHAR(100) NOT NULL, filler BINARY(2000) NULL DEFAULT(0x), CONSTRAINT PK_T1 PRIMARY KEY(col1) ); INSERT INTO dbo.T1(col2) SELECT top 100 'String ' + CAST(row_number()over(order by @@version) AS VARCHAR(10)) FROM sys.all_columns AS Nums; DECLARE @s AS VARCHAR(MAX); SET @s = ''; SELECT @s = @s + col2 + ';' FROM dbo.T1 ORDER BY col1; PRINT @s; CREATE NONCLUSTERED INDEX idx_nc_col2_i_col1 ON dbo.T1(col2, col1); GO DECLARE @s AS VARCHAR(MAX); SET @s = ''; SELECT @s = @s + col2 + ';' FROM dbo.T1 ORDER BY col1; PRINT @s; К сообщению приложен файл. Размер - 83Kb |
||
27 ноя 15, 16:47 [18482596] Ответить | Цитировать Сообщить модератору |
o-o
Guest |
К сообщению приложен файл. Размер - 125Kb |
27 ноя 15, 16:47 [18482597] Ответить | Цитировать Сообщить модератору |
Между сообщениями интервал более 1 года. |
BERSERC Member Откуда: Сообщений: 442 |
Удалось ли кому-то получить неверные результаты при использовании TOP (SELECT 100) ?declare @s int; set @s = 0; SELECT TOP (SELECT 100) percent @s = @s + 1 from dbo.nums order by n* 2 + 3; select @s; насколько я понимаю такая конструкция гарантированно заставляет сначала отработать сортировку а уже потом скаляр. |
18 апр 17, 14:54 [20410974] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9633 |
|
||
18 апр 17, 16:30 [20411454] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |