Unexpected results when comparing integer with string containing value out of range of that integer datatype

[suyZhong]

Consider the test case below. It is unexpected that, if the second query returns true, the third query returns an empty result, because the value of the WHERE predicate should be true as well. I'm not sure if the result of the second query is expected, but if it is, the third query should return -766027665. If removing the PK constraint, the third query returns -766027665, which is expected.

CREATE TABLE t0(c0 INTEGER, PRIMARY KEY(c0)); -- PK is needed
INSERT INTO t0 (c0) VALUES (-766027665);

SELECT t0.c0 FROM t0; -- -766027665
SELECT t0.c0 <= ((-9223372036854775808)||1) FROM t0; -- true
SELECT t0.c0 FROM t0 WHERE t0.c0 <= ((-9223372036854775808)||1);
-- Expected (if true for second query is expected): -766027665
-- Actual: Empty Table

I found this in version LI-T6.0.0.253 where I built from source code f4e725f3aff89e5599316a8d675f1

[dyemanov]

I suppose what is wrong is this:

SELECT (-766027665 <= -9223372036854775808) FROM t0;
======= 
<false> 

SELECT (-766027665 <= -92233720368547758081) FROM t0;
======= 
<true>  

[dyemanov]

CVT_get_numeric seems to be buggy, it detects -92233720368547758081 as dtype_long.

[AlexPeshkoff]

Expected results are:

SELECT t0.c0 <= ((-9223372036854775808)||1) FROM t0; -- false
SELECT t0.c0 FROM t0 WHERE t0.c0 <= ((-9223372036854775808)||1); -- Empty Table

[AlexPeshkoff]

As a side effect I've fixed overflow detection in CVT_decompose(). Therefore wait for QA before back-porting fix.

[pavel-zotov]

Therefore wait for QA before back-porting fix.

It seems to me that some problems still exist. Please consider following script (for datatypes: smallint, int, bigint and int128), and attached logs for 6.0.0.253 and 6.0.0.257:

--set bail on;

set list on;
set count on;
set echo on;

recreate table t_sml(x smallint, primary key(x) using index sml_pk); -- pk is needed
recreate table t_int(x integer, primary key(x) using index int_pk); -- pk is needed
recreate table t_bigint(x bigint, primary key(x) using index bigint_pk); -- pk is needed
recreate table t_int128(x int128, primary key(x) using index int128_pk); -- pk is needed

insert into t_sml(x) values (-16602);
insert into t_int(x) values (-166027665);
insert into t_bigint(x) values (-166027665);
insert into t_int128(x) values (-166027665);

select t.x <= ( (-32768) || 1 ) as sml_r1 from t_sml t;

select t.x <= ( (-214748364) || 8 ) as sml_r2 from t_sml t;

select t.x <= ( (-214748364) || 9 ) as sml_r3 from t_sml t;

select t.x as sml_r4 from t_sml t where t.x <= ( (-9223372036854775808) || 1 );

select t.x as sml_r5 from t_int t where t.x <= ( (-170141183460469231731687303715884105727) || 1 );

select t.x as sml_r6 from t_int t where t.x <= ( (-170141183460469231731687303715884105728) || 1 );

-- ==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##

select t.x <= ( (-9223372036854775808) || 1 ) as int_r1 from t_int t;

select t.x <= ( (-170141183460469231731687303715884105727) || 1 ) as int_r2 from t_int t;

select t.x <= ( (-170141183460469231731687303715884105728) || 1 ) as int_r3 from t_int t;

select t.x as int_r4 from t_int t where t.x <= ( (-9223372036854775808) || 1 );

select t.x as int_r5 from t_int t where t.x <= ( (-170141183460469231731687303715884105727) || 1 );

select t.x as int_r6 from t_int t where t.x <= ( (-170141183460469231731687303715884105728) || 1 );

-- ==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##

select t.x <= ( (-9223372036854775808) || 1 ) as bigint_r1 from t_bigint t;

select t.x <= ( (-170141183460469231731687303715884105727) || 1 ) as bigint_r2 from t_bigint t;

select t.x <= ( (-170141183460469231731687303715884105728) || 1 ) as bigint_r3 from t_bigint t;

select t.x as bigint_r4 from t_bigint t where t.x <= ( (-9223372036854775808) || 1 );

select t.x as bigint_r5 from t_bigint t where t.x <= ( (-170141183460469231731687303715884105727) || 1 );

select t.x as bigint_r6 from t_bigint t where t.x <= ( (-170141183460469231731687303715884105728) || 1 );

-- ==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##==++##

select t.x <= ( (-9223372036854775808) || 1 ) as int128_r1 from t_int128 t;

select t.x <= ( (-170141183460469231731687303715884105727) || 1 ) as int128_r2 from t_int128 t;

select t.x <= ( (-170141183460469231731687303715884105728) || 1 ) as int128_r3 from t_int128 t;

select t.x as int128_r4 from t_int128 t where t.x <= ( (-9223372036854775808) || 1 );

select t.x as int128_r5 from t_int128 t where t.x <= ( (-170141183460469231731687303715884105727) || 1 );

