#48251 [Opn->Fbk]: Mysql selects/deletes rows it shouldn't when using IS NULL

ID: 48251 Updated by: johannes@php.net Reported By: dan at manifestwebdesign dot com -Status: Open +Status: Feedback Bug Type: MySQL related Operating System: linux PHP Version: 5.2.9 New Comment: Thank you for this bug report. To properly diagnose the problem, we need a short but complete example script to be able to reproduce this bug ourselves. A proper reproducing script starts with <?php and ends with ?>, is max. 10-20 lines long and does not require any external resources such as databases, etc. If the script requires a database to demonstrate the issue, please make sure it creates all necessary tables, stored procedures etc. Please avoid embedding huge scripts into the report. PHP simply passes the query through to MySQL so the problem is somewhere there. PLease provide a complete example including all CREATE TABLE statements and version information (mysql server, mysql clien library, ...) Previous Comments: ------------------------------------------------------------------------ [2009-05-12 22:06:30] dan at manifestwebdesign dot com Description: ------------ Mysql ignores the IS NULL in the WHERE statement when the query is executed from php. In the example I give, the AddressID is auto_increment primary key. As you can see from the output, the address ID gets a value of 65 for the inserted row. I then DELETE WHERE AddressID IS NULL and the row is removed. It will also return the row when I execute SELECT * FROM Address WHERE AddressID IS NULL. It happens in with mysql_query, PDO::exec() or PDO::query() and with or without ` around the table name. Reproduce code: --------------- //PHP 5.2.9 on Hostmonster.com mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); mysql_select_db(DB_NAME); mysql_query("INSERT INTO Address (StreetAddress) VALUES ('blah')"); $result = mysql_query("SELECT * FROM Address"); while($row = mysql_fetch_assoc($result)){ print_r($row); } mysql_query("DELETE FROM Address WHERE AddressID IS NULL"); $result = mysql_query("SELECT * FROM Address"); while($row = mysql_fetch_assoc($result)){ print_r($row); } Expected result: ---------------- Array ( [AddressID] => 65 [NameOrCompany] => [StreetAddress] => blah [City] => [State] => [Zip] => [Country] => [DateTimeCreated] => [DateTimeModified] => ) Array ( [AddressID] => 65 [NameOrCompany] => [StreetAddress] => blah [City] => [State] => [Zip] => [Country] => [DateTimeCreated] => [DateTimeModified] => ) Actual result: -------------- Array ( [AddressID] => 65 [NameOrCompany] => [StreetAddress] => blah [City] => [State] => [Zip] => [Country] => [DateTimeCreated] => [DateTimeModified] => ) ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=48251&edit=1