Home Dashboard Directory Help
Search

NVarchar changes where conditional evaluation order by Luxspes


Status: 

Closed
 as By Design Help for as By Design


1
2
Sign in
to vote
Type: Bug
ID: 772990
Opened: 11/30/2012 6:49:32 AM
Access Restriction: Public
1
Workaround(s)
view
0
User(s) can reproduce this bug

Description

If you use nvarchar in a query, it inverts the predicate in the execution plan of "AND" causing an "Error converting data type nvarchar to float" that should not happen because once the first part of an AND evaluates to false the second part should not be evaluated.

I discovered this bug migrating queries from SQL 2000 to SQL 2012, this bug does not exist in SQL 2000, it might have been introduced by SQL 2012 (or perhaps other version)
Details
Sign in to post a comment.
Posted by Microsoft on 4/25/2013 at 2:28 PM
Hello,

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that are by design.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager
Posted by Microsoft on 12/19/2012 at 3:10 PM
Thanks for your feedback, though we do not consider this to be a bug,we will add it as an item for consider for future enhancements
Posted by Martin Smith on 12/2/2012 at 5:36 AM
SQL Server doesn't guarantee any particular order of evaluation. See http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/ and http://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors for more about this.
Posted by Luxspes on 11/30/2012 at 6:57 AM
More detailed info here: http://luxspes.blogspot.mx/2012/11/sql-2012-bug-nvarchar-changes-where.html
Posted by Luxspes on 11/30/2012 at 6:53 AM
The Operating System was Windows 8. It was in English but its regional configuration was for Spanish (Mexico)
Sign in to post a workaround.
Posted by Luxspes on 11/30/2012 at 6:50 AM
I tried using a CTE, but it does not work, the execution plan is the same faulty one:

with V_1 as (select * from T_1 where ISNUMERIC(C)=1)
select * from V_1 where CONVERT(float,C)=0.0

Using a subquery also fails:

select * from (select C from T_1 where ISNUMERIC(C)=1) V_1 where CONVERT(float,C)=0.0

We can change the type explicitly to varchar in the query, that "fixes the problem":

select * from T_1 where ISNUMERIC(C)=1 and CONVERT(float,convert(varchar(100),C))=0.0

And finally you can always change the type in the table from NVARCHAR to VARCHAR