Subject Re: [firebird-support] Firebird enters infinite? loop executing this query.
Author Andrew Guts
Helen Borrie wrote:
> At 09:40 PM 20/12/2006, you wrote:
> >Yes! Looks like an implicit hard-coded limitation or a bug. I've done
> >some experiments with that record and discovered this:
> >1) update cms_wiki set u_text = substring(text from 1 for 16383) where
> >id = 15; - works well.
> >2) update cms_wiki set u_text = substring(text from 1 for 16384) where
> >id = 15; - fails with the exception: "Cannot transliterate character
> >between character sets".
> >
> >This is the text fragment, which crosses 16384th byte:
> >"... In spite of simplicity, ...".
> >Character #16384 is a space (ASCII 32) between "of" and "simplicity".
> >
> >Did I miss something in the documentation? Or it has no relation to the
> >original problem, because of limitation of the SUBSTRING() itself?
> Substring indeed must be a legal varchar, i.e. not exceeding 32765
> ***bytes***. Your update is converting up to 16 384 single-byte
> characters to 2-byte characters. In UTF-8, this means that the
> transliteration will work up to and including character number 16 382
> and will overflow when it tries to transliterate the next input
> As to why your script is hanging on an overflow, I don't see an
> obvious reason for that. You are correctly getting an exception when
> running the statement interactively. What are you doing differently
> in your script? Or perhaps it is not an isql script you are using
> but some admin tool that is set to ignore exceptions..?

I am using isql.exe locally, feeding SQL-operators through the
clipboard. Of course, the script does not contain substring(), just
"update cms_wiki set u_text = text;" and it hangs eating resources.
Single-record operator "update cms_wiki set u_text = text where id =
15;" hangs also, without any exceptions, i.e. Firebird is eating CPU and
doing writes to HDD for a very long time (till I kill'em all to make the
computer usable again).
So I suspect some implicit limitation there, even without substring().