SQLServerCentral / Question of the Day (QOD) / Testing Center / Question of the Day for 01 Mar 2007 / Latest Posts

RE: Question of the Day for 01 Mar 2007

kapil_kk — Tue, 27 Nov 2012 05:21:55 GMT

easy one for NULL :-)

RE: Question of the Day for 01 Mar 2007

L' Eomot Inversé — Tue, 20 Apr 2010 15:42:41 GMT

I read this question, decided it was a bit weird because where clauses don't evaluate to anything and concluded that it must be asking what truth-value/values the expression in the where clause evaluates to and ansered accordingly (UNKNOWN). The answer given as correct is FALSE. The explanation simply says that the contained expression always evaluates UNKNOWN, and doen't make any suggestion of how FALSE comes from here. I guess the author has invented a new terminology: in this new terminology a filter evaluates to something instead of including or excluding a row, and "evaluates to false" means "excludes the current row". That's not very useful, as the existing terminology makes perfect sense.

RE: Question of the Day for 01 Mar 2007

peratchiselvi.m — Wed, 24 Mar 2010 21:59:40 GMT

ok. ok. got it....thanks.

RE: Question of the Day for 01 Mar 2007

Jasmine D. Adamson — Wed, 24 Mar 2010 10:07:18 GMT

[quote][b]peratchiselvi.m (3/24/2010)[/b][hr]s. ssc went wrong there then.... thank u...[/quote]No not really, it's a kind of a trick question... when you use the = operator and compare to null, the result is always unknown rather than true or false. The proper expression is "IS NULL" which will evaluate to true or false rather than unknown. That's what the question was about.

RE: Question of the Day for 01 Mar 2007

peratchiselvi.m — Wed, 24 Mar 2010 05:38:29 GMT

s. ssc went wrong there then.... thank u...

RE: Question of the Day for 01 Mar 2007

Jasmine D. Adamson — Tue, 23 Mar 2010 09:28:07 GMT

[quote][b]peratchiselvi.m (3/23/2010)[/b][hr]In the answer for this question, "The expression "MyID = NULL" evaluates to Unknown rather than any true or false condition."then y it is false... instead of unknown. can anyone pls answer this...[/quote]It isn't "false" it is "null" - but it will fail in a where clause or case statement because it's "not true"

RE: Question of the Day for 01 Mar 2007

peratchiselvi.m — Tue, 23 Mar 2010 03:54:29 GMT

In the answer for this question, "The expression "MyID = NULL" evaluates to Unknown rather than any true or false condition."then y it is false... instead of unknown. can anyone pls answer this...

RE: Question of the Day for 01 Mar 2007

Deepak Sharma-2311 — Wed, 29 Jul 2009 03:17:21 GMT

This question is quite wrong. The SET key word can not be used by = (EqualTo operator). The correct syntax is:SET ANSI_NULLS OFFthis is wrong:SET ANSI_NULLS= OFF

RE: Question of the Day for 01 Mar 2007

Jeremy Giaco — Tue, 04 Mar 2008 13:38:07 GMT

