The following bug has been logged on the website:
Bug reference: 15289
Logged by: Nick Farmer
Email address: farmernick-pg@varteg.nz
PostgreSQL version: 10.4
Operating system: Windows 10 (Version 1803 build 17134.165)
Description:
Type inference of parameters in prepared statements can sometimes fail even
though all the information is present and can be obtained if the statement
is altered in a way that really shouldn't make any functional difference.
Consider the prepared statement:
PREPARE first_test (unknown) AS
SELECT * FROM (
SELECT 17 AS v
UNION
SELECT 42 AS v
) AS fiducial
WHERE ((v = $1) OR ($1 IS NULL));
The statement is prepared without trouble, and works as can be expected: $1
acts like a filter condition that can be turned off by specifying null.
Now consider the statement (spot the differences):
PREPARE second_test (unknown) AS
SELECT * FROM (
SELECT 17 AS v
UNION
SELECT 42 AS v
) AS fiducial
WHERE (($1 IS NULL) OR (v = $1));
This statement fails. The result [as given by pgAdmin] is instead:
ERROR: could not determine data type of parameter $1
LINE 7: WHERE (($1 IS NULL) OR (v = $1));
^
SQL state: 42P08
Character: 112
Even though The two SELECTs are functionally identical, one can be used as a
prepared statement but the other can't. I can accept either behaviour
(though the former is more useful); it's the fact that I get both that's
unwelcome. It's like the type inference engine assumes that a parameter only
ever appears once, and if it can't decide at the first appearance then it
gives up.
A bit more context: there is nothing fancy about my configuration - it's a
plain 64-bit EnterpriseDB install. I have tried this with four different
clients/client libraries: psql, pgAdmin and both PHP interfaces (PDO with
prepared statement emulation turned off and native pgsql).