https://learn.microsoft.com/en-us/archive/blogs/craigfr/conversion-and-arithmetic-errors
Давайте посмотрим на простой запрос:
CREATE TABLE T1 (A INT, B CHAR(8))
INSERT T1 VALUES (0, '0')
INSERT T1 VALUES (1, '1')
INSERT T1 VALUES (99, 'Error')
SELECT T1.A, CONVERT(INT, T1.B) AS B_INT FROM T1
Невозможно преобразовать строку «Error» в целое число, поэтому неудивительно, что этот запрос завершается ошибкой преобразования:
A B_INT
----------- -----------
1 1
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Error ' to data type int.
Теперь давайте создадим ещё одну таблицу и сделаем соединение этих двух таблиц:
CREATE TABLE T2 (X INT) INSERT T2 VALUES (1) INSERT T2 VALUES (2) INSERT T2 VALUES (3) INSERT T2 VALUES (4) INSERT T2 VALUES (5) SELECT T1.A, CONVERT(INT, T1.B) AS B_INT FROM T1 JOIN T2 ON T1.A = T2.X
Хотя этот запрос с соединением включает в себя то же преобразование, что и первый запрос, строка с T1.A = 99 не соединяется ни с одной строкой в T2, поэтому кажется, преобразование не должно выполняться. Однако если мы выполним запрос, мы всё равно получим ошибку:
A B_INT ----------- ----------- Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'Error ' to data type int.
Почему так получилось? Оптимизатор запросов SQL Server может свободно перемещать выражения вверх и вниз по плану запроса. Поскольку выражение с преобразованием зависит только от Т1, оптимизатор может (и, в этом случае, так и делает) протолкнуть преобразование ниже соединения и ближе к просмотру Т1:
|--Hash Match(Inner Join, HASH:([T1].[A])=([T2].[X]), RESIDUAL:([T2].[X]=[T1].[A]))
** |--Compute Scalar(DEFINE:([Expr1008]=CONVERT(int,[T1].[B],0)))
** | |--Table Scan(OBJECT:([T1]))
|--Table Scan(OBJECT:([T2]))
Теперь давайте посмотрим, что произойдет, если мы принудительно укажем делать соединение Nested Loops:
SELECT T1.A, CONVERT(INT, T1.B) AS B_INT
FROM T1 JOIN T2 ON T1.A = T2.X
OPTION (LOOP JOIN)
|--Nested Loops(Inner Join, WHERE:([T2].[X]=[T1].[A]))
** |--Compute Scalar(DEFINE:([Expr1008]=CONVERT(int,[T1].[B],0)))
** | |--Table Scan(OBJECT:([T1]))
|--Table Scan(OBJECT:([T2]))
И снова оптимизатор помещает преобразование ниже соединения, поэтому можно подумать, что этот запрос также завершится с ошибкой. Удивительно, но этого не произойдёт:
A B_INT ----------- ----------- 1 1
Почему же здесь получилось иначе, чем выше? SQL Server включает в себя оптимизацию времени выполнения, которая откладывает вычисление большинства скалярных выражений до тех пор, пока на них не сошлются. Мы сможем увидеть, что такая оптимизации была, если запустим запрос с SET STATISTICS PROFILE ON:
1 1 |--Nested Loops(Inner Join, WHERE:([T2].[X]=[T1].[A])) 0 0 |--Compute Scalar(DEFINE:([Expr1008]=CONVERT(int,[T1].[B],0))) 3 1 | |--Table Scan(OBJECT:([T1])) 15 3 |--Table Scan(OBJECT:([T2]))
Обратите внимание, что Compute Scalar не выполняется ни разу. Результат преобразования для соединения не нужен и вычисляется непосредственно перед возвратом результата запроса клиенту. К этому моменту соединение уже отфильтровало ту строку, где преобразование завершалось ошибкой.
На самом деле, SQL Server одинаково оптимизирует соединения Nested Loops и Hash. Однако, для хеш-соединения создается хеш-таблица на входе слева. Она содержит значения для столбцов, которые необходимы для оценки соединения, и значения столбцов, которые выводятся в результате соединения. Таким образом, получается, что хеш-соединение должно выполнить преобразование и сохранить его результат в хеш-таблицу, вот тут-то и происходит ошибка, из-за того, что преобразование выполняется до того, как строка отфильтруется.
Как видите, возможность появления ошибки при преобразовании зависит от множества факторов, в том числе от того, где в плане оптимизатор выберет для него место и когда во время исполнения запроса преобразование будет реально выполнено. Более того, момент выполнения преобразования зависит от конкретных операторов и их поведения в плане.
Аналогичные проблемы с арифметическими ошибками можно наблюдать, когда выявляется деление на ноль или переполнение:
INSERT T1 VALUES (0, '0') SELECT T1.A, 1 / T1.A AS A_Reciprocal FROM T1 JOIN T2 ON T1.A = T2.X A A_Reciprocal ----------- ------------ Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
Что можно сделать, чтобы решить эту проблему? С точки зрения производительности лучшее вообще избегать подобного преобразования или гарантировать, что данные в преобразуемом столбце подходящие, или избегать использования таких выражений, которые могут привести к арифметическим ошибкам. Если без преобразования (или любого другого выражение, которое может привести к ошибке) никак не обойтись, можно использовать оператор CASE для предварительной проверки данных на входе преобразования/выражения. Например, следующий запрос удаляет начальные и конечные пробелы из столбца CHAR, а затем использует функцию PATINDEX для проверки того, что значение столбца является числом:
SELECT T1.A,
CASE WHEN PATINDEX('%[^0-9]%', RTRIM(LTRIM(T1.B))) = 0
THEN CONVERT(INT, T1.B)
ELSE NULL
END AS B_INT
FROM T1
Пример ниже показывает, как можем избежать ошибки деления на ноль:
SELECT T1.A,
CASE WHEN T1.A <> 0
THEN 1 / T1.A
ELSE NULL
END AS A_Reciprocal
FROM T1 JOIN T2 ON T1.A = T2.X
Хотя это не рекомендуется, в случае арифметических ошибок можно использовать параметры сеанса SET ANSI_WARNINGS, SET ARITHABORT и SET ARITHIGNORE, которые отключат генерацию подобных ошибок. Эти настройки не влияют на ошибки преобразования.
Стоит также отметить, что некоторые подсказки могут повлиять на размещение оптимизатором конвертации в плане запроса:
SELECT T1.A, CONVERT(INT, T1.B) AS B_INT
FROM T1 JOIN T2 ON T1.A = T2.X
OPTION (FORCE ORDER)
** |--Compute Scalar(DEFINE:([Expr1008]=CONVERT(int,[T1].[B],0)))
** |--Hash Match(Inner Join, HASH:([T1].[A])=([T2].[X]), RESIDUAL:([T2].[X]=[T1].[A]))
|--Table Scan(OBJECT:([T1]))
|--Table Scan(OBJECT:([T2]))
Обратите внимание, что преобразование осуществляется теперь после соединения. Я бы настоятельно не рекомендовал использовать эту подсказку в качестве обходного пути, за исключением, пожалуй, самых простых запросов, учитывая количество побочных эффектов, которые она имеет. Обычно я указываю на этот конкретный результат на тот случай, если у читателей возникнет искушение использовать эту или другие подсказки для изучения того, как другие типы соединений и порядки соединений могут повлиять на поведение запроса.
Комментариев нет:
Отправить комментарий