bug in ISNUMERIC function?

问题:

I have a problem with ISNUMERIC Function in SQL-server.

I have a table, which contains one column with nvarchar type. In this column I have such a values 123, 241, ... and sometimes string values like LK My Query:

WITH CTE AS (
    SELECT *
    FROM MyTable
    WHERE ISNUMERIC([Column1]) = 1
)
SELECT *
FROM CTE
WHERE CAST(cte.Column1 AS INT) > 8000

but this query returns this error message:

error converting data type nvarchar 'LK' to int

my expectation is, that the Common table expression filter all rows, which Column1 are numeric and not string? Is it correct?

Why do I receive this error?

回答1:

This is actually no bug.

The CTE is a temporary view (or acts like a temporary view). So think on our query like querying a view. Most likely SQL will try first to go through all rows and get results from both scalars (isnumeric and the cast) then proceed to the filtering.

That being said, it will fail way before it will try to filter the data on the cast.

If you want to make it work just filter the data before in a temporary table or in a table variable.

回答2:

IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl
GO

CREATE TABLE dbo.tbl (val NVARCHAR(1000))
INSERT INTO dbo.tbl
VALUES ('123'), ('234'), ('LK'), ('8001')

ALTER TABLE dbo.tbl
    ADD val2 AS CASE WHEN ISNUMERIC(val) = 1 THEN CAST(val AS INT) END
GO

SELECT *
FROM dbo.tbl
WHERE val2 > 8000

回答3:

You can force to materialize CTE table by adding TOP X, to avoid SQL server optimize the where condition to make it "short-circuit" like behavior.

WITH CTE AS (
    SELECT TOP 10000000000000 
        *
    FROM (VALUES('1'), ('LK')) t(Column1)
    WHERE ISNUMERIC([Column1]) = 1
)
SELECT *
FROM CTE
WHERE CAST(cte.Column1 AS INT) > 8000

回答4:

It has to do with how the query is parsed and executed. If adding a column as suggested by Devart is not possible, then using a temp table could be an alternate.

CREATE TABLE MyTable
(
Column1 nvarchar(50)
)

INSERT INTO MyTable
    VALUES ('123'),
    ('241'),
    ('LK'),
    ('786'),
    ('54321'),
    ('7999'),
    ('8001')

SELECT
    * INTO #TEMP
FROM MyTable
WHERE ISNUMERIC([Column1]) = 1

SELECT
    *
FROM #TEMP
WHERE CAST(Column1 AS int) > 8000

DROP TABLE #TEMP

回答5:

The main problem is that SQL is a declarative language that imposes very little order on the operations. This is necessary to allow the engine to optimize the query.

One case where short-circuiting works is in a case statement. So you can try something like this:

drop table #tbl;

create table #tbl (txt varchar(10))
insert into  #tbl
 values ('123'), ('234'), ('LK'), ('9001')

select * from #tbl 
where 
 (
  case 
   when isnumeric(txt) = 1 then 
    case when cast(txt as int) > 8000 then 1 end 
  end
 ) = 1

I can't tell you how the performance is going to work out, though. In general, you should really use normalized data when possible - it allows SQL databases to really shine :)

回答6:

I think that below query is more efficient:

SELECT *
FROM MyTable
WHERE ISNUMERIC([Column1]) = 1 AND  CAST(cte.Column1 AS INT) > 8000