To further complicate things...can you actually SET ANSI_NULLS [highlight=#ffff11][size="5"]= [/size][/highlight]ON?

RE: Question of the Day for 01 Mar 2007

SSIS\SSRS\SSAS — Wed, 13 Feb 2008 00:30:40 GMT

here i don't understand either!!. first there is mistake in question and that is followed by answer explanation. it says correct answer is "FALSE". AGaina in explanation it says , the where clause evalutes to UNKNOWN, neither true or false. LOOK beloew/.................Category: T-SQLWhat does this WHERE clause evaluate to? (there are rows with NULL values) SET ANSI_NULLS = ONselect top 10 * from MyTable where Myid = NULL Sorry - you were wrong Correct answer: [b]False [/b]Explanation: The expression "MyID = NULL" evaluates to Unkown rather than any true or false condition. Ref: NULL Versus NULL? - http://www.sqlservercentral.com/columnists/mcoles/2829.asp Join the discussion about this question

RE: Question of the Day for 01 Mar 2007

dde — Thu, 05 Apr 2007 21:44:00 GMT

But if actually when SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN why is the correct answer FALSE? At least that is what I got.(Also, there is this other question about upgrading from MSDE to SQL 2005, after I went back to the list of questions all my answers were deleted. Had to start again)

RE: Question of the Day for 01 Mar 2007

SQLBill — Wed, 14 Mar 2007 17:33:00 GMT

This is directly from the BOL's section on SET ANSI_NULLThe SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE....When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN.So, if the = or <> are used, it evaluates to FALSE, but if any other comparison is used it evaluates to UNKNOWN.-SQLBill

RE: Question of the Day for 01 Mar 2007

Steve Jones - SSC Editor — Fri, 02 Mar 2007 07:45:00 GMT

1. still FALSE2. TRUE3. still UNKNOWN4. FALSE5. Always TRUE6. YES!!7. One can only hope. It's just a game and not one I'd put on the resume/CV due to the moderator's inability to write great questions Steve

RE: Question of the Day for 01 Mar 2007

Larry Briscoe — Thu, 01 Mar 2007 17:16:00 GMT

I understand the True, False, Unknown of Null evaluations. However, It's not UNKOWN to me.

Will I receive results from the query? -- FALSE
Did the question specify "ANSI NULLS = ON"? -- FALSE
How does the predicate "Myid = NULL" evaluate? -- UNKNOWN
How does the WHERE clause evaluate? -- (I still say FALSE)
Does everyone get fired up about this? -- TRUE
Will I get my points back? -- UNKNOWN to me (Only Steve Knows)
Will I get over it? -- TRUE

RE: Question of the Day for 01 Mar 2007

Jeff Gray — Thu, 01 Mar 2007 13:38:00 GMT

What is a BOL? heh

RE: Question of the Day for 01 Mar 2007

Rick Townsend — Thu, 01 Mar 2007 13:30:00 GMT

Tracked down the most obvious quote from BOL:

< predicate >

Is an expression that returns TRUE, FALSE, or UNKNOWN.

Copied from the page on WHERE clause Search Conditions http://msdn2.microsoft.com/en-US/library/ms173545.aspx

RE: Question of the Day for 01 Mar 2007

Rick Townsend — Thu, 01 Mar 2007 13:16:00 GMT

That's the whole problem with three-part logic, it adds a third option to the usual true/false pair. ANSI SQL defines Unknown to handle the case where you're evaluating against rows where you don't know the value. Take a real-life example:

I have a sibling, who I call Sam. Is it a boy? It looks like a simple yes/no question, but you have to answer "Unknown", because you don't have sufficient information. Since ANSI NULLs represent "Unknown" values, you can't know if they match or not. (It's counter-intuitive, I know.)

Larry's example:

If NULL = NULL Print 'True'Else Print 'False'

seems correct because he assumed two-part logic. But it's a false-dichotomy; try expanding Jeff's example this way instead (remembering to set ANSI_NULLS ON):

if (null = null) print 'null = null is true'

else if not (null = null) print 'not (null = null) is true'

else print 'neither is true'

That shows it better. (null = null) isn't true, and it isn't false (not (false) would have been true). It's neither.

Coming back to Question of the Day, I think we all agree it's poorly written. After reviewing it, I'd like to see the question changed to:

What does the predicate in this WHERE clause evaluate to? (there are rows with NULL values, and ANSI_NULLS is ON)

RE: Question of the Day for 01 Mar 2007

Larry Briscoe — Thu, 01 Mar 2007 12:07:00 GMT

Jeff,

If the where clause is only true when the expression is true, then it's FALSE.

If NULL = NULL Print 'True'Else Print 'False'

RE: Question of the Day for 01 Mar 2007

TDuffy — Thu, 01 Mar 2007 11:02:00 GMT

I agree that this is poorly written for the reasons given before me. But, I got it right without waiting for tomorrow!

RE: Question of the Day for 01 Mar 2007

Jasmine D. Adamson — Thu, 01 Mar 2007 09:15:00 GMT

The answer for this is pretty wrong. I've been doin this stuff for a long time and all where clauses evaluate to either true or false. Always, 24/7/365, that's what you get. It's incorrect to have a where clause return NULL. Until we have computers that can simultaneously send back a row and not send back a row, our where clauses will be boolean, meaning two-valued.The comparison evaluates to NULL, but who cares - it's the effect on the where clause that's important. I'm irked that I didn't get points for this one.

RE: Question of the Day for 01 Mar 2007

Jeff Gray — Thu, 01 Mar 2007 08:03:00 GMT

Hi Larry,

The expression evaluates to Unknown. It is neither true nor false.

Try this

If NULL = NULL Print 'True'If NOT (NULL = NULL) Print 'False'

The problem with the question is that it asks what the WHERE clause evaluates to, which is only true if the expression is true.

Curiously, the explanation correctly indicates that the expression evaluates to Unknown. It's almost as if the explanation to a different question is given.

RE: Question of the Day for 01 Mar 2007

vinurajr — Thu, 01 Mar 2007 07:52:00 GMT

False is the Right Answer. That we know that we will get zero rows..

RE: Question of the Day for 01 Mar 2007

Larry Briscoe — Thu, 01 Mar 2007 07:51:00 GMT

How can that question be answered the way it is stated? I've known how NULLS work for ever and still got it wrong. I'm 100% confident in how NULLS work.

Myid = NULL and Myid != NULL

Will both evaluate FALSE, or not give results. If I received results would that be TRUE. By not getting results is FALSE!!!

I think the Question and Answer are phrased badly and incorrect. The answer should be FALSE. How can UNKNOWN be the answer, when I know what the results would be (FALSE).

Maybe, it's only UNKNOWN to the writer. Because, I know it's FALSE. It'll always return NO ROWS, that's pretty well KNOWN.

If you get the question right, it's obvious what UNKNOWN is. How NULLS work!!!

If you got the question right. Here's how NULLS work.

Myid IS NULL (is the proper way to write the statement)

Also, I agree with Jeff Gray on the ANSI NULLS setting.

Please give me my points back!

RE: Question of the Day for 01 Mar 2007

Eric Shaffer — Thu, 01 Mar 2007 07:42:00 GMT

If you want to know how many records there are with MyID that is null, you write the query this way:

select top 10 *  from MyTable where Myid IS NULL

Or to return all rows where Myid has a value:

select top 10 *  from MyTable where Myid IS NOT NULL

RE: Question of the Day for 01 Mar 2007

Jeff Gray — Thu, 01 Mar 2007 07:32:00 GMT

The question is somewhat completely and utterly wrong.

A where clause does not evaluate TO anything at all. In this case there will never be any rows matching the filter (assuming the normal ANSI NULLS setting), so the best answer among all of the possible but all incorrect answers is "False". "Unknown" is not correct, because it is "known" that no rows will match.

The expression IN the where clause evaluates to Unknown, but that is not what the question asks.

RE: Question of the Day for 01 Mar 2007

Hugo Felicio — Thu, 01 Mar 2007 02:16:00 GMT

I'm puzzled...isn't it the same as 'where 1=2' ? doesn't it always evaluate to false and isn't that why you don't get nothing on the results ??

RE: Question of the Day for 01 Mar 2007

Rick Townsend — Wed, 28 Feb 2007 21:25:00 GMT

Could we have the question expanded to include a comment that ANSI_NULLS is set to ON? Because if a user ran that with ANSI_NULLS OFF, the WHERE clause would evaluate to TRUE.

Question of the Day for 01 Mar 2007

Site Owners — Wed, 21 Feb 2007 10:05:00 GMT

Comments posted to this topic are about the Question of the Day for 01 Mar 2007 posted at http://www.sqlservercentral.com/testcenter/qod.asp?QuestionID=981.