Re: Resolve UNKNOWN type to relevant type instead of text type while bulk update using values

On Thu, Jan 5, 2023 at 12:42 PM David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

>
> The VALUES subquery has to produce its tabular output without being aware of how the outer query is going to use it. The second column of your values subquery lacks type information so the system chooses a default - text.
>
> Dealing with types is one of the harder medium-hard problems in computer science…encountering this problem in real life has never seen me motivated enough to gripe about it rather than just add an explicit cast and move on. And I’ve been around long enough to know that the project is, and long has been, aware of the dull pain points in this area.
>

being here for quite a few years now I agree. It's tempting to trying
to fix a problem in this area since it seems the fix is simple but it
is hard to realize the wider impact that simple fix has. Still let me
try to propose something :)

we cast a quoted value to UNKNOWN type, but this is a special value
null which can be casted to any SQL data type. Probably we could add a
ANYNULLTYPE or some such generic null type which can be casted to any
data type. Then a null value without any type is labeled as
ANYNULLTYPE if specific type information is not available. This
problem wouldn't arise then. Of course that's a lot of code to fix
seemingly rare problem so may not be worth it still.

--
Best Wishes,
Ashutosh Bapat