Informix

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

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; 

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

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'

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; 

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

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.

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'

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; 

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