次の方法で共有

Conversion and Arithmetic Errors

  • [アーティクル]

Let's take a look at a simple query:

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

There is no way to convert the string "Error" into an integer, so it should come as no surprise that this query fails with a conversion 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.

Now, let's create a second table and try a join:

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

Although this join query includes the same conversion as the first query, the row with T1.A = 99 does not join with any rows in T2, so at first glance, we might conclude that the conversion should never be executed.  However, if we execute the query, we still get the error:

A           B_INT
----------- -----------
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Error   ' to data type int.

What happened?  The SQL Server query optimizer is free to move expressions up and down within a query plan.  Since the expression with the conversion depends only on T1, the optimizer can (and does) push the conversion below the join and close to the scan of T1:

  |--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]))

Now, let's see what happens if we force a nested loops join:

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]))

Once again, the optimizer pushes the conversion below the join, so we might conclude that this query will also fail.  Surprisingly, it does not:

A           B_INT
----------- -----------
1           1

What's going on here?  SQL Server includes a runtime optimization that defers the evaluation of most scalar expressions until they are actually referenced.  We can see this optimization in action if we check the output of 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]))

Notice that the compute scalar is executed zero times.  The result of the conversion is not needed by the join and is computed immediately prior to returning the query result to the client.  By this time, the bad row has already been filtered by the join.

SQL Server actually uses the same optimization both for the nested loops join and for the hash join.  However, recall that a hash join builds a hash table on its left input.  This hash table includes values for all columns that are needed to evaluate the join and values for all columns that are output by the join.  Thus, the hash join must evaluate and store the result of the conversion in the hash table.  Thus, the conversion is evaluated and fails before the hash join can determine whether the bad row actually joins.

As you can see, whether or not a conversion causes a failure depends on a variety of factors including where in the plan the optimizer chooses to place the conversion and when during execution the conversion is actually evaluated.  Moreover, when the conversion is actually evaluated depends on the precise operators in the plan and the behavior of those operators.

We can see similar problems with arithmetic errors such as overflows or divide by zero:

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.

So, what can you do to work around this problem?  The best solution from a performance perspective is to avoid using the conversion at all or to ensure that the data in the column to be converted is valid (or to avoid using an expression that could fail due to an arithmetic error).  If you must use a conversion (or any other expression that could fail), you can use a CASE statement to validate the input.  For example, the following query trims away leading and trailing spaces from the CHAR column and then uses the PATINDEX function to validate that the column does not contain any non-numeric characters:

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

This example shows how we can avoid the divide by zero error:

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

Although I would not recommend it, for arithmetic errors, you can use the SET ANSI_WARNINGS, SET ARITHABORT, and SET ARITHIGNORE options to disable the error.  These settings do nothing for conversion errors.

Finally, it is worth pointing out that some hints can affect the placement of the conversion:

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]))

Notice that the conversion is now computed after the join.  I would strongly discourage the use of this hint as a workaround except perhaps in the simplest of queries given the number of side effects it has.  Mostly I point out this particular result in case any readers are tempted to use this or other hints to explore how other join types and join orders might affect the behavior of this query.

Comments

  • Anonymous
    May 06, 2008
    Hi Craig,Yet another excellent post!However, just as an FYI, there is a shorter way to avoid the division by zero error:SELECT T1.A,   1 / NULL(T1.A, 0) AS A_ReciprocalFROM T1 JOIN T2 ON T1.A = T2.X;Best, Hugo
  • Anonymous
    May 06, 2008
    In this post from last week, I gave an example of a query with a conversion where the optimizer pushes
  • Anonymous
    May 06, 2008
    Hi Hugo,Thanks for the tip.  I believe that you meant to write NULLIF (rather than just NULL):SELECT T1.A,  1 / NULLIF(T1.A, 0) AS A_ReciprocalFROM T1 JOIN T2 ON T1.A = T2.XCraig
  • Anonymous
    May 06, 2008
    Hi Craig,Yes, that was exactly what I meant. Thanks for catching my typo!Best, Hugo