Yet another null question

[doug]

My question question from MySQL 4.0.22: is it okay that a null interger tests as
equal to any value. I am not sure the table structure matters but in case it is
I included it.

I did not expect that null was not not-equal to 1. Cursory testing seems to
indicate that a null integer field is not not-equal to any specific value. If
this is the "known" behavior, I would suggest that this behavior be added to
section A.5.3. Testing the field=1 works as I would expect, that is the null
value is not equal to 1.

Examples follow:

mysql> explain new_payments;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| payid  | int(11)     |      | PRI | 0       |       |
| Custid | int(11)     |      | MUL | 0       |       |
| User   | varchar(16) |      |     |         |       |
| CkNum  | varchar(20) | YES  |     | NULL    |       |
| Paid   | double      | YES  |     | NULL    |       |
| Date   | date        | YES  |     | NULL    |       |
| Type   | varchar(8)  | YES  |     | NULL    |       |
| Notes  | varchar(80) | YES  |     | NULL    |       |
| Tag    | varchar(15) | YES  |     | NULL    |       |
| closed | tinyint(4)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

mysql> select * from new_payments where closed<>1;
Empty set (0.02 sec)

mysql> select * from new_payments where closed is null;
+-------+--------+---------+-------+--------+------------+------+---------+------+--------+
| payid | Custid | User    | CkNum | Paid   | Date       | Type | Notes   | Tag  | closed
|
+-------+--------+---------+-------+--------+------------+------+---------+------+--------+
|   956 |    198 | stepout | 3001  | 116.95 | 2005-09-16 | AmEx | 0508044 | 7069 |   NULL
|
|   957 |    145 | anytech | 62401 |    102 | 2005-09-16 | MC   | 0504304 | B058 |   NULL
|
+-------+--------+---------+-------+--------+------------+------+---------+------+--------+
2 rows in set (0.02 sec)

mysql> select count(*) from new_payments;
+----------+
| count(*) |
+----------+
|      945 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from new_payments where closed=1;
+----------+
| count(*) |
+----------+
|      943 |
+----------+
1 row in set (0.01 sec)


_____
Douglas Denault
http://www.safeport.com
doug@stripped
Voice: 301-469-8766
  Fax: [phone]