Re: weird error message

On Fri, May 6, 2016 at 9:35 AM, 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:
> > I don't know that this is covered all that well in the documentation.
>
> It'd be better to do something about it than document it.

​Don't look at me :)
​

> The core of
> the problem is that if we don't resolve the type of an unknown literal
> while processing the sub-SELECT's target list, it doesn't work to try
> to make a conversion later.
>

​Correct.
​

>
> I think there's a rough consensus that it'd be okay to resolve unknown to
> text at the time that the subquery is parsed, if there's no reason to
> resolve it to something else.

​I'm not sure that buys us a lot here...

>​SELECT case when dt IS NOT NULL then dt else i end FROM (SELECT null AS
dt, 1 AS i) q;
failed to find conversion function from unknown to integer

​>SELECT case when dt IS NOT NULL then dt else i end FROM (SELECT
'2'::text AS dt, 1 AS i) q;
​SQL Error: ERROR: CASE types integer and text cannot be matched

> There would be cases where that's not
> really what you want, but it would be unsurprising for it to act that way.
>
> The hard part is that we've historically allowed
>
> INSERT INTO sometab SELECT 'foo', ...
>
> to resolve 'foo' as the type of sometab's first column (and I think this
> is required by SQL spec, actually). So some work would have to be done
> to not break that behavior. But I think this could be managed by
> explicitly passing down knowledge of the INSERT's target column types into
> the parsing of the sub-SELECT, and then the rule could be "resolve an
> unknown SELECT output column to whatever target type is provided by
> context, or to TEXT if the context provides no target".
>

​Don't know enough here to comment on "push down" options.

I'm not certain why we wouldn't just add implicit casts from unknown to
other types. Then, we end up getting the same behavior when dealing with
sub-selects as we do when unknown (untyped) literals are present directly
within the main query.

IOW, instead of trying to carry type inference down to lower layers let
ambiguity remain as vars travel up until an unknown encounters context
which allows it be definitively typed. I'm sure there are some corner
cases involved but in a trivial setup the end result of the two algorithms
is the same.

I'm willing to go read where this option has been discussed and dismissed -
just point me in the right direction please. Nothing comes to mind at the
moment. While getting rid of implicit casting generally was a good idea
this seems like the one area that warrants it - and in fact already has it
locally.

David J.