Hi all,

I found a very weird behaviour of IB (bug?). I'm using Borland's IB
6.0.1 on W2K. The same behaviour occurs on Linux version too.

I have a simple tree table (ACC) like this:
50
|
+--- 50.1
|
+--- 50.2

And I have a corresponding table (ACCBALANCE), recording beginning
balance and total transaction for each period, eg:

2001-10-01
begin total
50 0 1000
|
+--- 50.1 0 1000
|
+--- 50.2 0 0

I create an SP (Upd_Balance) to update this ACCBALANCE table;
- an update to a child node will be propagated to its parent node
- when a new period is given, it should create a new record with
the beginning balance set to last period's beginning balance +
last period's total.
- A new period is always started on 1st of every month.

So if on 2001-11-01 I update 50.2 with 10, it should become:

2001-10-01 2001-11-01
begin total begin total
50 0 1000 1000 10
|
+--- 50.1 0 1000 1000 0
|
+--- 50.2 0 0 0 10



So here are my tables and SP script:


CREATE DATABASE 'test.gdb' USER 'foo' PASSWORD 'bar';

CREATE TABLE "ACC"
(
"ACCID" VARCHAR(10) NOT NULL,
"PARENTACCID" VARCHAR(10),
CONSTRAINT "ACC_PK" PRIMARY KEY ("ACCID")
);
ALTER TABLE "ACC" ADD CONSTRAINT "ACC_PARENTACCID_FK"
FOREIGN KEY ("PARENTACCID") REFERENCES ACC ("ACCID") ON UPDATE CASCADE;

/*
50
|
+--- 50.1
|
+--- 50.2
*/

INSERT INTO "ACC" ("ACCID", "PARENTACCID") VALUES ('50', NULL);
INSERT INTO "ACC" ("ACCID", "PARENTACCID") VALUES ('50.1', '50');
INSERT INTO "ACC" ("ACCID", "PARENTACCID") VALUES ('50.2', '50');

CREATE TABLE "ACCBALANCE"
(
"ACCID" VARCHAR(10) NOT NULL,
"PERIODSTART" TIMESTAMP NOT NULL,
"BEGIN_BALANCE" DOUBLE PRECISION default 0.00 NOT NULL,
"PERIOD_BALANCE" DOUBLE PRECISION default 0.00 NOT NULL,
CONSTRAINT "ACCBALANCE_PK" PRIMARY KEY ("ACCID", "PERIODSTART")
);
ALTER TABLE "ACCBALANCE" ADD CONSTRAINT "ACCBALANCE_ACC_FK"
FOREIGN KEY ("ACCID") REFERENCES ACC ("ACCID") ON UPDATE CASCADE;

/* sample balance for oct 2001 */
INSERT INTO "ACCBALANCE" ("ACCID", "PERIODSTART", "BEGIN_BALANCE", "PERIOD_BALANCE")
VALUES ('50.1', '2001/10/01', 0, 1000);
INSERT INTO "ACCBALANCE" ("ACCID", "PERIODSTART", "BEGIN_BALANCE", "PERIOD_BALANCE")
VALUES ('50', '2001/10/01', 0, 1000);
COMMIT;

set term ^;

/* update accbalance table
pAccID : AccID
pDt : Period Date (always 1st of every month)
pValue : Transaction value to be recorded */
create procedure Upd_Balance (
pAccID varchar(10),
pDt timestamp,
pValue numeric(18, 2)) as
begin
exit;
end^

alter procedure Upd_Balance (
pAccID varchar(10),
pDt timestamp,
pValue numeric(18, 2)) as
declare variable vParentAccID varchar(10);
declare variable vPeriodStart timestamp;
declare variable vLastBalance numeric(18, 2);
begin
while (pAccID is not null) do
begin
/* do we have beginning balance for this period already? */
vPeriodStart = null;
select periodstart from accbalance
where accid = :pAccID and periodstart = :pDt
into :vPeriodStart;

/* nope, create new balance by copying previous period's balance */
if (vPeriodStart is null) then
begin
vLastBalance = 0;
select (begin_balance + period_balance) from accbalance
where accid = :pAccID and periodstart = (
select max(periodstart) from accbalance
where accid = :pAccID and periodstart < :pDt )
into :vLastBalance;

