php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #44196 pg_query_params NULL not passing correctly
Submitted: 2008-02-20 23:25 UTC Modified: 2008-02-21 16:20 UTC
From: tyler at sleekcode dot net Assigned:
Status: Not a bug Package: PostgreSQL related
PHP Version: 5.2.5 OS: Windows
Private report: No CVE-ID: None
View Add Comment Developer Edit
 [2008-02-20 23:25 UTC] tyler at sleekcode dot net 
Description:
------------
Passing NULL via pg_query_params does not work resulting in situations where a possible NULL value must have a secondary branch of code to adjust the query accordingly.

Reproduce code:
---------------
using postgres 8x create this test table:

CREATE TABLE test
(
  id     serial PRIMARY KEY,
  name   text,
  extra  text,
);
INSERT INTO test (name,extra) VALUES ('Testing 123,'Testing');
INSERT INTO test (name) VALUES ('My extra is null');

<?php
$db = pg_connect("your connection string");

// set postgresql to allow = NULL instead of requiring IS NULL
pg_query("set transform_null_equals to on");

// query for all records where extra = NULL (postgres converts to extra IS NULL)
$res = pg_query_params("SELECT name FROM test WHERE extra=$1",array(NULL));

// print result, should be 'My extra is null'
print pg_fetch_result($res,0);
?>

Expected result:
----------------
The code should return the first result that matches the query.

Actual result:
--------------
The query does not run correctly.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-02-21 13:18 UTC] iliaa@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

Doing foo = NULL does not work in databases. To fetch based on NULL 
values of a column you need to do column IS NULL
 [2008-02-21 16:14 UTC] tyler at sleekcode dot net 
From documentation for PostgreSQL 8.3 (going all the way back to the 7x branch)

transform_null_equals (boolean)
When on, expressions of the form expr = NULL (or NULL = expr) are treated as expr IS NULL, that is, they return true if expr evaluates to the null value, and false otherwise. The correct SQL-spec-compliant behavior of expr = NULL is to always return null (unknown). Therefore this parameter defaults to off. 

However, filtered forms in Microsoft Access generate queries that appear to use expr = NULL to test for null values, so if you use that interface to access the database you might want to turn this option on. Since expressions of the form expr = NULL always return the null value (using the correct interpretation) they are not very useful and do not appear often in normal applications, so this option does little harm in practice. But new users are frequently confused about the semantics of expressions involving null values, so this option is not on by default. 

Note that this option only affects the exact form = NULL, not other comparison operators or other expressions that are computationally equivalent to some expression involving the equals operator (such as IN). Thus, this option is not a general fix for bad programming.
 [2008-02-21 16:20 UTC] tyler at sleekcode dot net 
Additionally, the correct syntax:

pg_query_params("SELECT name FROM test WHERE
extra IS $1",array(null));

...fails as well.
 [2012-02-29 00:32 UTC] falundir at gmail dot com 
It's pity that it was marked as not a bug. It really makes me ill to constantly 
write:

if ($column_value === null) }
    $where[] = "column is null";
} else {
    $where[] = "column = :column_value";
    $parameters['column_value'] = $column_value;
}

instead of just:

$where[] = "column = :column_value";
$parameters['column_value'] = $column_value;

IMHO, if someone decides to enable transform_null_equals on his PostgreSQL 
database then PDO should respect that decision.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Wed May 15 18:01:34 2024 UTC