Icon View Incident Report

Serious Serious
Reported By: Helmut Schiefer
Reported On: 4/14/2005
For: Version 4.19 Build 1
# 2012 Using Aggregate Functions in Expressions Can Cause Incorrect Results or AV

The following Query produce a wrong result if I use some expression with aggregate functions.


CREATE TABLE IF NOT EXISTS "Test1"
(
   "ID" AUTOINC,
   "PG" VARCHAR(10),
   "GSTK" INTEGER,
   "SSTK" INTEGER,
   "Fehler" VARCHAR(25),
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
LAST AUTOINC 5
);

INSERT INTO "Test1" VALUES (1,'DI', 10, 2,'A');
INSERT INTO "Test1" VALUES (2,'DI', 0, 1,'B');
INSERT INTO "Test1" VALUES (3,'DI', 0, 2,'C');
INSERT INTO "Test1" VALUES (4,'DI', 100, 25,'B');
INSERT INTO "Test1" VALUES (5,'DI', 0, 10,'C');

* Example without aggregate expression --> OK

Select
  PG,
  SUM(GSTK) as GUT,
  SUM(SSTK) as SCHLECHT,
  SUM(IF(Fehler='A' THEN SSTK ELSE 0)) as "SCHLECHT A",
  SUM(IF(Fehler='B' THEN SSTK ELSE 0)) as "SCHLECHT B",
  SUM(IF(Fehler='C' THEN SSTK ELSE 0)) as "SCHLECHT C"
FROM
  Test1
GROUP BY PG

* Example with aggregate expression --> Wrong result "SCHLECHT A", ...

Select
  PG,
  SUM(GSTK) as GUT,
  SUM(SSTK) as SCHLECHT,
  ROUND(100*SUM(SSTK)/SUM(SSTK+GSTK) TO 2) as "SCHLECHT PROZENT", 
  SUM(IF(Fehler='A' THEN SSTK ELSE 0)) as "SCHLECHT A",
  SUM(IF(Fehler='B' THEN SSTK ELSE 0)) as "SCHLECHT B",
  SUM(IF(Fehler='C' THEN SSTK ELSE 0)) as "SCHLECHT C"
FROM
  Test1
GROUP BY PG

* Example with aggregate expression --> Access Violation

Select
  SUM(GSTK) as GUT,
  SUM(SSTK) as SCHLECHT,
  ROUND(100*SUM(SSTK)/SUM(SSTK+GSTK) TO 2) as "SCHLECHT PROZENT", 
  PG,
  SUM(IF(Fehler='A' THEN SSTK ELSE 0)) as "SCHLECHT A",
  SUM(IF(Fehler='B' THEN SSTK ELSE 0)) as "SCHLECHT B",
  SUM(IF(Fehler='C' THEN SSTK ELSE 0)) as "SCHLECHT C"
FROM
  Test1
GROUP BY PG



Comments Comments and Workarounds
The workaround is to multi-stage the query into separate queries so that you can evaluate the expressions for the aggregates in a later query.


Resolution Resolution
Fixed Problem on 4/23/2005 in version 4.20 build 1


Products Affected Products Affected
DBISAM Additional Software and Utilities
DBISAM CLX Client-Server
DBISAM CLX Client-Server with Source
DBISAM CLX Standard
DBISAM CLX Standard with Source
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source

Image