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

go to bug id or search bugs for

Bug #36969 pg_query_params() fails for integer column with 'INSERT INTO..SELECT DISTINCT'
Submitted: 2006-04-04 17:00 UTC Modified: 2007-02-09 02:06 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:0 (0.0%)
From: alan dot harder at sun dot com Assigned: helly (profile)
Status: Not a bug Package: PostgreSQL related
PHP Version: 5.1.5 OS: Debian
Private report: No CVE-ID: None
View Developer Edit
 [2006-04-04 17:00 UTC] 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.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-08-30 18:50 UTC] alan dot harder at sun dot com 
Tested on PHP 5.1.5.. same result.
 [2006-09-26 22:27 UTC] tony2001@php.net 
Assigned to the maintainer.
 [2006-11-11 14:31 UTC] 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=#
 [2007-02-09 02:06 UTC] alan dot harder at sun dot com 
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.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Sat Dec 21 12:01:31 2024 UTC