Oh crud.

UNION [ALL] defaults to char() -- not varchar() -- for literal strings which resulted in the
longest string being matched, while the rest were space-padded and therefore did not match; if
several of the strings were all the same, longest, length then those would be matched.

That just about fills my quota for stupidity this week. Sorry for the false alarm!

*shame*

-Philip

---------- Original Message -----------
From: "unordained" <unordained_00@...>
To: firebird-support@yahoogroups.com
Sent: Thu, 10 Jul 2008 15:57:35 -0500
Subject: [firebird-support] bug: FB 2.1.1 incorrect result of join to rdb$ tables

> 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
>

------- End of Original Message -------