Subject RE: [IBO] problems with joins involving stored procedures
Author Claudio Valderrama C.
> -----Original Message-----
> From: gbien@... [mailto:gbien@...]
> Sent: Jueves 31 de Mayo de 2001 0:34
> Hi,
> Are there any known problems using joins involving
> stored procedures in IBO 3.6?? The following sql
> (and others) causes IBConsole (vers and
> ib_wisql to hang and the server to terminate
> abnormally. Whereas they work ok with some other tools.

This has NOTHING to do with IB_WISQL or IBConsole intrinsically. They only
trigger a bug in the server: either IB or FB will die immediately.

The reason is that both tools retrieve QUERY PLAN information. It's possible
to execute successfully some SQL queries if you don't ask the server how it
prepared the statement. However, as soon as you request plan information,
the server dies with a memory access error. (Of course, only some plans
fails or you would see the bug always.)

We at Firebird know how the bug happens, but fixing it is not
straightforward. The server generates a binary representation of the plan it
created (using byte code tags) and there's a routine that interprets such
info to convert it into human readable information, the "PLAN" line(s) you
see in IBConsole and IB_WISQL. The binary information is truncated hence the
routine that decodes it never finds the matching end (so it continues
advancing) and goes reading out of the input buffer limits, producing a
typical buffer overrun. Putting additional checks on the interpreter code
would stop the visible bug, but would hide the real cause and hence would
deliver an incomplete or wrong plan for the user that wants it. Sincerely,
since Borland implemented the "plan" thing, I would hope that they will take
a peek at it, assuming they are familiar with such code, because the binary
plan extraction is not necessarily a simple routine.

I see your other post but you didn't convey enough information. What's
needed to test:
- The query statement that leads to disaster.
- The full structure of your table CUSTOMER and your procedure AR_AGING.
- All tables used by your procedure! (Missing point actually.)
- Any index you could have defined manually on those tables by means of
Since most people in this list are concerned primarily about IBO, please
send the script to either Helen or me PRIVATELY, since it will be longer
than your previous attempt. Hope to hear from you soon.

Claudio Valderrama C.
Ingeniero en Informática - Consultor independiente -