ALL and empty set

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dimitri Furman - 29 Sep 2006 04:24 GMT

SQL Server 2000 SP4. Hoping to find a logical explanation for a certain
behavior.

Consider this script:

USE pubs
GO

IF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = 'CA')
PRINT 'TRUE'
ELSE
PRINT 'FALSE'

This, as expected, prints FALSE, since not all authors in CA are under
contract. Now, if the script is changed as follows:

USE pubs
GO

IF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = '')
PRINT 'TRUE'
ELSE
PRINT 'FALSE'

then the result is TRUE. In other words, the expression evaluates to TRUE
when the select statement produces an empty set, which doesn't make sense
to me. Even more interesting, the expression

NULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')

still evaluates to TRUE (ANSI_NULLS is ON).

Can anyone explain these results? Is this the expected behavior in the SQL
standard, or something that is specific to SQL Server? Thanks.

Signature

remove a 9 to reply by email

Reply to this Message

Ed Murphy - 29 Sep 2006 04:41 GMT

> Consider this script:
>
[quoted text clipped - 27 lines]
> Can anyone explain these results? Is this the expected behavior in the SQL
> standard, or something that is specific to SQL Server? Thanks.

It's vacuously true because there are no elements in the set to
produce a non-equality.

Reply to this Message

Robert Klemme - 29 Sep 2006 10:11 GMT

> SQL Server 2000 SP4. Hoping to find a logical explanation for a certain
> behavior.
[quoted text clipped - 30 lines]
> Can anyone explain these results? Is this the expected behavior in the SQL
> standard, or something that is specific to SQL Server? Thanks.

I think that's general boolean logic. If you evaluate AND over a set of
items you start with TRUE and go until you reach the first FALSE. In
Ruby:

>> def and_all(items)
>> items.each {|i| return false unless i}
>> true
>> end

=> nil

>> and_all [true, true, true]

=> true

>> and_all [true, true]

=> true

>> and_all [true]

=> true

>> and_all []

=> true

>> and_all [true, false, true]

=> false

>> and_all [true, true, false]

=> false

Kind regards

robert

Reply to this Message

Alex Kuznetsov - 29 Sep 2006 16:59 GMT

Dimitri,

I do not think it is a good idea to use ALL clause in production ever,
because it is not intuitive to understand.
The mainstream approach is to use IN and/or EXISTS and/or JOIN.
Also note that queries using mainstream features have a better chance
to get a decent execution plan.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Reply to this Message

Hugo Kornelis - 29 Sep 2006 22:24 GMT

(snip)

> the expression evaluates to TRUE
>when the select statement produces an empty set, which doesn't make sense
>to me.

Hi Dimitri,

Hmm, it actuallly makes perfect sense to me. This is a bit like me
bragging that I've slain every single dragon that has ever set fooot in
my back yard. And that I've rescued every princess that has been locked
away in a high tower during my life time.

Of course, I've never fought a dragon or rescued a princess - but since
no dragon has ever set foot in my back yard (see! they are THAT afraid
of me <g>) and no princess has been locked away in a high tower during
my life time, the statements above are still true.

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

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."

Since there are no values in the set, there are no pairs - just as there
are no dragons in my back yard.

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."

Signature

Hugo Kornelis, SQL Server MVP

Reply to this Message

Dimitri Furman - 30 Sep 2006 02:40 GMT

Hi Hugo,

>> Even more interesting, the expression
>>
[quoted text clipped - 7 lines]
> 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
[quoted text clipped - 3 lines]
> "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.

Signature

remove a 9 to reply by email

Reply to this Message