Subject Weird (non)results from select with long in-lists
Author Kjell Rilbe

Using FB 2.5 64 bit and FR Unicode 64 bit I try to select
records from a table "Företag" using a long in-list. I know there's a
limit to how long in-lists can be, so I split it like this:

select "Löpnr", "Orgnr"
from "Företag"
where "Löpnr" in (82578, 82804, 83517, .... /*100 entries*/)
or "Löpnr" in (1077252, 1079960, 1080436, .... /*100 entries*/)
or "Löpnr" in (1105724, 1107541, 1116134, .... /*100 entries*/)
or "Löpnr" in (1146750, 1151764, 1152146, .... /*100 entries*/)
or "Löpnr" in (1163992, 1165061, 1165068, .... /*100 entries*/)
or "Löpnr" in (1290017, 1297546, 1305078, .... /*71 entries*/)
order by "Löpnr";

I'm not sure this is a viable workaround, but at least I would expect to
get some reasonable error otherwise.

But what I do get is weird behavior...

FR doesn't accept user input during query execution, but does so during
record fetch.

When executing the query above FR "returns" instantly, but it the data
grid is empty - it doesn't even contain an empty result set - the grid
doesn't even contain the column headers. Using Sinática Monitor I can't
see the query anywhere, so FB seems to think it's finished.

Trying to retrieve the query plan causes FR to crash. Might be because
the plan is too large (ought to be roughly 10-15 kbyte). SQL size is
about 5 kbyte so should be ok.

I just noticed now, that it does return a recordset SOMETIMES - trying
execute/commit/execute/commit an a rapid sequence FR does actually
output a recordset now and again, but most often not.

What might be going on?


Kjell Rilbe
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64