NVarchar changes where conditional evaluation order

NVarchar changes where conditional evaluation order by Luxspes

Closed

as By Design Help for as By Design

Type:	Bug
ID:	772990
Opened:	11/30/2012 6:49:32 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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 (expand)

English

SQL Server 2012 - Developer Edition

SQL Engine

Other

Spanish

Here is what we found, lets say you have a table "T_1":

CREATE TABLE [dbo].[T_1](
[C] [nvarchar](50) NULL
)

Now, lets say you add some rows to it.

INSERT INTO [dbo].[T_1] ([C]) VALUES ('P')
INSERT INTO [dbo].[T_1] ([C]) VALUES ('Q')
INSERT INTO [dbo].[T_1] ([C]) VALUES ('R')

Now write this query:

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

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

The predicate in the execution plan is

CONVERT(float(53). [TestEvaltiationOrder) .[dbo] .[Tj].
[CLO)=(O.OOOOOOOOOOOOOOOeOOO) AND isnumeric
(CONVERT_IM PLICIT(varchar(1 OO),[TestEvaluationQrderj.
[dbo) . [T_1) .[Cj,O)) =(1)

As you can si it has changed from ISNUMERIC AND CONVERT to CONVERT AND ISNUMERIC

The query should return empty (since all the values for T_1.C are not numeric) and the predicate in the execution plan should be:

isnumeric
(CONVERT_IM PLICIT(varchar(1 OO),[TestEvaluationQrderj.
[dbo) . [T_1) .[Cj,O)) =(1) AND CONVERT(float(53). [TestEvaltiationOrder) .[dbo] .[Tj].
[CLO)=(O.OOOOOOOOOOOOOOOeOOO)

X64

0 attachments

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)

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