Re: HDR: MULTISET gives -626 on secondary
Alexey Sonkin said:
> 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) constraint> cl_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) constraint> tx_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
That is *seriously* weird. It looks like it doesn't like the date. Have
you logged a case?
--
Bye now,
Obnoxio
"C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule"
- Coluche
"I'm trying to see things your way, but I can't get my head up my ass"
- JCH
"Ogni uomo mi guarda come se fossi una testa di cazzo"
- Marco
http://www.catb.org/~esr/faqs/smart-questions.html
sending to informix-list