Skip site navigation (1) Skip section navigation (2)

Re: BUG #5974: UNION construct type cast gives poor error message

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jeff Wu" <jwu(at)atlassian(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5974: UNION construct type cast gives poor error message
Date: 2011-04-13 18:32:31
Message-ID: 9799.1302719551@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Jeff Wu" <jwu(at)atlassian(dot)com> writes:
> The UNION construct (as noted on this page:
> http://www.postgresql.org/docs/9.0/static/typeconv-union-case.html) will
> cast unknown types to TEXT, however, if you try to do three or more UNIONs
> the order in which the UNIONs are executed will cause some columns to be
> cast to TEXT prematurely.  The result is a type mismatch error.

Really the *right* fix for this would be to resolve the common type
just once across the whole nest of set operations.  That wouldn't be
terribly difficult from a coding standpoint, I think.  The reason we
haven't done it is that it looks like the SQL standard requires type
resolution for set-ops to happen one pair of input relations at a time.
See SQL:2008 7.13 <query expression>, in which everything that's said
about UNION/INTERSECT/EXCEPT is phrased in terms of exactly two input
subqueries; for instance INTERSECT's result type is defined in syntax
rule 18b as:

	The declared type of the i-th column of TR is determined by
	applying Subclause 9.3, "Result of data type combinations", to
	the declared types of the i-th column of T1 and the i-th column
	of T2.

If anyone can think of a way to read the spec to allow subclause 9.3 to
be applied to the whole set of columns at once, we could make this work
less surprisingly.  Or maybe we could find out that some other products
do it like that despite what the spec says?

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Heikki LinnakangasDate: 2011-04-13 19:10:44
Subject: Re: 9.1 doesn't start when died mid-backup
Previous:From: Paul DeschampsDate: 2011-04-13 18:15:31
Subject: BUG #5978: Running postgress in a shell script fails

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group