In DBISAM, comparing NULL to itself returns TRUE.  Under normal
circumstances this presents few problems for me, although it begs the
question of why "is null" exists.

However when chaining left outer joins, it can lead to strife and an
explosion of unwanted results.  e.g.

select *
  from T1
  left outer join T2 on T1.ID = T2.ID
  left outer join T3 on T2.Other = T3.Other

If T3.Other is sometimes NULL and the first join fails, then the second
join will succeed, although this is unlikely to be what was intended!

A bit of unscientific research using Google suggests that Sybase, MS SQL
Server and MySQL all consider the result of NULL = NULL to be NULL,
rather than TRUE.

Why does DBISAM work as it does?  Is this one of those situations that
the standards are silent on?


  Oliver

Oliver,

<< In DBISAM, comparing NULL to itself returns TRUE.  Under normal
circumstances this presents few problems for me, although it begs the
question of why "is null" exists.

However when chaining left outer joins, it can lead to strife and an
explosion of unwanted results.  e.g.

select *
  from T1
  left outer join T2 on T1.ID = T2.ID
  left outer join T3 on T2.Other = T3.Other

If T3.Other is sometimes NULL and the first join fails, then the second
join will succeed, although this is unlikely to be what was intended!

A bit of unscientific research using Google suggests that Sybase, MS SQL
Server and MySQL all consider the result of NULL = NULL to be NULL, rather
than TRUE.

Why does DBISAM work as it does?  Is this one of those situations that the
standards are silent on? >>

No, the standards are quite clear that NULL = NULL should return NULL.  I'll
have to look into this further and see what the reasons are, because I
believe that compatiblity with ranges has something to do with it.

--
Tim Young
Elevate Software
www.elevatesoft.com

Oliver,

It's because we allow for using the = operator with NULLs like this:

MyField = NULL

This is a legacy thing from back before we allowed IS NULL with filters, and
the filters are the underlying basis for the joins, WHERE clause, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> It's because we allow for using the = operator with NULLs like this:
>
> MyField = NULL
>
> This is a legacy thing from back before we allowed IS NULL with filters, and
> the filters are the underlying basis for the joins, WHERE clause, etc.

I guess you won't want to break existing code by changing the behavior
in WHERE clauses and filters, but what about in JOINs?  How about in v5?


  Oliver

Oliver,

<< I guess you won't want to break existing code by changing the behavior in
WHERE clauses and filters, but what about in JOINs?  How about in v5? >>

Version 5 has ANSI standard SQL NULL compliance.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hisss Boooo

Roy Lambert

Roy,

<< Hisss Boooo >>

That I can handle.  Just no more 300-post threads about the NULLs.

--
Tim Young
Elevate Software
www.elevatesoft.com