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

suyZhong · 2024-02-09T04:38:06Z

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 f4e725f

The text was updated successfully, but these errors were encountered:

dyemanov · 2024-02-09T13:48:06Z

I suppose what is wrong is this:

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

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

dyemanov · 2024-02-09T14:14:30Z

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

AlexPeshkoff · 2024-02-09T15:26:00Z

Expected results are:

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

AlexPeshkoff · 2024-02-09T17:11:44Z

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

pavel-zotov · 2024-02-10T13:05:50Z

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 · 2024-02-10T13:07:09Z

gh-7997.6x.logs.zip

…h string containing value out of range of that integer datatype

AlexPeshkoff · 2024-02-14T11:17:55Z

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 · 2024-02-15T08:53:55Z

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 · 2024-02-15T09:05:33Z

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 · 2024-02-15T09:30:13Z

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 · 2024-02-15T09:43:16Z

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 · 2024-02-15T10:04:09Z

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.

…ing containing value out of range of that integer datatype; fixed regression in test core_1274

…ing containing value out of range of that integer datatype; fixed overflows that happen when index key is composed

…ntaining integer value out of bigint range (cherry picked from commit 7531251)

…h string containing value out of range of that integer datatype (cherry picked from commit 1192082)

…ing containing value out of range of that integer datatype; fixed regression in test core_1274 (cherry picked from commit 69d52d3)

…ing containing value out of range of that integer datatype; fixed overflows that happen when index key is composed (cherry picked from commit 337ca49)

…ntaining integer value out of bigint range (cherry picked from commit 7531251)

…h string containing value out of range of that integer datatype (cherry picked from commit 1192082)

…ing containing value out of range of that integer datatype; fixed regression in test core_1274 (cherry picked from commit 69d52d3)

…ing containing value out of range of that integer datatype; fixed overflows that happen when index key is composed (cherry picked from commit 337ca49)

dyemanov assigned dyemanov and unassigned dyemanov Feb 9, 2024

dyemanov assigned AlexPeshkoff Feb 9, 2024

dyemanov added component: engine type: bug affect-version: 6.0 Initial affect-version: 5.0.0 labels Feb 9, 2024

AlexPeshkoff added the affect-version: 4.0.4 label Feb 9, 2024

AlexPeshkoff changed the title ~~Unexpected results when using string concatenation for INTEGER~~ Unexpected results when comparing integer with string containing integer value out of bigint range Feb 9, 2024

AlexPeshkoff closed this as completed in 7531251 Feb 9, 2024

AlexPeshkoff added the fix-version: 6.0 Alpha 1 label Feb 9, 2024

AlexPeshkoff reopened this Feb 9, 2024

pavel-zotov added the qa: deferred label Feb 10, 2024

AlexPeshkoff changed the title ~~Unexpected results when comparing integer with string containing integer value out of bigint range~~ Unexpected results when comparing integer with string containing value out of range of that integer datatype Feb 14, 2024

AlexPeshkoff added a commit that referenced this issue Feb 14, 2024

Reworked fix for #7997: Unexpected results when comparing integer wit…

1192082

…h string containing value out of range of that integer datatype

AlexPeshkoff added a commit that referenced this issue Feb 16, 2024

Postfix for #7997: Unexpected results when comparing integer with str…

69d52d3

…ing containing value out of range of that integer datatype; fixed regression in test core_1274

suyZhong mentioned this issue Feb 21, 2024

Unexpected results when using newline in string #8014

Closed

AlexPeshkoff added a commit that referenced this issue Mar 5, 2024

Postfix for #7997: Unexpected results when comparing integer with str…

337ca49

…ing containing value out of range of that integer datatype; fixed overflows that happen when index key is composed

pavel-zotov added qa: done successfully and removed qa: deferred labels Mar 11, 2024

AlexPeshkoff added a commit that referenced this issue Mar 12, 2024

Fixed #7997: Unexpected results when comparing integer with string co…

3940641

…ntaining integer value out of bigint range (cherry picked from commit 7531251)

AlexPeshkoff added a commit that referenced this issue Mar 12, 2024

Reworked fix for #7997: Unexpected results when comparing integer wit…

2103369

…h string containing value out of range of that integer datatype (cherry picked from commit 1192082)

AlexPeshkoff added a commit that referenced this issue Mar 12, 2024

Postfix for #7997: Unexpected results when comparing integer with str…

171cb83

…ing containing value out of range of that integer datatype; fixed regression in test core_1274 (cherry picked from commit 69d52d3)

AlexPeshkoff added a commit that referenced this issue Mar 13, 2024

Fixed #7997: Unexpected results when comparing integer with string co…

9930033

…ntaining integer value out of bigint range (cherry picked from commit 7531251)

AlexPeshkoff added a commit that referenced this issue Mar 13, 2024

Reworked fix for #7997: Unexpected results when comparing integer wit…

c909897

…h string containing value out of range of that integer datatype (cherry picked from commit 1192082)

AlexPeshkoff added a commit that referenced this issue Mar 13, 2024

Postfix for #7997: Unexpected results when comparing integer with str…

990ffc5

…ing containing value out of range of that integer datatype; fixed regression in test core_1274 (cherry picked from commit 69d52d3)

AlexPeshkoff added fix-version: 4.0.5 fix-version: 5.0.1 labels Mar 13, 2024

AlexPeshkoff closed this as completed Mar 13, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

suyZhong commented Feb 9, 2024

dyemanov commented Feb 9, 2024

dyemanov commented Feb 9, 2024

AlexPeshkoff commented Feb 9, 2024

AlexPeshkoff commented Feb 9, 2024

pavel-zotov commented Feb 10, 2024

pavel-zotov commented Feb 10, 2024

AlexPeshkoff commented Feb 14, 2024 via email

pavel-zotov commented Feb 15, 2024

AlexPeshkoff commented Feb 15, 2024 via email

pavel-zotov commented Feb 15, 2024

AlexPeshkoff commented Feb 15, 2024 via email

pavel-zotov commented Feb 15, 2024

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

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

Comments

suyZhong commented Feb 9, 2024

dyemanov commented Feb 9, 2024

dyemanov commented Feb 9, 2024

AlexPeshkoff commented Feb 9, 2024

AlexPeshkoff commented Feb 9, 2024

pavel-zotov commented Feb 10, 2024

pavel-zotov commented Feb 10, 2024

AlexPeshkoff commented Feb 14, 2024 via email

pavel-zotov commented Feb 15, 2024

AlexPeshkoff commented Feb 15, 2024 via email

pavel-zotov commented Feb 15, 2024

AlexPeshkoff commented Feb 15, 2024 via email

pavel-zotov commented Feb 15, 2024