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,
Pennsylvania19026