why the need for is null?

[Baldur Norddahl]

Hi,

How come "X=null" is not the same as "X is null"? I got a few selects with
queries like this:

select * from foo where customer=#custo mer# or (#customer# is null and customer
is null)

Without the last part, it will not correctly match null customers.

PS. I am using the ibatis framework for java, so the #customer# gets translated
to ? and the whole thing made into a prepared statement.

Thanks,

Baldur

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Martijn van Oosterhout]

Re: why the need for is null?

Because in trivalued logic, <anything> = NULL is also NULL. You need a
special operator to check if something is NULL, hence IS NULL and IS NOT
NULL. SQL standard says so.

This is a FAQ but I don't see it mentioned there.

On Thu, Jan 01, 2004 at 10:45:35PM +0100, Baldur Norddahl wrote:[color=blue]
> Hi,
>
> How come "X=null" is not the same as "X is null"? I got a few selects with
> queries like this:
>
> select * from foo where customer=#custo mer# or (#customer# is null and customer
> is null)
>
> Without the last part, it will not correctly match null customers.
>
> PS. I am using the ibatis framework for java, so the #customer# gets translated
> to ? and the whole thing made into a prepared statement.
>
> Thanks,
>
> Baldur
>
> ----------------------------------------------------------------
> This message was sent using IMP, the Internet Messaging Program.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postg resql.org so that your
> message can get through to the mailing list cleanly[/color]

--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> (... have gone from d-i being barely usable even by its developers
> anywhere, to being about 20% done. Sweet. And the last 80% usually takes
> 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/9J5SY5Twig3Ge+Y RAqrZAKCLvVVgXQ 9IyPWnjdXz5apiL md7kgCeOlp8
485iNa0msfRvMQA FKono8c8=
=7Si4
-----END PGP SIGNATURE-----

[Baldur Norddahl]

Re: why the need for is null?

Quoting Martijn van Oosterhout <kleptog@svana. org>:
[color=blue]
> Because in trivalued logic, <anything> = NULL is also NULL. You need a
> special operator to check if something is NULL, hence IS NULL and IS NOT
> NULL. SQL standard says so.
>
> This is a FAQ but I don't see it mentioned there.[/color]

Ok, but since this can be quite annoying and unexpected, could we get an
operator that does not use tristate logic but simply compares? Maybe == which
seems to be free :-)

So X==Y is true if X and Y are equal or both are null, false othervise.

Baldur


----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Mark Kirkwood]

Re: why the need for is null?

The relational model was designed using a 3 valued logic - true, false,null.

All relational database implementations will inflict this on you :-)

Not everyone is convinced that the 3 valued approach was the best way.
For some entertaining comments by Chris Date and Fabian Pascal see:

http://www.dbdebunk.citymax.com/page/page/622689.htm

best wishes

Mark

Baldur Norddahl wrote:
[color=blue]
>Hi,
>
>How come "X=null" is not the same as "X is null"? I got a few selects with
>queries like this:
>
>
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Martijn van Oosterhout]

Re: why the need for is null?

On Thu, Jan 01, 2004 at 11:53:29PM +0100, Baldur Norddahl wrote:[color=blue]
> Ok, but since this can be quite annoying and unexpected, could we get an
> operator that does not use tristate logic but simply compares? Maybe == which
> seems to be free :-)
>
> So X==Y is true if X and Y are equal or both are null, false othervise.[/color]

Annoying, not really. It's actually extremely useful. It's useful having a
value which is never equal to anything else, not even itself. If you use it
to represent "unknown" it will work for you. If you try to use it for
anything else, it will bite you.

You could create a new operator, but that means you'll have difficulty
moving it to any database that doesn't have that operator (which is most of
them).

