At 01:59 AM 16/11/2004 +0000, you wrote:

>Hello,
>
>I have a problem with a query using the 'IN' operator.
>
>The query looks like this:
>
>Select ID, Name, Contact
>from Customer
>where ID in (1,2,3,4,5,6,7,8,..... etc)
>
>I am using the standard TIBQuery component in Delphi 7. I am modifying
>the SQL property according to the user selection. When you send lots
>of numbers, (about 160 values or so), you get an exception:
>
>"EIBClientError: Unknown Error - Can't retrieve plan"
>
>I understand this is probably a delphi bug because the query text runs
>perfectly in both iSQL and IBConsole, but I thought I would ask on the
>chance that someone may know if there are some limitations at play
>with the Interbase Components. Has anyone else come across this and if
>so, how did you get around the restriction.

Well, probably and IBX bug (or limitation) rather than a Delphi one. There
is an absolute limit of 1500 arguments an in() predicate when it takes
explicit arguments. Resolution looks like this:

Select ID, Name, Contact
from Customer
where ID = 1 OR id=2 OR id =3 OR...

and the plan looks like this:

PLAN (CUSTOMER INDEX
(RDB$PRIMARY7,RDB$PRIMARY7,RDB$PRIMARY7,RDB$PRIMARY7,....))

So, if you have 160 arguments, you have 159 OR clauses in the resolving
statement and 160 references in the plan to the same index.

EIBClientError isn't a database error, it's a custom exception resulting from
some internal check by IBX itself. IBX possibly has set some hard-coded
limit on the length (in bytes) of the PLAN statement that it can accept
from the server in response to the Prepare call. In Firebird, a PLAN can't
exceed 48Kb, but you're obviously not topping out on this if you're able to
run the query using non-IBX clients.

Maybe it is one of the zillions of bugs that came through in the open
source IBX, that never got fixed, or maybe it is a new limitation added to
work around some extra limitation that exists in the IB 6.5 or IB 7 server
or client. If you're using Firebird, there's simply no guarantee of
ongoing compatibility with InterBase-specific data access stuff.

All that said by way of possible explanation, it's not very cool to pass
huge sets of constants as arguments in IN() predicates. If you can find
another way to predicate this search, do so. With a Firebird IN( select
aValue from....) predicate, the underlying resolution is to an EXISTS()
predicate and there isn't a limit on the number of arguments.

./heLen