Forum Discussion

AlanNsw's avatar
AlanNsw
Copper Contributor
Jun 02, 2021

Is this a bug of sql server 2017?

Dear All:

 

I found on the server that the conversion failed (SUM(CAST(testkey AS FLOAT)) part) when using the CAST conversion type, but I used another table to replace #tbs, and found that it can be converted normally, the testkey column contains null , Numbers, Chinese, my boss told me this is a bug of SQL SERVER, but I don’t believe it.

 

 

CREATE TABLE #tbs
(
id INT IDENTITY(1,1),
testkey NVARCHAR(max)
)
GO
INSERT INTO #tbs( testkey)VALUES(N'张三')
INSERT INTO #tbs( testkey)VALUES(null)
INSERT INTO # tbs(testkey)VALUES(N'10')
INSERT INTO #tbs(testkey)VALUES(N'20')

 

SELECT SUM(CAST(testkey AS FLOAT)) AS keys FROM (
SELECT id,testkey FROM #tbs WHERE id IN(3,4)
) t1

 

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Jun 03, 2021

    AlanNsw , agree with you.

    Even if you add an addition filter like

     

    AND ISNUMERIC(testkey) = 1

    there is no garantuee that the query processor don't hit a non-convertable value.

    Other example for proof: The first return 1 = true, means the value looks like a numeric one, but the second fails with conversion failure.

     

    SELECT ISNUMERIC('-')
GO
SELECT CONVERT(float, '-')

     

    Olaf

    • AlanNsw's avatar
      AlanNsw
      Copper Contributor
      Jun 03, 2021

      Dear olafhelper
      Thanks for your advice.
      I used CASE WHEN ISNUMERIC(testkey) = 1 THEN CAST(testkey AS FLOAT) ELSE 0 END to solve the problem, because it is always equal to true. I don’t understand why it will cause a conversion error, and there is no conversion failure on other servers

      Alan

Resources