At 09:07 AM 14/09/2004 +0300, you wrote:

>Hi,
>I am using firebird 1.5.1
>i want to ask a question to firebird users.
>
>is there any limit about "IN" clause
>
>example
>======
>select field1, field2 from table1
>where
> (field1 in (param1, param2, param3.... paramN)) and
> (field2 in (Fparam1, Fparam2, Fparam3.... FparamN))

This isn't a valid use of parameters. The argument for the IN() predicate
has to be a list of constants. Either, you provide the constants or you
provide a subsquery that will return that list.

>how many params i can write to IN clause

None. IN() takes one argument, a list.

>is there any limit about IN clause

IN(<list of constants>) has a limit of 1024 list members.

IN(<scalar subquery>) is limited by the final length of the plan (when
expanded), which is 48K bytes. The number of values possible with "blowing
up" is therefore dependent on not just the size of the items returned in
the list but things like the lengths of index names, the amount of other
stuff in the query, and so for.

Generally, limit your use of IN() for searches predicated on a small set of
constants. IN(<subquery>) will be optimised to an EXISTS() predicate
anyway, so you might as well use this from the start, and save some
processing cycles.

>and how many characters can be sql expression length
>is there any limit about sql expression length

Not to an sql expression length, per se, but each SQL statement is limited
to 64K bytes and they must not cause a plan that exceeds 48 Kb. IN(<list
of constants>) does have a habit of causing large plans, since each item in
the list will generate its own separate usage of any index on the other
operand in the comparison. It resolves like this

WHERE AColumn in ('a', 'b', 'c', 'd', 'e', ...)
resolves to
WHERE AColumn = 'a' OR 'Acolumn = 'b' OR AColumn = 'c' OR ....

with a plan like
PLAN (ATable INDEX (RDB$PRIMARY7,RDB$PRIMARY7,RDB$PRIMARY7,RDB$PRIMARY7, ...))

./heLen