Wrong NULL Comparing

Hello!

<=> worked fine.

The other way (in case it is a MySQL extension) would be:
select t2.id, t2.myint
from mytable t1 inner join mytable t2 on t1.id=t2.id
where (t1.myint = t2.myint or (t1.myint is null and t2.myint is null))
(never seen...)

hmm... two undefined values aren't the same although they are both
undefinded... hard to understand :-)

Informix 7.3 for example IS doing it with = (was just looking) and I thought
M$ should as well.

Having to set IS NULL on colums is expected behaviour. But column1=column2
(using field names) should return true in case both are NULL. I thought IS
NULL was just implemented in SQL to express something undefined (otherwise
you WOULD define something)

But that's gettin' philosophic...

You should add NULL=NULL as well as NULL<=>NULL in the documentation. I just
found this in http://www.mysql.com/doc/en/Working_with_NULL.html:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+

And I found that on the same page: "Two NULL values are regarded as equal in
a GROUP BY".

hmm... is that consequent?

Thanks for quickly answering.

TM


Hello.

On Fri 2003-02-28 at 19:46:29 +0100, thomas.mayer@depot172.dpd.de wrote:
[...]
> How-To-Repeat:
>
> I want to check if complete rows in two different tables are the same.
>
> Doing this, I did a Join and compared all other values except the PRIMARY
> KEY as it should be different.
>
> I was VERY surprised to see, that this didn't work with NULL Values.
[...]
> This is wrong for me! It should return the same result as shown above
> because NULL=NULL is true!

Sorry, but that's wrong. NULL means an unknown value. The result of
comparing an unknown value to another unknown value gives an unknown
result, which is represented by NULL.

mysql> SELECT 0=0, 0=1, 0=null, 1=null, null=null, null IS NULL;
+-----+-----+--------+--------+-----------+--------------+
| 0=0 | 0=1 | 0=null | 1=null | null=null | null IS NULL |
+-----+-----+--------+--------+-----------+--------------+
| 1 | 0 | NULL | NULL | NULL | 1 |
+-----+-----+--------+--------+-----------+--------------+

Please search the manual before reporting bugs:

http://www.mysql.com/doc/en/Problems_with_NULL.html

> I think, to fix this is a MUST! Any other DB should be able to do that.

No DB conforming to the SQL standard should do that. You can easily
see that if you consider why else would it define special expressions
to handle NULL values (like "IS NULL", etc.).


About your problem: The easiest way to archieve what you want is to
use the special '<=>' operator, which has the behaviour you expect. I
am not sure if that is part of the standard of a MySQL-specific
extension.

mysql> SELECT 0<=>0, 0<=>1, 0<=>null, 1<=>null, null<=>null;
+-------+-------+----------+----------+-------------+
| 0<=>0 | 0<=>1 | 0<=>null | 1<=>null | null<=>null |
+-------+-------+----------+----------+-------------+
| 1 | 0 | 0 | 0 | 1 |
+-------+-------+----------+----------+-------------+

See http://www.mysql.com/doc/en/Comparison_Operators.html

HTH,

Benjamin.

--
benjamin-mysql@pflugmann.de


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13868@lists.mysql.com
To unsubscribe, e-mail

Re: Wrong NULL Comparing

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thomas Mayer wrote:
> Hello!
>
> <=> worked fine.
>
> The other way (in case it is a MySQL extension) would be:
> select t2.id, t2.myint
> from mytable t1 inner join mytable t2 on t1.id=t2.id
> where (t1.myint = t2.myint or (t1.myint is null and t2.myint is null))
> (never seen...)
>
> hmm... two undefined values aren't the same although they are both
> undefinded... hard to understand :-)
>
> Informix 7.3 for example IS doing it with = (was just looking) and I thought
> M$ should as well.

Informix is slightly broken with respects to the SQL standard and the
relational model, then :). SQL Server will let you use '=' if you set
'ANSI NULLS' to off, but even that is slightly broken behavior.

> Having to set IS NULL on colums is expected behaviour. But column1=column2
> (using field names) should return true in case both are NULL. I thought IS
> NULL was just implemented in SQL to express something undefined (otherwise
> you WOULD define something)

NULL is sort of a philosophical thing, it represents an unknowable
quantity. There is no way to do an equality operator for it. It's tough
to not think of NULL being '0' (which is the programming point of view),
you need think of it as more the 'unknown' or 'I don't know' kind of
thing, and thus you can't compare the unknown to the unknown.

-Mark
- --
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/

For technical support contracts, visit https://order.mysql.com/?ref=mmma

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+X8W7tvXNTca6JD8RAjlHAJ9GMemkAyGE8y3NfnQFM/D6/NClkQCd HKsm
U06KQ0nVCXUq51HZeRPMXdQ=
=i0Xg
-----END PGP SIGNATURE-----


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13869@lists.mysql.com
To unsubscribe, e-mail

Re: Wrong NULL Comparing

Hi.

This is getting off-topic for the bug lists really fast now...

Reply-To is set to the main discussion list. Feel free to ignore it,
if you can turn this into a reproducible bug report. Else, it is
simply a discussion of the behaviour which is not supposed to be on
this list.

On Fri 2003-02-28 at 20:58:59 +0100, thomas.mayer@depot172.dpd.de wrote:
[...]
> The other way (in case it is a MySQL extension) would be:
> select t2.id, t2.myint
> from mytable t1 inner join mytable t2 on t1.id=t2.id
> where (t1.myint = t2.myint or (t1.myint is null and t2.myint is null))
> (never seen...)
>
> hmm... two undefined values aren't the same although they are both
> undefinded... hard to understand :-)
>
> Informix 7.3 for example IS doing it with = (was just looking) and I thought
> M$ should as well.
>
> Having to set IS NULL on colums is expected behaviour. But column1=column2
> (using field names) should return true in case both are NULL.

The standards say the result is NULL with good reasoning. There is no
sense in arguing about the contents, except if you can show the
standards to fundamentally flawed in this regard.

> I thought IS NULL was just implemented in SQL to express something
> undefined (otherwise you WOULD define something)

I am not sure what you mean. Sounds as if you are mixing up the column
attribute IS NULL / NOT NULL with the logical operator IS NULL which
you just used yourself in the SELECT above.

> You should add NULL=NULL as well as NULL<=>NULL in the documentation. I just
> found this in http://www.mysql.com/doc/en/Working_with_NULL.html:

Good idea. I also noticed it missing when I looked up the manual
references for you.

> And I found that on the same page: "Two NULL values are regarded as
> equal in a GROUP BY".
>
> hmm... is that consequent?

Maybe. I suppose it is meant as "group all unknown values together".

Usually you use GROUP BY to see some kind of distribution. If you
take, for example, which country some users are coming from, you want
to see the count for each country. Having a row showing all "unknown"
is a far more reasonable result than getting an addtional row for
every user which did not fill in the country field (think hundreds of
thousands for a moment).

But it is kind of irrelevant. Fact is, this, too, is what the standard
says and there is no compelling reason to think this was done by
mistake, because it makes sense from the right point of view (and there
is no better alternative behaviour, IMHO).

HTH,

Benjamin.


--
benjamin-mysql@pflugmann.de

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13870@lists.mysql.com
To unsubscribe, e-mail