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

Mon, 19 Jul 1999 22:59:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [SQL] Re: [HACKERS] Counting bool flags in a complex query 
> 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.

Tom, can you give me a list for the TODO list?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 19 Jul 1999 23:33:06 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [SQL] Re: [HACKERS] Counting bool flags in a complex query
Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> We've got a few bugs to fix here :-(

> Tom, can you give me a list for the TODO list?

The two cases I mentioned yesterday can be summarized as

  * SELECT ... UNION ... ORDER BY fails when sort expr not in result list
  * INSERT ... SELECT ... UNION is not reliable

Another thing I realized last night is that Except_Intersect_Rewrite's
coercion of all the sub-select target lists to compatible types is
poorly done; for example in the regression database

regression=> select f1 from int4_tbl union select q1 from int8_tbl;
ERROR:  int8 conversion to int4 is out of range

I think we want to use logic similar to what exists for CASE expressions
to find the common supertype of the sub-select results and coerce all
the sub-selects to that type.  (Thomas, any comments here?  Can we pull
the CASE logic out of transformExpr and make it into a utility routine?)

  * Be smarter about promoting types when UNION merges different data types

Finally, heaven help you if you have a GROUP BY in one of the subselects
whose column gets coerced to a different type by Except_Intersect_Rewrite,
because the sortop for the GROUP BY has already been assigned.
(This is another situation where a multi-level output representation
would be a better answer...)

  * SELECT ... UNION ... GROUP BY fails if column types disagree

			regards, tom lane

Tue, 20 Jul 1999 11:29:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [SQL] Re: [HACKERS] Counting bool flags in a complex query 
> > Tom, can you give me a list for the TODO list?
> 
> The two cases I mentioned yesterday can be summarized as
> 
>   * SELECT ... UNION ... ORDER BY fails when sort expr not in result list
>   * INSERT ... SELECT ... UNION is not reliable
> 
> Another thing I realized last night is that Except_Intersect_Rewrite's
> coercion of all the sub-select target lists to compatible types is
> poorly done; for example in the regression database
> 
> regression=> select f1 from int4_tbl union select q1 from int8_tbl;
> ERROR:  int8 conversion to int4 is out of range
> 
> I think we want to use logic similar to what exists for CASE expressions
> to find the common supertype of the sub-select results and coerce all
> the sub-selects to that type.  (Thomas, any comments here?  Can we pull
> the CASE logic out of transformExpr and make it into a utility routine?)
> 
>   * Be smarter about promoting types when UNION merges different data types
> 
> Finally, heaven help you if you have a GROUP BY in one of the subselects
> whose column gets coerced to a different type by Except_Intersect_Rewrite,
> because the sortop for the GROUP BY has already been assigned.
> (This is another situation where a multi-level output representation
> would be a better answer...)
> 
>   * SELECT ... UNION ... GROUP BY fails if column types disagree

All added to TODO.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 20 Jul 1999 13:23:01 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [SQL] Re: [HACKERS] Counting bool flags in a complex query