Search

SQL Server should not raise illogical errors by Erland Sommarskog

Active

62
4
Sign in
 to vote
Type: Bug
ID: 537419
Opened: 2/28/2010 11:36:01 AM
Access Restriction: Public
Duplicates: 551465
3
Workaround(s)
17
User(s) can reproduce this bug
You already have tons of these bug reports, and I bet that you have closed them all "by design" and told people to use the CASE statement. But, no, you are wrong. This is a bug, and should be fixed. Although the fix is to add new functionality, hang on.

Consider this silly repro:

create table albert(a int NOT NULL,
                        b varchar(23) NOT NULL)
create table stina (a int NOT NULL)
go
insert albert (a, b)
     values (1, '99'),
             (2, 'Gurka'),
             (3, '89')
insert stina (a) values(1), (3), (9), (12)
go
SELECT a.a, a.b + 100
FROM albert a
JOIN stina s ON s.a = a.a


This query fails with "Conversion failed when converting the varchar value 'Gurka' to data type int.", despite this row would never appear in the output if the error was not raised. This is because the SELECT list is computed before the filtering. This is may be an OK optimization, but instead of raising the error on the spot, you should defer the error, and the operator should insert an error for the column value in the output stream, and only if there is a reference to this column value further afield, an error should be raised.

A similar case is this query (same data as above)

WITH numonly AS (
     SELECT a, b
     FROM albert
     WHERE b NOT LIKE '%[^0-9]%'
)
SELECT a, b
FROM numonly
WHERE b < 100

This query should never fail with "Conversion failed when converting the varchar value 'Gurka' to data type int.", even if there is non-numeric data in b. Logically, I am filtering out the non-numeric values in the CTE. Yes, I know you recast calculation order as you see fit. And certainly, if you didn't, performance would suffer in many cases. So keep recast the calculation order, but you need to add a prioritation order so that internally this is computed as

SELECT a, b FROM numonly WHERE b NOT LIKE '%[^0-9]%' AND THEN b < 100

For more complex queries it may not be equally trivial, but I have in this Connect item suggest two mechanisms to avoid illogical errors to be raised.
Details (expand)
Product Language
English

Version

SQL Server 2008 SP1

Category

SQL Engine

Operating System

Windows Vista 64-bit SP1
Operating System Language
US English
Steps to Reproduce
create table albert(a int NOT NULL,
                    b varchar(23) NOT NULL)
create table stina (a int NOT NULL)
                    
go
insert albert (a, b)
values (1, '99'),
         (2, 'Gurka'),
         (3, '89')
insert stina (a) values(1), (3), (9), (12)
go
WITH numonly AS (
SELECT a, b
FROM albert
WHERE b NOT LIKE '%[^0-9]%'
)
SELECT a, b
FROM numonly
WHERE b < 100
go
SELECT a.a, a.b + 100
FROM albert a
JOIN stina s ON s.a = a.a
go
drop table albert
drop table stina
Actual Results

(3 row(s) affected)


(1 row(s) affected)

(4 row(s) affected)


(1 row(s) affected)
a         b
----------- -----------------------
1         99
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Gurka' to data type int.

a        
----------- -----------
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Gurka' to data type int.
Expected Results

(3 row(s) affected)


(1 row(s) affected)

(4 row(s) affected)


(1 row(s) affected)
a         b
----------- -----------------------
1         99

a        
----------- -----------
1             199
3             189

Platform

X64
File Attachments
0 attachments
Sign in to post a comment.
Posted by SAinCA on 10/18/2012 at 5:35 PM
And it is AWFUL when the SELECT list is prepared from an XML element-value extract where the WHERE clause has excluded all possible string values and only integers are to be CAST(intString> AS int). Cost me quite some time to fudge around and nest queries just to get around this. Until I found this, I thought it was my XML queries that were throwing off the WHERE...

