From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Jeff Davis <pgsql(at)j-davis(dot)com>, Postgres-Bugs <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: Failure to coerce unknown type to specific type |
Date: | 2015-04-30 16:06:45 |
Message-ID: | 1439794807.1364461.1430410005618.JavaMail.yahoo@mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I have some recollection that we'd also put it off pending
> resolution of debates about how to handle unknown-type literals
> in UNIONs and similar contexts. But poking at such examples
> right now, the behavior seems generally reasonable: it seems like
> we resolve "unknown" as text when forced to make a decision, but
> otherwise put it off as long as possible. So that consideration
> may be obsolete.
I recall two constructs that we had in production that caused some
pain moving to PostgreSQL.
Here's one:
test=# create table x (d date);
CREATE TABLE
test=# insert into x values (null);
INSERT 0 1
test=# 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.
I know these worked in Sybase ASE, SAP DB, MySQL MaxdDB, IBM OS/2
EE's port of DB2, and early versions of MS SQL Server. I have
confirmed (using SQL Fiddle) that it works in Oracle 11g R2, MySQL
5.5 and 5.6, and SQLite (SQL.js). Interestingly, MS SQL Server
2014 now throws this error:
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.
Here the other:
test=# select null as ts union all select null union all select now();
ERROR: UNION types text and timestamp with time zone cannot be matched
LINE 1: ...ect null as ts union all select null union all select now();
^
test=# create table n (id int not null);
CREATE TABLE
test=# insert into n values (1);
INSERT 0 1
test=# select null as ts from n
test-# union all
test-# select null from n
test-# union all
test-# select 1 from n;
ERROR: UNION types text and integer cannot be matched
LINE 5: select 1 from n;
^
This runs in *all* of the above environments.
I don't know of any other database product which chokes on the above.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2015-04-30 16:51:10 | Re: Re: BUG #12990: Missing pg_multixact/members files (appears to have wrapped, then truncated) |
Previous Message | David G. Johnston | 2015-04-30 16:02:14 | Re: Suggestions on postgres |
From | Date | Subject | |
---|---|---|---|
Next Message | Fabrízio de Royes Mello | 2015-04-30 16:15:26 | Re: Proposal : REINDEX xxx VERBOSE |
Previous Message | Robert Haas | 2015-04-30 16:01:20 | Re: feature freeze and beta schedule |