If you want it to match perhaps you should forget NULL and use '' (zero
length string) instead.
--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> (... have gone from d-i being barely usable even by its developers
> anywhere, to being about 20% done. Sweet. And the last 80% usually takes
> 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/9KmGY5Twig3Ge+Y RAj3UAKDWp/e0BmmC+mFhjBAgP eSLX4FplQCgrQcn
3wzYOtI5oaoWaTZ 7rO/DdtA=
=X8fs
-----END PGP SIGNATURE-----

[Christopher Browne]

Re: why the need for is null?

In an attempt to throw the authorities off his trail, bbn-pgsql.general@c lansoft.dk (Baldur Norddahl) transmitted:[color=blue]
> How come "X=null" is not the same as "X is null"?[/color]

Because NULL is not really a "value" in SQL. Nothing can ever be
equal to a NULL, and that includes another NULL.

In Some Pseudo-SQL Database Systems, NULL is treated as a sort of
"zero" value, which is contrary to the SQL standards.

I seem to recall that in Microsoft's port of Sybase SQL Server,
there's some syntactic sugar that "x = NULL" is treated as if it were
querying "x is NULL."

It would presumably be _possible_ to modify PostgreSQL's query parser
to handle "x = NULL" similarly; feel free to submit a patch to that
end, if you consider it a vital change to make.
--
output = reverse("ac.not elrac.teneerf" "@" "454aa")

http://www.ntlug.org/~cbbrowne/oses.html

Rules of the Evil Overlord #212. "I will not send out battalions
composed wholly of robots or skeletons against heroes who have qualms
about killing living beings. <http://www.eviloverlor d.com/>

[Tom Lane]

Re: why the need for is null?

Martijn van Oosterhout <kleptog@svana. org> writes:[color=blue]
> If you want it to match perhaps you should forget NULL and use '' (zero
> length string) instead.[/color]

Yes. The SQL semantics essentially define NULL as meaning "unknown",
which does not mean "empty" or "not applicable" or anything like that
--- it means "I am not sure what this field should contain". The
spec's semantics work properly under that interpretation. For other
interpretations they will confuse and distress you.

It's better to choose a specific non-null value to represent "empty",
if you want the semantics that "empty" is equal to "empty".


BTW, the actual spec text that mandates this is SQL99 Part 2 section
8.2 <comparison predicate>, general rule 1:

1) Let XV and YV be two values represented by <value expression>s X
and Y, respectively. The result of:

X <comp op> Y

is determined as follows:

Case:

a) If either XV or YV is the null value, then

X <comp op> Y

is unknown.

b) Otherwise, [ etc etc ]

It may be illuminating that the boolean value "unknown" is the same as
(or at least the standard does not distinguish it from) boolean NULL.
Cf. section 4.6:

The data type boolean comprises the distinct truth values true and
false. Unless prohibited by a NOT NULL constraint, the boolean
data type also supports the unknown truth value as the null value.
This specification does not make a distinction between the null
value of the boolean data type and the unknown truth value that is
the result of an SQL <predicate>, <search condition>, or <boolean
value expression>; they may be used interchangeably to mean exactly
the same thing.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Re: why the need for is null?

Mark Kirkwood <markir@paradis e.net.nz> writes:[color=blue]
> The relational model was designed using a 3 valued logic - true, false,null.
> All relational database implementations will inflict this on you :-)[/color]

Not sure that it's fair to characterize this as a property of the
relational model. It is a property of the SQL standard. There are
many purists who say that SQL is not really relational at all (Chris
Date being one of the more prominent ones, IIRC), but in any case,
SQL drew three-valued logic from other sources than the relational
model of databases.

However, it is true that all spec-conforming implementations of SQL
will inflict this on you. MS SQL Server, for one, has apparently been
non-compliant on this point in the past, and I'm not too sure about
Oracle.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Mark Kirkwood]

Re: why the need for is null?


Tom Lane wrote:
[color=blue]
>Not sure that it's fair to characterize this as a property of the
>relational model. It is a property of the SQL standard.
>[/color]
Yes indeed - I fell into the classic "Relational model and SQL are not
the same thing" trap !

Mark



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Baldur Norddahl]

Re: why the need for is null?

Quoting Martijn van Oosterhout <kleptog@svana. org>:
[color=blue]
> Annoying, not really. It's actually extremely useful. It's useful having a
> value which is never equal to anything else, not even itself. If you use it
> to represent "unknown" it will work for you. If you try to use it for
> anything else, it will bite you.[/color]

