|
|
Posted by Dimitri Furman on 11/23/43 11:59
Hi Hugo,
On Sep 29 2006, 05:24 pm, Hugo Kornelis
<hugo@perFact.REMOVETHIS.info.INVALID> wrote in
news:ap2rh2540dnp6ssibojofp8fcdv73cn4mp@4ax.com:
> On Fri, 29 Sep 2006 03:24:09 -0000, Dimitri Furman wrote:
>
>> Even more interesting, the expression
>>
>>NULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')
>
> This is an interesting, since you can defend two answers. The simple
> version is that any comparison involving NULL should result in
> unknown. The other, almost equally simple, version is that if the
> subquery produces an empty set, it doesn;t matter what value is on the
> left-hand side; we can be sure that it'll be equal to all values in
> the empty set. So we don't care if the value for the left-hand side is
> supplied or missing, since we can evaluate anyhow.
All right, this is not too surprising considering how "consistently" NULLs
are treated in SQL. I guess we can write it off as another example. Now
when anyone says that a NULL is not equal to anything, I'll have a
valid objection! :)
> Since both definitions have their merit, we'll have to resort to
> consulting the documentation. Books Online says this about ALL:
>
> "Returns TRUE when the comparison specified is TRUE for all pairs
> (scalar_expression, x), when x is a value in the single-column set;
> otherwise returns FALSE."
This is what bothers me. In the case of an empty set there are no pairs,
and the comparisons cannot be either TRUE or FALSE - there can be no
comparisons in the first place - so according to the above, this should
fall under "otherwise" and return FALSE.
> To double-check, I consulted the description of ALL in the ISO
> standard SQL-2003. That one puts it even more bluntly, since it
> excplicitly mentions that case that the subquery returns an empty set:
>
> "Case:
> "a) If T is empty or if the implied <comparison predicate> is True for
> every row RT in T, then R <comp op> <all> T is True."
Well, this settles it. Thanks for digging this up - that's really the
answer I was looking for.
--
remove a 9 to reply by email
Navigation:
[Reply to this message]
|