#36969 [Bgs]: pg_query_params() fails for integer column with 'INSERT INTO..SELECT DISTINCT'

ID: 36969 User updated by: alan dot harder at sun dot com Reported By: alan dot harder at sun dot com Status: Bogus Bug Type: PostgreSQL related Operating System: Debian PHP Version: 5.1.5 Assigned To: helly New Comment: for what it's worth, in my setup i only see the problem via PHP.. psql won't let me "prepare" without a type. maybe the problem is in the pg8 client library, setting a default type to text if you don't specify. array(3) { ["client"]=> string(5) "8.1.4" ["protocol"]=> int(3) ["server"]=> string(6) "7.4.13" } in psql: testdb=> prepare blah as insert into test select distinct $1; ERROR: there is no parameter $1 testdb=> prepare blah(int) as insert into test select distinct $1; PREPARE but via PHP, Warning: pg_query_params() [function.pg-query-params]: Query failed: ERROR: column "val" is of type integer but expression is of type text HINT: You will need to rewrite or cast the expression. adding ::int in the pg_query_params sql does work, as you suggested. Previous Comments: ------------------------------------------------------------------------ [2006-11-11 14:31:52] dave@php.net This is not a bug in PHP itself. As you can see below, the same thing happens using psql (and I'm using PostgreSQL 8.2beta2). Perhaps you should contact the Postgres guys, or perhaps distinct simply needs you to be more specific about the type it needs to distinguish against? $1::int works with distinct, for example. testdb=# prepare blah as insert into test select distinct $1; ERROR: column "i" is of type integer but expression is of type text HINT: You will need to rewrite or cast the expression. testdb=# prepare blah as insert into test select $1; PREPARE testdb=# ------------------------------------------------------------------------ [2006-09-26 22:27:47] tony2001@php.net Assigned to the maintainer. ------------------------------------------------------------------------ [2006-08-30 18:50:24] alan dot harder at sun dot com Tested on PHP 5.1.5.. same result. ------------------------------------------------------------------------ [2006-04-04 17:00:24] alan dot harder at sun dot com Description: ------------ parameter given as integer but treated as text with particular sql syntax. remove "distinct" from the sql and it works. Tested with PHP 5.1.2 and PHP 5.1.3-RC2 pg_version output: array(3) { ["client"]=> string(5) "8.1.2" ["protocol"]=> int(3) ["server"]=> string(6) "7.4.11" } Reproduce code: --------------- First in psql: create table test (val integer); Test code: <?php $db = pg_connect('dbname=testdb'); if (!$db) return; $s = pg_query_params($db, 'insert into test select distinct $1', array(666)); if ($s === false) { print pg_last_error($db); } else { print "OK\n"; } pg_close($db); ?> Expected result: ---------------- OK Actual result: -------------- Warning: pg_query_params() [function.pg-query-params]: Query failed: ERROR: column "val" is of type integer but expression is of type text HINT: You will need to rewrite or cast the expression. in /usr/home/mindless/public_html/pgtest.php on line 5 ERROR: column "val" is of type integer but expression is of type text HINT: You will need to rewrite or cast the expression. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=36969&edit=1