Re: BUG #5182: query with deferents results

On Thu, 12 Nov 2009, artur saldanha wrote:

>
> The following bug has been logged online:
>
> Bug reference: 5182
> Logged by: artur saldanha
> Email address: artur(dot)saldanha(at)gmail(dot)com
> PostgreSQL version: 8.3.5
> Operating system: Fedora 64
> Description: query with deferents results
> Details:
>
> 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 the cdnrnota values different for those rows? UNION removes duplicates
even of rows from the same side, so that might have something to do with
the behavior you're seeing. A complete report with table definitions,
sample data and results would probably be helpful.