Обсуждение: COALESCE requires NULL from scalar subquery has a type
Hi Not an important question, but a niggle. CREATE TABLE gwtest (id INT PRIMARY KEY); INSERT INTO gwtest VALUES (1),(2),(3); SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid; gives an error failed to find conversion function from unknown to text I can work around this with SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4)::varchar, 'No') AS valid; I'm guessing this is because Postgres can't deduce the type of the string column from the source when the result isn't returned. Oddly, it also seems to cope when I do: SELECT COALESCE((SELECT 'Yes'::varchar FROM gwtest WHERE id=4), 'No') AS valid which _really_ blows my mind. I'm aware I could use EXISTS instead (and it would be much nicer) and (as above) I can fix it with an explicit cast, but it seems odd that a) COALESCE can infer the type from the string when one is returned and from an explicitly cast string that _isn't_ returned, but can't infer the type from the non-cast version, and b) it needs a type for NULL at all (since any NULL is going to be treated the same). (running 9.5, if it matters) Am I missing something? Geoff
Geoff Winkless <pgsqladmin@geoff.dj> writes:
> SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid;
> gives an error
> I'm guessing this is because Postgres can't deduce the type of the
> string column from the source when the result isn't returned. Oddly,
> it also seems to cope when I do:
> SELECT COALESCE((SELECT 'Yes'::varchar FROM gwtest WHERE id=4), 'No') AS valid
Yup. The output column type of the sub-SELECT is determined without
reference to its context, so there's nothing causing the unknown-type
literal to get assigned a definite type.
There's been occasional discussion of changing that behavior, but it's
not real clear that it wouldn't create as many problems as it solves.
regards, tom lane
On 8 February 2016 at 14:49, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Yup. The output column type of the sub-SELECT is determined without > reference to its context, so there's nothing causing the unknown-type > literal to get assigned a definite type. Mm. I can follow that, although it makes me unhappy that casting the literal to a known type fixes this, it seems unintuitive. > There's been occasional discussion of changing that behavior, but it's > not real clear that it wouldn't create as many problems as it solves. A more simple solution (to my problem, at least!) might be to stop COALESCE trying to coerce NULLs into a type at all. I don't see how that could ever cause any problems, since NULL is only ever discarded in this context. I would understand it would be difficult if the coercion is taking place at a higher level, but I don't see how that can be the case, because the type it tries to coerce the NULL into is defined by the second argument (which must be COALESCE-specific behaviour, I would think). Geoff
On 8 February 2016 at 14:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Yup. The output column type of the sub-SELECT is determined without
> reference to its context, so there's nothing causing the unknown-type
> literal to get assigned a definite type.
Mm. I can follow that, although it makes me unhappy that casting the
literal to a known type fixes this, it seems unintuitive.
While explicit casting of literals can at times be annoying and seemingly unncessary I wouldn't call it unintuitive. And, the errors are usually sufficiently specific to know where one is required.
> There's been occasional discussion of changing that behavior, but it's
> not real clear that it wouldn't create as many problems as it solves.
A more simple solution (to my problem, at least!) might be to stop
COALESCE trying to coerce NULLs into a type at all. I don't see how
that could ever cause any problems, since NULL is only ever discarded
in this context.
I would understand it would be difficult if the coercion is taking
place at a higher level, but I don't see how that can be the case,
because the type it tries to coerce the NULL into is defined by the
second argument (which must be COALESCE-specific behaviour, I would
think).
This has little to do with COALESCE, nor NULL, specifically. I may be over generalizing a bit here but consider that the select-list of a query returns strongly typed data - of which "unknown" is one such type. In some cases, say "INSERT INTO SELECT FROM", the surrounding context (in this case the relation referred to by the INSERT) can impart type information thus informing the SELECT query of the type for any "untyped" literals it is faced with and thus allows it to implicitly cast the "untyped" literal to the imparted type prior to freezing. However, when the SELECT is part of a sub-query no such contextual information is passed down to it and any "untyped" literals are thus frozen as "unknown" and then passed back up to the parent query. Typically, you cannot count on PostgreSQL to cast "unknown" typed data to other types.
David J.
On 8 February 2016 at 16:05, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> While explicit casting of literals can at times be annoying and seemingly
> unncessary I wouldn't call it unintuitive.
Well.... that very much depends on your definition of intuitive. If
something is "seemingly unnecessary" I would say that's the same thing
as "unintuitive", isn't it?
> Typically, you cannot count on PostgreSQL to cast
> "unknown" typed data to other types.
I don't believe that I'm suggesting that Postgres should. As far as I
can see, COALESCE takes values of type anyelement and attempts to
decide if the types are the same: for example it's unexpectedly quite
happy to take
SELECT COALESCE('1', 0);
because (I guess) it takes the "unknown" typed literal '1' and decides
that it can coerce it into an int; note that it _won't_ do
COALESCE('1'::text, 0) because that is explicitly typed...
I'm not asking that it coerce an actual value with a genuinely unknown
type to a text value: I'm simply suggesting that it's unnecessary for
COALESCE to coerce an unknown-typed NULL into anything (even if you
ignore that NULL is, as far as I know, equivalent, no matter what its
type), because as far as COALESCE is concerned the NULL can be
instantly ignored.
Geoff
On Tue, Feb 9, 2016 at 5:42 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> On 8 February 2016 at 16:05, David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> While explicit casting of literals can at times be annoying and seemingly
>> unncessary I wouldn't call it unintuitive.
>
> Well.... that very much depends on your definition of intuitive. If
> something is "seemingly unnecessary" I would say that's the same thing
> as "unintuitive", isn't it?
>
>> Typically, you cannot count on PostgreSQL to cast
>> "unknown" typed data to other types.
>
> I don't believe that I'm suggesting that Postgres should. As far as I
> can see, COALESCE takes values of type anyelement and attempts to
> decide if the types are the same: for example it's unexpectedly quite
> happy to take
>
> SELECT COALESCE('1', 0);
>
> because (I guess) it takes the "unknown" typed literal '1' and decides
> that it can coerce it into an int; note that it _won't_ do
> COALESCE('1'::text, 0) because that is explicitly typed...
>
> I'm not asking that it coerce an actual value with a genuinely unknown
> type to a text value: I'm simply suggesting that it's unnecessary for
> COALESCE to coerce an unknown-typed NULL into anything (even if you
> ignore that NULL is, as far as I know, equivalent, no matter what its
> type), because as far as COALESCE is concerned the NULL can be
> instantly ignored.
Adding special case behavior to coalesce() is probably not the answer.
coalesce() btw is itself something of a special case due to not being
a proper function. Special cases breed special surprises. I'm too
familiar with the status quo to care much anymore, but if you were to
fix this you'd be wanting to expand the scenarios where 'unknown' can
used.
There are some quirks with the type system but as they say familiarity
can breed contempt. Be advised of the enormous backwards
compatibility baggage here...history has been fairly unkind to
attempted improvements. Please don't take that as discouragement --
just setting the stage.
merlin
Geoff Winkless <pgsqladmin@geoff.dj> writes:
> I'm not asking that it coerce an actual value with a genuinely unknown
> type to a text value: I'm simply suggesting that it's unnecessary for
> COALESCE to coerce an unknown-typed NULL into anything (even if you
> ignore that NULL is, as far as I know, equivalent, no matter what its
> type), because as far as COALESCE is concerned the NULL can be
> instantly ignored.
Leaving aside the question of whether that is actually feasible or
a good idea: how would that improve your original complaint?
SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid;
There's no null visible anywhere in that. I suppose that if there's
no row with id=4, there would be a null at runtime, but that's not
going to make any difference for parse-time determination of what
type the COALESCE() will return.
regards, tom lane
On 9 February 2016 at 14:53, Tom Lane <tgl@sss.pgh.pa.us> wrote: > SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid; > > There's no null visible anywhere in that. I suppose that if there's > no row with id=4, there would be a null at runtime, Well yes, that was the whole point. > but that's not > going to make any difference for parse-time determination of what > type the COALESCE() will return. But when the gwtest subquery _does_ return a value it works, so the problem can't be parse-time determination, can it? Geoff
On 9 February 2016 at 14:53, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> SELECT COALESCE((SELECT 'Yes' FROM gwtest WHERE id=4), 'No') AS valid;
>
> There's no null visible anywhere in that. I suppose that if there's
> no row with id=4, there would be a null at runtime,
Well yes, that was the whole point.
> but that's not
> going to make any difference for parse-time determination of what
> type the COALESCE() will return.
But when the gwtest subquery _does_ return a value it works, so the
problem can't be parse-time determination, can it?
SELECT COALESCE((SELECT 'Yes' FROM (VALUES (1),(2),(3)) tst (id) WHERE id=2), 'No') AS valid;
Same error...I tested using the table as well...also the same error for values of id between 1 and 3.
9.5.0
David J.
On 9 February 2016 at 15:16, David G. Johnston <david.g.johnston@gmail.com> wrote: > Same error...I tested using the table as well...also the same error for > values of id between 1 and 3. Oh my. In my memory, this was working. I try it now, and it doesn't. Apologies: I've obviously managed to lose track of what worked and what didn't. Sorry for wasting time. Geoff
David G. Johnston wrote on 08/02/2016 16:05:
On 8 February 2016 at 14:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Yup. The output column type of the sub-SELECT is determined without
> reference to its context, so there's nothing causing the unknown-type
> literal to get assigned a definite type.
Mm. I can follow that, although it makes me unhappy that casting the
literal to a known type fixes this, it seems unintuitive.While explicit casting of literals can at times be annoying and seemingly unncessary I wouldn't call it unintuitive.
I think if I was designing Postgres's type system (or SQL itself?) from scratch, I'd try to make literals look less like strings. I think part of what's unintuitive is that we're so used to thinking of 'Yes' as representing a text value, when Postgres doesn't see it that way. Perhaps if it was "Select text<yes>", and even "Select int<42>" it would be more obvious that "Select <yes>" or "Select <42>" required type inference.
But that's just dreaming...
Regards,
--
Rowan Collins
[IMSoP]