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 back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
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 23:01:28 2024 UTC