Herouth Maoz <herouth@oumail.openu.ac.il> writes:
>> I think the problem results from using non-standard constructs such as
>> order by expression, and indeed ordering by columns that don't appear in
>> the select list.
I replied:
> No, that's not the problem.
Looks like I spoke too soon :-(. On further investigation, it does seem
that the main problem in Richards' example is that he is trying to sort
the result of a UNION by a resjunk attribute. That would work fine as
far as the primary SELECT goes, but there's no mechanism right now for
creating the same resjunk attribute in the sub-selects.
Indeed, we seem to have a whole passel of problems that are related to
transformations done on the target list --- not only resjunk attribute
addition, but rearrangement of the tlist order for INSERT ... SELECT,
and probably other things. In a UNION query these will get done on the
top-level target list but not propagated into the union'd selects.
For example:
create table src (a text, b text, c text);
insert into src values ('a', 'b', 'c');
create table dest (a text default 'A', b text default 'B', c text default 'C');
insert into dest (a,c) select a,b from src;
select * from dest;
a|b|c
-+-+-
a|B|b
(1 row)
-- OK so far, but now try this:
insert into dest (a,c) select a,b from src union select a,c from src;
ERROR: Each UNION | EXCEPT | INTERSECT query must have the same number
of columns.
-- The default for B was added to the first select, but not the second.
-- Even more interesting:
insert into dest (a,c,b) select a,b,c from src union select a,b,c from src;
select * from dest;
a|b|c
-+-+-
a|B|b
a|c|b
a|b|c
(3 rows)
-- The first select's columns were rearranged per the insert column
-- spec, but the second's were not.
I'm also worried about what happens when different sub-selects have
different collections of resjunk attributes and they all get APPENDed
together...
We've got a few bugs to fix here :-(
Meanwhile, I suspect that Richards' SELECT ... UNION ... ORDER BY
would work OK so long as the ORDER BY was for one of the displayed
columns.
regards, tom lane
