Or condition in where clause works differently in MS SQL comparing with Sybase
-
Hello,
I ran below statements in MS SQL, and got error, but fine in Sybase:
create table testDiffTypeSameCol (IssueNo int null, StringNumeric varchar(30) null)
insert into testDiffTypeSameCol values (1, 'this is string')
insert into testDiffTypeSameCol values (2, '123.456')
select * from testDiffTypeSameCol
where ( ( IssueNo = 2 and convert(numeric(15,2),StringNumeric) > 1.0 ) or ( IssueNo = 1 and substring(StringNumeric,1, 4) = 'this' )) -- Does any one know how to see how the engine processes the query internally? Execution plan does not give a lot of details.
Thank you!
-
The error message would help but I strongly suspect it is objecting to converting 'this is a string' to numeric. Search for TRY_CONVERT().
-
The error message is...
Msg 8114, Level 16, State 5, Line 12
Error converting data type varchar to numeric.
... and I'm surprised that this doesn't fail in Sybase but I don't have Sybase to test on. Apparently Sybase is smart enough to realize the conversion would not equate to a number and that fails the boolean test without alerting anyone that it did such a thing. If that's what happens in SyBase, I actually consider that to be a failure on the part of SyBase because such an "ignore the failure" action was not programmed in the code.
If you want it to ignore the failed action (a test that first requires an implicit conversion, in this case), then I agree with Ken McKelvey... use a TRY_CONVERT in SQL Server.
A better thing to do would be to respect the datatype of the column and test without using conversions to prevent from making a NON SARGable predicate on the secondary search column.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
SELECT * FROM testDiffTypeSameCol WHERE ( (IssueNo = 2 AND TRY_CONVERT(numeric(15,2), StringNumeric) > 1.0) OR (IssueNo = 1 AND SUBSTRING(StringNumeric, 1, 4) = 'this') );
-
For:
select * from testDiffTypeSameCol
where ( ( IssueNo = 2 and convert(numeric(15,2),StringNumeric) > 1.0 ) or ( IssueNo = 1 and substring(StringNumeric,1, 4) = 'this' ))
In Sybase it will call "convert(numeric(15,2),StringNumeric) > 1.0" ONLY when IssueNo = 2; it will call substring(StringNumeric,1, 4) = 'this' ) ONLY when IssueNo = 1. That is why it works in Sybase.
My question: how MS SQL handle "OR" in this case? I know how to make it work. I just want to understand internally how SQL Server process this query.
-
Thank you Moideen1254. Your query is the best and yet simple one. Our query actually is very long and complex. Simply replacing convert with try_convert will get rid of errors.
In Sybase, both works and the results are identical, but not in MS SQL. That keeps me wondering what is going on inside the MS SQL engine for "OR" condition.
1:
select * from testDiffTypeSameCol
where ( IssueNo = 2 and convert(numeric(15,2),StringNumeric) > 1.0 ) or ( IssueNo = 1 and substring(StringNumeric,1, 4) = 'this' )
2:
select * from testDiffTypeSameCol
where IssueNo = 2 and convert(numeric(15,2),StringNumeric) > 1.0
union
select * from testDiffTypeSameCol
where IssueNo = 1 and substring(StringNumeric,1, 4) = 'this'
Thank you again, Moideen1254 for your great thought and help!
-
I suspect that SQL Server evaluates all of the terms of the comparison while Sybase has success on the first term and stops. Try reversing the order of the terms in the or clause in Sybase and see what happens
-
For us mere humans, we read IssueNo = 2 as the first condition to be satisfied, and think the convert(...) should only be done if condition 1 is true.
For SQL, an AND condition is transitive, so both sides are eligible for evaluation for true/false before the final combination of true/false is calculated.
If the statement works in Sybase I am a bit surprised, it should equally be treating both sides of the AND as transitive. Maybe if you have a few million more rows and a high core-count server the multi-tasking that would involve would surface the problem in Sybase also.
If you want to enforce a particular order in predicate resolution you need to write code that tells the DBMS to do so.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
In Sybase,
this works:
select * from testDiffTypeSameCol
where ( ( IssueNo = 2 and convert(numeric(15,2),StringNumeric) > 1.0 ) or ( IssueNo = 1 and substring(StringNumeric,1, 4) = 'this' ))
I reversed the order for the first part, and it failed:
select * from testDiffTypeSameCol
where ( (convert(numeric(15,2),StringNumeric) > 1.0 and IssueNo = 2 ) or ( IssueNo = 1 and substring(StringNumeric,1, 4) = 'this' ))
Error:
11:52:29.890 DBMS TORDEVCCMS -- Number (249) Severity (16) State (1) Server (ASED114) Syntax error during explicit conversion of VARCHAR value 'this is string' to a NUMERIC field.
I reversed the for order only on the second part, and it works:
select * from testDiffTypeSameCol
where ( ( IssueNo = 2 and convert(numeric(15,2),StringNumeric) > 1.0 ) or ( substring(StringNumeric,1, 4) = 'this' and IssueNo = 1 ))
-
The following 2 queries work in Sybase, and also work in MS SQL:
select * from testDiffTypeSameCol
where IssueNo = 2
and convert(numeric(15,2),StringNumeric) > 1.0
select * from testDiffTypeSameCol
where convert(numeric(15,2),StringNumeric) > 1.0
and IssueNo = 2
It works maybe because the optimizer happens to choose IssueNo to process first; it might choose the other condition to process first if somethings are different. That is my understanding. I did not know that until I got error while converting to MS SQL from Sybase. I was trying to get a traces that would show in what order the SQL process multiple predicates in " where clause", ( the query execution plan shows the join order, but not predicates of where clause), but so far no luck. Thank you for your time and sharing your thoughts. Much appreciated!
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply