Plan/Performance regression when using special construct for IN in FB5.x compared to FB3.x

[dpankov]

The following query results in full table scan instead of using the dedicated indexes on the two fields in the IN clause

SELECT e.*
FROM Employees e
WHERE :SomeID IN (e.LeaderID, e.DispEmpID)

which as expected leads to big degradation in the performance compared to FB3.x.
If we modify the query syntax to use
e.LeaderID = :SomeID OR e.DispEmpID = :SomeID
the plan changes to
PLAN (E INDEX (EMPLOYEESBYLEADERID, EMPLOYEESBYDISPEMPID))
and expectedly the performance is great in both versions of Firebird.

Here is а snippet of the table definition
`CREATE TABLE Employees(
EmpID BIGINT NOT NULL,

LeaderID BIGINT,
DispEmpID BIGINT,
....
CONSTRAINT PK_EmpID PRIMARY KEY (EmpID)
);

....
CREATE INDEX EmployeesByLeaderID ON Employees(LeaderID);
CREATE INDEX EmployeesByDispEmpID ON Employees(DispEmpID);
....`

[dyemanov]

Such usage was surely not considered in the new IN implementation. However, I will take a look whether it's possible to re-optimize this case.

[dpankov]

Great, thank you.
In the meantime is there by any chance a configuration option in FB5 that we can use to switch back to the old implementation?