From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pavel Borisov <pashkin.elfe@gmail.com>
Cc: depesz@depesz.com, ganguly.04@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17450: SUBSTRING function extracting lesser characters than specified
Date: Mon, 28 Mar 2022 09:55:21 -0400
Message-ID: <703080.1648475721@sss.pgh.pa.us> (raw)
In-Reply-To: <CALT9ZEE-kh8KufDLsohpvOw5rG9eMQXYfMTdE9dt-gawe1F+5g@mail.gmail.com>
References: <17450-69df48455c28fbea@postgresql.org>
<20220328110119.GA28018@depesz.com>
<CALT9ZEE-kh8KufDLsohpvOw5rG9eMQXYfMTdE9dt-gawe1F+5g@mail.gmail.com>
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.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, pashkin.elfe@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17450: SUBSTRING function extracting lesser characters than specified
In-Reply-To: <703080.1648475721@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox