Добрый день, коллеги!

Нашел интересную особенность, и объяснения ей найти никак не могу. Вроде ничего противоестественного, всё в рамках описанной функциональности. История начинается как обычно — система, пережившая несколько версий SQL сервера, была мигрирована с 2005 Standard редакции на 2012 Enterprise (вер 11.0.5592). В ходе эксплуатации обнаружился запрос, который стал сбоить…

select *
from dbo.Test1 t1 
left join dbo.Test2 t2 on t1.Name = t2.IdField
order by t1.SortOrder

Сообщение об ошибке дословно выглядит так

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Данное поведение наблюдается только в редакции Enterprise. В редакциях Standard и Express аналогичной проблемы не обнаружено.

Примечательно в этом запросе одно – соединение двух таблиц происходит по полям разных типов (varchar(20) и numeric(20, 0)) Ниже я предоставил структуру. Запрос авто генерированный и по всей видимости промахнулись с описанием типов в метаданных. Да, он подвержен неявным преобразованиям, но решение в целом имеет право на существование и работало несколько лет до этого, особенно при условии, что присоединяемая таблица слева dbo.Test2 почти всегда пустая. Скажу сразу, запрос гарантировано работает при любых условиях и на тех же данных где предикаты соединения одинаковых типов (например: varchar(20))

select *
from dbo.Test1  t1 
left join dbo.Test2 t2 on t1.Name = t2.StringField
order by t1.SortOrder

	Структура
	create table dbo.Test1 ( Name varchar(20), SortOrder varchar(20) ) create table dbo.Test2 ( IdField numeric(20, 0), StringField varchar(20) ) go

С ходу повторить ошибку не удалось, поэтому пришлось экспериментировать и вот что выяснилось.

1. Если в таблицу dbo.Test1 вставить 234 символьных записи длиной по 4 байта, то запрос успешно отрабатывает, но без указания сортировки

insert into Test1(Name, SortOrder)
select 'aa', 'bb'
go 234

select *
from dbo.Test1  t1 
left join dbo.Test2 t2 on t1.Name = t2.IdField

2. Если в таблицу dbo.Test1 добавить 235 строку длиной 4 байта, то запрос без указания сортировки так же начинает сбоить с той же ошибкой «Error converting data type varchar to numeric»

insert into Test1(Name, SortOrder)
select 'aa', 'bb'
go 235

select *
from dbo.Test1  t1 
left join dbo.Test2 t2 on t1.Name = t2.IdField

3. Если в таблицу dbo.Test1 добавить неограниченное количество числовых записей, то все прекрасно работает даже с сортировкой.

insert into Test1(Name, SortOrder)
select '11', '22'
go 1000

select *
from dbo.Test1 t1 
left join dbo.Test2 t2 on t1.Name = t2.IdField
order by t1.SortOrder

Пока склоняюсь, что это ошибка производителя, других версий просто нет. Хотя причем здесь преобразование строки к числу, сортировка и какие-то ограничения на объем данных в редакции Enterprise ?!

Здравствуйте, Olaf, Вы писали:


O>Пока склоняюсь, что это ошибка производителя, других версий просто нет. Хотя причем здесь преобразование строки к числу, сортировка и какие-то ограничения на объем данных в редакции Enterprise ?!

Склоняюсь к "баг, воспроизводится в зависимости от плана".
Я бы взял скрипт на создание + планы в xml и закинул бы на http://dba.stackexchange.com/

UPD Вроде бы нашёл:
http://stackoverflow.com/questions/19018377/msg-8114-level-16-state-5-line-1-error-converting-data-type-varchar-to-numeri
http://stackoverflow.com/questions/17054604/msg-8114-level-16-state-5-line-1-error-converting-data-type-nvarchar-to-real

Здравствуйте, Sinix, Вы писали:

O>>Пока склоняюсь, что это ошибка производителя, других версий просто нет. Хотя причем здесь преобразование строки к числу, сортировка и какие-то ограничения на объем данных в редакции Enterprise ?!

