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

Report SqlBase bugs and possible workarounds.
ninelabs
Australia
Posts: 15
Joined: 29 Mar 2017, 01:52
Location: Perth, Australia

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

Post by ninelabs » 09 Mar 2022, 07:49

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;

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests