public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Tom Lane <[email protected]>
Cc: Martin Handsteiner <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: stringtype=unspecified is null check problem
Date: Wed, 11 Jan 2023 18:59:59 -0700
Message-ID: <CAKFQuwYtmLLkmjaKghYXPnBWwvUvsqhZR=9J8KDcLzavf8707w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <VI1PR1001MB1423CDC3E737B4763436B730E8FF9@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
	<CAKFQuwZA+9+FNovGXA6moj+sDzR6ShyzzP3H77m-05n3dXDv-w@mail.gmail.com>
	<VI1PR1001MB1423C002817FECB26B1AFB3DE8FC9@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
	<CAKFQuwZrn0F9RyM=WL9Z=pioGX5pwH1+mbBe-jGNnjy_J2E+Jw@mail.gmail.com>
	<[email protected]>

On Wed, Jan 11, 2023 at 6:33 PM Tom Lane <[email protected]> wrote:

> "David G. Johnston" <[email protected]> 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.

view thread (14+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: stringtype=unspecified is null check problem
  In-Reply-To: <CAKFQuwYtmLLkmjaKghYXPnBWwvUvsqhZR=9J8KDcLzavf8707w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox