Re: [SQL] Re: [HACKERS] Counting bool flags in a complex query - Mailing list pgsql-hackers

From Tom Lane
Subject Re: [SQL] Re: [HACKERS] Counting bool flags in a complex query
Date
Msg-id 29691.932484572@sss.pgh.pa.us
Whole thread Raw
In response to Re: [SQL] Re: [HACKERS] Counting bool flags in a complex query  (Bruce Momjian <maillist@candle.pha.pa.us>)
Responses Re: [SQL] Re: [HACKERS] Counting bool flags in a complex query
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: Thomas Lockhart
Date:
Subject: Re: [HACKERS] Contributing
Next
From: Tom Lane
Date:
Subject: Re: [HACKERS] selectivity calculation for or_clause is wrong ?