SQL Server 2005 too smart?

<< Clever way to get the records you want with certain number of a given character | Home | Save some time and key-typing >>

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.

Feedback

# 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]%'

9/27/2007 6:13 PM | Trevor

# 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.

9/27/2007 10:26 PM | Jeff

# 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

9/28/2007 4:53 PM | Peter Larsson

# 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

10/10/2007 8:04 PM | Chris Leonard

# 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 ?

9/16/2009 11:10 PM | Paul

# 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

9/16/2009 11:16 PM | Peso

Comments have been closed on this topic.

Thinking outside the box

My Links

Advertisement

News

Archives

Post Categories