From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Adrian Klaver <adrian.klaver@aklaver.com>,
        Michael Moore <michaeljmoore@gmail.com>,
        postgres list <pgsql-sql@postgresql.org>
Subject: Re: weird error message
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>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Fri, 06 May 2016 10:00:57 -0700"
Date: Fri, 06 May 2016 13:14:13 -0400
Message-ID: <6975.1462554853@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

"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