From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, Michael Moore <michaeljmoore(at)gmail(dot)com>, postgres list <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: weird error message |
Date: | 2016-05-06 17:35:02 |
Message-ID: | CAKFQuwYTW+Z8VMUZehtEbboEc589aOEHVDm9=BY4-nzuGGq2Cg@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Moore | 2016-05-10 23:25:54 | python install defies all efforts and leaves programmer a broken shell of a man |
Previous Message | Michael Moore | 2016-05-06 17:30:54 | Re: weird error message |