Exists

Question

Exists

Viewing 15 posts - 16 through 30 (of 43 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Toreador (8/3/2011)
So if Microsoft are correct to claim that this is by design, then presumably they would acknowledge all the counter-examples provided by SQLkiwi as bugs?

Yes they have:

http://connect.microsoft.com/SQLServer/feedback/details/649957/case-expression-evaluates-else-branch-at-compile-time

All fixed in Denali CTP3 (the queries all return 'test' as they should)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 2

marlon.seton (8/3/2011)
I'm not convinced that EXPRESSION is not executed

More complex subqueries do indeed need to be evaluated to determine if a row is produced or not - it's just subqueries of the form SELECT <expression> where <expression> contains only constants or outer references that may be simplified. Adding the WHERE clause in your example adds a start-up filter expression that has to be evaluated, and that evaluation produces the expected error.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 3

Good question thanks.

http://brittcluff.blogspot.com/

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 4

SQLkiwi (8/2/2011)
deepak.a (8/2/2011)
Even for the below statement also
SELECT 'Test' WHERE EXISTS (SELECT SQRT(-1))
Some more:
SELECT 'Test' WHERE EXISTS (SELECT ACOS(PI()))
SELECT 'Test' WHERE EXISTS (SELECT ASIN(PI()))
SELECT 'Test' WHERE EXISTS (SELECT LOG10(0))

If you think these are strange, then what about this one?

SELECT 'Test' WHERE EXISTS (SELECT LOG10(1/0))

🙂

Best Regards,

Chris Büttner

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 5

Carlo Romagnano (8/3/2011)
EXISTS check if subquery returns ROWS and does not execute EXPRESSION or read column value. So, in this case divide by zero is never executed, but ONE ROW exists.

I'm still missing one piece of the puzzle. I understand everything up to "but ONE ROW exists"... what row? If the subquery is never executed, where is the one row coming from?

ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Nakul Vachhrajani SSChampion Points: 10283 More actions · Answer 6

Great question! Thank-you for sharing!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

OCTom SSChampion Points: 11785 More actions · Answer 7

ronmoses (8/3/2011)
Carlo Romagnano (8/3/2011)
EXISTS check if subquery returns ROWS and does not execute EXPRESSION or read column value. So, in this case divide by zero is never executed, but ONE ROW exists.
I'm still missing one piece of the puzzle. I understand everything up to "but ONE ROW exists"... what row? If the subquery is never executed, where is the one row coming from?
ron

That's what I'm thinking. Where does the phantom row come from in the subquery? There's a divide by zero error. Is that considered a row?

Thomas Abraham SSChampion Points: 10761 More actions · Answer 8

Carlo Romagnano (8/3/2011)
EXISTS check if subquery returns ROWS and does not execute EXPRESSION or read column value. So, in this case divide by zero is never executed, but ONE ROW exists.

Thanks for the explanation, and thanks to OP for question. Would it more correct to say something like "SQL Server determines that one row would exist if it finished processing the subquery", or am I misunderstanding what's going on?

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

OCTom SSChampion Points: 11785 More actions · Answer 9

Carlo Romagnano (8/3/2011)
EXISTS check if subquery returns ROWS and does not execute EXPRESSION or read column value. So, in this case divide by zero is never executed, but ONE ROW exists.

I just reread this explanation. Thanks Carlo.

So, the divide by zero is not executed. Why does a row exist?

Carlo Romagnano SSC-Insane Points: 22599 More actions · Answer 10

OCTom (8/3/2011)
ronmoses (8/3/2011)
Carlo Romagnano (8/3/2011)
EXISTS check if subquery returns ROWS and does not execute EXPRESSION or read column value. So, in this case divide by zero is never executed, but ONE ROW exists.
I'm still missing one piece of the puzzle. I understand everything up to "but ONE ROW exists"... what row? If the subquery is never executed, where is the one row coming from?
ron
That's what I'm thinking. Where does the phantom row come from in the subquery? There's a divide by zero error. Is that considered a row?

SELECT <constant expression> always returns ONE ROW (e.g. SELECT 1 returns ONE ROW).

For the EXISTS clause does not matter about expression or columns in the SELECT list, but the important thing is that it returns at least ONE ROW. So, the following queries are all equivalent:

SELECT 'TEST' WHERE EXISTS (SELECT 1,2,3,4)

or SELECT 'TEST' WHERE EXISTS (SELECT 1/0/2/3/4)

or SELECT 'TEST' WHERE EXISTS (SELECT * FROM sys.objects)

Finally, all expressions and columns in the select list of EXISTS clause are discarded.

Paul White SSC Guru Points: 150467 More actions · Answer 11

Christian Buettner-167247 (8/3/2011)
If you think these are strange, then what about this one?
SELECT 'Test' WHERE EXISTS (SELECT LOG10(1/0))
🙂

That's my new favourite! :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Ninja's_RGR'us

SSC Guru

Points: 294069

August 3, 2011 at 6:48 am

#1362994

This is insane... thanx for posting. :hehe:

Rose Bud Hall of Fame Points: 3147 More actions · Answer 13

Fascinating topic! The blog (link in explanation) was excellent - great examples to illustrate what's going on and a good dose of humor, as well. I recommend reading the blog, including the comments at the end where you may find some examples where this behavior might even be useful.

Adding link for convenience:

http://bradsruminations.blogspot.com/2009/09/age-old-select-vs-select-1-debate.html

OCTom SSChampion Points: 11785 More actions · Answer 14

Carlo Romagnano (8/3/2011)
OCTom (8/3/2011)
ronmoses (8/3/2011)
Carlo Romagnano (8/3/2011)
EXISTS check if subquery returns ROWS and does not execute EXPRESSION or read column value. So, in this case divide by zero is never executed, but ONE ROW exists.
I'm still missing one piece of the puzzle. I understand everything up to "but ONE ROW exists"... what row? If the subquery is never executed, where is the one row coming from?
ron
That's what I'm thinking. Where does the phantom row come from in the subquery? There's a divide by zero error. Is that considered a row?
SELECT <constant expression> always returns ONE ROW (e.g. SELECT 1 returns ONE ROW).
For the EXISTS clause does not matter about expression or columns in the SELECT list, but the important thing is that it returns at least ONE ROW. So, the following queries are all equivalent:
SELECT 'TEST' WHERE EXISTS (SELECT 1,2,3,4)
or SELECT 'TEST' WHERE EXISTS (SELECT 1/0/2/3/4)
or SELECT 'TEST' WHERE EXISTS (SELECT * FROM sys.objects)
Finally, all expressions and columns in the select list of EXISTS clause are discarded.

Thanks Carlo. I appreciate your help. Is it simply inconsistency that the following returns an error?

SELECT 'Test' WHERE EXISTS (SELECT LOG(0))

As noted earlier, this returns an error instead of ignoring the select list of the EXISTS.

:unsure:

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 15

Great question and some interesting info out of the references too.