FB (1.5.2) seems to have a problem with this query join structure.
If I run it in MS SQL, it returns about 750 records in just a few
seconds which is reasonable and expected for the data. If I run it
against the same data loaded into Firebird, it goes nuts. Tested it
in
IBAccess and it kept counting to well over 200K before I canceled it.

select S1.userid, S1.groupid, S.username
from S
inner join S S1 on
S1.userid=S.userid
where S.groupid='004'

The purpose is to get all the S records for a specific groupid,
and for those users, also get their S records in any other group.

S.userid is indexed.

Any ideas?