BUG #15289: Type inference of parameters in prepared statements cansometimes fail or succeed, depending...

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).