Original Message:
Sent: Sat September 02, 2023 05:13 PM
From: David Williams
Subject: Strange Error -1215 under Informix 14.10 (but not 12.10)
Hi,
Question - why is deadline.event_id not an integer?
With different data types for the join the optimizer is free to do the conversion either way around!
Regards,
David.
------------------------------
David Williams
Original Message:
Sent: Mon August 28, 2023 04:05 PM
From: Michael Hoffman
Subject: Strange Error -1215 under Informix 14.10 (but not 12.10)
Well, thanks to Art, I believe we've discovered that it is a "bad data" issue which snuck into the deadline table right before migration.
The script which has been running for years, is now failing on the version 12 instance (luckily we still have 1 still running).
Yes, casting as varchar(11) or char(11) allows the query to work. (I did note that in my original post. It's how we've been working around the error. It's just slow)
So the issue isn't Informix.... please excuse the interruption.
However, since Art asked, here are the Explain Plans for the query with and without the varchar cast.
QUERY: (OPTIMIZATION TIMESTAMP: 08-28-2023 14:00:27)
------
select * from deadline
where event_id in (select trans_seq_id::varchar(11) from transaction
where trans_id = '540982919'
)
Estimated Cost: 30115
Estimated # of Rows Returned: 32695
1) devdba.deadline: INDEX PATH
(1) Index Name: informix.pk_deadline
Index Keys: event_id batch_category (Serial, fragments: ALL)
Lower Index Filter: devdba.deadline.event_id = ANY <subquery>
Subquery:
---------
Estimated Cost: 3011
Estimated # of Rows Returned: 16412
1) devdba.transaction: INDEX PATH
(1) Index Name: informix.trn_ti_stat_idx
Index Keys: trans_id record_status_cd (Serial, fragments: ALL)
Lower Index Filter: devdba.transaction.trans_id = '540982919'
QUERY: (OPTIMIZATION TIMESTAMP: 08-28-2023 14:00:27)
------
select * from deadline
where event_id in (select trans_seq_id from transaction
where trans_id = '540982919'
)
Estimated Cost: 139101
Estimated # of Rows Returned: 32737
1) devdba.deadline: SEQUENTIAL SCAN
Filters: devdba.deadline.event_id = ANY <subquery>
Subquery:
---------
Estimated Cost: 3011
Estimated # of Rows Returned: 16412
1) devdba.transaction: INDEX PATH
(1) Index Name: informix.trn_ti_stat_idx
Index Keys: trans_id record_status_cd (Serial, fragments: ALL)
Lower Index Filter: devdba.transaction.trans_id = '540982919'
------------------------------
Michael Hoffman
Original Message:
Sent: Mon August 28, 2023 01:49 PM
From: Art Kagel
Subject: Strange Error -1215 under Informix 14.10 (but not 12.10)
It is no unusual for the optimizer to decide to convert numeric strings to integer for comparison a four byte integer comparision is more efficient than an 11 byte string comparison after all. The surprising part is that v12 and v14 are handling this query differently. I'd like to see the SET EXPLAIN output from both if possible, just for curiosity sake.
That said, try this query which I believe will be at least as fast and probably not suffer from the issue as long as both deadline:event_id and transaction:trans_id are both indexed:
SELECT d.*
FROM deadline AS d
JOIN transaction t
ON d.event_id = t.trans_seq_id::CHAR(11) AND d.trans_id = 540982919;
or using the older syntax:
SELECT d.*
FROM deadline AS d, transaction AS t
WHERE d.event_id = t.trans_seq_id::CHAR(11)
AND d.trans_id = 540982919;
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Mon August 28, 2023 01:34 PM
From: Michael Hoffman
Subject: Strange Error -1215 under Informix 14.10 (but not 12.10)
Hi All,
This should be simple, but it's had me spinning for days.
We recently upgraded from Informix 12.10.FC4 on Solaris 10 --> Informix 14.10.FC9 on Linux 4.18.
Since the migration, nearly all of the SQL code and procedures work, no changes needed.
EXCEPT one, which is suddenly returning " 1215: Value exceeds limit of INTEGER precision" error.
select * from deadline
where event_id in (select trans_seq_id from transaction where trans_id = '540982919' )
;
(If I cast the trans_seq_id as trans_seq_id::varchar the query works. This makes no sense because the event_id is a char(11) and the trans_seq_id is integer. It seems as if the inner query is defining the "data type' of the main query, which is totally backwards)
SCHEMA and Data
---------------------------
Deadline:
create table deadline
(
mstr_trans_id char(11) not null ,
event_id char(11) not null ,
event_typ char(7) not null ,
deadline_dtm datetime year to second not null
}
Transaction:
create table transaction
(
trans_seq_id integer not null ,
trans_id char(11),
mstr_trans_id char(11) not null ,
trans_typ char(12) not null ,
received_dtm datetime year to second not null
}
select trans_seq_id from transaction
where trans_id = 540982919'';
trans_seq_id
55941453
55941454
55941455
If I use the trans_seq_id values directly, I get the desired results:
select * from deadline where event_id in (55941453, 55941454, 55941455);
mstr_trans_id 20111546507
event_id 55941453
event_typ TRAN
deadline_dtm 2023-10-31 23:59:00
mstr_trans_id 20111546507
event_id 55941454
event_typ TRAN
deadline_dtm 2023-10-31 23:59:00
mstr_trans_id 20111546507
event_id 55941455
event_typ TRAN
deadline_dtm 2023-10-31 23:59:00
Thanks,
Michael
------------------------------
Michael Hoffman
------------------------------