Exists

Question

Post reply

Exists

Rafael Krisller

Right there with Babe

Points: 737
More actions
August 2, 2011 at 8:15 pm

#254802

Comments posted to this topic are about the item Exists

Viewing 15 posts - 1 through 15 (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

Good question, but try:

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

TSQL is not a model of consistency.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

deepak.a SSCertifiable Points: 5330 More actions · Answer 2

deepak.a

SSCertifiable

Points: 5330

August 2, 2011 at 10:59 pm

#1362804

Good Question thanks 🙂

deepak.a SSCertifiable Points: 5330 More actions · Answer 3

SQLkiwi (8/2/2011)
Good question, but try:
SELECT 'Test' WHERE EXISTS (SELECT LOG(0))
TSQL is not a model of consistency.

Even for the below statement also

SELECT 'Test' WHERE EXISTS (SELECT SQRT(-1))

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

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

M&M SSC-Insane Points: 21700 More actions · Answer 5

M&M

SSC-Insane

Points: 21700

August 3, 2011 at 12:03 am

#1362822

Thanks for the question

M&M

Gobikannan SSCrazy Points: 2735 More actions · Answer 6

Gobikannan

SSCrazy

Points: 2735

August 3, 2011 at 12:20 am

#1362830

Good question

-----------------
Gobikannan

andagr SSC-Addicted Points: 465 More actions · Answer 7

andagr

SSC-Addicted

Points: 465

August 3, 2011 at 12:43 am

#1362837

Interesting question, thanks!

/@devandreas

Mighty SSCrazy Eights Points: 9063 More actions · Answer 8

I didn't like the "explanation".

What I am missing is the real explanation why the sub query in the EXISTS doesn't result in an error.

It actually matters what is in that sub query. For example:

SELECT 'Test' WHERE EXISTS (SELECT 1 WHERE 1 = 0)

will not return anything.

DugyC Hall of Fame Points: 3804 More actions · Answer 9

Interesting question, and something definitely to be aware of.

Thanks

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

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

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.

paul s-306273 SSChampion Points: 10727 More actions · Answer 11

DugyC (8/3/2011)
Interesting question, and something definitely to be aware of.
Thanks

I'm not convinved that I need to be aware of this.

Interesting question though.

Carlo Romagnano SSC-Insane Points: 22603 More actions · Answer 12

paul s-306273 (8/3/2011)
DugyC (8/3/2011)
Interesting question, and something definitely to be aware of.
Thanks
I'm not convinved that I need to be aware of this.
Interesting question though.

I agree with you.

Toreador SSChampion Points: 11360 More actions · Answer 13

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?

P.S. I wonder how many of the 60% who got this right did so without running the code!

marlon.seton SSCrazy Points: 2623 More actions · Answer 14

I'm not convinced that EXPRESSION is not executed

Try this:

SELECT 'TEST'

WHERE EXISTS (SELECT 1/0 WHERE 1/0 =1)