HDR: MULTISET gives -626 on secondary
Hi, everybody,
We've encountered very unpleasant problem:
a lot of our reports (containing MULTISET in SELECT statement) give -626
error
when run on secondary server in HDR pair , while they run OK on primary
Steps to reproduce (IDS 9.40uc4, logged database)
---- PRIMARY ----------
set isolation to dirty read;
set lock mode to wait 100;
create table client_request (req_id serial , descr varchar(30));
alter table client_request add constraint primary key (req_id) constraintcl_req_pk;
create table transactions
(tx_id serial not null,
amount money(10,2),
request_id int,
tx_date date,request_origin char(4));
alter table transactions add constraint primary key (tx_id) constrainttx_pk;
insert into client_request values (1, 'aaaa');
insert into client_request values (2, 'bbbb');
insert into transactions values(1, 1.0, 1, TODAY, 'wwww');
insert into transactions values(2, 2.0, 1, TODAY, 'wwww');
insert into transactions values(3, 3.0, 2, TODAY, 'wwww');
SELECT {+ FIRST_ROWS} FIRST 150
SUBSTR(MULTISET(
SELECT
t.amount as amount,
t.tx_date as date
FROM transactions t
WHERE req.req_id = t.request_id
)::lvarchar,1,2048)
as related_transactions
FROM client_request req <------ that SQL runs OK on primary and gives
-626 on secondary
---------------------
---------- SECONDARY ----
--- SQL-1
SELECT {+ FIRST_ROWS} FIRST 150
SUBSTR(MULTISET(
SELECT
t.amount as amount,
t.tx_date as date
FROM transactions t
WHERE req.req_id = t.request_id
)::lvarchar,1,2048)
as related_transactions
FROM client_request req <---- ERROR 626
--- SQL-2
SELECT {+ FIRST_ROWS} FIRST 150
SUBSTR(MULTISET(
SELECT
t.amount as amount
FROM transactions t
WHERE req.req_id = t.request_id
)::lvarchar,1,2048)
as related_transactions
FROM client_request req <---- NO ERROR !!!!!!!!!!!!!!!!!!!!!
--- SQL-3
SELECT {+ FIRST_ROWS} FIRST 150
SUBSTR(MULTISET(
SELECT
t.tx_date as date
FROM transactions t
WHERE req.req_id = t.request_id
)::lvarchar,1,2048)
as related_transactions
FROM client_request req <---- ERROR 626 AGAIN
--------------------------
Please, note, that the difference between SQL-2 and SQL-3
is only in the returned datatype
best regards,
Alexey Sonkin
sending to informix-list