select t.x as int128_r6 from t_int128 t where t.x <= ( (-170141183460469231731687303715884105728) || 1 );

quit;
[gh-7997.6x.logs.zip](https://github.com/FirebirdSQL/firebird/files/14229311/gh-7997.6x.logs.zip)

For SMALLINT it seems strange that following raises num ovf:

select t.x <= ( (-214748364) || 9 ) as sml_r3 from t_sml t;

Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range

(in both snapshots)

Similar for BIGINT:

select t.x <= ( (-170141183460469231731687303715884105727) || 1 ) as bigint_r2 from t_bigint t;

Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range

And this seems regression (?):

select t.x as bigint_r4 from t_bigint t where t.x <= ( (-9223372036854775808) || 1 );

Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range

(expected: one record with value = -166027665).

And for INT128 we can see the same strange result for:

select t.x <= ( (-170141183460469231731687303715884105727) || 1 ) as int128_r2 from t_int128 t;

Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range

[pavel-zotov]

gh-7997.6x.logs.zip

[AlexPeshkoff]

On 2/10/24 16:06, Pavel Zotov wrote:

Therefore wait for QA before back-porting fix.

It seems to me that some problems still exist.

Pavel, I suppose now I've fixed all related bugs. Please recheck with next snapshot.

[pavel-zotov]

I suppose now I've fixed all related bugs. Please recheck

I still can't understand whether following script issues correct (expected) output or no:

set list on;

recreate table t_sml(x smallint, primary key(x) using index sml_pk); -- pk is needed
recreate table t_int(x integer, primary key(x) using index int_pk); -- pk is needed
recreate table t_bigint(x bigint, primary key(x) using index bigint_pk); -- pk is needed
recreate table t_int128(x int128, primary key(x) using index int128_pk); -- pk is needed

insert into t_sml(x)    values (-1);
insert into t_int(x)    values (-1);
insert into t_bigint(x) values (-1);
insert into t_int128(x) values (-1);

set count on;
set echo on;

-- ########################################## check-1 ####################################################

select t.x as sml_r6 from t_sml t       where t.x = -1 and t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as int_r6 from t_int t       where t.x = -1 and t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as bigint_r6 from t_bigint t where t.x = -1 and t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as int128_r6 from t_int128 t where t.x = -1 and t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as sml_r6 from t_sml t       where  t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as int_r6 from t_int t       where  t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as bigint_r6 from t_bigint t where  t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as int128_r6 from t_int128 t where  t.x <= ( (-170141183460469231731687303715884105728) || 1 );

set echo off;
set count off;

delete from t_sml;
delete from t_int;
delete from t_bigint;
delete from t_int128;

insert into t_sml(x)    values (1);
insert into t_int(x)    values (1);
insert into t_bigint(x) values (1);
insert into t_int128(x) values (1);

set count on;
set echo on;

-- ########################################## check-2 ####################################################

select t.x as sml_r6 from t_sml t       where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );

select t.x as int_r6 from t_int t       where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );

select t.x as bigint_r6 from t_bigint t where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );

select t.x as int128_r6 from t_int128 t where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );

select t.x as sml_r6 from t_sml t       where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );

select t.x as int_r6 from t_int t       where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );

select t.x as bigint_r6 from t_bigint t where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );

select t.x as int128_r6 from t_int128 t where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );

Output:

-- ########################################## check-1 ####################################################

select t.x as sml_r6 from t_sml t       where t.x = -1 and t.x <= ( (-170141183460469231731687303715884105728) || 1 );
Records affected: 0

select t.x as int_r6 from t_int t       where t.x = -1 and t.x <= ( (-170141183460469231731687303715884105728) || 1 );
Records affected: 0

select t.x as bigint_r6 from t_bigint t where t.x = -1 and t.x <= ( (-170141183460469231731687303715884105728) || 1 );
Records affected: 0

select t.x as int128_r6 from t_int128 t where t.x = -1 and t.x <= ( (-170141183460469231731687303715884105728) || 1 );
Records affected: 0

select t.x as sml_r6 from t_sml t       where  t.x <= ( (-170141183460469231731687303715884105728) || 1 );
Records affected: 0

select t.x as int_r6 from t_int t       where  t.x <= ( (-170141183460469231731687303715884105728) || 1 );
Records affected: 0

select t.x as bigint_r6 from t_bigint t where  t.x <= ( (-170141183460469231731687303715884105728) || 1 );

Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range
After line 29 in file gh-7997-x.sql
Records affected: 0

select t.x as int128_r6 from t_int128 t where  t.x <= ( (-170141183460469231731687303715884105728) || 1 );

Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range
After line 31 in file gh-7997-x.sql
Records affected: 0

set echo off;

-- ########################################## check-2 ####################################################

select t.x as sml_r6 from t_sml t       where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );
Records affected: 0

select t.x as int_r6 from t_int t       where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );
Records affected: 0

select t.x as bigint_r6 from t_bigint t where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );
Records affected: 0

select t.x as int128_r6 from t_int128 t where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );
Records affected: 0

