Re: NULLs: theoretical problems?
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 24 Aug 2007 09:53:52 -0300
Message-ID: <46ced497$0$4025$9a566e8b_at_news.aliant.net>
>
>
> If I understand it correctly, the first evaluates to TRUE for rows where
> t.a is defined, and FALSE for rows where t.a is not; whereas the second
> evaluates to TRUE regardless.
>
> In other words, the DEF query is equivalent to
>
> SELECT * FROM t WHERE t.a IS NOT NULL AND (t.a = 5 OR TRUE)
>
> or, if we simplify, it's the difference between
>
> SELECT * FROM t WHERE t.a IS NOT NULL -- the DEF query
> and
> SELECT * FROM t -- The other one
>
>
> But I believe these queries are equivalent:
>
> SELECT * FROM t WHERE DEF t.a : t.a = 5
> SELECT * FROM t WHERE t.a = 5
Date: Fri, 24 Aug 2007 09:53:52 -0300
Message-ID: <46ced497$0$4025$9a566e8b_at_news.aliant.net>
Jon Heggland wrote:
> Quoth V.J. Kumar:
>
>>I do not understand why they are not equivalent. >> >>The first SQL predicate, according to your rules, evaluates to 'true': >>'DEF t.a : (t.a = 5 OR TRUE)' -> 'FALSE OR TRUE' -> 'TRUE' >> >>The second SQL predicate also evaluates to 'true': >>'t.a = 5 OR TRUE' -> 'UNKNOWN OR TRUE' -> 'TRUE' >> >>What is the difference ?
>
>
> If I understand it correctly, the first evaluates to TRUE for rows where
> t.a is defined, and FALSE for rows where t.a is not; whereas the second
> evaluates to TRUE regardless.
>
> In other words, the DEF query is equivalent to
>
> SELECT * FROM t WHERE t.a IS NOT NULL AND (t.a = 5 OR TRUE)
>
> or, if we simplify, it's the difference between
>
> SELECT * FROM t WHERE t.a IS NOT NULL -- the DEF query
> and
> SELECT * FROM t -- The other one
>
>
> But I believe these queries are equivalent:
>
> SELECT * FROM t WHERE DEF t.a : t.a = 5
> SELECT * FROM t WHERE t.a = 5
That depends. One could have a system where evaluating an undefined t.a would raise an error condition. Received on Fri Aug 24 2007 - 14:53:52 CEST