Обсуждение: BUG #5182: query with deferents results

The following bug has been logged online:

Bug reference:      5182
Logged by:          artur saldanha
Email address:      artur.saldanha@gmail.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.

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.saldanha@gmail.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.

"artur saldanha" <artur.saldanha@gmail.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