| Author |
Topic |
|
jshepler
Yak Posting Veteran
60 Posts |
 Posted - 2007-04-21 : 10:16:28
|
I have a query where the execution plan changes the order of evaluation and is causing errors.For example,WHERE <expression1> AND <expression2> it is important that expression1 be evaulated first, but sometimes the execution plan will switch them and evaulate expression2 first and throw an error. My expression2 is doing a conversion from varchar to bigint, but not all the varchars are numbers. My expression1 only allows rows where the varchars are numbers.I don't understand why SQL server would do that, but is there a way that I can force the order of evaulation?/jeff |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-21 : 11:01:36
|
if you have this:select * from x where <Exp1> and <Exp2> and you need exp1 evaluated before exp2, try changing it to this:select tmp.* from (select * from x where <expr1> ) tmp where <expr2> - Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
jshepler
Yak Posting Veteran
60 Posts |
Posted - 2007-04-21 : 14:27:34
|
| Same thing happened. It errored and the execution plan shows expr2 being evaluated first./jeff |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-04-21 : 21:10:00
|
order of evaluation is meaningless with an and operator.if you're converting varchar to bigint usewhere case when isNumeric(yourCol) then convert(bigint, yourCol) else null end and <your 2nd condition> you might want to show us your exact query becaues you're issue doesn't really belong in the where part of the select statement._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-22 : 01:16:58
|
| select * from table1 where col1 like '%[^0-9]%' -- this will give you all records that is not excusively numeric.Peter LarssonHelsingborg, Sweden |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-04-22 : 01:48:35
|
| I think the OP is asking about short circuting. You can't really have that in set based operations. Because SQL Server prepares a plan in advance of execution, it attempts to optimise the entire statement. It can only do that by evaluating each condition.DavidMProduction is just another testing cycle |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-22 : 06:34:58
|
I've always done this with a CASE statement, as Spirit suggests.Particularly when trying to use IsDate()  CASE WHEN MyStringColumn IS NULL OR MyStringColumn = '' OR IsDate(MyStringColumn) = 0 THEN NULL ELSE CONVERT(datetime, MyStringColumn) END Kristen |
|
|
|
jshepler
Yak Posting Veteran
60 Posts |
Posted - 2007-04-23 : 23:44:09
|
| The query in question can be found in [link="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82443"]this thread[/link].I started a new thread because that other one is too long a read, but it leads into the question that started this thread.Short version:I'm dynamically constructing the query based on search/filter criteria. The search can be constructed by any number of filters - even multiple filters with different values to be OR'd. I haven't figured out a good way to dynamically add table joins and keep track of which ones were added and why. The constructed queries have worked perfectly for months until we recently moved the database to from SQL2000 to SQL2005. Some saved searched started throwing conversion errors. The result of that other thread shows that SQL changed the order of evaluation.I never knew you couldn't depend on the order of evaluation. For years, my queries have always been evaluated in the order specified. Given "expr1 AND expr2", I've always been able to count on expr1 being evaluated first and if false, expr2 not being evaluated. I'm boggled that this isn't guaranteed(?).I'll use CASE. Don't know why I hadn't thought of that. Must have been my dumbfoundedness of SQL changing the order of evaulation on me./jeff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-24 : 04:23:14
|
| This isn;t a bug, or a "change of the order of execution" int eh new version. I have had this problem under SQL 2000 with simple queries such as:WHERE IsDate(MyStringColumn) = 1 AND CONVERT(datetime, MyStringColumn) > '20000101'where "illegal" date strings have blown up the CONVERT rather than failing the [earlier] IsDate() function.It would be purely down to how the Optimiser decided to build the query, and depending on available statistics, indexes, etc. that could change over time, let alone between versions!So actually you've just been lucky up to now!Kristen |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-04-24 : 14:23:58
|
| Hey Jeff,I think the only options left to solve your issue are to either redesign the database to be correct or to use a temp table. I'm not sure how complicated your actual query is, but a temp table may be a realtivly simple solution that can get around the order of evaluation issue you are experiencing.-Ryan |
|
|
|
jshepler
Yak Posting Veteran
60 Posts |
Posted - 2007-04-24 : 15:30:47
|
I believe the database design is fine, in and of itself. It's the constructing the dynamic query based on search/filter parameters that makes the query not as efficient as it could be - and I think that's just my inexperience.The fix that I used is a CASE. I went from...WHERE ov.ItemID = 68 AND (convert(bigint, ov.Value) & 16) = 16... toWHERE CASE WHEN ov.ItemID = 68 THEN convert(bigint, ov.Value) ELSE 0 END & 16) = 16 and it works just fine./jeff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-24 : 16:00:03
|
| "I think that's just my inexperience"Nah, its Experience!!Kristen |
|
|
|
|
How can I force the order of evaluation?