Thread: weird error message
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
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
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:
"""
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.
Love it! Thanks David.
On Fri, May 6, 2016 at 8:22 AM, David G. Johnston <david.g.johnston@gmail.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:"""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.
"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
"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.
"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
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
"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.