SQLB-3128 - V12.3 – Regression - Err 01607 TYP IET Invalid external data type when UPDATE with multiple sub-selects

[ninelabs]

Hi all, just wanted to pass on a newly logged bug in SQLBase 12.3 logged as a blocker by the dev team. It occurs when using an UPDATE statement to update more than one column using sub-selects. It was fine in 12.1 and 12.2. They are working on a fix for me at the moment in the form of a one off patch.
For those curious the following script replicates the problem:

Code: Select all

CREATE TABLE test1 (order INTEGER, item INTEGER, qty FLOAT, unit_cost FLOAT, total_cost FLOAT);
INSERT INTO test1 VALUES(:1, :2, :3, :4, :5)
\
1,1,1.2,10,12
1,2,5,2,10
1,3,10,1.5,15
1,4,10,50,500
2,1,1,1,1
2,2,10,5,50
3,1,1,10,10
/
CREATE TABLE test2 (order INTEGER, total_qty FLOAT, total_cost FLOAT);
INSERT INTO test2 (order) VALUES (:1)
\
1
2
3
/

REMARK
\
The following UPDATE fails
/
UPDATE test2 t2
SET t2.total_qty = ( SELECT SUM( t1.qty ) FROM test1 t1 WHERE t1.order = t2.order ),
t2.total_cost = ( SELECT SUM( t1.total_cost ) FROM test1 t1 WHERE t1.order = t2.order )
WHERE order = 1;

REMARK
\
If we split the above UPDATE into 2 separate UPDATE statements they work
/
UPDATE test2 t2
SET t2.total_qty = ( SELECT SUM( t1.qty ) FROM test1 t1 WHERE t1.order = t2.order )
WHERE order = 1;

UPDATE test2 t2
SET t2.total_cost = ( SELECT SUM( t1.total_cost ) FROM test1 t1 WHERE t1.order = t2.order )
WHERE order = 1;