Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Teaser 3

Status
Not open for further replies.
gmmastros

gmmastros

Programmer
Feb 15, 2005
14,901
US
Without running these queries, predict the output.

Code: 
[COLOR=blue]If[/color] 1/0 = 10 And 1/1 = 0
  [COLOR=blue]Select[/color] [COLOR=red]'True'[/color] [COLOR=blue]As[/color] Query1
[COLOR=blue]Else[/color]
  [COLOR=blue]Select[/color] [COLOR=red]'False'[/color] [COLOR=blue]As[/color] Query1

Code: 
[COLOR=blue]If[/color] 1/0 = 10 And [COLOR=#FF00FF]sqrt[/color](1)=0
  [COLOR=blue]Select[/color] [COLOR=red]'True'[/color] [COLOR=blue]As[/color] Query2
[COLOR=blue]Else[/color]
  [COLOR=blue]Select[/color] [COLOR=red]'False'[/color] [COLOR=blue]As[/color] Query2

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Sort by date Sort by votes
interesting


it looks like it just skips over the 1/0 because 1/1 =0 is false

take a look at these two

Code: 
If 1/0 = 10 And 1/1 = 1
  Select 'True' As Query1
Else
  Select 'False' As Query1


or
Code: 
If 1/0 = 10 or 1/1 = 0
  Select 'True' As Query1
Else
  Select 'False' As Query1

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
Upvote 0 Downvote
this is boolean - so my guess is they're both false, - why because they evaluatate effectively to false and false

in boolean logic, the only time it would evaluate to true would be if both evaluate to true, like this:

If 5/5 = 1 And 1/1 = 1
Select 'True' As Query1
Else
Select 'False' As Query1

or am i completely missing the point.......
 
Upvote 0 Downvote
You're missing the point. In my original example, there was a 1/0. When you run this...

[tt][blue]Select 1/0[/blue][/tt]

You get....

[tt][red]Server: Msg 8134, Level 16, State 1, Line 1[/red]
Divide by zero error encountered.[/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
ha ha, ok... but you told us not to run it.... so my guess was that it would evaluate to 'false'

i guess i am missing the point here..., b/c if i cut and paste your code and run the query , it actually does evaluate to false....

i'll bow out and watch what you folks have to say about it
 
Upvote 0 Downvote
it looks like the optimizer doesn't even look at the 1/0 because the 1/1 = 0 is false anyway, just because we read from left to right doesn't matter SQL does

that said
If 1/0 = 10 And 1/1 = 0
or
If 1/1 = 0 and 1/0 = 10

both give the same result

when you change the and to an or it will blow up because SQL has to evaluate both expressions


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
Upvote 0 Downvote
nujoizey said:
i guess i am missing the point here..., b/c if i cut and paste your code and run the query , it actually does evaluate to false....
Click to expand...

Yup, they both return false - but the 2nd produces a division by 0 error and the 1st one doesn't. That's the teaser.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]
 
Upvote 0 Downvote
The optimizer does evaluate literals and expressions as part of determining its execution plan, and will take interesting shortcuts. I've noticed another one somewhat like this teaser.

Do these queries against some table in your database

select top 100 * from mytable
select top 100 * from mytable where 1 = 0

The execution plan for the second query doesn't involve any table access. That part is optimized right out of the equation. The filegroup the table is in could be completely missing and you might still get your empty resultset (which is not empty in the sense of being meaningless, it still has column names and data types and a row count).
 
Upvote 0 Downvote
esquared said:
The optimizer does evaluate literals and expressions as part of determining its execution plan, and will take interesting shortcuts.
Click to expand...

I'll buy that. But still... It's weird. Like the execution plan for these.

Code: 
Select top 100 * From myTable Where 1=SQRT(0)
Select top 100 * From myTable Where 1=29 * 0
Select top 100 * From myTable Where 1=0

The first one does a clustered index scan, but the last 2 don't show any table access at all.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
One would think that the optimizer should run deterministic system functions against literals as part of the same process as evaluating other literals. Oh well.

Look at this:

Code: 
select top 100 * from child where 1=1
select top 100 * from child where 0=sqrt(0)
The second query has an extra step in the stream, a filter:

WHERE:(STARTUP EXPR(0=sqrt(0)))

The first where clause is removed completely, but the second one is not pre-evaluated even though it seems the optimizer should be smart enough to do that.

 
Upvote 0 Downvote
I think the pot is calling the kettle black.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I expected most of the answers to be the same as aspvbnetnerd.

Either both will give a divide by zero error, or both will return false. It's interesting that this is NOT what happens. One query returns False and the other gives a divide by zero error.

Who cares?

I do. What's interesting here is that ONLY ONE expression is evaluated. More specifically, since this is an AND condition, if one of the expressions evaluates to FALSE then it does not matter what the other expression evaluates to, and is therefore NOT calculated. This is significant.

Of course, the examples I show above are trivial, but there are broader implications. Suppose the query was...

[tt][blue]If Exists(Select * From Table1 Where Id=1) And Exists(Select * From Table2 Where ID=1)
Print 'Yes'
Else
Print 'No'
[/blue][/tt]

The optimizer will decide which expression to evaluate first. If the result of the first expression is False, the next line executed is 'Print No'. If the result of the first expression is True, then the second expression is evaluated.

I will assume that the query optimizer makes a decision on which expression to evaluate first. If this code is stored in the db (view, stored procedure, function, etc...) then it is possible that (over time) the query plan that was originally calculated could become inefficient. Take a look at the previous query (with the 2 Exists statements). If one table has 1000 records and the other has 10 records, you would expect the optimizer to check the smaller table first. Now, fast forward 6 months. It could be that the row counts are drastically different and you could end up using an inefficient query plan.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
[lol]

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
I remember that article.

The Nullity Freak said:
If your heart pacemaker divides by zero, you're dead.
Click to expand...
HAHAHAHAHAHAHA ROFL

Gotta watch out for those pacemakers dividing by zero man, it's deadly.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
377
pjw001
pjw001
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
385
Volvere
V
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
246
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
T-SQL Dialog Box
Replies
8
Views
232
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
288
SaltyTheFrog
SaltyTheFrog

Part and Inventory Search

Sponsor

Back
Top