select t.x as sml_r6 from t_sml t       where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );
Records affected: 0

select t.x as int_r6 from t_int t       where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );
Records affected: 0

select t.x as bigint_r6 from t_bigint t where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );
Records affected: 0

select t.x as int128_r6 from t_int128 t where  t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 );
Records affected: 0

IMO, comparison for negative values must follow same rules as for positive ones.

Checked on 6.0.0.264.

[AlexPeshkoff]

On 2/15/24 11:54, Pavel Zotov wrote:

|-- ########################################## check-1 #################################################### select t.x as sml_r6 from t_sml t where t.x <= ( (-170141183460469231731687303715884105728) || 1 ); select t.x as int_r6 from t_int t where t.x <= ( (-170141183460469231731687303715884105728) || 1 ); select t.x as bigint_r6 from t_bigint t where t.x <= ( (-170141183460469231731687303715884105728) || 1 ); select t.x as int128_r6 from t_int128 t where t.x <= ( (-170141183460469231731687303715884105728) || 1 ); -- ########################################## check-2 #################################################### select t.x as sml_r6 from t_sml t where t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 ); select t.x as int_r6 from t_int t where t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 ); select t.x as bigint_r6 from t_bigint t where t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 ); select t.x as int128_r6 from t_int128 t where t.x = 1 and t.x >= ( (999999999999999999999999999999999999999) || 9 ); | IMO, comparison for negative values must follow same rules as for positive ones.

Yes - it should, provided you issue same (symmetric) comparisons. Which is definitely not your case - why additional "t.x = 1"?

[pavel-zotov]

I've done copy-paste "dummy block" in previous query, sorry. Proper query is:

set list on;

recreate table t_sml(x smallint, primary key(x) using index sml_pk); -- pk is needed
recreate table t_int(x integer, primary key(x) using index int_pk); -- pk is needed
recreate table t_bigint(x bigint, primary key(x) using index bigint_pk); -- pk is needed
recreate table t_int128(x int128, primary key(x) using index int128_pk); -- pk is needed

insert into t_sml(x)    values (-1);
insert into t_int(x)    values (-1);
insert into t_bigint(x) values (-1);
insert into t_int128(x) values (-1);

set count on;
set echo on;

-- ########################################## check-1 ####################################################

select t.x as sml_r6 from t_sml t       where t.x = -1 and t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as int_r6 from t_int t       where t.x = -1 and t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as bigint_r6 from t_bigint t where t.x = -1 and t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as int128_r6 from t_int128 t where t.x = -1 and t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as sml_r6 from t_sml t       where  t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as int_r6 from t_int t       where  t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as bigint_r6 from t_bigint t where  t.x <= ( (-170141183460469231731687303715884105728) || 1 );

select t.x as int128_r6 from t_int128 t where  t.x <= ( (-170141183460469231731687303715884105728) || 1 );

set echo off;
set count off;

delete from t_sml;
delete from t_int;
delete from t_bigint;
delete from t_int128;

insert into t_sml(x)    values (1);
insert into t_int(x)    values (1);
insert into t_bigint(x) values (1);
insert into t_int128(x) values (1);

set count on;
set echo on;

-- ########################################## check-2 ####################################################

select t.x as sml_r6 from t_sml t       where  t.x = 1 and t.x >= ( (170141183460469231731687303715884105727) || 1 );

select t.x as int_r6 from t_int t       where  t.x = 1 and t.x >= ( (170141183460469231731687303715884105727) || 1 );

select t.x as bigint_r6 from t_bigint t where  t.x = 1 and t.x >= ( (170141183460469231731687303715884105727) || 1 );

select t.x as int128_r6 from t_int128 t where  t.x = 1 and t.x >= ( (170141183460469231731687303715884105727) || 1 );

select t.x as sml_r6 from t_sml t       where  t.x >= ( (170141183460469231731687303715884105727) || 1 );

select t.x as int_r6 from t_int t       where  t.x >= ( (170141183460469231731687303715884105727) || 1 );

select t.x as bigint_r6 from t_bigint t where  t.x >= ( (170141183460469231731687303715884105727) || 1 );

select t.x as int128_r6 from t_int128 t where  t.x >= ( (170141183460469231731687303715884105727) || 1 );

(changes start from line marked as 'check-2').

And my question actually is: why BIGINT and INT128 can not be properly compared with 't.x' when we do NOT use additional "where t.x = -1 and ..." or "t.x = 1 and ..." ?

Overall results are in attached screen.

[AlexPeshkoff]

On 2/15/24 12:30, Pavel Zotov wrote:

And my question actually is: why BIGINT and INT128 can not be properly compared with 't.x' when we do NOT use additional "where t.x = -1 and ..." or "t.x = 1 and ..." ?

They can't be compared because a value, causing overflow of given type, can't be located in the index. First condition x = -1 (or +1) makes is evauated using index, and second condition is checked in usual way for records, selected from index. For smaller types that value fits into index range, therefore it works.

[pavel-zotov]

For smaller types that value fits into index range, therefore it works.

It looks weird. For SMALLER types we can compare but for bigger - not.