[2009-05-19 15:31 UTC] andrew at labyrinth-it dot co dot uk

I am using:
PHP 5.2.10-dev (cli) (built: May 19 2009 15:40:36) 
and still get the same error result.

This error is also in the pg_update function, where NULl values are compared with "= NULL" rather than "IS NULL" in the WHERE part of the generated SQL.

 [2009-05-19 15:34 UTC] andrew at labyrinth-it dot co dot uk

For completeness, I get the error running on Fedora 10 with Postgres 8.3.5

 [2009-05-31 06:27 UTC] andrew at labyrinth-it dot co dot uk

Sorry, I think I posted my reply in the wrong place, so let me try again.

I have just downloaded and tested the latest PHP version:
PHP 5.2.10RC2-dev (cli) (built: May 31 2009 07:16:36)

With this version I still get the same error. The Postgresql version I am testing against is:
PostgreSQL 8.3.5 on i386-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.3.2 20081007 (Red Hat 4.3.2-6)

The call to:
print(pg_delete($db,'demo',$row,PGSQL_DML_STRING)) 
still prints out:
DELETE FROM demo WHERE id=1 AND col1=NULL;

The final test should use the SQL language "IS NULL" test rather than "=NULL" which never evaluates to true.

The same problem exists if using pg_update, which produces:
UPDATE demo SET id=2,col1='Two' WHERE id=1 AND col1=NULL;

Again, "col1=NULL" will never evaluate to true using SQL, and the test col1 IS NULL should be used instead.

 [2010-11-06 18:44 UTC] felipe@php.net

 [2010-11-06 18:44 UTC] felipe@php.net

This bug has been fixed in SVN.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.

Thanks for the patch!

 [2010-12-09 18:52 UTC] andrew at labyrinth-it dot co dot uk

Hi,
I have just downloaded the latest 5.2 version from SVN, and tested this again. 
It is still 
not working. I have created a more complete test case that can be used.

===========Start===========
<?php

$linenum=0;
function _print($str) {
  global $linenum;
  print($linenum++."\t".$str."\n");
}

_print('PHP_VERSION : '.PHP_VERSION);
_print('PHP_OS : '.PHP_OS);

$db = pg_connect("dbname=test");
if (! $db) {
  die("Not connected");
}

//Create a test table
$create = 'create table testcase (id integer, col1 varchar(20))';
$result = pg_query($db,$create);

//Insert some test data
$row = array('id'=>1, 'col1'=>'I');
_print(pg_insert($db,'testcase',$row,PGSQL_DML_STRING));
pg_insert($db,'testcase',$row);
$row = array('id'=>2, 'col1'=>'');
_print(pg_insert($db,'testcase',$row,PGSQL_DML_STRING));
pg_insert($db,'testcase',$row);
$row = array('id'=>3, 'col1'=>null);
_print(pg_insert($db,'testcase',$row,PGSQL_DML_STRING));
pg_insert($db,'testcase',$row);

//Selects
$search = array('col1'=>'I');
_print(pg_select($db,'testcase',$search,PGSQL_DML_STRING));
$result = pg_select($db,'testcase',$search);
if ($result) {
  _print($result[0]['id']."\t".$result[0]['col1']);
} else {
  _print('Not found');
}
$search = array('col1'=>'');
_print(pg_select($db,'testcase',$search,PGSQL_DML_STRING));
$result = pg_select($db,'testcase',$search);
if ($result) {
  _print($result[0]['id']."\t".$result[0]['col1']);
} else {
  _print('Not found');
}
$search = array('col1'=>null);
_print(pg_select($db,'testcase',$search,PGSQL_DML_STRING));
$result = pg_select($db,'testcase',$search);
if ($result) {
  _print($result[0]['id']."\t".$result[0]['col1']);
} else {
  _print('Not found');
}

//Updates
$data = array('id'=>5);
$condition = array('col1'=>'I');
_print(pg_update($db,'testcase',$data,$condition,PGSQL_DML_STRING));
$condition = array('col1'=>'');
_print(pg_update($db,'testcase',$data,$condition,PGSQL_DML_STRING));
$condition = array('col1'=>null);
_print(pg_update($db,'testcase',$data,$condition,PGSQL_DML_STRING));

//Deletes
$condition = array('col1'=>'I');
_print(pg_delete($db,'testcase',$condition,PGSQL_DML_STRING));
$condition = array('col1'=>'');
_print(pg_delete($db,'testcase',$condition,PGSQL_DML_STRING));
$condition = array('col1'=>null);
_print(pg_delete($db,'testcase',$condition,PGSQL_DML_STRING));

//clean up
$drop = 'drop table testcase';
$result = pg_query($db,$drop);

=============End===============

The generated output from this script is:

0       PHP_VERSION : 5.2.16-dev
1       PHP_OS : Linux
2       INSERT INTO testcase (id,col1) VALUES (1,'I');
3       INSERT INTO testcase (id,col1) VALUES (2,'');
4       INSERT INTO testcase (id,col1) VALUES (3,NULL);
5       SELECT * FROM testcase WHERE col1='I';
6       1       I
7       SELECT * FROM testcase WHERE col1='';
8       2
9
10      Not found
11      UPDATE testcase SET id=5 WHERE col1='I';
12      UPDATE testcase SET id=5 WHERE col1='';
13      UPDATE testcase SET id=5 WHERE col1=NULL;
14      DELETE FROM testcase WHERE col1='I';
15      DELETE FROM testcase WHERE col1='';
16      DELETE FROM testcase WHERE col1=NULL;


Notes:
Line 0: php version 5.2.16-dev - latest version just downloaded.
Line 9: No SELECT string is generated if the parameter contains NULL
Line 10: No data is found when searching for NULL
Line 13: Still using col1=NULL instead of col1 IS NULL
Line 16: Still using col1=NULL instead of col1 IS NULL

Has the patch been merged with the version at snaps.php.net?

 [2010-12-09 18:58 UTC] felipe@php.net

Hello, the fix was committed to 5.3.x branch and trunk, not to 5.2.x.

 [2010-12-09 19:08 UTC] andrew at labyrinth-it dot co dot uk

 [2010-12-09 19:08 UTC] andrew at labyrinth-it dot co dot uk

OK, Sorry. I have downloaded and tested 5.3.5 and this is working perfectly! 
Thanks for fixing this.