This is more than a problem with the execution plan. FB (1.5.2) is
creating a cartesian product!

I set up a test table with your 3 columns and primary key (GroupID,
UserID). I added 5 users in a single group, then 2 users were added to
a second group. Running the query for users of the second group
resulted in 14 rows - in MSSQL it correctly produced 4 rows.

Removing the where clause produced 49 rows in FB and 11 in MSSQL.

Ooooohhhhhhhh ...

Dave

--- In firebird-support@yahoogroups.com, "lance8086" <lance8086@y...>
wrote:

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