Plan/Performance regression when using special construct for IN in FB5.x compared to FB3.x #8109
Description
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);
....`