Definitely a BUG when SELECT list comes before WHERE filters.
Posted by eliman on 5/2/2012 at 2:43 PM
I was doing running total and run into the same issue. Has this issue been addressed?
Running : SQL Server 2008 R2
I wounder what the next version of SQL Server would be 2012
Posted by Martin Smith on 7/31/2011 at 9:13 AM
Comment to _ed. Your "workaround" doesn't work for precisely the reasons being complained about here! There is no guarantee that the Where clause will be processed before the Select list is computed.
Posted by ErikEckhardt on 5/12/2011 at 9:20 AM
Peter,

I am sure it's a bug because it's inconsistent. SQL is a mathematical tool that denotes the expected final result of a particular combination of particular data. The internal workings of the engine are supposed to be transparent. For example, if I use an INNER JOIN, it doesn't matter to me whether the engine does that internally through a loop, hash, or merge join. It also doesn't matter what order it chooses (which could be completely backwards from the order of the tables listed in the query).

If the engine's internal choices about join order suddenly change the validity of an identical query on identical data, then there is a disconnect, because now the correctness of the logical/abstract SQL has become dependent on the internals that the query writer should not have to consider. What if your query worked with LOOP joins but broke when a HASH join was chosen? That would be incorrect.

The solution is to either throw an error every time or none of the time. I'd be happy to get an error at development time that forces me to convert to int explicitly. I'm happy for my query to work as Erland has suggested. But I am NOT happy for my query to sometimes work and sometimes not, when the query and the data haven't changed.
Posted by HeavenCore on 5/11/2011 at 2:30 AM
I'd just like to add that this "bug" has also cost me countless hours of development time, systems that work fine for years suddenly decide (i presume after statistics have been updated) to invoke this behaviour and trigger a 4 hour session of digging through SQL procs
Posted by Robert Heinig II on 3/11/2011 at 4:11 AM
I agree that there is room for improvement here, though I don't necessarily agree that less errors should be raised.

I once had to analyze a report that turned out to rely on a custom to only use integers in a de facto varchar(9) key field. It worked when the query was designed and simply stopped working after someone mistyped, and immediately marked the wrongly keyed record for deletion. The author contends that a query that works once should not throw errors unless the schema actually changes. I'd say the query shouldn't have worked in the first place.

My suggestion: Throw warnings on any implicit char-int conversion (including some trace flag to turn them off). Throw warnings for char literals used as date literals unless they conform to iso8601 (was that the number?). Optionally define hints to allow specifying one way ("pester me until the query is safe against *any* data") or the other ("if it works now, please ignore future data-driven complications) per-query. Potentially do these things differently under WITH SCHEMABINDING.
Posted by Peter Recore on 3/9/2011 at 12:08 PM
Are you sure this is a bug emtucifor? SQL is a standard, and it has very specific rules about when expressions can be evaluated in a SQL expression. Do we know for a fact where it is specified that the optimizer is not allowed to evaluate expressions in the Select clause before those in the where clause?
Posted by ErikEckhardt on 7/21/2010 at 5:36 PM
This bug cost me and others about 8 hours of troubleshooting this week. And the query was working until statistics were updated. That's the spooky part, that a new join order can cause an error in a previously-working query. This was compounded by the fact that the query was in a UDF and 1) error messages don't list the line in the UDF but rather the calling SP and 2) I know of no way to see the execution plan of a UDF, which would have helped because I could have seen in the plan the conversion to float before the JOIN to the filtering table.
Posted by Microsoft on 3/3/2010 at 10:44 AM
Hi Erland,
Thanks for your feedback. We are aware of this request and hope to address it in a future version of SQL Server.

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.
Posted by Martin Smith on 9/29/2012 at 10:56 AM
SQL Server 2012 offers an additional work around which is replacing expressions that explicitly or implicitly cast to int with

TRY_CONVERT(int, expression_to_convert)
Posted by ErikEckhardt on 7/21/2010 at 5:34 PM
The CASE statement Erland referred to:

SELECT a.a, CASE WHEN IsNumeric(a.b) THEN a.b ELSE NULL END + 100
FROM albert a
JOIN stina s ON s.a = a.a

Not a great solution, but it works.
Posted by _ed on 4/29/2010 at 8:29 AM
why don't you use
where isnumeric(a.b) = 1
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement