July 10, 2009 at 7:35 am
Hi, does anyone know how to prevent this bug from occurring. Essentially the execution engine is creating the wrong execution plan causing the query to fail. Here are the steps to reproduce the problem:
Create and populate a table as so:
CREATE TABLE devTable (field1 nchar(1))
GO
INSERT devTable VALUES ('a')
INSERT devTable VALUES ('1')
INSERT devTable VALUES ('2')
INSERT devTable VALUES ('3')
INSERT devTable VALUES ('4')
GO
Run the following query against the table:
SELECT i.field1
FROM (
SELECT field1
FROM devTable
WHERE ISNUMERIC(field1) = 1
) i
WHERE i.field1 > 1
GO
The query will fail because the engine will try and retrieve records from the table using the 'i.field1 > 1' criteria in the outer query before applying the 'ISNUMERIC(field1) = 1' criteria in the inner query. Click the 'Display Estimated Execution Plan' to verify this.
Is there a way to force the engine to evaluate things in the correct (and obvious) order ?
July 10, 2009 at 7:50 am
By using :
SELECT field1
FROM devTable
WHERE case when ISNUMERIC(field1) = 1 then field1 else 1 end > 1
The problem is dat sql while try fields1 > 1 because there is no function on the field1.
By useing the case you tell sql to do the isnumeric first.
July 10, 2009 at 7:51 am
Greetings,
This might not be a way to cause it to process in the correct order, but it would still work.
SELECT
field1
FROM devTable
WHERE
ISNUMERIC(field1) = 1
AND field1 > '1'
This way, your check will still work for field1 being a number and the value will be greater than 1.
Have a good day.
Terry Steadman
July 10, 2009 at 7:56 am
terrance.steadman (7/10/2009)
Greetings,This might not be a way to cause it to process in the correct order, but it would still work.
SELECT
field1
FROM devTable
WHERE
ISNUMERIC(field1) = 1
AND field1 > '1'
This way, your check will still work for field1 being a number and the value will be greater than 1.
Have a good day.
Terry Steadman
That might not be safe ...if you say field1 > '1', aren't you looking at the ASCII codes of the string? Try this:
IF '2' > '11'
PRINT 'YES'
ELSE
PRINT 'NO'
You'll get the result as 'YES', even though obviously 2 for 11. In this case it might work, since all other ascii codes for numbers are greater than that of 1, but it's still risky i think.
July 10, 2009 at 8:02 am
I suppose I should also point out that this problem originally surfaced in a much more complex query in which the sub-query was a call to a remote server. It took some time to figure out what was going wrong with a query which is correct but mis-executed. I have found this a bit disturbing, especially since it was a junior apprentice whose query was failing through what turned out to be no fault of their own.
July 10, 2009 at 8:05 am
Greetings,
You are correct, the ascii comparison is character by character for higher value. But, his requirement was only that it was greater than 1. In that case, then the check will still work. If you want something a bit more complex and uses a numeric type comparison that you can change the WHERE clause to:
WHERE
(
CASE ISNUMERIC(field1)
WHEN 1 THEN
CASE
WHEN CAST(field1 AS int) > 1 THEN 1
ELSE 0
END
ELSE 0
END
) = 1
Have a good day.
Terry Steadman
July 10, 2009 at 8:10 am
Hi,
Sql determines how to apply the filter. He figures out it would be better the to the field1>1 because that would use an index on the column while Isnumeric(field1) will never do that.
This is not done like in .net where the order is maintained, so the fact that you got the errors is because we where trying to do a numeric function > 1 (which is a number) to a char column.
If you look at the execution pla of my query you will see that he compare an int with > 1
SELECT field1
FROM devTable
WHERE case when ISNUMERIC(field1) = 1 then field1 else 1 end > 1
July 10, 2009 at 8:14 am
This is not a bug. And it is documented in Books Online.
Also see this blog post http://weblogs.sqlteam.com/peterl/archive/2007/09/27/SQL-Server-2005-too-smart.aspx
Books Online here (Transact-SQL, Expressions in queries)
http://msdn.microsoft.com/en-us/library/ms143359(SQL.90).aspx
N 56°04'39.16"
E 12°55'05.25"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply