Icon View Incident Report

Serious Serious
Reported By: Hedley Muscroft
Reported On: 3/23/2009
For: Version 2.02 Build 10
# 2966 Single-Row Aggregate Queries with Only Constants and Aggregate Functions Return Incorrect Results

I have a number of routines designed to get back summary information about a person's finances.

Here's a simple query I use to get back the SUMs of their payments, credits, invoices and refunds. I do it ONE query (instead of 4 separate queries) to try to minimize traffic back-and-forth to the db engine:

The person in question only has 2 invoices (one for £20, the other for £25) totaling £45 on their account (no other financial records are present).

The query returns this:

idx | sum()
2 | 0

which is not correct.

select 0 as idx, coalesce(sum(pay.amount),0) from pay where 
per_id=1000000010000000004
union
select 1 as idx, coalesce(sum(credit.total),0) from credit where 
per_id=1000000010000000004
union
select 2 as idx, coalesce(sum(inv.total),0) from inv where 
per_id=1000000010000000004
union
select 3 as idx, coalesce(sum(refund.amount),0) from refund where 
per_id=1000000010000000004



Comments Comments
This is related to incident report # 2958 that was reported for Build 10. However, there is an additional problem here, which is EDB not handling the constants in the single-row SELECT statements properly. If the WHERE clause causes no rows to be selected, then the constants were being returned as NULL instead of their actual value.


Resolution Resolution
Fixed Problem on 3/24/2009 in version 2.02 build 11


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image