Windows, 32-bit, SuperServer, build "WI-V2.1.1.17910 Firebird 2.1", ODS 11.1, dialect 3, NTFS.

My experiments indicate that when joining to rdb$triggers and rdb$relations (at least), it matches
only a subset of the rows it should match. This happens with "WITH xxx as (...) ... join xxx
on ..." syntax and "... join (select ...) on ..." and also (my last test) even with true
tables "join zzz on ..." syntax. (I've been typing this email up as I run various tests, sorry.)

Examples:

with
things as (select trim(rdb$relation_name) thing_name from rdb$relations where rdb$relation_name
like 'BT%' escape '\'),
trans as (
select 'sale' sing, 'sales' plur from rdb$database union all
select 'pause' sing, 'pauses' plur from rdb$database
)
select sing, plur, thing_name
from trans
left join things on things.thing_name like '%' || upper(trans.sing) || '%' or things.thing_name
like '%' || upper(trans.plur) || '%'

-> finds 'pauses' but not 'sales'

with
things as (select trim(rdb$relation_name) thing_name from rdb$relations where rdb$relation_name
like 'BT%' escape '\'),
trans as (
select 'sale' sing, 'sales' plur from rdb$database
)
select sing, plur, thing_name
from trans
left join things on things.thing_name like '%' || upper(trans.sing) || '%' or things.thing_name
like '%' || upper(trans.plur) || '%'

-> finds 'sales'

with
things as (select trim(rdb$relation_name) thing_name from rdb$relations where rdb$relation_name
like 'BT%' escape '\'),
trans as (
select 'sale' sing, 'sales' plur from rdb$database
union all select 'deed' sing, 'deeds' plur from rdb$database
)
select sing, plur, thing_name
from trans
left join things on things.thing_name like '%' || upper(trans.sing) || '%' or things.thing_name
like '%' || upper(trans.plur) || '%'

-> finds both 'sales' and 'deeds'

with
things as (select trim(rdb$relation_name) thing_name from rdb$relations where rdb$relation_name
like 'BT%' escape '\'),
trans as (
select 'sale' sing, 'sales' plur from rdb$database
union all select 'deed' sing, 'deeds' plur from rdb$database
union all select 'abstract' sing, 'abstracts' plur from rdb$database
)
select sing, plur, thing_name
from trans
left join things on things.thing_name like '%' || upper(trans.sing) || '%' or things.thing_name
like '%' || upper(trans.plur) || '%'

PLAN JOIN ((TRANS RDB$DATABASE NATURAL)
PLAN (TRANS RDB$DATABASE NATURAL)
PLAN (TRANS RDB$DATABASE NATURAL), THINGS RDB$RELATIONS INDEX (RDB$INDEX_0))

-> finds 'abstracts' only, not 'sales' or 'deeds'

I would suspect that it's somehow related to the alphabetic order of the names in the unions, but I
managed to find an example where that wasn't the case:

with
things as (select trim(rdb$relation_name) thing_name from rdb$relations where rdb$relation_name
like 'BT%' escape '\'),
trans as (
select 'deed' sing, 'deeds' plur from rdb$database
union all select 'hearing' sing, 'hearings' plur from rdb$database
union all select 'document' sing, 'documents' plur from rdb$database
)
select sing, plur, thing_name
from trans
left join things on things.thing_name like '%' || upper(trans.sing) || '%' or things.thing_name
like '%' || upper(trans.plur) || '%'

-> finds 'documents' but not 'deeds' or 'hearings'

It's very consistent about which rows it returns for a given query, I just can't figure out what
rule it's applying. Since the PLAN is NATURAL, maybe it's the order of the tables or triggers in
their respective rdb$ tables? But then I also can't determine when it matches only one entry, and
when it matches several (but not always all, even then).

Haven't tried any other versions of FB yet.


create table test_tx (sing varchar(50), plur varchar(50));
insert into test_tx (sing, plur) select 'deed' sing, 'deeds' plur from rdb$database
union all select 'hearing' sing, 'hearings' plur from rdb$database
union all select 'document' sing, 'documents' plur from rdb$database

Further testing reveals that this happens even if you're joining from a real table!

create table test_tx (sing varchar(50), plur varchar(50));
insert into test_tx (sing, plur)
select 'deed' sing, 'deeds' plur from rdb$database
union all select 'hearing' sing, 'hearings' plur from rdb$database
union all select 'document' sing, 'documents' plur from rdb$database;

with
things as (select trim(rdb$relation_name) thing_name from rdb$relations where rdb$relation_name
like 'BT%' escape '\'),
trans as (select sing, plur from test_tx)
select sing, plur, thing_name
from trans
left join things on things.thing_name like '%' || upper(trans.sing) || '%' or things.thing_name
like '%' || upper(trans.plur) || '%'

-> again finds just 'documents' but not 'deeds' or 'hearings'

delete from test_tx where sing = 'document';
delete from test_tx where sing = 'deed';

-> now matches nothing! ('hearings' remains, is not matched)

insert into test_tx (sing, plur) values ('abstract', 'abstracts');

-> now matches 'abstracts', but still not 'hearings'

-Philip