Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 select @x = column + order by substring  [new]
Stimo
Member

Откуда: Leeds
Сообщений: 814
Здравствуйте.
Кто-нибудь может это объяснить:

declare @t table (i char(1))
declare @x varchar(50)

insert into @t
select 'a'
union all
select 'b'
 
set @x = ''
select @x = @x + i
from @t
order by i desc
 
select @x as correct
 
set @x = ''
select @x = @x + i
from @t
order by substring(i, 1, 1) desc

select @x as incorrect
26 сен 09, 13:17    [7711295]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
iljy
Member

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

"корректное" поведение нигде не документировано. Утверждается только, что использоваться должно последнее значение, про остальное ни слова. А объяснить могут планы запросов. Для сборки строки используйте FOR XML
declare @t table (i char(1))
declare @x varchar(50)

insert into @t
select 'a'
union all
select 'b'
 
set @x = (select '' +i
              from @t
              order by substring(i, 1, 1) desc
              for xml path(''))
select @x
26 сен 09, 13:22    [7711300]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
iljy
"корректное" поведение нигде не документировано. Утверждается только, что использоваться должно последнее значение, про остальное ни слова.
Или просто: тупое надувательство народа или баг. Третьего не дано.
Должно быть или декларативно, т.е. переменная "до" и "после" никак не связана или полноценно поддерживать до конца изменение переменных рантайм.

iljy
А объяснить могут планы запросов.
Нифига:
-- SET SHOWPLAN_XML ON
DECLARE	@Table TABLE (
	 ID	SmallInt IDENTITY
	,Data	Char(1)
)insert	@Table	SELECT	'a'
UNION ALL	SELECT	'c'
UNION ALL	SELECT	'b'
DECLARE	@Data	VarChar(50)

SET	@Data = ''
SELECT	@Data = X.X
FROM	@Table T CROSS APPLY (SELECT @Data + T.Data AS X)X
SELECT	@Data

SET	@Data = ''
SELECT	@Data = @Data + Data
FROM	@Table
SELECT	@Data
Главное интересно, как интерпретить это (на 2008)?:
SET	@Data = ''
SELECT	@Data += Data
FROM	@Table
ORDER	BY SubString(Data,1,1)
SELECT	@Data
Ладно, вот ещё для понимания работы:
SET	@Data = ''
UPDATE	T
SET	@Data += X.Data
FROM	     @Table T
	JOIN @Table X ON X.Data = @Data + T.Data
SELECT	@Data
А вот с работающим ордером:
SET	@Data = ''
SELECT	@Data = @Data + T.Data
FROM	@Table T JOIN (SELECT NULL X) X ON T.Data = T.Data
ORDER	BY SubString(T.Data,1,1)
SELECT	@Data

Короче, как не крути баг, баг, баг. А в BOLе они могут писать что "мы ничего не гарантируем".

А если вспомнить про табличные переменные, что они иногда тупят (в лупе делают фул скан по кластеру и потом связывают по PK - идиотизм) ... Вот никак не могу сделать простой тестовый пример, блин, сложный и времени не хватает.
29 сен 09, 18:38    [7721965]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Glory
Member

Откуда:
Сообщений: 104760
Mnior

Короче, как не крути баг, баг, баг. А в BOLе они могут писать что "мы ничего не гарантируем".

Баг -это когда декларируется одно поведение, а на практике получается другое поведение
Вы нашли где нибудь декларацию хоть какого то поведения для SELECT ?

Сообщение было отредактировано: 29 сен 09, 19:45
29 сен 09, 19:40    [7722154]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Mnior
А если вспомнить про табличные переменные, что они иногда тупят (в лупе делают фул скан по кластеру и потом связывают по PK - идиотизм) ... Вот никак не могу сделать простой тестовый пример, блин, сложный и времени не хватает.

Ну дык, предполагается (почему-то) что в тейбл-варе будет немного строк.
И статистика по ним считается немного иначе, чем по временным таблицам.
обычнно option(recompile) спасает от "тупления" с тейб-варами.
29 сен 09, 19:43    [7722160]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Glory
Баг -это когда декларируется одно поведение, а на практике получается другое поведение
Вы нашли где нибудь декларацию хоть какого то поведения для SELECT ?
Вот вот, сначала есть реализация, в ней туева хуча багов, а потом выходит БОЛ где эти баги описываются как фича или хуже "мы ничего не гарантируем", т.е фактически, есть белое пятно в описании поведения.
А потом обратная совместимость багов, ни дай бог хотябы один баг исправиться.
Но чётко видно, что было специально (!) дополнительно реализовано "инкрементальное" поведение, подстроен генератор планов в убыток производительности (хоть и небольшой) и в итоге эта вся хренатень не работает полноценно!
Не та ситуация, чтоб бумажечками прикрываться. Фактически в боле они официально констатировали - мы накосячили, но фичу оставим. Т.е. это задокументированный баг.

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

