Bug #44196 [Com]: pg_query_params NULL not passing correctly

Edit report at https://bugs.php.net/bug.php?id=44196&edit=1 ID: 44196 Comment by: falundir at gmail dot com Reported by: tyler at sleekcode dot net Summary: pg_query_params NULL not passing correctly Status: Not a bug Type: Bug Package: PostgreSQL related Operating System: Windows PHP Version: 5.2.5 Block user comment: N Private report: N New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [2008-02-21 16:20:24] 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. ------------------------------------------------------------------------ [2008-02-21 16:14:24] 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 13:18:21] 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-20 23:25:19] 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. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=44196&edit=1