Re: Apparent anomaly with views and unions

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> On Fri, 11 Feb 2005, Guy Rouillier wrote:
>> Now I create a view like this:
>> create or replace view v1 as select * from t1;
>>
>> Next, I attempt to update this view like this:
>> create or replace view v1 as select * from t1 union select * from t2;
>>
>> I receive: ERROR: cannot change data type of view column "serv_id"

> I'm pretty sure PostgreSQL treats the type of serv_id in the new view as
> varchar with no limit rather than varchar(50).

This is the same issue reported here:
http://archives.postgresql.org/pgsql-general/2004-12/msg00508.php
http://archives.postgresql.org/pgsql-hackers/2004-12/msg00408.php
and is pretty closely related to this:
http://archives.postgresql.org/pgsql-bugs/2005-02/msg00008.php

I was looking at fixing it yesterday. The obvious path to a fix is to
do select_common_type across all the members of a set-operation tree
at once, rather than pairwise as we do it now. That bothers me a bit
because it could change the semantics. Consider for example

SELECT float8col UNION ALL (SELECT numericcol UNION SELECT numericcol)

The final result will be float8 in any case, but I think that a strict
reading of the spec requires the righthand UNION to be done in numeric
type, before we promote to float8. If we promote to float8 and then
union, roundoff could make formerly distinct values not distinct,
resulting in a different number of output rows.

The above is a pretty contrived case though --- in particular,
I had to write UNION ALL for the upper level, else it would do a
duplicate-elimination pass that would mask the issue anyway. In
practice I'm not sure anyone would ever notice the difference if we
took the simple path of selecting one type across the whole tree.

Maybe it would work to resolve types only across nested nodes of exactly
the same set operator. I haven't worked through the details of this
however.

regards, tom lane