Thread: Re: [SQL] Re: [HACKERS] Counting bool flags in a complex query

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

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

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

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