Re: BUG #17450: SUBSTRING function extracting lesser characters than specified

пн, 28 мар. 2022 г. в 17:55, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Pavel Borisov <pashkin(dot)elfe(at)gmail(dot)com> writes:
> > Yes, the behavior expected in a report seems to be right. I've attached a
> > very small fix for that issue. Probably it should be backpatched into all
> > versions having 4bd3fad80e5c i.e. since v11.
>
> The current calculation matches the SQL standard; this makes
> it not match the standard. From SQL99:
>
> 3) If <character substring function> is specified, then:
>
> a) Let C be the value of the <character value expression>, let
> LC be the length of C, and let S be the value of the <start
> position>.
>
> b) If <string length> is specified, then let L be the value of
> <string length> and let E be S+L. Otherwise, let E be the
> larger of LC + 1 and S.
>
> c) If either C, S, or L is the null value, then the result of
> the <character substring function> is the null value.
>
> d) If E is less than S, then an exception condition is raised:
> data exception - substring error.
>
> e) Case:
>
> i) If S is greater than LC or if E is less than 1 (one), then
> the result of the <character substring function> is a
> zero-
> length string.
>
> ii) Otherwise,
>
> 1) Let S1 be the larger of S and 1 (one). Let E1 be the
> smaller of E and LC+1. Let L1 be E1-S1.
>
> 2) The result of the <character substring function> is
> a character string containing the L1 characters of C
> starting at character number S1 in the same order that
> the characters appear in C.
>
> Perhaps there's something to do here documentation-wise,
> but there is no bug.
>

Ok, thanks for clarification!

--
Best regards,
Pavel Borisov

Postgres Professional: http://postgrespro.com <http://www.postgrespro.com>