SQL Server не может определить правильную строку
У меня есть этот запрос
create table A
(
[CODEID] [int] NOT NULL,
[PRJID] [int] not null,
[CODEVALUE] [varchar](200) NULL
)
create table B
(
[CODEID] [int] NOT NULL,
[CODENAME] [varchar](100)
)
go
insert into A
select 1,99,'ABC'
union
select 2,99,'5-0-0'
go
insert into B
select 1, 'ONE'
union
select 2, 'TWO'
;with dt as
(
select cast(replace(CODEVALUE,'-','') as int) VAL
from A
join B on B.CODEID = A.CODEID and B.CODENAME = 'TWO' and A.PRJID = 99
)
select MSG
from
(select
case when VAL>25 then 'BRAVO' end MSG
from dt) q
where
MSG is not null
Я ожидал вернуть MSG, вместо этого сервер пытается преобразовать «ABC» в int, отсюда и ошибка.
Но если я удалю
where MSG is not null
оно работает
Соединение указывает на одну строку, не знаю, почему оно должно опрашивать всю таблицу.... более того, какая разница в предложенииwhere?
Возможно, вам стоит использовать TRY_CAST/TRY_CONVERT? Но реальная проблема заключается в том, что вы храните значения, которые хотите рассматривать как числовые, в строковом типе данных.
«Соединение указывает на одну строку, не знаю, почему оно должно опрашивать всю таблицу». Вы предполагаете, что SQL Server сначала выполнит JOIN; это ошибка. SQL Server будет выполнять операции в том порядке, в котором он считает наиболее подходящим; вы не говорите ему, как что-то делать, вы говорите ему, что вы хотите.
Почему мне следует использовать TRY_CAST, если он работает без предложенияwhere? В чем разница?
Потому что SQL работает не так, как вы думаете. Вы описываете желаемые результаты, а то, как движок их достигнет, зависит от него. И по какой-то причине, когда вы добавляете предложениеwhere, он определяет, что лучший способ получить доступ ко всем данным до присоединения к ним. Вам всегда придется писать «безопасные» запросы, если вы испортили такие данные. Вот почему try_cast/try_convert существуют.
@RaduB. объясните логику, а не то, как, по вашему мнению, должно выглядеть решение. Этот запрос очень и очень необычный. С какой стороны ни посмотри, хранение чисел в текстовых полях — это ошибка. Что касается tries converting the 'ABC' into an int, это ваш собственный код, а не серверный
Чтобы узнать больше, просмотрите план выполнения с предложениемwhere и без него и посмотрите, что он делает.
Я предполагаю, что, поскольку у вас есть MSG is not null, это заставляет столбец, определенный как MSG, быть полученным в начале; поскольку тогда в SQL Server меньше строк от dbo.A до JOIN и dbo.B, и нет индексов, которые помогли бы SQL Server искать данные в точке JOIN. Итак, это означает, что выражение case when VAL>25 then 'BRAVO' end должно быть получено, а это означает, что cast(replace(CODEVALUE,'-','') as int) должно быть получено и, ох... 'ABC' не является допустимым int.
Это похоже на Проблему XY : существует проблема X, и вы предполагаете, что решение — Y. Когда Y не работает, вы спрашиваете об Y, а не о настоящей проблеме. Прямо сейчас нам предстоит догадаться, в чем настоящая проблема. Нам даже приходится гадать, каковы ожидаемые результаты. Это не I expect all rows, ожидаемые результаты — это фактические значения, которые вы ожидаете, в виде таблицы.
@Dale K Очевидно, я не хочу преобразовывать «ABC» в число, поэтому я указываю значения для поиска в предложении соединения.
Если вы объясните реальную проблему и ожидаемый результат, мы сможем создать SQL-запрос, который выдаст ее. Прямо сейчас нам нужно угадать результаты и расшифровать ошибочный запрос, чтобы определить логику. Возможно, вам все очевидно, но запрос не работает, и эксперты по SQL спрашивают what is this doing?
Панайотис Канавос Я ожидаю, что сервер найдет строку со значениями, указанными в предложении соединения, и вернет правильное значение, то есть «5-0-0», которое можно преобразовать в целое число. Когда он делает это без последнего предложенияwhere, это работает, но с предложениемwhere — нет. Мне кажется, это несоответствие в плане сервера.
Вы все еще думаете, что SQL Server будет анализировать запрос «сверху вниз», @RaduB. . В этом запросе нет ничего, что могло бы заставить SQL Server сначала получить JOIN. Он обрабатывает запрос целиком и определяет наилучший порядок операций для получения запрошенных вами результатов.
@Thom A Почему работает без предложенияwhere?
TRY_CAST помогает. Он возвращает NULL, если приведение не удалось, вместо того, чтобы выдать ошибку. Это не означает, что запрос и дизайн таблицы неплохие. Они все еще
Потому что, когда WHERE отсутствует, порядок или операции меняются, @RaduB. . Разные запросы, разные методы.
Хорошо, спасибо за TRY_CAST, я обязательно им воспользуюсь.
Если вы сравните планы выполнения двух запросов, вы увидите, что фактическая операция — это всего лишь два сканирования таблицы и JOIN, несмотря на сложность запроса. (Это также означает, что вы можете заменить все это одним простым JOIN). Сервер должен фильтровать A, когда вы используете WHERE msg is not null, прежде чем он сможет присоединиться к таблицам.
@Panagiotis Kanavos Как любой мог видеть, в этом поле находятся только строки, и логика приложения знает, что некоторые из них можно преобразовать в числа. Для меня ответ должен показать, как избежать этой ошибки. Наконец, я нахожу решение.
@RaduB. «кто-нибудь» целый день говорит, что SQL работает не так, как вы предполагали. Ответ ThomA говорит вам то же самое. Когда я сказал вам проверить планы выполнения, я ожидал, что вы это сделаете, и в этом случае вы бы увидели, что в каждом случае приведение выполняется в разных местах.
@RaduB. the logic of the app неизвестен как SQL Server, так и всем, кто ответил, пока не узнает. Если вы объясните эту логику, мы сможем рассказать вам, как преобразовать ее в эффективный SQL. Создание дополнительных таблиц не является решением
Ответы 1
Проблема здесь в вашем понимании того, как работает SQL Server (и многие другие механизмы обработки данных); у вас сложилось впечатление, что сначала он выведет JOIN в вашем CTE dt, и поэтому, когда он переходит к получению выражения VAL, ошибка не может возникнуть, поскольку только одна строка приведет к повторному запуску этого выражения. Это понимание неверно.
При выполнении запроса именно механизм обработки данных определяет наиболее эффективный способ определения искомых результатов; вы сообщаете механизму обработки данных, чего вы хотите, и механизм обработки данных определяет, как получить эти результаты.
Здесь у вас также сложилось впечатление, что ваши два запроса, один с WHERE MSG IS NOT NULL, а другой без, будут выполняться одинаково; это опять неправильно. SQL Server проанализирует каждый запрос и определит лучший метод получения результатов для этого запроса; 2 запроса могут привести к 2 методам.
Скорее всего, здесь включение WHERE MSG IS NOT NULL означает, что SQL Server определил, что лучшим действием является первое получение этого выражения, поскольку оно будет фильтровать количество строк из таблицы dbo.A вниз, и тогда у него будет меньше строк для обработки. JOIN к столу dbo.B. Чтобы получить это выражение, сначала необходимо выяснить, имеет ли MSG значение NULL, а это значит, что необходимо вывести выражение CASE WHEN VAL > 25 THEN 'BRAVO' END. Однако для этого также необходимо вывести дальнейшее выражение для расчета: CAST(REPLACE(CODEVALUE, '-', '') AS int). Теперь у нас есть столбцы из таблиц, поэтому мы можем выполнить выражение для набора данных в dbo.A:
CAST(REPLACE('5-0-0', '-', '') AS int)
CAST(REPLACE('ABC, '-', '') AS int)
Что становится:
CAST('500' AS int)
CAST('ABC' AS int)
И ах, у нас проблема; какое значение "int" имеет 'ABC'? Это недопустимое значение, поэтому вы получаете сообщение об ошибке.
Однако, если вы опустите предложение WHERE MSG IS NOT NULL, SQL Server, вероятно, определит, что JOIN сначала обрабатывать данные «лучше», поскольку сейчас у него нет никаких предложений WHERE и нигде нет индексов, которые могли бы помочь ему определить, какие строки искать, поэтому сканирование обеих таблиц и их JOIN кажется наиболее подходящим вариантом. Тогда после JOIN можно получить SELECT.
Так в чем же решение? Честно говоря, не храните данные, которые вам нужно рассматривать как числовые, в строковом столбце; если значение необходимо рассматривать как числовое значение, используйте числовой тип данных (например, int, decimal). Если вы «должны» хранить такие данные в строковом столбце, используйте функцию TRY_, чтобы преобразование возвращало NULL, если значение недопустимо:
TRY_CAST(REPLACE(CODEVALUE, '-', '') AS int)
TRY_CONVERT(int,REPLACE(CODEVALUE, '-', '')
На самом деле мы можем доказать это, используя планы запросов. Если мы проверим (предполагаемый) план запроса с помощью WHERE MSG IS NOT NULL и посмотрим на скан таблицы dbo.A, мы увидим, что фильтр применяется при сканировании:
[
Однако если мы посмотрим на запрос без WHERE, мы увидим, что фильтр включен PRJID = 99.
Поэтому во втором запросе, когда выводится CONVERT, выводится только строка со значением '5-0-0.
Как я могу обеспечить использование второго плана? Исходный запрос намного сложнее, и ни один из TRY_CAST не работает. Мы не можем спроектировать два поля отдельно для числовых и строковых, потому что мы априори не знаем, какие данные поступают, но мы точно знаем во время опроса, является ли строка числовой или нет...
Вы не заставляете это делать. Похоже, вы слишком упростили проблему; как предложил Панайотис Канавос , похоже, у вас Проблема XY, @RaduB. . Я предлагаю вам опубликовать новый вопрос, посвященный X, а не Y (как здесь). Хотя, опять же, я подозреваю, что настоящая проблема в вашем дизайне.
В качестве обходного пути я объявляю табличную переменную, содержащую поле MSG, из случая выбора, когда VAL>25, затем «BRAVO» завершает MSG из dt, а затем я могу выбрать значение из таблицы с предложениемwhere, где MSG не равно нулю без ошибки.
Ну да,
cast(replace(CODEVALUE,'-','') as int)пытается преобразовать «ABC» в целое число… что вы действительно хотите сделать?