pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17450: SUBSTRING function extracting lesser characters than specified
7+ messages / 6 participants
[nested] [flat]
* BUG #17450: SUBSTRING function extracting lesser characters than specified
@ 2022-03-28 10:30 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: PG Bug reporting form @ 2022-03-28 10:30 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: ganguly.04@gmail.com
The following bug has been logged on the website:
Bug reference: 17450
Logged by: Suman Ganguly
Email address: ganguly.04@gmail.com
PostgreSQL version: 10.17
Operating system: x86_64-pc-linux-gnu
Description:
select substring('123456', 0 , 5)
On running this, Postgres returns '1234'
Expecting '12345' to be returned as per the documentation
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17450: SUBSTRING function extracting lesser characters than specified
@ 2022-03-28 11:01 hubert depesz lubaczewski <depesz@depesz.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: hubert depesz lubaczewski @ 2022-03-28 11:01 UTC (permalink / raw)
To: ganguly.04@gmail.com; pgsql-bugs@lists.postgresql.org
On Mon, Mar 28, 2022 at 10:30:07AM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17450
> Logged by: Suman Ganguly
> Email address: ganguly.04@gmail.com
> PostgreSQL version: 10.17
> Operating system: x86_64-pc-linux-gnu
> Description:
>
> select substring('123456', 0 , 5)
> On running this, Postgres returns '1234'
> Expecting '12345' to be returned as per the documentation
Well, the problem is that you're trying to provide character number 0,
which is leading to bad results.
substring works on base-1 numbering.
depesz
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17450: SUBSTRING function extracting lesser characters than specified
@ 2022-03-28 12:35 Pavel Borisov <pashkin.elfe@gmail.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 3 replies; 7+ messages in thread
From: Pavel Borisov @ 2022-03-28 12:35 UTC (permalink / raw)
To: depesz@depesz.com, Tom Lane <tgl@sss.pgh.pa.us>; +Cc: ganguly.04@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
пн, 28 мар. 2022 г. в 15:01, hubert depesz lubaczewski <depesz@depesz.com>:
> On Mon, Mar 28, 2022 at 10:30:07AM +0000, PG Bug reporting form wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 17450
> > Logged by: Suman Ganguly
> > Email address: ganguly.04@gmail.com
> > PostgreSQL version: 10.17
> > Operating system: x86_64-pc-linux-gnu
> > Description:
> >
> > select substring('123456', 0 , 5)
> > On running this, Postgres returns '1234'
> > Expecting '12345' to be returned as per the documentation
>
> Well, the problem is that you're trying to provide character number 0,
> which is leading to bad results.
>
> substring works on base-1 numbering.
>
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.
--
Best regards,
Pavel Borisov
Postgres Professional: http://postgrespro.com <http://www.postgrespro.com;
Attachments:
0001-Fix-text_substring-for-correct-processing-of-zero-or.patch (application/octet-stream, 2.1K) download
From 2081902e0a722e8e4a0b8132aae380887d71d33e Mon Sep 17 00:00:00 2001
From: Pavel Borisov <pashkin.elfe@gmail.com>
Date: Mon, 28 Mar 2022 16:23:28 +0400
Subject: [PATCH] Fix text_substring for correct processing of zero or negative
start position
It is expected that zero or negative start position should default to
1, and string end to be caclulated accordingly. Probably this change is
a valid addition to recent 4bd3fad80e5c and is proposed to be
backpatched into all versions since v11 which is mentioned commit committed to.
---
src/backend/utils/adt/varlena.c | 4 ++--
src/test/regress/expected/strings.out | 8 ++++----
2 files changed, 6 insertions(+), 6 deletions(-)
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 22ab5a4329f..59ab32a0ad5 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -890,7 +890,7 @@ text_substring(Datum str, int32 start, int32 length, bool length_not_specified)
errmsg("negative substring length not allowed")));
L1 = -1; /* silence stupider compilers */
}
- else if (pg_add_s32_overflow(S, length, &E))
+ else if (pg_add_s32_overflow(S1, length, &E))
{
/*
* L could be large enough for S + L to overflow, in which case
@@ -953,7 +953,7 @@ text_substring(Datum str, int32 start, int32 length, bool length_not_specified)
errmsg("negative substring length not allowed")));
slice_size = L1 = -1; /* silence stupider compilers */
}
- else if (pg_add_s32_overflow(S, length, &E))
+ else if (pg_add_s32_overflow(S1, length, &E))
{
/*
* L could be large enough for S + L to overflow, in which case
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index 0f95b9400b6..afb119d0355 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -1810,10 +1810,10 @@ insert into toasttest values(repeat('1234567890',10000));
SELECT substr(f1, -1, 5) from toasttest;
substr
--------
- 123
- 123
- 123
- 123
+ 12345
+ 12345
+ 12345
+ 12345
(4 rows)
-- If the length is less than zero, an ERROR is thrown.
--
2.24.3 (Apple Git-128)
* Re: BUG #17450: SUBSTRING function extracting lesser characters than specified
@ 2022-03-28 13:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pavel Borisov <pashkin.elfe@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2022-03-28 13:55 UTC (permalink / raw)
To: Pavel Borisov <pashkin.elfe@gmail.com>; +Cc: depesz@depesz.com, ganguly.04@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17450: SUBSTRING function extracting lesser characters than specified
@ 2022-03-28 14:00 Japin Li <japinli@hotmail.com>
parent: Pavel Borisov <pashkin.elfe@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Japin Li @ 2022-03-28 14:00 UTC (permalink / raw)
To: Pavel Borisov <pashkin.elfe@gmail.com>; +Cc: depesz@depesz.com, Tom Lane <tgl@sss.pgh.pa.us>; ganguly.04@gmail.com; pgsql-bugs@lists.postgresql.org
On Mon, 28 Mar 2022 at 20:35, Pavel Borisov <pashkin.elfe@gmail.com> wrote:
> пн, 28 мар. 2022 г. в 15:01, hubert depesz lubaczewski <depesz@depesz.com>:
>
>> On Mon, Mar 28, 2022 at 10:30:07AM +0000, PG Bug reporting form wrote:
>> > The following bug has been logged on the website:
>> >
>> > Bug reference: 17450
>> > Logged by: Suman Ganguly
>> > Email address: ganguly.04@gmail.com
>> > PostgreSQL version: 10.17
>> > Operating system: x86_64-pc-linux-gnu
>> > Description:
>> >
>> > select substring('123456', 0 , 5)
>> > On running this, Postgres returns '1234'
>> > Expecting '12345' to be returned as per the documentation
>>
>> Well, the problem is that you're trying to provide character number 0,
>> which is leading to bad results.
>>
>> substring works on base-1 numbering.
>>
> 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.
It seems bytea_substring also has the problem.
inline patch 1: src/backend/utils/adt/varlena.c (+1/-1 lines) download inline-1.patch
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 22ab5a4329..a48f2fa2a7 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -3335,7 +3335,7 @@ bytea_substring(Datum str,
errmsg("negative substring length not allowed")));
L1 = -1; /* silence stupider compilers */
}
- else if (pg_add_s32_overflow(S, L, &E))
+ else if (pg_add_s32_overflow(S1, L, &E))
{
/*
* L could be large enough for S + L to overflow, in which case the
* Re: BUG #17450: SUBSTRING function extracting lesser characters than specified
@ 2022-03-28 14:00 Pavel Borisov <pashkin.elfe@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Pavel Borisov @ 2022-03-28 14:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: depesz@depesz.com, ganguly.04@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
пн, 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 <http://www.postgrespro.com;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17450: SUBSTRING function extracting lesser characters than specified
@ 2022-03-28 14:09 David G. Johnston <david.g.johnston@gmail.com>
parent: Pavel Borisov <pashkin.elfe@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2022-03-28 14:09 UTC (permalink / raw)
To: Pavel Borisov <pashkin.elfe@gmail.com>; +Cc: Hubert Lubaczewski <depesz@depesz.com>; Tom Lane <tgl@sss.pgh.pa.us>; ganguly.04@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, Mar 28, 2022 at 5:35 AM Pavel Borisov <pashkin.elfe@gmail.com>
wrote:
> пн, 28 мар. 2022 г. в 15:01, hubert depesz lubaczewski <depesz@depesz.com
> >:
>
>> On Mon, Mar 28, 2022 at 10:30:07AM +0000, PG Bug reporting form wrote:
>> > The following bug has been logged on the website:
>> >
>> > Bug reference: 17450
>> > Logged by: Suman Ganguly
>> > Email address: ganguly.04@gmail.com
>> > PostgreSQL version: 10.17
>> > Operating system: x86_64-pc-linux-gnu
>> > Description:
>> >
>> > select substring('123456', 0 , 5)
>> > On running this, Postgres returns '1234'
>> > Expecting '12345' to be returned as per the documentation
>>
>
You should reference the documentation you are basing your conclusion off
of for this kind of report.
This example in the documentation clearly demonstrates the 1-based nature
of the numbering:
substring('Thomas' from 2 for 3) → hom
as does this one:
substr('alphabet', 3, 2) → ph
https://www.postgresql.org/docs/current/functions-string.html
Oddly, I don't actually see a non-standard form of substring spelled that
way though indeed the example works.
Probably it should be backpatched into all versions having 4bd3fad80e5c
> i.e. since v11.
>
The behavior of the example command is identical both before and since v11
so I don't see how that commit has anything to do with this. Nor, as shown
above, does this contradict the documentation. The bug report is simply
wrong and you seem to have attempted to supply a fix without confirming it.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-03-28 14:09 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-28 10:30 BUG #17450: SUBSTRING function extracting lesser characters than specified PG Bug reporting form <noreply@postgresql.org>
2022-03-28 11:01 ` hubert depesz lubaczewski <depesz@depesz.com>
2022-03-28 12:35 ` Pavel Borisov <pashkin.elfe@gmail.com>
2022-03-28 13:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-03-28 14:00 ` Pavel Borisov <pashkin.elfe@gmail.com>
2022-03-28 14:00 ` Japin Li <japinli@hotmail.com>
2022-03-28 14:09 ` David G. Johnston <david.g.johnston@gmail.com>
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