Icon View Incident Report

Serious Serious
Reported By: Gary Jones
Reported On: 1/29/2002
For: Version 3.04 Build 1
# 1013 UNIONed SELECT Queries Where One Query Uses SUM() on an Integer Column Causes Incorrect Results

I have two tables (described below). This is not an exact representation of the situation I have, but, in essence I want a
summary table giving a cross-tab of all the data. The required result from the above test data would be as indicated below.

Table structures:

Table1
------------------
Name      String10
Number1   Integer

Table2
------------------
Name      String10
Number2   Integer


Table data:

Table1
---------
Smith   3
Jones   4

Table2
---------
Smith   2
Jones   3

Result required:

Jones   4   3
Smith   3   2

In DBSYS 3.03, the following SQL produces the correct results:

select name,SUM(number1) As Tot1, SUM(0) As Tot2
from table1
union
select name,0,number2
from table2
GROUP BY name

A workaround that seems to work is to adapt the SQL slightly as 
follows:

select name,SUM(number1) As Tot1, SUM(0) As Tot2
from table1
union
select name,sum(0),sum(number2)
from table2
GROUP BY name

The same SQL in DBSYS 3.04 produces:

Jones   4   493921239043
Smith   3   446676598786




Comments Comments and Workarounds
The problem was with the integer promotion to Int64 when summing 32-bit integers, and DBISAM was not trapping this properly and indicating that there is a type mismatch situation.


Resolution Resolution
Fixed Problem on 1/31/2002 in version 3.05 build 1
Image