I need it to represent "empty" because the field in question is a foreign key to
another table. If it represented "unknown" the foreign key should block it as
it could not possible know if that "unknown" value was valid. But I can't argue
against the SQL standard of course.
[color=blue]
> You could create a new operator, but that means you'll have difficulty
> moving it to any database that doesn't have that operator (which is most of
> them).[/color]

Any commercial database vendor would be happy to make such a feature just for
that reason: to lock me in to their database :-). I do not try to stay database
neutral, and use lots of other features that will only work in postgresql.
[color=blue]
> If you want it to match perhaps you should forget NULL and use '' (zero
> length string) instead.[/color]

Then I need to have a meaningless entry in the foreign table, and fill my code
with special cases that filter out that fake entry before showing the data to
the user.

Besides who said I didn't want to allow the empty string as valid data? This
would be even more an issue if the field was a nummeric, where any nummeric
value is ok. If I can not use NULL to represent "empty" or "not applicateable"
I would have to make a special field that tells me if I should ignore the
previous field or not. Does not sound reasonable when NULL works fine for just
that.

The best compromise I found so far is this "X=Y or X is null and Y is null"
construct. Just looks hard to understand and cumpersome for someone which is
not expert on this issue.

Baldur

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Chris Travers]

Re: why the need for is null?

Hi all;

Here is a brief guide to NULL's and Referential Integrity:

NULL is a special SQL value meaning 'unknown.' Well, it is a little more
complicated and NULL can mean "value does not exist." Therefore X = NULL is
NULL becuase we don't know if the NULL is equal to X. So:
NULL does not equal NULL
NULL does not equal anything else.
NULL does not equal an empty string
You get the picture.

Think of it this way: 2 non-existant or unknown values don't equal any
other existant or non-existant value, known or unknown.

Now, referential integrity is defined as follows:
For every non-NULL foreign key, there is a corresponding primary key in the
referenced table.
Note that NULL's are specifically handled in the RI definition.

When do I use NULL's? NULL's have a few uses:
1: To indicate that the foreign key does NOT correspond with a primary key
in the referencing table. In this case, NULL means something like "value
does not exist" (this is not the same as an empty value). In an employee's
table, a NULL in the manager's field would mean "Employee does not report to
any other employee as a manager."
2: To indicate that the value probably exists, but is unknown at present
(we don't know this customer's address, so we set it to NULL).

Best Wishes,
Chris Travers


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Guy Fraser]

Re: why the need for is null?

Baldur Norddahl wrote:
[color=blue]
> Quoting Martijn van Oosterhout <kleptog@svana. org>:
>[/color]
....
[color=blue][color=green]
>>You could create a new operator, but that means you'll have difficulty
>>moving it to any database that doesn't have that operator (which is most of
>>them).
>>
>>[/color]
>
>Any commercial database vendor would be happy to make such a feature just for
>that reason: to lock me in to their database :-). I do not try to stay database
>neutral, and use lots of other features that will only work in postgresql.
>
>[/color]
There already is an operator, and it is the ANSI SQL operator "IS". Just
because "IS" does not use puctuation characters does not mean it is not
an operator.
[color=blue][color=green]
>>If you want it to match perhaps you should forget NULL and use '' (zero
>>length string) instead.
>>
>>[/color][/color]
An empty string is an empty string, and a NULL is the lack of there being a string, they are not the same. If you want to emulate what you have proposed then use the function "coalesce".

Example:

select coalesce(string _column,'') from some_table ;

This will return an empty string for all records that have no data in
string_column.

I have designed a number of realtime data collection programs, and when inserting only the available data into the proper columns of the table, I often end up will "NULL" columns because there was no data for that column. It is very usefull to know if you had data available or if the data was 0 or an empty string.

If you still don't understand, then use MySQL it is messed up and allows weird things like most of what you want to do.

Happy New Year




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Gaetano Mendola]

Re: why the need for is null?

