Forum Discussion
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
- olafhelperBronze Contributor
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
- AlanNswCopper Contributor
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