Re: weird error message - Mailing list pgsql-sql
| From | David G. Johnston |
|---|---|
| Subject | Re: weird error message |
| Date | |
| Msg-id | CAKFQuwYTW+Z8VMUZehtEbboEc589aOEHVDm9=BY4-nzuGGq2Cg@mail.gmail.com Whole thread Raw |
| In response to | Re: weird error message (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-sql |
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, May 6, 2016 at 9:35 AM, Tom Lane <tgl@sss.pgh.pa.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.