Re: Failure to coerce unknown type to specific type
От | Tom Lane |
---|---|
Тема | Re: Failure to coerce unknown type to specific type |
Дата | |
Msg-id | 23667.1430681283@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: Failure to coerce unknown type to specific type (Kevin Grittner <kgrittn@ymail.com>) |
Ответы |
Re: Failure to coerce unknown type to specific type
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
Kevin Grittner <kgrittn@ymail.com> writes: > Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I don't have a lot of sympathy for that one. coalesce(null, null) >> isn't legal at all per SQL spec > I don't get that from my reading of the SQL spec. A COALESCE > clause is (and always has been) considered a short form of the CASE > clause (not to be mistaken for a function, for example). The spec > section 6.11 1) c) very explicitly requires > COALESCE(NULL, NULL) > be the exact equivalent of > CASE WHEN NULL IS NOT NULL THEN NULL ELSE NULL END Sure, and that isn't legal per spec either (see below). > Yet in PostgreSQL the long form of the CASE clause returns the same > thing as a bare NULL, while the short form (COALESCE) gives an > error. Hm? I get the same thing for either variant: regression=# create table x (d date); CREATE TABLE regression=# insert into x values (coalesce(null, null)); ERROR: column "d" is of type date but expression is of type text LINE 1: insert into x values (coalesce(null, null)); ^ HINT: You will need to rewrite or cast the expression. regression=# insert into x values ( CASE WHEN NULL IS NOT NULL THEN NULL ELSE NULL END ); ERROR: column "d" is of type date but expression is of type text LINE 1: insert into x values ( CASE WHEN NULL IS NOT NULL THEN NULL ... ^ HINT: You will need to rewrite or cast the expression. The reason these things aren't legal per spec is that the spec says that a bare NULL keyword is a <contextually typed value specification> a/k/a <implicitly typed value specification>, and those are only valid in situations where a type can be inferred from the *immediate* context. For example, insert into x values (null); is legal because the source of an INSERT can be a <contextually typed table value constructor> which is a VALUES clause that can contain a <contextually typed value specification>. On the other hand, result subexpressions of a CASE are just <value expression>s, and you will not find any production that allows a bare NULL literal to be a <value expression>. So far as I can find in SQL:2008, the only contexts where <contextually typed anything> is syntactically legal are (1) INSERT, MERGE, and UPDATE source expressions, (2) CAST source expressions, and (3) table-column DEFAULT expressions, all of which have a well-defined target type available from the immediately surrounding semantic context. In short, the text of the spec only allows a bare NULL literal as the immediate argument of one of those constructs. Allowing it in any other context is an extension. The spec doesn't have a notion of unknown-type literal in the same way we do, but we've modeled our handling of those as being like bare NULL literals for type resolution purposes. Our choice has been to resolve as text in situations where there is no other info available from immediate context. I agree with the spec that it would be a bad idea to allow "action at a distance" in the sense of allowing such info to propagate through multiple levels of semantic context. regards, tom lane
В списке pgsql-bugs по дате отправления: