|
|
Posted by Robert Klemme on 11/23/98 11:59
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
Navigation:
[Reply to this message]
|