agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedweird error message
9+ messages / 4 participants
[nested] [flat]
* weird error message
@ 2016-05-05 20:14 Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Moore @ 2016-05-05 20:14 UTC (permalink / raw)
To: pgsql-sql
SELECT COALESCE(dt, i) FROM (SELECT null AS dt, null AS i) q;
gives
ERROR: failed to find conversion function from unknown to text
********** Error **********
ERROR: failed to find conversion function from unknown to text
SQL state: XX000
So, I understand the datatype of 'null' is 'unknown', but what does 'text'
have to do with it?
Mike
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: weird error message
@ 2016-05-06 13:53 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2016-05-06 13:53 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; pgsql-sql
On 05/05/2016 01:14 PM, Michael Moore wrote:
> SELECT COALESCE(dt, i) FROM (SELECT null AS dt, null AS i) q;
> gives
> ERROR: failed to find conversion function from unknown to text
> ********** Error **********
>
> ERROR: failed to find conversion function from unknown to text
> SQL state: XX000
>
> So, I understand the datatype of 'null' is 'unknown', but what does
> 'text' have to do with it?
Hmm:
hplc=> SELECT null AS dt, null AS i;
dt | i
----+---
|
(1 row)
hplc=> select null::text as dt, null::text as i;
dt | i
----+---
|
(1 row)
hplc=> SELECT COALESCE(dt, i) FROM (SELECT null::text AS dt,
null::text AS i) q;
coalesce
----------
(1 row)
hplc=> SELECT COALESCE(dt, i) FROM (SELECT null AS dt, null AS i) q;
ERROR: failed to find conversion function from unknown to text
So it is not the conversion from NULL to text per se, just when it is
done on the output of a derived table. I don't why that is, maybe
someone else can chime in.
>
> Mike
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: weird error message
@ 2016-05-06 15:22 David G. Johnston <david.g.johnston@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 9+ messages in thread
From: David G. Johnston @ 2016-05-06 15:22 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Michael Moore <michaeljmoore@gmail.com>; pgsql-sql
On Fri, May 6, 2016 at 6:53 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
>
>
> hplc=> SELECT COALESCE(dt, i) FROM (SELECT null::text AS dt, null::text
> AS i) q;
> coalesce
> ----------
>
> (1 row)
>
>
> hplc=> SELECT COALESCE(dt, i) FROM (SELECT null AS dt, null AS i) q;
> ERROR: failed to find conversion function from unknown to text
>
>
> So it is not the conversion from NULL to text per se, just when it is done
> on the output of a derived table. I don't why that is, maybe someone else
> can chime in.
>
The message would be more accurately written "failed to find implicit
conversion function from unknown to text".
The answer is partially given by #3 in the type conversion documentation:
http://www.postgresql.org/docs/9.5/interactive/typeconv-union-case.html
"""
3. If all inputs are of type unknown, resolve as type text (the preferred
type of the string category).
"""
However, the conversion from unknown to text can only happen implicitly if
the unknown type has not been "locked" due to the value in question being
passed up from a subquery. The distinction is one I personally call
"untyped" versus "unknown (typed)". An "untyped" value has an unknown type
but it can be implicitly cast to any other type. If it ends up being cast
so that it is "unknown typed" further implicit casts are not allowed -
there are no implicit casts in the system from "unknown typed".
So dt and i become unknown typed and then passed through the case which
chooses text (per #3) as the common type but fails when it goes looking for
an implicit cast from unknown type to text. Removing the subquery the case
succeeded because the nulls are simply untyped.
I don't know that this is covered all that well in the documentation. I've
been meaning to write a patch to add substantially what I've written above
(I've made this similar response a number of times now) but haven't gotten
around to it yet. It doesn't come up that often and when it does it is
confusion but hardly problematic.
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: weird error message
@ 2016-05-06 15:31 Michael Moore <michaeljmoore@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Michael Moore @ 2016-05-06 15:31 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-sql
Love it! Thanks David.
On Fri, May 6, 2016 at 8:22 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Fri, May 6, 2016 at 6:53 AM, Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>
>>
>>
>> hplc=> SELECT COALESCE(dt, i) FROM (SELECT null::text AS dt, null::text
>> AS i) q;
>> coalesce
>> ----------
>>
>> (1 row)
>>
>>
>> hplc=> SELECT COALESCE(dt, i) FROM (SELECT null AS dt, null AS i) q;
>> ERROR: failed to find conversion function from unknown to text
>>
>>
>> So it is not the conversion from NULL to text per se, just when it is
>> done on the output of a derived table. I don't why that is, maybe someone
>> else can chime in.
>>
>
> The message would be more accurately written "failed to find implicit
> conversion function from unknown to text".
>
> The answer is partially given by #3 in the type conversion documentation:
>
> http://www.postgresql.org/docs/9.5/interactive/typeconv-union-case.html
> """
> 3. If all inputs are of type unknown, resolve as type text (the preferred
> type of the string category).
> """
>
> However, the conversion from unknown to text can only happen implicitly if
> the unknown type has not been "locked" due to the value in question being
> passed up from a subquery. The distinction is one I personally call
> "untyped" versus "unknown (typed)". An "untyped" value has an unknown type
> but it can be implicitly cast to any other type. If it ends up being cast
> so that it is "unknown typed" further implicit casts are not allowed -
> there are no implicit casts in the system from "unknown typed".
>
> So dt and i become unknown typed and then passed through the case which
> chooses text (per #3) as the common type but fails when it goes looking for
> an implicit cast from unknown type to text. Removing the subquery the case
> succeeded because the nulls are simply untyped.
>
> I don't know that this is covered all that well in the documentation.
> I've been meaning to write a patch to add substantially what I've written
> above (I've made this similar response a number of times now) but haven't
> gotten around to it yet. It doesn't come up that often and when it does it
> is confusion but hardly problematic.
>
> David J.
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: weird error message
@ 2016-05-06 16:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2016-05-06 16:35 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Michael Moore <michaeljmoore@gmail.com>; pgsql-sql
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I don't know that this is covered all that well in the documentation.
It'd be better to do something about it than document it. The core of
the problem is that if we don't resolve the type of an unknown literal
while processing the sub-SELECT's target list, it doesn't work to try
to make a conversion later.
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. There would be cases where that's not
really what you want, but it would be unsurprising for it to act that way.
The hard part is that we've historically allowed
INSERT INTO sometab SELECT 'foo', ...
to resolve 'foo' as the type of sometab's first column (and I think this
is required by SQL spec, actually). So some work would have to be done
to not break that behavior. But I think this could be managed by
explicitly passing down knowledge of the INSERT's target column types into
the parsing of the sub-SELECT, and then the rule could be "resolve an
unknown SELECT output column to whatever target type is provided by
context, or to TEXT if the context provides no target".
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: weird error message
@ 2016-05-06 17:00 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2016-05-06 17:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Michael Moore <michaeljmoore@gmail.com>; pgsql-sql
On Fri, May 6, 2016 at 9:35 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > I don't know that this is covered all that well in the documentation.
>
> It'd be better to do something about it than document it.
Don't look at me :)
> The core of
> the problem is that if we don't resolve the type of an unknown literal
> while processing the sub-SELECT's target list, it doesn't work to try
> to make a conversion later.
>
Correct.
>
> 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
> There would be cases where that's not
> really what you want, but it would be unsurprising for it to act that way.
>
> The hard part is that we've historically allowed
>
> INSERT INTO sometab SELECT 'foo', ...
>
> to resolve 'foo' as the type of sometab's first column (and I think this
> is required by SQL spec, actually). So some work would have to be done
> to not break that behavior. But I think this could be managed by
> explicitly passing down knowledge of the INSERT's target column types into
> the parsing of the sub-SELECT, and then the rule could be "resolve an
> unknown SELECT output column to whatever target type is provided by
> context, or to TEXT if the context provides no target".
>
Don't know enough here to comment on "push down" options.
I'm not certain why we wouldn't just add implicit casts from unknown to
other types. Then, we end up getting the same behavior when dealing with
sub-selects as we do when unknown (untyped) literals are present directly
within the main query.
IOW, instead of trying to carry type inference down to lower layers let
ambiguity remain as vars travel up until an unknown encounters context
which allows it be definitively typed. I'm sure there are some corner
cases involved but in a trivial setup the end result of the two algorithms
is the same.
I'm willing to go read where this option has been discussed and dismissed -
just point me in the right direction please. Nothing comes to mind at the
moment. While getting rid of implicit casting generally was a good idea
this seems like the one area that warrants it - and in fact already has it
locally.
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: weird error message
@ 2016-05-06 17:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Tom Lane @ 2016-05-06 17:14 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Michael Moore <michaeljmoore@gmail.com>; 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. 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: weird error message
@ 2016-05-06 17:30 Michael Moore <michaeljmoore@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Michael Moore @ 2016-05-06 17:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-sql
Not that it matters but the result set for this is a single row single
column with a value of 'NULL for Oracle.
SELECT case WHEN COALESCE(dt, i) IS NULL THEN 'NULL' else 'NOTNULL' END
rslt
FROM (SELECT null AS dt, null as i FROM dual ) q;
Probably, making an easy conversion path from Oracle to Postgres is not
high on your list of considerations but right now it is high on mine.
On Fri, May 6, 2016 at 10:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: weird error message
@ 2016-05-06 17:35 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2016-05-06 17:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Michael Moore <michaeljmoore@gmail.com>; pgsql-sql
On Fri, May 6, 2016 at 10:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "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.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2016-05-06 17:35 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-05-05 20:14 weird error message Michael Moore <michaeljmoore@gmail.com>
2016-05-06 13:53 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-05-06 15:22 ` David G. Johnston <david.g.johnston@gmail.com>
2016-05-06 15:31 ` Michael Moore <michaeljmoore@gmail.com>
2016-05-06 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-05-06 17:00 ` David G. Johnston <david.g.johnston@gmail.com>
2016-05-06 17:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-05-06 17:30 ` Michael Moore <michaeljmoore@gmail.com>
2016-05-06 17:35 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox