Hi,

there is a bug in Firebird 2 in handling the IN clause statement:

The following simple query takes about 45 seconds to complete. If I
add more than 100 Ids to the where clause the query is not finishing
within multiple hours. The tables are containing about 20000 rows. I
have tested this with Firebird 2.1 RC1 and Firebird 2.0.1.

SELECT "address".ID,
"address"."EnvironmentID",
"address"."eBayUserDBID" AS "EBayUserDBID",
"address"."SiteID",
"address"."Name",
"address"."Company",
"address"."Phone",
"address"."Street1",
"address"."Street2",
"address"."CityName",
"address"."StateOrProvince",
"address"."Country" AS "CountryBasevalue",
"address"."CountryName",
"address"."PostalCode",
"address"."AddressID",
"address"."ExternalAddressID",
"address"."AddressOwner" AS "AddressOwnerBasevalue",
"address"."AddressStatus" AS "AddressStatusBasevalue",
"address"."IntlName",
"address"."IntlStateAndCity",
"address"."IntlStreet",
"address"."ts" AS "Ts",
"address"."Salutation",
"address"."Firstname"
FROM "address"
WHERE
("address".ID IN (
SELECT "apOrders"."ShippingAddressID"
FROM "apOrders"
WHERE
("apOrders".ID IN (243, 244, 245, 246, 247, 248,
175, 176,
177, 178, 179, 180, 181, 182, 183, 184, 186, 187,
188, 189,
190, 191, 192, 193, 194, 195, 196, 197, 198, 199,
200, 201,
202, 203, 204, 205, 206, 207, 208, 209, 210, 211,
212, 213,
215, 216, 217, 218, 219, 220))
))


Here is some additional information form the IBExpert Performance
Analysis:

Plan
------------------------------------------------
PLAN (apOrders INDEX (RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513))
PLAN (address NATURAL)

Adapted Plan
------------------------------------------------
PLAN (apOrders INDEX (INTEG_654, INTEG_654, INTEG_654, INTEG_654,
INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654,
INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654,
INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654,
INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654,
INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654,
INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654,
INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654,
INTEG_654, INTEG_654, INTEG_654, INTEG_654))
PLAN (address NATURAL)

Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 42.516,00 ms
Avg fetch time: 6.073,71 ms

Memory
------------------------------------------------
Current: 34.918.376
Max : 35.133.556
Buffers: 2.048

Operations
------------------------------------------------
Read : 1.897
Writes : 0
Fetches: 36.208.955


Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes |
Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| address| 0 | 0 | 143681 | 0 | 0 | 0 |
| apOrders| 0 | 7182825 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+




I have reported this in the firebird bug tracker, but the answer was
not helpfull. The issue was closed, because it is a known issue. But I
couldn't find another issue regarding to that, so i don't know
anything about it.

http://tracker.firebirdsql.org/browse/CORE-1759

Is a fix for this issue planed?

Thanks,
Anton