php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #46575 NULL comparison when using "not in" not consistent with Windows SQL
Submitted: 2008-11-14 16:35 UTC Modified: 2011-12-01 05:28 UTC
From: ben at thelocust dot org Assigned:
Status: Not a bug Package: MSSQL related
PHP Version: 5.2.6 OS: *
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: ben at thelocust dot org
New email:
PHP Version: OS:

 

 [2008-11-14 16:35 UTC] ben at thelocust dot org
Description:
------------
When querying a MSSQL database table, and using the "not in" syntax, PHP's mssql_query will also return rows with NULL in the field specified.



Reproduce code:
---------------
SQL Server 2000 or 2005

Table "test"
test_id (int)  test_name (vchar)    test_number (int)
1              Foo                  1
2              Bar                  2
3              <null>               3
4              Baz                  <null>

$sql = "select test_id from test where test_number not in (1,2)";
$res = mssql_query($sql);
while ($row = mssql_fetch_array($res)) {
?>
<?=$row['test_id'];?><br/>
<?
}


Expected result:
----------------
In PHP, either using FreeTDS on Linux or the MSSQL extension on Windows, the above returns:

3
4

In ASP/VBScript or using Query Analyzer on Windows the following is returned:

3

Windows' MSSQL driver apparently will not compare the "not in" values to a NULL field.  PHP, apparently, will.

This creates and inconsistency between the two platforms.


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-01-07 18:17 UTC] kalle@php.net
I don't see the error here, your asking SQL server to return 'test_id' from the table 'test' where 'test_number' doesn't even to 1 or 2, and since NULL is different from 1 and 2 then why shouldn't it be returned? I see that more of a bug in the ASP/VBScript drivers, and I don't think we should put such an inconsistency into because others do it.

I changed the category of this to a Feature/Change request, letting the extension maintainer decided whenever to do it or not :)
 [2011-04-08 20:48 UTC] jani@php.net
-Package: Feature/Change Request +Package: MSSQL related
 [2011-12-01 05:20 UTC] ssufficool at gmail dot com
This is a behavior set by the MSSQL "ANSI NULLS" setting. Depending if ANSI NULLS is on or off, it will return the NULL for the NOT IN selection. 

This is a server setting, not a PHP setting.

Try issuing a "SET ANSI_NULLS ON" before the query using both VBScript and PHP. The behavior should then be the same.
 [2011-12-01 05:28 UTC] ssufficool@php.net
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php


 [2011-12-01 05:28 UTC] ssufficool@php.net
-Status: Open +Status: Bogus
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 21 12:01:31 2024 UTC