Subject Re: [firebird-support] IN clause limitation
Author Milan Babuskov
G. Plante wrote:
> I have to generate queries with IN clause that looks like something
> like:
> 0000666','CUS-0000888', ... );
> My problem is the limitation of 1499 items of the IN clause.

I believe your problem is actually something else, i.e. bad report
generator. But, let's not get into that.

> The user (according to his security level) can access only 10 000
> customers.

You have a big problem here. Even it there were no 1499 limit on the IN
clause, there is 64k limit on the statement size. Since your
CUSTOMER_CODE is 11 characters, plus 3 for two ' and one comma (,) we
get to 14 characters per customer_code. This means that you can put
around 4600 codes before you run into statement size limit.

> Since computers now have a lot of memory and since computers are now
> faster than ever, why there is still a 1499 limitation in the IN
> clause?

To prevent bad design and force people to use the software in the way it
is meant to be used. I'm pretty sure that there isn't anyone out there
who will cherry-pick 5000 values for the IN clause - which means you
obtain them automatically - which also means you could rewrite the query
to use the same logic and do everything more efficiently.

The real limitation is not in Firebird, but in your reporting engine,
and that's where it has to be fixed. IMHO, of course.

Milan Babuskov