agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Michael Moore <michaeljmoore@gmail.com>
Cc: postgres list <pgsql-sql@postgresql.org>
Subject: Re: weird error message
Date: Fri, 06 May 2016 13:14:13 -0400
Message-ID: <6975.1462554853@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwYafhAJiqpt8ws2+-C507SyK55to9tv3x2QtN2LR++nTg@mail.gmail.com>
References: <CACpWLjMK8J2O_zjrhBgfYoYU=eO=+CNO7DE0BNM6YNTkbYV4Hw@mail.gmail.com>
<e9b9f162-ad10-74a8-9f5a-3198b2a36144@aklaver.com>
<CAKFQuwbyw_zayifKfKwKzdso+y021kjxfXkLpki0VBrsih2Jow@mail.gmail.com>
<5722.1462552524@sss.pgh.pa.us>
<CAKFQuwYafhAJiqpt8ws2+-C507SyK55to9tv3x2QtN2LR++nTg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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. 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. 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.
> 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'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.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (9+ messages) latest in thread
Message-ID: <6975.1462554853@sss.pgh.pa.us>
Permalink: ../6975.1462554853@sss.pgh.pa.us/
Also on: postgresql.org/message-id/6975.1462554853@sss.pgh.pa.us
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, adrian.klaver@aklaver.com, michaeljmoore@gmail.com
Subject: Re: weird error message
In-Reply-To: <6975.1462554853@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox