ALL and empty set

[Dimitri Furman]

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.

--
remove a 9 to reply by email

[Ed Murphy]

Dimitri Furman wrote:

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.

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

[Robert Klemme]

On 29.09.2006 05:24, Dimitri Furman wrote:

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.

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

[Alex Kuznetsov]

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/

[Hugo Kornelis]

On Fri, 29 Sep 2006 03:24:09 -0000, Dimitri Furman wrote:

(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 predicateis True for
every row RT in T, then “R <comp op<allT” is True."

--
Hugo Kornelis, SQL Server MVP

[Dimitri Furman]

Hi Hugo,

On Sep 29 2006, 05:24 pm, Hugo Kornelis
<hu**@perFact.REMOVETHIS.info.INVALIDwrote in
news:ap********************************@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 predicateis True for
every row RT in T, then “R <comp op<allT” 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