SQL Server should not raise illogical errors
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)
insert albert (a, b)
values (1, '99'),
insert stina (a) values(1), (3), (9), (12)
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
WHERE b NOT LIKE '%[^0-9]%'
SELECT a, b
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.
Upvotes: 82<=-=Mar 3 2010 10:44AM=-=>
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
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.<=-=Mar 9 2011 12:08PM=-=>
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?<=-=Mar 11 2011 4:11AM=-=>
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.<=-=May 11 2011 2:30AM=-=>
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<=-=May 12 2011 9:20AM=-=>
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.<=-=Jul 31 2011 9:13AM=-=>
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.<=-=May 2 2012 2:43PM=-=>
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
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. 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.<=-=May 13 2014 5:46AM=-=>
I don’t think this can be classified as bug.
Firstly SQL Engine is design to work in most efficient manner. In the query above if server evaluates where clause (or join condition) first then it will need to store the result in some temporary space, then do type conversion to this result set. What will happen if there are millions or rows?
Secondly server can not pinpoint!! :) exact rows to select has to go through a table scan. If we add index to column A then in some cases it will quickly be able to apply cast only on selected items.<=-=May 16 2014 7:44AM=-=>
To Tausif Ahmed – yes, it should be classified as a bug. There is a logical processing order that SQL engines should follow, and indeed Microsoft themselves include it in their documentation under http://technet.microsoft.com/en-us/library/ms189499.aspx. The WHERE clause should logically be processed before the SELECT clause.
SQL engines are allowed to rearrange operations to achieve better efficiency, provided that the results they produce do not differ from the results that would be produced if the query had been processed in the logical processing order. Clearly, there is a vast difference between producing a correct set of results (had the logical processing order been followed) and producing an error and refusing to provide any results.<=-=Sep 28 2015 10:59AM=-=>
This is certainly not a bug. Stop relying on implicit conversion of data types! For even scenarios where it does work, it is often more expensive than it’s worth. I’d much rather have a traceflag that I can set that warns or even prevents all implicit conversions from happening entirely (is this possible query optimizer team?). Implicit conversion is a nice feature but not always worth the false-positives it produces like in the case described here, and in the case where large scans are required to convert data before joining or filtering.<=-=Nov 24 2015 7:52AM=-=>
To Jonathan MacCollum – you seem to have completely missed the point of this bug report. The bug report is effectively saying “If SQL Server behaves correctly, it shouldn’t even be attempting to perform a conversion – so the fact that it’s raising a conversion error is indicative of the fact that SQL Server doesn’t behave correctly”.
The specific error here isn’t relevant (it could be, for example the use of a negative length specification to SUBSTRING). The point is, the database engine is performing an evaluation that is irrelevant to the final result set and, if the product was correctly following the logical processing order, it would never have attempted the evaluation.<=-=Feb 24 2016 11:06AM=-=>
@Jonathan MacCollum, it most certainly is. See ErikEckhardt’s comment. He is dead on.
I have inherited a data warehouse. Some of the rows (out of millions) have this problem.
If I do a select * from table_with_bad rows, it works just fine. (note, no WHERE clause)
If I then take one of the rows from the above result set – remember – it did not error, and do:
select * from table_with_bad_rows WHERE row = bad_row, I get an error. I expect that, because I have “*” and probably one of the columns is “bad”.
But, if I do:
select varchar_column from table_with_bad_rows where row=bad_row – I get the error – even though I am selecting ONLY a varchar column! There is NO conversion.
As ErikEckhardt points out below, this is exposing the inner workings of the SQL Server engine which we should not have to worry about.
So, we have three things:
1. Different behavior if a WHERE clause is present or not.
2. An Error thrown, even though the SELECT does NOT contain the offending column.
3. No way to find the offending data – the result set does not appear in SSMS, and in other tools, like the excellent DB Solo, I can see the data, but, cannot find the column with the offending value. It is probably being converted by the tool. But, the error message from SQL Server doesn’t even bother to tell us WHICH column has the error.<=-=Jul 12 2016 7:39AM=-=>
I am getting this exact same error when I put my query into a table valued function. I can run the query outside the function with no issue, but once I call the function supplying the exact same data, I get this issue. This bug was reported in 2010, it is now 2016. I think we can safely say MS isn’t doing anything about this. This is the very reason so many people are looking for non-MS solutions.