|
|
Posted by Dimitri Furman on 10/29/49 11:59
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
Navigation:
[Reply to this message]
|