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

On 2018-07-22 04:48, David G. Johnston wrote:
> On Saturday, July 21, 2018, PG Bug reporting form
> <noreply(at)postgresql(dot)org <mailto:noreply(at)postgresql(dot)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))".)