И главное выходит уже N-ая версия (с APPLY, OVER, OUTPUT, MERGE, подкрученным и оптимизированным генератором запросов), а корыто всё тоже и сподвижек ваще не предвидется. И главное, как убрать фичу, так и исправить - проблем не составляет никаких. При условии конечно, что разработчики ядра живы, работают на том же месте и не разрабатывают совершенно новое ядро с нуля.

locky
Ну дык, предполагается (почему-то) что в тейбл-варе будет немного строк.
И статистика по ним считается немного иначе, чем по временным таблицам.
обычнно option(recompile) спасает от "тупления" с тейб-варами.
Да, да, да, но option не спасает. Скока там небыло строк, как бы ни отсутствовала статистика, но в 99% он выбирает seek по PK, когда это и так не нужно на 3 строчках, но пи..ть, при большом плане запроса он прилупливает в самом конце(!) эту долбаный кластерный индекс уже со сканом. Не на сколько seek тормознее, чтоб считать, что на скане можно заметно выиграть. Ладно, это фигня очень редкая и обходная, во всяком случае понятно, что проблема скорее в математике, чем в программировании.

Хорошо, что хоть для MERGE при INSERT в OUTPUT клаузе можно писать не только INSERTED колонки, но и любые из FROM, а ещё можно свободно обновлять с ним вьюхи на которых висит INSTEAD OF. Невероятная сказка какаято, в чём подвох то ?

Вот такой он слоёный тортик MSSQL - кое где и говнецом пахнет, а кое где и вкусный.
29 сен 09, 23:17    [7722585]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Mnior,

репро было бы неплохо, ибо я слабо представляю себе, зачем оптимизатору делать два джойна с тейбл варом, указанным один раз, тем более - со сканом.
Там ведь не может быть в теории BL - pk на тейбл варах кластерный.
30 сен 09, 00:26    [7722683]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Glory
Member

Откуда:
Сообщений: 104760
Mnior
Glory
Баг -это когда декларируется одно поведение, а на практике получается другое поведение
Вы нашли где нибудь декларацию хоть какого то поведения для SELECT ?
Вот вот, сначала есть реализация, в ней туева хуча багов, а потом выходит БОЛ где эти баги описываются как фича или хуже "мы ничего не гарантируем"


БОЛ _ничего_ не описывает про такую конструкцию SELECT.
Если вы нашли, что описывает, то дайте ссылку
Если ссылки нет, то не кричите, что это баг, тем более задокументированный
30 сен 09, 10:00    [7723175]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
locky
зачем оптимизатору делать два джойна с тейбл варом, указанным один раз, тем более - со сканом.
Что то я невнятно описал, джоин один раз, но скановый, по PK.
- Nested Loops; Predicate @T.PK = X.Col
-- <Мега подплан> ... X.Col ...
-- Clustered Index Scan @T.PK__#...

А нуно как обычно:
- Nested Loops
-- <Мега подплан> ... X.Col ...
-- Clustered Index Seek @T.PK__#...; Predicate @T.PK = X.Col

locky
Там ведь не может быть в теории BL - pk на тейбл варах кластерный.
Планы показывают, поведение генератора планов вроде понятны (кроме этих некоторых случаев).
30 сен 09, 11:40    [7723728]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Glory
БОЛ _ничего_ не описывает про такую конструкцию SELECT.
Mnior
фактически, есть белое пятно в описании поведения.
Функционал есть, а описания нет.

BOL это хелп и не более. Он может быть не полным, он может не соответствовать реальному поведению и сказать больше нельзя, это не баг сервера и не баг BOL-а, это просто не соответствие, понятия "баг" и "BOL" никак не связаны.
Нигде не описывается что 3 + 6 будет 9, так что если где-то сумма не будет работать как предполагалось, то можно и кричать про BOL, но это будет критический баг. Где сказано что сервер не должен валится в дамп на ровном месте? И никто не кричит про BOL, исправляют.
Так что это баг, логический или в программировании но баг.

Хотя кто кричит :) , тут 3 человека, а смотрят топик не более 2х сот.

Вот показывал пример:
SET	@Data = ''
SELECT	@Data = @Data + X.Data
FROM	     @Table T
	JOIN @Table X ON X.Data = @Data + T.Data
