Query issue conversion error from string

[psryan]

We have a query that was working fine up until FB 4.0.2. Recently applied patch 4.0.4 and now the query fails with "conversion error from string 'xxx'". I have made a small repro case below except this also exhibits the odd behavior on 4.0.2. Work-arounds follow.

--drop table session$test;
--drop table staff$test;
create table session$test (sess_user char(63));
insert into session$test values ('TEST');
insert into session$test values ('1');
create table staff$test (staffid smallint, primary key (staffid));
insert into staff$test values (1);

select sess.sess_user, stf.staffid 
  from session$test sess
       left join rdb$database rdb
          on 1 = 1
       left join staff$test stf
          on trim(sess.sess_user) similar to '[0-9]+' 
         and stf.staffid = cast(trim(sess.sess_user) as smallint)
;

SQL Error [335544334] [22018]: conversion error from string "TEST" [SQLState:22018, ISC error code:335544334]

If you run the above you'll get conversion error from string "TEST". Seems to be related to the primary key index causing the parser to prioritize the focus on the 2nd predicate of the 3rd join which is casting the field to SMALLINT.
and stf.staffid = cast(trim(sess.sess_user) as smallint)

Interestingly, by commenting out the 2nd join, the query works fine:

select sess.sess_user, stf.staffid 
  from session$test sess
--       left join rdb$database rdb
--          on 1 = 1
       left join staff$test stf
          on trim(sess.sess_user) similar to '[0-9]+' 
         and stf.staffid = cast(trim(sess.sess_user) as smallint)
;

SESS_USER	STAFFID
--------------- ---------
TEST                 
1                      1

And also, preventing usage of the index (stf.staffid+0) also allows the query to work as expected:

select sess.sess_user, stf.staffid 
  from session$test sess
       left join rdb$database rdb
          on 1 = 1
       left join staff$test stf
          on trim(sess.sess_user) similar to '[0-9]+' 
         and stf.staffid+0 = cast(trim(sess.sess_user) as smallint)
;

SESS_USER	STAFFID
--------------- ---------
TEST                 
1                      1

[dyemanov]

I see the same error on v4.0.2, as well as on v3.0.11. Moreover, I'd say it could never work any different way, unless staffid wasn't indexed before.

Condition trim(sess.sess_user) similar to '[0-9]+' may be checked only after the record from staff$test is fetched. However, condition stf.staffid = cast(trim(sess.sess_user) as smallint) can (and should be) evaluated earlier to allow an index scan on field staffid. Thus order of condition evaluation is different from what your logic implies and it simply cannot work.

[psryan]

The original query was working up until 4.0.2, This attempt at a repro doesn't.

Another method to work around the issue is to use a single predicate rather than the two separate ones:

select sess.sess_user, stf.staffid 
  from session$test sess
       left join rdb$database rdb
          on 1 = 1
       left join staff$test stf
          on stf.staffid = case when trim(sess.sess_user) similar to '[0-9]+' then cast(trim(sess.sess_user) as smallint) end
;

[psryan]

I see the same error on v4.0.2, as well as on v3.0.11. Moreover, I'd say it could never work any different way, unless staffid wasn't indexed before.

Condition trim(sess.sess_user) similar to '[0-9]+' may be checked only after the record from staff$test is fetched. However, condition stf.staffid = cast(trim(sess.sess_user) as smallint) can (and should be) evaluated earlier to allow an index scan on field staffid. Thus order of condition evaluation is different from what your logic implies and it simply cannot work.

I would argue that the condition trim(sess.sess_user) similar to '[0-9]+' should negate the need to perform a fetch of staff$test if false. Hence the left join making it an optional result.

[psryan]

This is the actual real query that works on FB 4.0.2 and but fails to work on 4.0.4:

select sess.mon$attachment_id,
       sess.mon$remote_process,
       sess.mon$remote_address,
       sess.mon$remote_os_user,
       coalesce(stf.fullname, mon$user) as ice_user,
       sess.mon$timestamp,
       case mon$state when 0 then 'Idle' else 'Active' end as session_state,
       mem.mon$memory_allocated / 1024/1024 as memory_mb 
  from mon$attachments sess
       inner join mon$memory_usage mem
          on mem.mon$stat_id = sess.mon$stat_id
       left join systaff stf
          on trim(sess.mon$user) like '1_________'
         and stf.staffid = (1514872367 - (gen_id(g_staffbaseid,0) * 65536)) - cast(trim(sess.mon$user) as int)
 order by sess.mon$remote_process, stf.fullname, sess.mon$timestamp;

Table systaff has a primary key index on STAFFID here also.

Working explain plan 4.0.2:

Failing explain plan on 4.0.4:

Perhaps 4.0.2 had the issue and shouldn't have allowed it to work. I can work around the issue either way, thanks.

[dyemanov]

I see the same error on v4.0.2, as well as on v3.0.11. Moreover, I'd say it could never work any different way, unless staffid wasn't indexed before.
Condition trim(sess.sess_user) similar to '[0-9]+' may be checked only after the record from staff$test is fetched. However, condition stf.staffid = cast(trim(sess.sess_user) as smallint) can (and should be) evaluated earlier to allow an index scan on field staffid. Thus order of condition evaluation is different from what your logic implies and it simply cannot work.

I would argue that the condition trim(sess.sess_user) similar to '[0-9]+' should negate the need to perform a fetch of staff$test if false. Hence the left join making it an optional result.

Yes, you're right here, my answer wasn't fully correct. I need to investigate this further, maybe some improvement could be possible.

As for your original query, I suppose the v4.0.2 optimizer could not detect that expression stf.staffid = (1514872367 - (gen_id(g_staffbaseid,0) * 65536)) - cast(trim(sess.mon$user) as int) may actually use an index, while it became possible in v4.0.4. Thus you have this issue.