Subject Re: [firebird-support] Block size limit exceeded preparing query
Author Ann Harrison

> On a recent project we hit an issue with one of our queries that was producing this error:
> "Implementation limit exceeded.
> block size exceeds implementation restriction."
> A bit of digging revealed this can be caused by queries / plans that are too large.
> However in our case we found we could cut it down to a simple query, yet it still kept happening - seemingly due to performing an aggregate function on a concatenated string with UDF call more than once in the query.
> Here's an example you can test, it's a nonsense query but highlights the issue:
> select mon$user
>     , min(mon$timestamp || lpad(mon$attachment_id, 8, '0')) compound_string_min
>     , max(mon$timestamp || lpad(mon$attachment_id, 8, '0')) compound_string_max
> from mon$attachments
> group by mon$user
> 1.  If you comment out either of the compound strings the query runs.
> 2.  If you remove the calls to lpad the query runs.

> Confirmed the issue on Firebird 2.5 as well as older versions.
> Anyone know why this breaks?

Well, sort of. The simple answer is there's a finite limit on the size
of the compile scratch block (formerly CSB, possibly something else
now). The question is why does this query hit that limit. That question
might better be asked on the dev list, or perhaps even submitted as
to the tracker.

Good luck,