Re: Adding CORRESPONDING to Set Operations

Kerem Kat <keremkat(at)gmail(dot)com> writes:
> While testing I noticed that ordering is incorrect in my implementation. At
> first I thought that removing mismatched entries from ltargetlist and
> rtargetlist would be enough, it didn't seem enough so I added rtargetlist
> sorting.

I don't think you can get away with changing the targetlists of the
UNION subqueries; you could break their semantics. Consider for
instance

select distinct a, b, c from t1
union corresponding
select b, c from t2;

If you discard the A column from t1's output list then it will deliver a
different set of rows than it should, because the DISTINCT is
considering the wrong set of values.

One possible way to fix that is to introduce a level of sub-select,
as if the query had been written

select b, c from (select distinct a, b, c from t1) ss1
union
select b, c from (select b, c from t2) ss2;

However, the real problem with either type of hackery is that these
machinations will be visible in the parsed query, which means for
example that a view defined as

create view v1 as
select distinct a, b, c from t1
union corresponding
select b, c from t2;

would come out looking like the transformed version rather than the
original when it's dumped, or even just examined with tools such as
psql's \d+. I think this is bad style. It's certainly ugly to expose
your implementation shortcuts to the user like that, and it also can
cause problems down the road: if in the future we think of some better
way to implement CORRESPONDING, we've lost the chance to do so for any
stored views that got transformed this way. (There are several places
in Postgres now that take such shortcuts, and all of them were mistakes
that we need to clean up someday, IMO.)

So I think that as far as the parser is concerned, you just want to
store the CORRESPONDING clause more or less as-is, and not do too much
more than verify that it's valid. The place to actually implement it is
in the planner (see prepunion.c). Possibly the add-a-level-of-subselect
approach will work, but you want to do that querytree transformation at
plan time not parse time.

regards, tom lane