Skip site navigation (1) Skip section navigation (2)

UNION makes strange duplicates

From: Tomasz Brzezina <biuro(at)sam(dot)w3(dot)pl>
To: pgsql-bugs(at)postgresql(dot)org
Subject: UNION makes strange duplicates
Date: 2005-05-27 09:22:51
Message-ID: 4296E6EB.6090806@sam.w3.pl (view raw or flat)
Thread:
Lists: pgsql-bugs
--------------
$psql -V
psql (PostgreSQL) 7.4.7
contains support for command-line editing
--------------
I have table with two _id (document_id and document_store_id). And I 
have a view:

  SELECT document_items.document_store_id AS document_id, 
document_items.vat_type_id, vat_type_value, 
sum(document_items.document_item_value) AS document_netto_value
    FROM document_items
    JOIN vat_type ON document_items.vat_type_id = vat_type.vat_type_id
   GROUP BY document_items.document_store_id, 
document_items.vat_type_id, vat_type_value
UNION
  SELECT document_items.document_id, document_items.vat_type_id, 
vat_type_value, sum(document_items.document_item_value) AS 
document_netto_value
    FROM document_items
    JOIN vat_type ON document_items.vat_type_id = vat_type.vat_type_id
   GROUP BY document_items.document_id, document_items.vat_type_id, 
vat_type_value;

each of these two SELECTs almost always produce the same results 
(because document_store_id almost always is equal to document_id , so 
the result of VIEW should be exact the same as result of each SELECT.

BUT it isn't - example:
(SELECT * FROM document_values_by_vat WHERE document_id = '65615')

document_id 	vat_type_id 	vat_type_value 	document_netto_value
65615		1		0		0	
65615		4		0.07		-12.5327
65615		5		0.22		-7.31148
65615		5		0.22		-7.31148

the expected result is:
document_id 	vat_type_id 	vat_type_value 	document_netto_value
65615		1		0		0	
65615		4		0.07		-12.5327
65615		5		0.22		-7.31148

the result of first select is:
document_id 	vat_type_id 	vat_type_value 	document_netto_value
65615		1		0		0	
65615		4		0.07		-12.5327
65615		5		0.22		-7.31148

the result of second select is:
document_id 	vat_type_id 	vat_type_value 	document_netto_value
65615		1		0		0	
65615		4		0.07		-12.5327
65615		5		0.22		-7.31148

Any ideas?
-- 
T.

Responses

pgsql-bugs by date

Next:From: evgeny gridasovDate: 2005-05-27 10:06:07
Subject: INSERT deadlocks (bug or feature?)
Previous:From: Ilya KonyuhovDate: 2005-05-27 08:03:35
Subject: BUG #1683: Error in PQresultErrorField function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group