View Incident Report

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

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

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

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

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap