Icon View Incident Report

Minor Minor
Reported By: Alessandra Romano
Reported On: 3/21/2011
For: Version 4.30 Build 3
# 3407 Altering a Table Created Using INTO with Aggregate Expressions Can Cause Corruption

I've upgraded to the latest build, developed a sql script right today, andfound what seems a bug (I've not tested on the former dbisam builds,though).

We have a table called "data1" and we want to create a table "data2" with some aggregate fields from data1

Running the following script ends without errors using dbsys.. you can browse the data (memos are empty, you can open them and see them empty).

..BUT..

Now close dbsys.. open dbsys again, and try to manually open the same table "data2".

"Memo1" field suddenly seems initialized to something (it is shown in CAPITAL letters)..

Now try to double click it.. ACCESS VIOLATION.

Problem:

select rifart,sum(qta)+sum(qtastoccaggio) as total into data2 from data1
group by rifart;
alter table data2 add memo1 memo, add text char(5), add memo2 memo;
select * from data2;

Workaround (use intermediate table):

select rifart,sum(qta)+sum(qtastoccaggio) as total into "memory\temp" from 
data1 group by rifart;
select * into data2 from "memory\temp";
alter table data2 add memo1 memo, add text char(5), add memo2 memo;



Comments Comments and Workarounds
The problem was that the Sum() expressions are populated internally as hidden fields, and these are messing up the subsequent ALTER TABLE on the resultant table. Using the intermediate SELECT statement removed these hidden fields
and resolved this issue. This is only necessary for queries where you're using aggregate function calls in an expression: SUM()+SUM(), COUNT()-COUNT(), etc.


Resolution Resolution
Fixed Problem on 3/21/2011 in version 4.30 build 4


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 ODBC Trial
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source
DBISAM VCL Trial

Image