Subject The known limit (1499) in IN clause
Author partsi
Firebird does not support more than 1499 elements in an IN clause
list. Is there any effort to raise this limit? I understand that we
must try to avoid cases where the number of elements in an IN list is
big, but there are circumstances such as DSQL (e.g. using DSQL in a
c++ code) where we are faced with this limit. For example, an
application receives 2000 identifiers and it must perform a query to
get records identified by these identifiers.

So, what is the recommended workaround for this limit (e.g. in terms
of performance)?


A) Store IDs into a temporary table and use that table in an IN

FROM <table>
WHERE ID IN ( SELECT ID FROM <temporary_table> )

B) Execute multiple queries, each having maximally 1499 elements in
an IN list, and then combine the results.

FROM <table>
WHERE ID IN ( 1, 2, 3, ..., 1499 )

combined with

FROM <table>
WHERE ID IN ( 1500, 1501, 1502, ..., 2998 )

Other solutions?

Thanks in advance.