Re: BUG #5182: query with deferents results

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "artur saldanha" <artur(dot)saldanha(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5182: query with deferents results
Date: 2009-11-12 19:00:30
Message-ID: 14676.1258052430@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2009-11-12 21:44:25 Re: BUG #5183: Wrong select results using multicolumn gin indexes
Previous Message Stephan Szabo 2009-11-12 18:40:23 Re: BUG #5182: query with deferents results