Re: stringtype=unspecified is null check problem

On Wed, Jan 11, 2023 at 6:33 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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
>

I'm just thinking that if at the end of the parse the system is left with
"unknown" as a parameter type it chooses text, just like seems to happen
most other places in the server during type resolution. Even if doing that
takes a second pass it seems such a pass would only ever have to occur when
this specific error arises. But I agree that if it takes a second pass it
is much less appealing to work out what those mechanics look like compared
to just a deferred reclassification if this specific error is detected.

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.

Right, just not explicitly like that or for every parameter, always., just
the ones that lack any other context fixing their type.

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.
>
.
Yes, the non-determinism of the above (i.e., reversing the order of the
tests removes the error), which implies the error is not sufficiently
delayed to give other parts of the statement a chance to provide context,
is also annoying. Which I suppose is why you are saying a second pass
would be needed to get that delay in a minimally-invasive way.

We've just introduced soft-error handling for data type handling. Is there
any way to leverage some of that to turn the specific error into a soft
one, place references to problematic Vars somewhere, then reclassify them
at the end of the parse?

David J.