Re: weird error message

On Fri, May 6, 2016 at 10:14 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:
> > On Fri, May 6, 2016 at 9:35 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> 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
>
> Well, you're right that that type of situation isn't going to "just work";
> the user is going to have to cast the null explicitly, because I do not
> think it's reasonable to expect the system to guess that resolving the
> null as integer is what's needed.

​Its a least a consideration that dt could remain unknown and then when the
case needs it to be integer to comport with i it sees it has an unknown and
is successfully able to apply the cast.​ Just like it presently does when
faced with:

>SELECT case when null IS NOT NULL then null else 1 end
1

The point is to give a less opaque
> error message, and I think the latter error message is much better than
> what you get now.

I thought you were trying to address some of the previously expressed
concerns that our type conversion behavior is draconian. I seem to recall
folks like Merlin Moncure and Robert​ Haas expressing such a sentiment.

Also, defaulting to text is what happens in some
> related cases, notably
>
> SELECT case when dt IS NOT NULL then dt else i end FROM (SELECT DISTINCT
> null AS dt, 1 AS i) q;
> ERROR: CASE types integer and text cannot be matched
>
> In this case we resolved the unknown as text so that the DISTINCT could
> have some well-defined behavior. An ORDER BY targeting that column would
> do the same. So IMV it's already surprising that we don't resolve the
> unknown as text without those things.
>
>
​This convinces me that at least we'd be consistent, even if it isn't
ideal. I suspect that for the limited benefit it would gain that the
desire to make it so would not be high. I'm likely to get to writing the
documentation patch first - which maybe will occur next time this question
arises.

> I'm not certain why we wouldn't just add implicit casts from unknown to
> > other types.
>
> Because that would involve *far* more widespread, and less principled,
> changes in behavior. Implicit casts affect every part of the language,
> whereas the actual problem here is restricted to "what's the type of
> this sub-select output column?".
>
>
I don't get this. The only time you can get a "frozen" unknown type is
when dealing with the fact that a sub-select's output column was
indeterminable directly from the sub-select's context. Your example above
is one where the sub-query itself needed a known type and so resolved the
unknown to text to meet that need.

​Maybe I just need an idea of what "other parts" would be affected by this
​particular, limited, change. Even one example would be nice compared to
the blanket "far more widespread".

> I'm willing to go read where this option has been discussed and dismissed
> -
> > just point me in the right direction please.
>
> It's come up repeatedly, though I do not have time right now to search
> the archives.
>

​Fair enough, and I don't really have the time to read it at the moment
anyway.
​
But I suspect that at least some of it involves global implicit casting
which is not what I am talking about here - but from the previous paragraph
seems like you are.
​
David J.