The following problem applies to Firebird 3.0. Cant test right now on
older versions.

Suppose we have a table called AnyTable with column ID as primary key.

The query

select * from AnyTable where ID = '123'

produce the following plan...

PLAN (AnyTable INDEX (PK_AnyTable))

Now a second query, semantically identical to the first...

select * from AnyTable where ID in ( '123' )

will produce the same plan:

PLAN (AnyTable INDEX (PK_AnyTable))

However that third query, also semantically identical to the first...

select * from AnyTable where ID in ( select '123' from RDB$DATABASE )

will produce a really bad plan:

PLAN (RDB$DATABASE NATURAL)
PLAN (AnyTable NATURAL)

If I try to force the primary key index, an error occurs:

SQL Message : -284
index  cannot be used in the specified plan

Are there any way to force index usage on a situation like that?

Ivan Cruz.