Thread: Intentional, or bug?

Hi!

I probably found an inconsistence of the working of the =
operator. Please read ahead, if you're interested.

I have two tables, let't call them a and b. They are:

a)      x   |  y
-----------------------------      1   |  100      2   |  NULL

b)      y   |  z
-----------------------------      1   |  'hallelujah'     NULL |  'hmm...'


Now let's issue this query:

select b.z from a,b where a.y=b.y;

The answer is: 'hallelujah', but it omits 'hmm...'.

Now let's issue the following:

select NULL = NULL;

The answer is: true!!!

My question is why does it omit the rows with NULL at the
first case?

However, I have a real life problem, when not all data is given in
a column, so I need a query to include the NULL=NULL rows. I can
workaround it as defining a new operator, ~=, with the
meaning "both null, or =". I did it, it works, but very slow.

So, is it an intentinal way of functioning, or it is bug somewhere?

Thanks,
Baldvin

On Sun, 16 Sep 2001, Kovacs Baldvin wrote:

> select NULL = NULL;
>
> The answer is: true!!!

This has got to be a bug.  NULL is not equal to anything (nor is it
unequal to anything).

SQL uses tri-valued logic, where answers are either true, false, or
unknown.

When selecting where a = b, if either is null the row will not be
selected.  Likewise for a <> b.  This is standard correct behaviour for
SQL.  One rationale is, if null represents an unknown value then how can
you know if it's equal or not?  It might in fact be equal but we don't
know.

> However, I have a real life problem, when not all data is given in
> a column, so I need a query to include the NULL=NULL rows. I can
> workaround it as defining a new operator, ~=, with the
> meaning "both null, or =". I did it, it works, but very slow.

If you want the rows where a = b and where a and b are both NULL, you must
specify it precisely:

where a = b or (a is null and b is null)

> So, is it an intentinal way of functioning, or it is bug somewhere?

This is how it is supposed to work.
-- 
Tod McQuillin

Tod McQuillin <devin@spamcop.net> writes:
> On Sun, 16 Sep 2001, Kovacs Baldvin wrote:
>> select NULL = NULL;
>> 
>> The answer is: true!!!

> This has got to be a bug.  NULL is not equal to anything (nor is it
> unequal to anything).

Indeed, this *should* yield NULL.  But right now, postgres' parser
rewrites "= NULL" as "IS NULL" for compatibility with certain brain-dead
Microsoft products.

There have been several discussions on pghackers about undoing this bit
of nonstandardness, but so far nothing's been changed.  See the
list archives for more detail than you really wanted.
        regards, tom lane

On Mon, 17 Sep 2001, Tod McQuillin wrote:

> On Sun, 16 Sep 2001, Kovacs Baldvin wrote:
> 
> > select NULL = NULL;
> >
> > The answer is: true!!!
> 
> This has got to be a bug.  NULL is not equal to anything (nor is it
> unequal to anything).

This is because of a hack due to a broken MS client that provided
=NULL for IS NULL in some (possibly small) case.  There's a grammar
hack which makes the token sequence = NULL into IS NULL.  There's
been talk about making this optional or ripping it out entirely (yay), 
see past discussions on this topic for more detail.