SQL Server 2005 too smart?
I was involved in a discussion today about the ISNUMERIC() function
Someone proposed a nested solution like this
SELECT Column_Name
from (
SELECT Column_Name
FROM (
select 'staff' as Column_Name union all
select '234000' as Column_Name union all
select '12d1' as Column_Name union all
select '45e0' as Column_Name union all
select '$123.45' as Column_Name union all
select '$12,345' as Column_Name
) as Table_Name
WHERE ISNUMERIC(Column_Name) = 1
) as d
where CAST(Column_Name AS INT) <= 1000000
But is does not work. I suggested an alternative method that seems to work.
SELECT d.Column_Name
from (
SELECT x.Column_Name
FROM (
select 'staff' as Column_Name union all
select '234000' union all
select '12d1' union all
select '45e0' union all
select '$123.45' union all
select '$12,345'
) as x
where x.column_name not like '%[^0-9]%'
) as d
where CAST(d.Column_Name AS INT) <= 1000000
However, it does only work on SQL Server 2000!
On SQL Server 2005 I still get a conversion error.
if you comment out the outer second where, and run on SQL Server 2005, you get the expected resultset.
Is this a bug, or feature?
However, I have found a workaround that works in both SQL Server 2000 and SQL Server 2005
SELECT d.ColumnValue
FROM (
SELECT 'staff' AS ColumnValue UNION ALL
SELECT '234000' UNION ALL
SELECT '12d1' UNION ALL
SELECT '45e0' UNION ALL
SELECT '$123.45' UNION ALL
SELECT '$12,345'
) AS d
WHERE d.ColumnValue NOT LIKE '%[^0-9]%'
AND CAST(d.ColumnValue AS INT) <= 1000000
It do seem that SQL Server occasionnaly do short-circuit evaluations. This is one of these times.
Because if you change the order for the two WHERE's, you will get a conversion error.
Legacy Comments
|
Trevor
2007-09-27 |
re: SQL Server 2005 too smart? Just realized that you answered the question already, but here's my 2 cents. The query plans in 2000 and 2005 differ. I think SQL 'shortcuts' AND logic, much like other programming languages. e.g. with a clause like "where condition1 and condition2" - if SQL verifies that condition1 is false, it won't process condition2. In this case, SQL 2000's optimizer happens to resolve the NOT LIKE clause first, and SQL 2005's optimizer happens to resolve the CAST first - causing the error only on 2005. (check the estimated execution plans - simplified queries to demo this are at end of post) Incidentally, either 2000 or 2005 evaluate both the NOT LIKE and CAST conditions in the same step - a Filter operator. I doubt there's a way to force SQL to choose an order of evaluation here. Unless the query is rewritten. Point is that I think this query just squeaked by the optimizer in 2000. -- This always works in 2000 and 2005 select * from ( select '2x34000' as col1 ) as d where col1 not like '%[^0-9]%' and CAST(col1 AS INT) <= 1000000 -- This always fails in 2000 and 2005 select * from ( select '2x34000' as col1 ) as d where CAST(col1 AS INT) <= 1000000 and col1 not like '%[^0-9]%' |
|
Jeff
2007-09-27 |
re: SQL Server 2005 too smart? Peso -- I think the issue is the ISNUMERIC function. This: select isnumeric('12d1') returns 1, it thinks that 12d1 is numeric. But if you try to cast 12d1 to an integer: select cast('12d1' as integer) you'll get an error. |
|
Peter Larsson
2007-09-28 |
re: SQL Server 2005 too smart? Yes, I noticed that. The problem however is that SQL Server performs the outer WHERE before the INNER one. Frank Kalis pointed out why in this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90059&whichpage=2 |
|
Chris Leonard
2007-10-10 |
re: SQL Server 2005 too smart? '12d1' is considered numeric and can be cast to a float (not an int). select cast('12d1' as float) ---------- 120.0 Cheers, Chris Leonard |
|
Paul
2009-09-16 |
re: SQL Server 2005 too smart? I try to run his code in SQL 2008 SELECT CAST (Col1 AS Float) FROM Table I --3454 WHERE ActualValue NOT LIKE '%[^0-9]%' AND CAST(Col1 AS INT) <= 1000000 It gives me this error : Msg 8116, Level 16, State 1, Line 1 Argument data type sql_variant is invalid for argument 1 of like function. What could be workaround ? |
|
Peso
2009-09-16 |
re: SQL Server 2005 too smart? Something like this SELECT CAST(ActualValue AS FLOAT) FROM Table WHERE CASE WHEN ActualValue LIKE '%[^0-9]%' THEN 0 WHEN LEN(ActualValue) NOT BETWEEN 1 AND 6 THEN 0 WHEN CAST(ActualValue AS INT) < 1000000 THEN 1 ELSE 0 END = 1 |