Re: BUG #17450: SUBSTRING function extracting lesser characters than specified - Mailing list pgsql-bugs

From Pavel Borisov
Subject Re: BUG #17450: SUBSTRING function extracting lesser characters than specified
Date
Msg-id CALT9ZEHN6a+55vQM4p2NMjj0NJWgUFtCBVicEDufw-Zf=vLoUw@mail.gmail.com
Whole thread Raw
In response to Re: BUG #17450: SUBSTRING function extracting lesser characters than specified  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
пн, 28 мар. 2022 г. в 17:55, Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Borisov <pashkin.elfe@gmail.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

pgsql-bugs by date:

Previous
From: Japin Li
Date:
Subject: Re: BUG #17450: SUBSTRING function extracting lesser characters than specified
Next
From: "David G. Johnston"
Date:
Subject: Re: BUG #17450: SUBSTRING function extracting lesser characters than specified