S>Склоняюсь к "баг, воспроизводится в зависимости от плана".
S>Я бы взял скрипт на создание + планы в xml и закинул бы на http://dba.stackexchange.com/

S>UPD Вроде бы нашёл:
S>http://stackoverflow.com/questions/19018377/msg-8114-level-16-state-5-line-1-error-converting-data-type-varchar-to-numeri
S>http://stackoverflow.com/questions/17054604/msg-8114-level-16-state-5-line-1-error-converting-data-type-nvarchar-to-real

Спасибо. Еще раз окинул все свежим взглядом и вот что обнаружил. Ошибка не относится к редакции и другим ограничениям, а зависит от того какой план строит СУБД, все верно.

1. Во всех редакциях работает запрос, в котором используется стратегия соединения LOOP JOIN, но без сортировки, т.е.

select *
from dbo.Test1 t1 
left join dbo.Test2 t2 on t1.Name = t2.IdField
option(loop join)

С использованием HASH JOIN и MERGE JOIN возникает ошибка, причем во всех редакциях, но она в данном случае связана с механизмами построения результирующих данных.

2. Запрос с сортировкой вида…

select *
from dbo.Test1 t1 
left join dbo.Test2 t2 on t1.Name = t2.IdField
order by t1.SortOrder
option(loop join)

работает, только в том случае, если сначала идет сортировка, а потом выполняется неявное преобразование через Compute Scalar. Например, в Express редакции план выглядит так и запрос успешно выполняется



В Enterprise редакции, все наоборот, т.е. сначала выполняется неявное преобразование, а потом выполняется сортировка, причем сортировка выполняется по двум полям

a. [dbo].[Test1].SortOrder Ascending
б. Expr1006 Ascending = Scalar Operator(tertiary_weights([dbo].[Test1].[SortOrder] as [t1].[SortOrder]))



По факту получается к производителю никаких претензий, все в рамках функциональности.

Здравствуйте, Olaf, Вы писали:

O>В Enterprise редакции, все наоборот, т.е. сначала выполняется неявное преобразование, а потом выполняется сортировка, причем сортировка выполняется по двум полям

O>По факту получается к производителю никаких претензий, все в рамках функциональности.

Немного не по теме, но у нас тоже был похожий код, написанный сто лет назад еще под SQL Server 2005. Там было неявное преобразование строки к дате. Так вот никто не парился, так как и в 2005, и в 2012 он работал. Но вот в 2014 он работать перестал Просто SQL Server кидает ошибку, что такое неявное преобразование невозможно. В целом, с моей точки зрения это оправдано — нефиг писать кривой код Так что прежде чем пустить что-то спорное в продакшен, лучше подумать — а может себе дешевле переписать красиво и эффективно, чтобы потом стыдно не было?

Здравствуйте, Milena, Вы писали:

M> Так что прежде чем пустить что-то спорное в продакшен, лучше подумать — а может себе дешевле переписать красиво и эффективно, чтобы потом стыдно не было?

Хороший вопрос. Если пишешь код сейчас, то себе дешевле сразу писать красиво и эффективно. А если у тебя есть куча кода, "написанного сто лет назад еще под SQL Server 2005", в котором где-то есть парочка таких мест (а те, кто его писал, уже очень далеко), то дешевле выходит оставить все как есть (работает же), раз в пять лет словить такой баг и исправить его.

Но это себе так дешевле, а бизнесу может быть совсем наоборот.

Здравствуйте, wildwind, Вы писали:

W>Хороший вопрос. Если пишешь код сейчас, то себе дешевле сразу писать красиво и эффективно. А если у тебя есть куча кода, "написанного сто лет назад еще под SQL Server 2005", в котором где-то есть парочка таких мест (а те, кто его писал, уже очень далеко), то дешевле выходит оставить все как есть (работает же), раз в пять лет словить такой баг и исправить его.

W>Но это себе так дешевле, а бизнесу может быть совсем наоборот.

Согласна. На самом деле мы действительно не знали, что у нас есть такоооой кусок кода до тех пор, пока в 2014 он не сломался А если бы не миграция с 2012, то так глядишь и еще 10 лет проработал бы