SELECT	@Data
В BOL написано, что присваивается последнее значение, а он мне заинкрементил. Видно что значение @Data в строчке с JOIN отличается от значения @Data в строке SELECT после равно, переменная одна, а значений одновременно два и в плане явно видно.
30 сен 09, 14:55    [7725044]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
Я думаю, этот баг давно бы поправили путем запрещения использования.
Слишком много заморочек на уровне синтаксиса и зависимостей результата от плана выполнения.
Но ведь потом те, кто "воспользовался" этой "фичей", сами начали бы кричать, что новая версия сервера несовместима со старой базой.
30 сен 09, 15:04    [7725102]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Glory
Member

Откуда:
Сообщений: 104760
Mnior
Glory
БОЛ _ничего_ не описывает про такую конструкцию SELECT.
Mnior
фактически, есть белое пятно в описании поведения.
Функционал есть, а описания нет.

Раз нет описания, то значит такого функционала нет. Несерьезно рассуждать о том, что что-то работает неправильно, если это что-то вообще не должно работать.

Mnior

Нигде не описывается что 3 + 6 будет 9, так что если где-то сумма не будет работать как предполагалось, то можно и кричать про BOL, но это будет критический баг. Где сказано что сервер не должен валится в дамп на ровном месте?

Не надо утрировать. В хелпе написано, что при операции + результат должен соответствовать арифметическому сложению. И когда вы найдете такие параметры, при которых вы не получите такого результата или сервер "будет валиться в дамп", то это и будет багом
В хелпе не должно быть описания ненормального поведения сервера. Это абсурд. Потому что там описывается как раз нормально поведение, а все остальное может считаться багом
30 сен 09, 15:16    [7725179]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Glory
Раз нет описания, то значит такого функционала нет.
Несерьезно рассуждать о том, что что-то работает неправильно, если это что-то вообще не должно работать.
Несерьёзно закрывать глаза на реальные вещи.

Mnior
Не надо утрировать. сервер "будет валиться в дамп", то это и будет багом
Где написано, что сервер не должен валится в дамп? Не я утрирую, просто хочу показать, что доведённым до крайности подходом прикрываться BOL-ом можно дойти и до маразма.
И опять повторю: понятие "Баг" и "BOL" никак не связаны. С BOL-ом можно сказать лишь о не соответствии. Если мы завтра увидим в BOL-е, что 2 + 2 = 5, это будет возмутительно, а если не увидим, то это не докажет связи понятий "Баг" и "BOL".

Glory
Потому что там описывается как раз нормально поведение, а все остальное может считаться багом
В том-то и дело что там есть белые пятна. И считать всё остальное багом абсурдно, а с другой стороны вот пжалуста, не описанное поведение - баг?! Ну значит мы согласны друг с другом.

DeColo®es
Я думаю, этот баг давно бы поправили путем запрещения использования.
Слишком много заморочек на уровне синтаксиса и зависимостей результата от плана выполнения.
Но ведь потом те, кто "воспользовался" этой "фичей", сами начали бы кричать, что новая версия сервера несовместима со старой базой.
Угу.
Хотя, есть sp_configure. Вон disallow results from triggers, стоит же по умолчанию 0, но вещь то неприятная (IMXO), влияет на OUTPUT.

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

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

Вырезать везде *=, =* и *=* невероятно просто, по сравнению с перелопачиванием всех select-ов (update-ов) где есть присвоения переменных. Переписывать главные базы (master ...) будет невероятно сложно. (Кстати, как я понял пользовательские базы не содержат мелкомягкий код?)

Короче, подсунутая свинья неподъёмная. Поезд ушёл, надо было думать десять лет назад - за две версии можно было бы всё подготовить, ждать ещё столько же - спасибо.

Кстати, а разве FOR XML не "сломает" некоторые символы? (не помню)
Оконную функцию могли бы всё таки сделать, разницы почти ноль. Вон пивот то сделали, а нового в нём ничего нет.
1 окт 09, 01:39    [7727737]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Mnior,

к сожалению, когда полагаешься на недокументированное поведение - ты действуешь на свой страх и риск.
Так было, так есть и так будет :)
1 окт 09, 02:04    [7727752]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
aleks2
Guest
В 2005 есть документированный способ

declare @t table (i char(1))

insert into @t
select 'a'
union all
select 'c'
union all
select 'd'
union all
select 'b'
 
;with strconcat(str, s)
as
(select top 1 cast(i as varchar(8000)) as str, i as s
 from @t order by i desc
 union all
 select str+t.i as str, t.i as s 
 from @t t inner join strconcat
 ON t.i<s 
 where not exists(select * from @t x WHERE t.i<x.i AND x.i<s)
)
select MAX(str) from strconcat
1 окт 09, 07:49    [7727868]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Jaffar
Member

