Thread: Re: Query 'Bout A Bug.

[ Charset ISO-8859-1 unsupported, converting... ]
> Bruce,
>  
> The bug list includes the following:
>  
> a.. SELECT foo UNION SELECT foo is incorrectly simplified to SELECT foo 
> 
> Wy is this simplification incorrect? I don't get it.

Not sure.  Maybe someone can comment. 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@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
 

At 22:30 12/07/00 -0400, Bruce Momjian wrote:
>[ Charset ISO-8859-1 unsupported, converting... ]
>> Bruce,
>>  
>> The bug list includes the following:
>>  
>> a.. SELECT foo UNION SELECT foo is incorrectly simplified to SELECT foo 
>> 
>> Wy is this simplification incorrect? I don't get it.
>
>Not sure.  Maybe someone can comment. 
>

As far as I can see, we'd need to know the definition of 'foo'.

eg. 
   select nextval('id') UNION SELECT nextval('id') 

should produce two rows. 

If foo is invariant, then you should be fine because the default behaviour
for union should be to do a set union of the tuples (ie. only *distinct*
rows are added to the result set). 

But, determining invariance is pretty hard for a complex foo (eg. a select
statement that causes rewrite rules to fire).

Finally, select 1 union ALL select 1

should produce two rows.

----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> The bug list includes the following:
>> a.. SELECT foo UNION SELECT foo is incorrectly simplified to SELECT foo 
>> Wy is this simplification incorrect? I don't get it.

> Not sure.  Maybe someone can comment. 

UNION implies DISTINCT according to the spec.  Thus correct output from
the first query will contain no duplicates.  The "simplified" version
will produce duplicates if there are any in the table.

We get this case right:

regression=# explain select f1+1 from int4_tbl union select f1+2 from int4_tbl;

Unique  (cost=2.27..2.29 rows=1 width=4) ->  Sort  (cost=2.27..2.27 rows=10 width=4)       ->  Append  (cost=0.00..2.10
rows=10width=4)               ->  Seq Scan on int4_tbl  (cost=0.00..1.05 rows=5 width=4)               ->  Seq Scan on
int4_tbl (cost=0.00..1.05 rows=5 width=4)
 

(EXPLAIN doesn't show the expressions being computed at each step, but
you can see a UNIQUE is getting done) but not this case:

regression=# explain select f1+1 from int4_tbl union select f1+1 from int4_tbl;

Seq Scan on int4_tbl  (cost=0.00..1.05 rows=5 width=4)

Why, you ask?  Because someone thought it'd be a clever idea to simplify
redundant UNION/INTERSECT/EXCEPT query trees by pretending they are
OR/AND/AND NOT boolean-expression trees and handing them to cnfify().
cnfify knows "x OR x" reduces to "x".  Neat idea, too bad the semantics
aren't quite the same.  But really it's a waste of planning cycles
anyway, since who'd be likely to write such a query in the first place?
I'm planning to rip out the whole foolishness when we redo querytrees
for 7.2.

It would also be incorrect to simplify SELECT foo UNION ALL SELECT foo,
btw, since this should produce all the tuples in foo twice.  This one
we get right, although I don't recall offhand what prevents us from
blowing it --- cnfify() certainly wouldn't know the difference.
        regards, tom lane