CODEWITHSUNDEEP
sqlsql-server
世界知名伟人
世界知名伟人

Reputation: 155

Why will this sql cause type conversion error?

WITH tb_testl AS （
    SELECT 1 AS id ,'hehe' AS value 
    UNION ALL 
    SELECT 1 AS id, '1' AS value 
    UNION ALL 
    SELECT 2 AS id, '2' AS value 
    UNION ALL 
    SELECT 2 AS id, '2' AS value
), tb_test2 AS (
    SELECT CONVERT(INT , value) AS value FROM tb_testl WHERE id = 2 
)
SELECT * FROM tb_test2 WHERE value = 2;

this sql will cause error

Conversion failed when converting the varchar value 'hehe' to data type int.

but the table tb_test2 dosen't have the value 'hehe' which is in the anthor table tb_test1. And I found that this sql will work well if I don't append the statement WHERE value = 2; .I've tried ISNUMBERIC function but it didn't work.

version:mssql2008 R2

Upvotes: 4

Views: 2910

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

SQL Server does not guarantee the order of processing of statements (with one exception below). That is, there is no guarantee that WHERE filtering happens before the SELECT. Or that one CTE is evaluated before another. This is considered an advantage because it allows SQL Server to rearrange the processing to optimize performance (although I consider the issue that you are seeing a bug).

Obviously, the problem is in this part of the code:

tb_test2 AS (
    SELECT CONVERT(INT, value) AS value
    FROM tb_testl
    WHERE id = 2 
)

(Well, actually, it is where tb_test2 is referenced.)

What is happening is that SQL Server pushes the CONVERT() to where the values are being read, so the conversion is attempted before the WHERE clause is processed. Hence, the error.

In SQL Server 2012+, you can easily solve this using TRY_CNVERT():

tb_test2 AS (
    SELECT TRY_CONVERT(INT, value) AS value
    FROM tb_testl
    WHERE id = 2 
)

However, that doesn't work in SQL Server 2008. You can use the fact that CASE does have some guarantees on the order of processing:

tb_test2 AS (
    SELECT (CASE WHEN value NOT LIKE '%[^0-9]%' THEN CONVERT(INT, value)
            END) AS value
    FROM tb_testl
    WHERE id = 2 
)

Upvotes: 2

HoneyBadger
HoneyBadger

Reputation: 15150

With respect to the why this occurs:

There is a Logical Processing Order, which describes the order in which clauses are evaluated. The order is:

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

You can also see the processing order when you SET SHOWPLAN_ALL ON. For this query, the processing is as follows:

  1. Constant scan - this is the FROM clause, which consists of hard coded values, hence the constants.
  2. Filter - this is the WHERE clause. While it looks like there are two where clauses (WHERE id = 2 and WHERE value = 2). SQL Server sees this differently, it considers a single WHERE clause: WHERE CONVERT(INT , value) = 2 AND id = 2.
  3. Compute scaler. This is the CONVERT function in the select.

Because both WHERE clauses are executed simultaneously, the hehe value is not filtered out of the CONVERT scope.

Effectively, the query is simplified to something like:

SELECT  CONVERT(INT, tb_testl.value) AS Cvalue
FROM    (
            SELECT  1 AS id
            ,       'hehe' AS value
            UNION ALL
            SELECT  1 AS id
            ,       '1' AS value
            UNION ALL
            SELECT  2 AS id
            ,       '2' AS value
            UNION ALL
            SELECT  2 AS id
            ,       '2' AS value
        ) tb_testl
WHERE   CONVERT(INT, tb_testl.value) = 2
    AND tb_testl.id = 2

Which should clarify why the error occurs.

With SQL, you cannot read code in the same way as imperative languages like C. Lines of SQL code are not necessarily (mostly not at all, in fact) executed in the same order it is written in. In this case, it's an error to think the inner where is executed before the outer where.

Upvotes: 3

Dmitry Kolchev
Dmitry Kolchev

Reputation: 2216

error caused by this part of statement

), tb_test2 AS (
    SELECT CONVERT(INT , value) AS value FROM tb_testl WHERE id = 2

value has type of varchar and 'hehe' value cannot be converted to integer

WITH tb_testl AS （
   SELECT 1 AS id ,'hehe' AS value

UPDATE: sql try convert all value(s) to integer in you statement. to avoid error rewrite statement as

WITH tb_testl AS (
 SELECT 1 AS id ,'hehe' AS value 
 UNION ALL SELECT 1 AS id, '1' AS value 
 UNION ALL SELECT 2 AS id, '2' AS value 
 UNION ALL SELECT 2 AS id, '2' AS value
), tb_test2 AS (
 SELECT value AS value FROM tb_testl WHERE id = 2 
),
 tb_test3 AS (
 SELECT cast(value as int) AS value FROM tb_test2
)
SELECT * FROM tb_test3

Upvotes: 1

Related Questions