Re: BUG #16804: substring() function returns "negative substring length" error when using a large length argument - Mailing list pgsql-bugs

From Pavel Stehule
Subject Re: BUG #16804: substring() function returns "negative substring length" error when using a large length argument
Date
Msg-id CAFj8pRC8VwazNN9G=oOt7E=kxpCwjE9pu8Hx0ku6-FJxAJYRRA@mail.gmail.com
Whole thread Raw
In response to BUG #16804: substring() function returns "negative substring length" error when using a large length argument  (PG Bug reporting form <noreply@postgresql.org>)
Responses Re: BUG #16804: substring() function returns "negative substring length" error when using a large length argument
List pgsql-bugs


po 4. 1. 2021 v 19:44 odesílatel PG Bug reporting form <noreply@postgresql.org> napsal:
The following bug has been logged on the website:

Bug reference:      16804
Logged by:          Rafi Shamim
Email address:      rafiss@gmail.com
PostgreSQL version: 13.1
Operating system:   MacOS 10.15.7
Description:       

Reproduction steps:

1. Connect to any PostgreSQL database.
2. Run this query:
> select substring('string' from 2 for 2147483646);

Actual result:

2021-01-04 12:43:13.145 EST [85734] ERROR:  negative substring length not
allowed
2021-01-04 12:43:13.145 EST [85734] STATEMENT:s
negative substring length not allowed


Expected result:

I don't know if this is covered by these docs:
https://www.postgresql.org/docs/13/functions-string.html
But I would expect one of the following:
1. There should be no error message, and the result should be 'tring'.
(Meaning it just goes to the end of the string.)
2. An error message that says that the length argument is too long.

I don't know what the SQL standard says, but when I try similar queries with
SQLite and MSSQL, the result is 'tring'.

Minimally this is a bug and it should raise an error "integer out of range". Probably in this case we can use MAX_INT as a special value of unlimited length, although it is a little bit scary, because length is an optional value. The attached patch should fix this issue. I do not have access to Oracle to check the behaviour of this case there.

Regards

Pavel

Attachment

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #16803: create a table only one text/varchar column, storage options toast_tuple_target doesn't work
Next
From: Jerry Sievert
Date:
Subject: Re: BUG #16804: substring() function returns "negative substring length" error when using a large length argument