"Helen Borrie" <helebor@...> wrote in message
news:<4.2.0.58.20011129223149.01915990@...>...

> At 10:26 PM 29-11-01 +1100, I wrote:
>
>
> select substring('The magicians are assembled here' FROM 5 FOR 5) as

sub_5_5 from rdb$database

>
> returned
>
> SUB_5_5
> =======
> agici
>
>
> and this:
>
> select substring('The magicians are assembled here' FROM 1 FOR 12) as

sub_1_12 from rdb$database

>
> returned
>
> SUB_1_12
> ========
> he magicians
>
> Sorry, dear Claudio.

And I'm very sorry for you because you don't take time to read my messages.
I said clearly that this was a bug that was closed in October, right? You
simply are using a release that was made available before the fix was
compiled into the engine. Again, it's a DSQL layer's stupid problem that
doesn't decrement the first argument internally. When you use AS to create a
column title that should be really a true column alias in the SQL standard,
the DSQL layer branches to some if's that test a map. I still don't
understand what maps means here (subselects, aggregate functions, whatever
doesn't fit, etc.) but that branch wasn't covered. The solution was to
decrement the starting position immediately in the DSQL parser, so no rogue
alternative path will have a chance to get the unadjusted value. The reason
is that SQL is one-based but the engine is zero-based. A problem of
adjustment, same as the column position in the alter table command: in IB is
zero based; in FB is one based because there's no position zero in SQL.

SQL> select substring('The magicians are assembled here' FROM 5 FOR 5) as
sub_5_5 from rdb$database;

SUB_5_5
================================
magic

select substring('The magicians are assembled here' FROM 1 FOR 12) as
sub_1_12 from rdb$database;

SUB_1_12
================================
The magician

Go and get a newer version, you, too, Helen!
:-)

Anything comnpiled after mid August should be clean.

Hmm, I seem to detect subversion inside our own FB crew, what am I supposed
to do, bloody repression?
;-)

C.
---------
Claudio Valderrama C.
Ingeniero en Informática - Consultor independiente
http://www.cvalde.com - http://www.firebirdSQL.org