Re: Apparent anomaly with views and unions

"Guy Rouillier" <guyr(at)masergy(dot)com> writes:
> We appear to have two issues here:

> (1) What is the meaning of "replace" as in replace view?
> (2) What are the semantics for multiple set operations?

> My original issue deals with (1). I'm unfortunately not well versed in
> the SQL spec, but from a layman's point of view, if I'm replacing a view
> definition, I don't expect that to be rejected because it is
> incompatible with the original view definition.

Well, we don't have the ability to cope with redefining a view in a way
that changes its result column list --- we don't know how to propagate
that into dependent views in any clean way. (Now, ALTER TABLE deals
with this by allowing the change only when there are no dependent views;
maybe it'd be sensible to do the same for REPLACE VIEW.) The immediate
issue here is that the redefinition shouldn't be seen as changing the
result column type. The underlying cause of that hasn't got anything
to do with views per se, it has to do with the way that we impute a
result datatype to a UNION structure.

> My new definition may
> use entirely different set of tables than the original view. "create or
> replace view" should accomplish the same thing as "drop view; create
> view".

In the cases I'm concerned about, DROP VIEW would fail unless you used
CASCADE, and the subsequent CREATE VIEW would not magically bring back
the dependent objects the CASCADE had killed. The entire *point* of
REPLACE VIEW is that it does not have the side effect of destroying
dependent objects; and therefore it has to act in a way that ensures
the dependent objects still make sense. So, for the moment anyway,
that means disallowing changes in the output column names and datatypes.

>> Consider for example
>>
>> SELECT float8col UNION ALL (SELECT numericcol UNION SELECT
>> numericcol)

> This seems to be addressing point (2). In this particular case, I would
> expect the query engine to follow the submitter's explicit directions,
> since he or she provided an explicit grouping. If no parentheses are
> included, then the optimizer or query rewriter is free to do as it
> wishes.

I don't think we get to do it that way. The way the SQL spec is
written, there is not a difference between A UNION B UNION C and
(A UNION B) UNION C. The result is *always* built up from pairwise
UNIONs, and the semantic definition of UNION talks about combining
only two tables at a time.

This consideration doesn't stop us from fixing your problem (since in
the case you care about, all the datatypes are the same anyway) ...
but it puts rather a large crimp in Woehling's problem, which I was
hoping to fix at the same time.

regards, tom lane