Re: stringtype=unspecified is null check problem - Mailing list pgsql-jdbc
| From | David G. Johnston |
|---|---|
| Subject | Re: stringtype=unspecified is null check problem |
| Date | |
| Msg-id | CAKFQuwYtmLLkmjaKghYXPnBWwvUvsqhZR=9J8KDcLzavf8707w@mail.gmail.com Whole thread Raw |
| In response to | Re: stringtype=unspecified is null check problem (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: stringtype=unspecified is null check problem
|
| List | pgsql-jdbc |
On Wed, Jan 11, 2023 at 6:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"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
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.
pgsql-jdbc by date: