Exists

Question

Exists

Viewing 14 posts - 31 through 43 (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

OCTom (8/3/2011)
[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.

It's a bug that is fixed in the latest public preview build of SQL Server code name Denali (CTP 3).

In current SQL Server builds, the engine attempts to evaluate LOG(0) early - a process known as constant-folding. The bug is that constant-folding should never cause an error condition like this, and it happens before the optimizer gets a chance to simplify the query to remove the expression entirely.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TomThomson SSC Guru Points: 104773 More actions · Answer 2

Great question, and the excellent comments from Paul make it even better.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 3

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.

Except that quite often it does evaluate the expression - it's extremely inconsistent. There's no rhyme or reason to it at all.

example (A): select 'test' where exists (select LOG(1/0)) returns 'test'

example (B): select 'test' where exists (select LOG(0/1)) returns an error

This would make sense if SQL floating point included NaN as a value, but as far as I know it doesn't. It's clear that example (B) is evaluating the argument of LOG, 0/1, and getting 0; then attempting to evaluate LOG(0) and getting a domain error (0 is not in the domain of the log function); and returning that error. Example (A) is either not trying to evaluate the argument of LOG (which is clearly inconsistent with the treatment of LOG's argument in example (B)) or is trying to evaluate it, getting the same domain error (0 is not in the rh domain of the divide function) and throwing the domain error away (which is inconsistent with the treatment of domain errors in example (B)).

That pair of examples might be accounted for by different treatment of functions which are syntactically functions in SQL and other functions which are syntactically operators in SQL. But

example (C): declare @I int; set @I = 0; select 'test' where exists (select log(@I)) returns 'test'

so clearly this time there is no attempt to evaluate the function call although there could be no error in evaluating its arument, which blows that explanation out of the water.

It's just a sloppy inconsistent mess. Perhaps it's a sloppy inconsistent mess by design (I hope not), but that would be far from reassuring about the design consistency of T-SQL's semantics. Fortunately it may be fixed in the next release after SQL 2008 R2.

Tom

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Interesting question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

Tom.Thomson (8/3/2011)
Except that quite often it does evaluate the expression - it's extremely inconsistent.

Can't argue with the general sentiments there!

In their defence, I would say that quite a number of inconsistencies in SQL Server have been built up over the years, and are preserved to avoid breaking existing applications. Customers tend to make quite a lot of noise about breaking changes, so some fraction of the blame lies with us. Mostly though, I do agree that TSQL (and SQL come to think of it) is amazingly incoherent at times.

There's no rhyme or reason to it at all.

Well there is some logic to it.

The parser handles the error thrown by evaluating 1/0 and stops trying to constant-fold that expression. As the 1/0 is nested inside the LOG, that results in example A returning 'test'.

In example B, 0/1 can be safely constant-folded to 0 at compile time, but the bug with LOG (domain errors are unhandled) results in the error at compile time (estimated plan) as well as at run time.

In example C, the variable's value is not known at compile time (batches are compiled all at once and in a single pass) so again, no error results because constant-folding it not applied. If you add WITH (RECOMPILE) to the final SELECT, you will get a domain error (or invalid floating point operation) on recent builds of SQL Server because it compiles a one-off plan which will not be cached, and so can use the actual value of the variable at run time.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TomThomson SSC Guru Points: 104773 More actions · Answer 6

SQLkiwi (8/3/2011)
Well there is some logic to it.
The parser handles the error thrown by evaluating 1/0 and stops trying to constant-fold that expression. As the 1/0 is nested inside the LOG, that results in example A returning 'test'.
In example B, 0/1 can be safely constant-folded to 0 at compile time, but the bug with LOG (domain errors are unhandled) results in the error at compile time (estimated plan) as well as at run time.
In example C, the variable's value is not known at compile time (batches are compiled all at once and in a single pass) so again, no error results because constant-folding it not applied. If you add WITH (RECOMPILE) to the final SELECT, you will get a domain error (or invalid floating point operation) on recent builds of SQL Server because it compiles a one-off plan which will not be cached, and so can use the actual value of the variable at run time.

Thanks Paul, that does make sense of a sort. I'm glad things will be cleaner in the next release, though.

I've been on the development end of "you have to retain that bug because some customers think it's a feature" argument a few times in the past; and imposed it on outraged colleagues when acting as system authority for a range of products and having to help keep the company solvent by ruling in favor of changing the product that did confom to spec to interwork with the one that didn't. So I can certainly appreciate how MS gets into this sort of mess. But I find it hard to imagine anyone depending on "where exists (select ACOS(2))" throwing an error (even less on "where exists (select ACOS(2) from Tally where 1=0)" throwing an error.)

Tom

OCTom SSChampion Points: 11785 More actions · Answer 7

SQLkiwi (8/3/2011)
OCTom (8/3/2011)
[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.
It's a bug that is fixed in the latest public preview build of SQL Server code name Denali (CTP 3).
In current SQL Server builds, the engine attempts to evaluate LOG(0) early - a process known as constant-folding. The bug is that constant-folding should never cause an error condition like this, and it happens before the optimizer gets a chance to simplify the query to remove the expression entirely.

Thanks Paul. And, thanks to everyone who contributed to this interesting discussion.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 8

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

Nice question, and good remark Paul.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 9

Great question. I loved reading the blog and the discussion to date. I think I'm going to have to get myself a SQL Internals book one of these days, some of this stuff can be fascinating.

I have to say though from now on I'm doing a "select 1/0" in all of my subqueries, just to annoy and confuse my developers.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Anipaul SSC-Insane Points: 24681 More actions · Answer 10

Anipaul

SSC-Insane

Points: 24681

August 4, 2011 at 11:29 pm

#1364086

Great question... very interesting.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 11

Hardy21

SSCrazy Eights

Points: 9708

August 5, 2011 at 2:35 am

#1364146

Great question. Good to know.

Thanks

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 12

That's a great question but it doesn't seem too consistent. I would expect the query to return no errors only if both ARITHABORT and ANSI_WARNINGS were set to OFF.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

thoover SSC-Addicted Points: 423 More actions · Answer 13

Thanks for the question. this one gave me something to think about.

zymos SSCommitted Points: 1960 More actions · Answer 14

Great question that reminds of some great basic tips to keep in mind.

Thanks.