Re: NULLs: theoretical problems?

On 24 aug, 14:42, Jon Heggland <jon.heggl..._at_idi.ntnu.no> 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

Indeed. My compliments, althought I'm a bit surprised that others find it so hard to grasp.

> But I believe these queries are equivalent:
>
> SELECT * FROM t WHERE DEF t.a : t.a = 5
> SELECT * FROM t WHERE t.a = 5

As Bob correctly remarked, the second would raise some kind of (static, if that applies) error.

• Jan Hidders