Re: BUG #5182: query with deferents results

"artur saldanha" <artur(dot)saldanha(at)gmail(dot)com> writes:
> PostgreSQL 8.3.5 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.3.2
> 20081007 (Red Hat 4.3.2-6
> this query presents 7 rows...
> SELECT *
> FROM ( (SELECT n.nrnota, i.cdproduto, i.qtde as batata, i.vrpunit,
> n.cdfinalidade, cdnrnota
> FROM nfiscais n
> JOIN itnfiscais i ON n.cnnota = i.cnnota
> WHERE n.cdfinalidade = '5A7'::bpchar AND n.status = '131'::bpchar)
> UNION
> (SELECT n.nrnotaorigem AS nrnota, i.cdproduto, i.qtde *
> (-1)::numeric AS batata, i.vrpunit, n.cdfinalidade, cdnrnota
> FROM nfiscais n
> JOIN itnfiscais i ON n.cnnota = i.cnnota
> WHERE n.cdfinalidade = '5A8'::bpchar AND n.status = '131'::bpchar)) q
> where nrnota = '009426'

> same query that omit a colum cdnrnota present only 3 rows..
> the rows omitted are in second query and rows omitted are equal
> contents.

Are you sure that's a bug? UNION is defined to eliminate duplicate rows,
and removing one column might make rows that were distinct no longer
distinct (if they differed only in that column value). If you don't
want duplicate rows removed you should use UNION ALL not plain UNION.

If that isn't it, we are going to need significantly more information
to investigate the problem. Please read
http://www.postgresql.org/docs/8.3/static/bug-reporting.html
about how to report a bug in a way that makes it possible for someone
else to investigate it.

regards, tom lane