insert into accbalance (accid, periodstart, begin_balance, period_balance)
values (:pAccID, :pDt, :vLastBalance, 0.00);
end

/* update tx balance for this period */
update accbalance
set period_balance = cast((period_balance + :pValue) as numeric(18, 2))
where accid = :pAccID and periodstart = :pDt;

/* update parent */
vParentAccID = null;
select parentaccid from acc
where accid = :pAccID into :vParentAccID;
pAccID = vParentAccID;
end
end^

set term ;^


The problem is the above SP doesn't correctly runs on the 2nd iteration,
it doesn't create beginning balance correctly.

select * from accbalance order by periodstart, accid;

ACCID PERIODSTART BEGIN_BALANCE PERIOD_BALANCE
========== ========================= ======================= =======================

50 2001-10-01 00:00:00.0000 0.000000000000000 1000.000000000000
50.1 2001-10-01 00:00:00.0000 0.000000000000000 1000.000000000000


If I run:

execute procedure Upd_Balance '50.1', '2001-11-01', 10;

select * from accbalance order by periodstart, accid;

ACCID PERIODSTART BEGIN_BALANCE PERIOD_BALANCE
========== ========================= ======================= =======================

50 2001-10-01 00:00:00.0000 0.000000000000000 1000.000000000000
50.1 2001-10-01 00:00:00.0000 0.000000000000000 1000.000000000000
50 2001-11-01 00:00:00.0000 1000.000000000000 10.00000000000000
50.1 2001-11-01 00:00:00.0000 1000.000000000000 10.00000000000000

which is correct behaviour.

but if I rollback, and then run:

execute procedure Upd_Balance '50.2', '2001-11-01', 10;

select * from accbalance order by periodstart, accid;

ACCID PERIODSTART BEGIN_BALANCE PERIOD_BALANCE
========== ========================= ======================= =======================

50 2001-10-01 00:00:00.0000 0.000000000000000 1000.000000000000
50.1 2001-10-01 00:00:00.0000 0.000000000000000 1000.000000000000
50 2001-11-01 00:00:00.0000 0.000000000000000 10.00000000000000
^
|---- this should be 1000
50.2 2001-11-01 00:00:00.0000 0.000000000000000 10.00000000000000

the beginning balance for '50' on 2001-11-01 should be 1000 instead of 0.


If I break the SP to 2 SPs like below (I put the main loop in separate SP),
then everything works as expected:

create procedure Upd_Balance_Main (
pAccID varchar(10),
pDt timestamp,
pValue numeric(18, 2)) as
begin
exit;
end^

alter procedure Upd_Balance_Main (
pAccID varchar(10),
pDt timestamp,
pValue numeric(18, 2)) as
declare variable vPeriodStart timestamp;
declare variable vLastBalance numeric(18, 2);
begin
/* do we have beginning balance for this period already? */
vPeriodStart = null;
select periodstart from accbalance
where accid = :pAccID and periodstart = :pDt
into :vPeriodStart;

/* nope, create new balance by copying previous period's balance */
if (vPeriodStart is null) then
begin
vLastBalance = 0;
select (begin_balance + period_balance) from accbalance
where accid = :pAccID and periodstart = (
select max(periodstart) from accbalance
where accid = :pAccID and periodstart < :pDt )
into :vLastBalance;

insert into accbalance
(accid, periodstart, begin_balance, period_balance)
values (:pAccID, :pDt, :vLastBalance, 0.00);
end

/* update tx balance total for this period */
update accbalance
set period_balance = cast((period_balance + :pValue) as numeric(18, 2))
where accid = :pAccID and periodstart = :pDt;
end^

alter procedure Upd_Balance (
pAccID varchar(10),
pDt timestamp,
pValue numeric(18, 2)) as
declare variable vParentAccID varchar(10);
begin
while (pAccID is not null) do
begin

execute procedure Upd_Balance_Main pAccID, pDt, pValue;

/* update parent */
vParentAccID = null;
select parentaccid from acc
where accid = :pAccID into :vParentAccID;
pAccID = vParentAccID;
end
end^


I'm not sure what's wrong here, it's completely the same logic, but the 1st SP
doesn't work. It looks like IB cached the SQL statement / parameter, and got
the wrong result. Should I file this as an IB/FB bug?

regards,
Sindu