Comparing to null queries
Hello, I have a query: select * from table where del != 1; Let's assume that I have a record where del is null (del is a single character field). In version 3.23.22-beta I get the record returned with the above query, in version 4.1.10a I get nothing returned. Did something change between these versions or is this a bug? I couldn't find anything specific to this on the archives. TIA -- Mike Rykowski NU-IT Telecommunications and Network Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing to null queries
Hi,
'l' is neither equal to null nor different from null.
you can try select ('l'!=NULL) or select ('l'=NULL).
in 4.1.x you should write :
select * from table where del != l' or del is null;
mysql select * from tbl;
+--+
| del |
+--+
| NULL |
| a|
| b|
| l|
| m|
| l|
+--+
6 rows in set (0.02 sec)
mysql
mysql
mysql select * from tbl where del !='l';
+--+
| del |
+--+
| a|
| b|
| m|
+--+
3 rows in set (0.00 sec)
mysql select * from tbl where del != 'l' or del is null;
+--+
| del |
+--+
| NULL |
| a|
| b|
| m|
+--+
4 rows in set (0.00 sec)
Mathias
Selon Mike Rykowski [EMAIL PROTECTED]:
Hello,
I have a query:
select * from table where del != 1;
Let's assume that I have a record where del is null (del is a single
character field).
In version 3.23.22-beta I get the record returned with the above query,
in version 4.1.10a I get nothing returned.
Did something change between these versions or is this a bug? I
couldn't find anything specific to this on the archives.
TIA
--
Mike Rykowski
NU-IT Telecommunications and Network Services
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing to null queries
Mike Rykowski wrote: Hello, I have a query: select * from table where del != 1; Let's assume that I have a record where del is null (del is a single character field). In version 3.23.22-beta I get the record returned with the above query, in version 4.1.10a I get nothing returned. Did something change between these versions or is this a bug? I couldn't find anything specific to this on the archives. TIA You should get nothing returned. NULL is not a value, so it cannot be equal, *or not equal*, to anything. The result of NULL != 1 is NULL. NULL is not TRUE, so no rows where del is NULL should be returned. If 3.23.222-beta returned such rows, it was a bug in that ancient version which has since been fixed. If you want rows where del is null, you need to SELECT * FROM table WHERE del IS NULL; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing to null queries
Mike, If 3.23.22 gave (NULL != 1) = TRUE, that was a bug, because in SQL, (NULL != 1) is NULL. This 3.23.26 change history item might be your culprit: "Fixed `' to work properly with `NULL'." PB - Mike Rykowski wrote: Hello, I have a query: select * from table where del != "1"; Let's assume that I have a record where del is null (del is a single character field). In version 3.23.22-beta I get the record returned with the above query, in version 4.1.10a I get nothing returned. Did something change between these versions or is this a bug? I couldn't find anything specific to this on the archives. TIA No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 5/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
