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

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> 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.

This is not a bug.  If you don't specify a type for a parameter, the
parser attempts to infer it on first use.  If the first use is
"v = $1", it will guess that $1 is meant to be the same type as v
(whose type is already known from having processed the FROM clause).
If the first use is "$1 IS NULL", there's no basis to infer a type.

One could imagine some complicated parse-time processing that tries
to defer resolving the parameter's type in hopes that some other use
will be seen later; but there is no such thing in PG, and I doubt
it will appear soon.

            regards, tom lane

I can accept either behaviour
(though the former is more useful); it's the fact that I get both that's
unwelcome.

On 2018-07-22 04:48, David G. Johnston wrote:
> On Saturday, July 21, 2018, PG Bug reporting form 
> <noreply@postgresql.org <mailto:noreply@postgresql.org>> wrote:
> 
>     I can accept either behaviour
>     (though the former is more useful); it's the fact that I get both that's
>     unwelcome.
> 
> 
> But each of those behaviors is consistently encountered every time the 
> query is run so there isn't any hidden danger involved here; compared to 
> if the error only occurred if certain data was encountered during 
> execution.  As Tom said, while a bit inconcsistent the effort to fix 
> outweighs the the fact there is no actual problem, just an unexpected 
> dependency on the written query.
> 
> David J.
> 

Yes, that's true. Easy enough to avoid - it's more of a "Huh?" than 
something that breaks anything, so its priority is much lower that way - 
but if you encounter it in the wild without prior warning you could have 
some work ahead.

My original statement was quite a bit more complex and it was much less 
clear what the problem was and what to do about it ("What do you mean 
'could not determine data type'? It's right there!"). After some time 
spent whittling it down to essentially what I posted and having this 
surprise, I worked backwards to rearrange the original statement so that 
every parameter had its type nailed down as soon as it appeared. Now I 
know to write it like that in the first place.

I use PREPARE's type declaration header, but obviously that's not always 
available. (Something I learned while working on this: PHP's PDO-pgsql 
driver, when it's asked to prepare a statement, first wraps the 
statement in a cursor and chucks that at the server to see what types 
come back, then uses those to prepare the statement for real.)



It's more in the nature of undocumented behaviour; may I suggest mention 
of this point in the manual to save time for others later? Three times 
in sql-prepare.html it says "[the type] is inferred from the context in 
which the parameter is used"; it never says only the _first_ context is 
used.

Nick

(Just for giggles, have a second unknown parameter and make the 
condition "(($1 = $2) OR ($2 = v))".)