Откуда:
Сообщений: 633
Я использую такой прием чтобы избежать затирания переменной @STR - у меня всегда работает правильно если использовать top (100) percent.
если его убрать - ошибка появляется.

--- суто здесь top top (@p) percent
--- если есть top - то все работает правильно

declare @STR varchar(max), @p int
select @STR = '', @p = 100

select top (@p) percent @STR = @STR + a.Account+char(13)
from @tla_Account a
where
		a.Account like '40817810700001000000/%'
order by cast(substring(a.Account, 22, 10) as int) asc
print @STR

1 окт 09, 10:18    [7728209]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Glory
Member

Откуда:
Сообщений: 104760
Mnior
Glory
Раз нет описания, то значит такого функционала нет.
Несерьезно рассуждать о том, что что-то работает неправильно, если это что-то вообще не должно работать.
Несерьёзно закрывать глаза на реальные вещи.

Микроволновка предназначена для разогрева пищевых продуктов. Но кто-то попробовал сушить в ней носки. И у него получилось. О чем он радостно поведал миру. Все обрадовались. Но через некоторое время одна бабушка обратилась в суд, потому что попробовала просушить в микроволновке свою помытую кошку. И тоже напирала на то, что нигде в руководстве пользователю не было сказано, что микроволновку нельзя использовать для сушки всего, включая животных. Т.е. налицо явный промах производителя микроволновки, который оставил большой пробел в документации, не описав там методы сушки вещей. И мало того, производитель почему то попрежнему игнорирует "баг" микроволновки при сушке животных. Доколе ?
1 окт 09, 10:26    [7728253]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
Glory,
Производители микроволновок уже давно не игнорируют этот баг, они внесли в BOL по микроволновкам что сушить кошёк нельзя-))
1 окт 09, 10:32    [7728292]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Glory
Member

Откуда:
Сообщений: 104760
Сергей Мишин
Glory,
Производители микроволновок уже давно не игнорируют этот баг, они внесли в BOL по микроволновкам что сушить кошёк нельзя-))

Т.е. они согласились, что в микроволновку теперь еще добавлена функция "сушка вещей" ?
1 окт 09, 10:34    [7728300]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Abbey Road
Member

Откуда:
Сообщений: 26
Выполняю скрипт топикстартера на:
Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
Collation SQL_Latin1_General_CP1251_CI_AS
результат:

(2 row(s) affected)
correct
--------------------------------------------------
a

(1 row(s) affected)

incorrect
--------------------------------------------------
a

(1 row(s) affected)

Тот же скрипт на
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Collation Cyrillic_General_CI_AS

(2 row(s) affected)
correct
--------------------------------------------------
ba

(1 row(s) affected)

incorrect
--------------------------------------------------
a

(1 row(s) affected)

Замена строк на unicode приводит к тому, что результат везде как во втором случае.

Так же, я столкнулся с тем, что скрипт вида
select @dt = @dt + col1
from (select from ... (select from ... etc ORDER by bla-bla))
на 2008 сортировал как надо, а на 2005 не сортировал
Использование временной таблицы с промежуточным результатом помогло.

Так что имейте это ввиду при миграции.
1 окт 09, 10:43    [7728350]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
Glory
Т.е. они согласились, что в микроволновку теперь еще добавлена функция "сушка вещей" ?

Кто то пишет что нельзя, кто то молчит.
p.s. Я не утверждаю что если чего то нет в инструкции, то это должно работать, при этом работать так как хочу я. Так же и насчет "select @x= @x +column ... order by ..."
1 окт 09, 11:00    [7728445]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Glory
Member

Откуда:
Сообщений: 104760
Сергей Мишин
Glory
Т.е. они согласились, что в микроволновку теперь еще добавлена функция "сушка вещей" ?

Кто то пишет что нельзя, кто то молчит.
p.s. Я не утверждаю что если чего то нет в инструкции, то это должно работать, при этом работать так как хочу я. Так же и насчет "select @x= @x +column ... order by ..."


Ответьте на простой вопрос.
В функционал микроволновки входит функция "сушка вещей" или не входит ? Не возможность сушить, а именно функция
Потому что если НЕ входит, то какой смысл рассуждать о том, что при попытке сушить в микроволновке разные вещи получается разный результат
1 окт 09, 11:05    [7728489]     Ответить | Цитировать Сообщить модератору
 Re: select @x = column + order by substring  [new]
Сергей Мишин
Member

Откуда:
Сообщений: 376
Glory,
я ответил постом выше
1 окт 09, 11:10    [7728514]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить