Query that uses index on numeric column may not found existing data if search criterion is a string that represents negative numbers

[pavel-zotov]

Consider script:

set bail on;
set list on;
shell del r:\temp\tmp4test.fdb 2>nul;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
 
create table t1(x char(2));
create table t2(x char(2));
insert into t1 values(1);
insert into t1 values(-2);
 
insert into t2 select * from t1;
commit;
 
create index t2x on t2(x);
 
select * from t1;
 
set plan on;
set count on;
set echo on;
 
--########## using full scan #######
 
select x as nr_case_1 from t1 where x = '1';
 
select x as nr_case_2 from t1 where x = '-2';
 
select x as nr_case_3 from t1 where x = -(-('1'));
 
select x as nr_case_4 from t1 where x = -('2');
 
--########### using index ##########
 
select x as ir_case_1 from t2 where x = '1';
 
select x as ir_case_2 from t2 where x = '-2';
 
select x as ir_case_3 from t2 where x = -(-('1'));
 
select x as ir_case_4 from t2 where x = -('2');

On all FB 3.x ... 6.x output will be:

1. for table T1 (which has no index):

select x as nr_case_1 from t1 where x = '1';
PLAN ("PUBLIC"."T1" NATURAL)
NR_CASE_1                       1 
Records affected: 1
 
select x as nr_case_2 from t1 where x = '-2';
PLAN ("PUBLIC"."T1" NATURAL)
NR_CASE_2                       -2
Records affected: 1
 
select x as nr_case_3 from t1 where x = -(-('1'));
PLAN ("PUBLIC"."T1" NATURAL)
NR_CASE_3                       1 
Records affected: 1
 
select x as nr_case_4 from t1 where x = -('2');
PLAN ("PUBLIC"."T1" NATURAL)
NR_CASE_4                       -2
Records affected: 1

(all expected)

2. for table T2 (which has index):

select x as ir_case_1 from t2 where x = '1';
PLAN ("PUBLIC"."T2" INDEX ("PUBLIC"."T2X"))
IR_CASE_1                       1 
Records affected: 1
 
select x as ir_case_2 from t2 where x = '-2';
PLAN ("PUBLIC"."T2" INDEX ("PUBLIC"."T2X"))
IR_CASE_2                       -2
Records affected: 1
 
select x as ir_case_3 from t2 where x = -(-('1'));
PLAN ("PUBLIC"."T2" INDEX ("PUBLIC"."T2X"))
Records affected: 0 -- <<< WHY ?
 
select x as ir_case_4 from t2 where x = -('2');
PLAN ("PUBLIC"."T2" INDEX ("PUBLIC"."T2X"))
Records affected: 0 -- <<< WHY ?

Results for case-3 & case-4 are empty.

[dyemanov]

I fail to see a "numeric column" mentioned in the issue title. X is declared as CHAR(2).

[pavel-zotov]

I fail to see a "numeric column" mentioned in the issue title. X is declared as CHAR(2).

Yes, sorry - the title is incorrect. Table has textual column with values '1' and '-2'. But the question is about diff. results for NR vs IR.

[dyemanov]

SQL> select -(-('1')) from t2;
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-expression evaluation not supported
-Strings cannot be negated (applied the minus operator) in dialect 3

So the question is whether the WHERE clause should work at all...

[mrotteveel]

Those -(-('1')) and -('2') should IMHO definitely result in an error in dialect 3.