Martijn van Oosterhout wrote:
[color=blue]
> On Thu, Jan 01, 2004 at 11:53:29PM +0100, Baldur Norddahl wrote:
>[color=green]
>>Ok, but since this can be quite annoying and unexpected, could we get an
>>operator that does not use tristate logic but simply compares? Maybe == which
>>seems to be free :-)
>>
>>So X==Y is true if X and Y are equal or both are null, false othervise.[/color]
>
>
> Annoying, not really. It's actually extremely useful. It's useful having a
> value which is never equal to anything else, not even itself. If you use it
> to represent "unknown" it will work for you. If you try to use it for
> anything else, it will bite you.
>
> You could create a new operator, but that means you'll have difficulty
> moving it to any database that doesn't have that operator (which is most of
> them).
>
> If you want it to match perhaps you should forget NULL and use '' (zero
> length string) instead.[/color]

Don't mentioning the fact that for Oracle a zero length string is NULL!
Isn't that funny ?

Regards
Gaetano Mendola

[Baldur Norddahl]

Re: why the need for is null?

Quoting Guy Fraser <guy@incentre.n et>:
[color=blue]
> There already is an operator, and it is the ANSI SQL operator "IS". Just
> because "IS" does not use puctuation characters does not mean it is not
> an operator.[/color]

"IS" is not an operator in postgresql, at least not in the same sense that "="
is an operator. You can not do "\do is" while "\do =" works fine.
[color=blue]
> select coalesce(string _column,'') from some_table ;[/color]

Will postgresql still make effective use of the indexes if I use a function like
coalesce on the column before comparing it?

Even if it does, the method I already use is more effective.
[color=blue]
> If you still don't understand, then use MySQL it is messed up and allows
> weird things like most of what you want to do.[/color]

1) I understand the issues involved perfectly. I just happens to have a table
where it would be usefull that NULL=NULL is true. It is not so, and therefore I
have to use a syntax that is hard to read and I have been made to understand
that I will have to accept that. Fine.

2) What kind of crap is that flaming me like this? Do all users that ask a
question about why postgresql or the sql standard implements a feature in a
specific way, end up being told to switch to mysql?

3) Mysql knows how to compare nulls:

mysql> select null=null;
+-----------+
| null=null |
+-----------+
| NULL |
+-----------+
1 row in set (0.01 sec)

mysql> select null is null;
+--------------+
| null is null |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)

Baldur


----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Mike Mascari]

Re: why the need for is null?

Baldur Norddahl wrote:
[color=blue]
>Will postgresql still make effective use of the indexes if I use a function like
>coalesce on the column before comparing it?
>
>[/color]
PostgreSQL doesn't index NULLs, which may or may not be a problem for
you. Perhaps creating a functional index on the COALESCE(myfiel d, '')
would achieve what you want, if you are querying the data in a similar
manner.
[color=blue]
>1) I understand the issues involved perfectly. I just happens to have a table
>where it would be usefull that NULL=NULL is true. It is not so, and therefore I
>have to use a syntax that is hard to read and I have been made to understand
>that I will have to accept that. Fine.
>
>[/color]
If you don't want to change your code, you can optionally set
TRANSFORM_NULL_ EQUALS to TRUE in postgresql.conf :

http://www.postgresql.org/docs/current/static/runtime-config.html

This is a parse-time transformation, so a comparison between two
attributes whose value is NULL using the equality operator will still
yield NULL:

[test@lexus] select NULL = NULL;
?column?
----------
t
(1 row)

[test@lexus] create table foo (x integer, y integer);
CREATE TABLE
[test@lexus] insert into foo values (NULL, NULL);
INSERT 164948 1
[test@lexus] select (x = y) from foo;
?column?
----------

(1 row)

[test@lexus] select (x = NULL) from foo;
?column?
----------
t
(1 row)

I suggest it only as a temporary stop-gap until the code can be changed
into something SQL compliant.
[color=blue]
>2) What kind of crap is that flaming me like this? Do all users that ask a
>question about why postgresql or the sql standard implements a feature in a
>specific way, end up being told to switch to mysql?
>
>[/color]
No. :-)

Mike Mascari




---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster