Re: stringtype=unspecified is null check problem

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> The equality operator forces both sides of it to be of the same type.
> Since the unquoted number 1 is a typed integer that fixes the null to be an
> integer.  Likewise, since both the single-quoted A and null both end up
> being interpreted as text that is what you get.  The "is null" test, unlike
> the equals operator, does not force any particular concrete data type,
> hence the error.  You've asked the driver to not specify a concrete type
> when sending text-like content and the server, respecting that, realizes it
> cannot infer one either, and bam!

Yeah.  I concur that this is annoying, but the law of conservation of
cruft means that we can't easily fix it without creating new warts.

It's possible to experiment with this behavior without messing with
extended query mode, by seeing what PREPARE does with unspecified
parameters:

regression=# prepare foo as select $1 is null;
ERROR:  could not determine data type of parameter $1

The complained-of problem.  Annoying, especially since the seemingly
comparable

regression=# prepare foo as select $1 is true;
PREPARE

works fine.  But that's not really comparable, since the IS TRUE
context offers the hint we need about the data type of $1.

You can fix it by providing an explicit statement of the intended
data type:

regression=# prepare foo as select $1::text is null;
PREPARE

and what the OP seems to wish is that the server would do that
automatically.  Trouble is, what if the parameter is in fact
*not* text?  That would get us into trouble with something like

regression=# prepare foo2 as select $1 is null or $1 = 42;
ERROR:  operator does not exist: text = integer
LINE 1: prepare foo2 as select $1 is null or $1 = 42;
                                                ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Maybe that's still better than the current behavior, in that
it's strictly fewer failures.  But I'm worried about queries
silently succeeding with different semantics than the user
expected, which this seems like it'd open the door to.

(In theory maybe we could fix this with two passes over the
query, but I don't really want to go there.)

            regards, tom lane