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

go to bug id or search bugs for

Bug #70789 Datatype mismatch for postgres expression
Submitted: 2015-10-26 10:40 UTC Modified: 2015-10-27 14:07 UTC
Votes:1
Avg. Score:4.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: stefan at php-engineer dot de Assigned: mbeccati (profile)
Status: Not a bug Package: PDO PgSQL
PHP Version: 5.6.14 OS: Ubuntu 14.04.1 LTS
Private report: No CVE-ID: None
View Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
If you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: stefan at php-engineer dot de
New email:
PHP Version: OS:

 

 [2015-10-26 10:40 UTC] stefan at php-engineer dot de 
Description:
------------
More information about this issue:
https://github.com/cakephp/cakephp/issues/7534

Test script:
---------------
$db = new PDO('pgsql:host=localhost;dbname=cake_test_db;user=markstory');

$stmt = $db->prepare('UPDATE categories SET priority = (CASE WHEN id = :c0 THEN :c1 END) WHERE id in (:c2)');
$stmt->bindValue('c0', '52852e5c-5761-4b87-91d3-456f1dff1469', PDO::PARAM_STR);
$stmt->bindValue('c1', 2, PDO::PARAM_INT);
$stmt->bindValue('c2', '52852e5c-5761-4b87-91d3-456f1dff1469', PDO::PARAM_STR);

$out = $stmt->execute();
var_dump($out);
var_dump('Error code', $stmt->errorCode());
var_dump('Error info', $stmt->errorInfo());

Actual result:
--------------
SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  column "priority" is of type integer but expression is of type text
LINE 1: UPDATE categories SET priority = (CASE WHEN id = $1 THEN $2 ...
                                          ^
HINT:  You will need to rewrite or cast the expression.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2015-10-27 14:07 UTC] mbeccati@php.net
-Status: Open +Status: Not a bug -Assigned To: +Assigned To: mbeccati
 [2015-10-27 14:08 UTC] mbeccati@php.net 
Thanks for your bug submission.

I'm happy to report it's not a PDO_pgsql bug. Nor a PostgreSQL bug, to be fair.

In fact if you try to prepare such a query from psql you will see the very same error.

postgres=# PREPARE foo AS UPDATE categories SET priority = (CASE WHEN id = $1 THEN $2 END) WHERE id in ($3);
ERROR:  column "priority " is of type integer but expression is of type text

You just need to cast the CASE expression, e.g.

(CASE WHEN id = $1 THEN $2 END)::int, or
(CASE WHEN id = $1 THEN $2::int END), or
(CASE WHEN id = $1 THEN $2 ELSE 0 END)

for Postgres to know it's an integer and accept it.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Thu Dec 26 09:01:29 2024 UTC