Re: BUG #15971: Behaviour of SUBSTR function depending on its arguments

On Wed, Aug 21, 2019 at 7:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Ashutosh Sharma <ashu.coek88@gmail.com> writes:
> > I don't think it's a bug. I think it's just that the SUBSTR in
> > PostgreSQL is more SQL compliant than any other databases that you
> > mentioned.  As per the SQL standard, if the start position is zero or
> > negative number, it should be adjusted to the start of the string and
> > not to the end of the string and that way I feel the behaviour of
> > SUBSTR in PostgreSQL is more SQL compliant than other databases.
>
> Yes, the standard provides no wiggle room here.  The behavior of
> substring() with integer parameters, as specified in SQL:2008
> 6.29 <string value function> general rule 3, is
>
> 3) If <character substring function> is specified, then:
>
>   a) If the character encoding form of <character value expression> is
>   UTF8, UTF16, or UTF32, then, in the remainder of this General Rule, the
>   term “character” shall be taken to mean “unit specified by <char length
>   units>”.
>
>   b) Let C be the value of the <character value expression>, let LC be the
>   length in characters of C, and let S be the value of the <start
>   position>.
>
>   c) 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.
>
>   d) If either C, S, or L is the null value, then the result of the
>   <character substring function> is the null value.
>
>   e) If E is less than S, then an exception condition is raised: data
>   exception — substring error.
>
>   f) 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.
>
> I believe our implementation does this exactly.
>
> Even if it were true that Oracle's behavior is more common than the
> spec's definition, it's quite unlikely that we could be talked into
> abandoning spec-compliant behavior to match Oracle.
>

Absolutely. We shouldn't do that. We are more accurate than Oracle as
per the SQL standard.

--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com