|
Home > Archive > PostgreSQL Bugs > May 2005 > UNION makes strange duplicates
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
UNION makes strange duplicates
|
|
| Tomasz Brzezina 2005-05-27, 9:23 am |
| --------------
$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_v
at WHERE document_id = '65615')
document_id vat_type_id vat_type_value document_netto_valu
e
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_valu
e
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_valu
e
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_valu
e
65615 1 0 0
65615 4 0.07 -12.5327
65615 5 0.22 -7.31148
Any ideas?
--
T.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
| |
| Tom Lane 2005-05-27, 11:24 am |
| Tomasz Brzezina <biuro@sam.w3.pl> writes:
> document_id vat_type_id vat_type_value document_netto_valu
e
> 65615 1 0 0
> 65615 4 0.07 -12.5327
> 65615 5 0.22 -7.31148
> 65615 5 0.22 -7.31148
Perhaps document_item_value is float4, and there is some low-order-bit
difference in the sums?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
|
|
|
|
|