Apparent anomaly with views and unions

I using 8.0.1. I create 3 tables with these definitions:

create table t1 (serv_id varchar(50) not null);
create table t2 (serv_id varchar(50) not null);
create table t3 (serv_id varchar(50) not null);

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"

Next, I drop view v1 and recreate it using the union above. I am now
able to do the following without error:

create or replace view v1 as select * from t1 union select * from t2
union select * from t3;

So it looks like changing a view from a non-union to a union is
producing an error, but changing a view that already contains a 2-way
union to one that contains a 3-way union is allowed (and I'm assuming
any n to n+1 union transition is also allowed.)

Is this intentional? It was certainly not predictable.

--
Guy Rouillier

On Fri, 11 Feb 2005, Guy Rouillier wrote:

> I using 8.0.1. I create 3 tables with these definitions:
>
> create table t1 (serv_id varchar(50) not null);
> create table t2 (serv_id varchar(50) not null);
> create table t3 (serv_id varchar(50) not null);
>
> 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). I think that's also not
entirely up to spec.

In the two to three case it already was a varchar with no limit and so the
type was the same.

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