Wrong NULL Comparing

====================
Wrong NULL Comparing
====================

=================
MySQL 4.0.11 gamma
RedHat Linux 7.3
Dell PowerEdge 2600
2GB RAM
MySQL RPM-Binaries installed
=================
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.

To give you a simple example, I used one table twice and I used the Primary
key as it then is the same:

drop table if exists mytable;
create table mytable
(id int (3) UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT,
myint int(10),
PRIMARY KEY(id))
TYPE=InnoDB;;

insert into mytable values (NULL, 1),(NULL, NULL);

select * from mytable;
-----------
RETURNS:
id myint
1 1
2 NULL
-----------

select t2.id, t2.myint
from mytable t1 inner join mytable t2 on t1.id=t2.id
where t1.myint=t2.myint;
-----------
RETURNS:
id myint
1 1
-----------

This is wrong for me! It should return the same result as shown above
because NULL=NULL is true!

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

Best Regards!

Thomas Mayer


------------------------------------------------------------ ---------
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-thread13864@lists.mysql.com
To unsubscribe, e-mail

Re: Wrong NULL Comparing

Thomas Mayer writes:
> ====================
> Wrong NULL Comparing
> ====================
>
> =================
> MySQL 4.0.11 gamma
> RedHat Linux 7.3
> Dell PowerEdge 2600
> 2GB RAM
> MySQL RPM-Binaries installed
> =================
> How-To-Repeat:
>

[skip]

>
> This is wrong for me! It should return the same result as shown above
> because NULL=NULL is true!
>
> I think, to fix this is a MUST! Any other DB should be able to do that.
>
> Best Regards!
>
> Thomas Mayer

No, it is not and it is thoroghly explained in our manual.

Any comparison with NULL returns false.

The only way to get comparisons with NULL's is with IS NULL , IS NOT
NULL ....

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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-thread13865@lists.mysql.com
To unsubscribe, e-mail

Re: Wrong NULL Comparing

Hello,

Sinisa Milivojevic wrote:
> Thomas Mayer writes:


>>This is wrong for me! It should return the same result as shown above
>>because NULL=NULL is true!

Wrong.

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

Anu other DB must do the same as this is how NULL is defined in theory and standards.

> No, it is not and it is thoroghly explained in our manual.
>
> Any comparison with NULL returns false.

Sinisa, is wrong here :)

Any comparison with NULL results in NULL not False :)

Try this:

mysql> select NULL = NULL, NULL != NULL, NULL < NULL, NULL > NULL;

+-------------+--------------+-------------+-------------+
| NULL = NULL | NULL != NULL | NULL < NULL | NULL > NULL |
+-------------+--------------+-------------+-------------+
| NULL | NULL | NULL | NULL |
+-------------+--------------+-------------+-------------+

mysql> select NULL = 1, NULL != 1, NULL < 1, NULL > 1;
+----------+-----------+----------+----------+
| NULL = 1 | NULL != 1 | NULL < 1 | NULL > 1 |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+


The concept behind NULL is that NULL means "missing", "impossible", "undefined",
"unknown"

When you compare "undefined" = "unefined" obviously result is "undefined" and
can't be True or False.


> The only way to get comparisons with NULL's is with IS NULL , IS NOT
> NULL ....




--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
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-thread13867@lists.mysql.com
To unsubscribe, e-mail

Re: Wrong NULL Comparing

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-thread13866@lists.mysql.com
To unsubscribe, e-mail