Subject Block size limit exceeded preparing query
Author Ken Coffman

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.

For our project we worked around it by doing the aggregates in 2 separate selects unioned, then selected from that derived table.
Strangely, that technique is not working for me on this much smaller example query.

Confirmed the issue on Firebird 2.5 as well as older versions.

Anyone know why this breaks?


Ken Coffman
Software Developer
Communicare Systems Pty Ltd