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

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.
That's my opinion though, let's see what others have to say on it.

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

On Wed, Aug 21, 2019 at 12:33 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 15971
> Logged by: VIMAL VICTOR B
> Email address: bvimalvictor(at)gmail(dot)com
> PostgreSQL version: 10.4
> Operating system: Linux
> Description:
>
> Hi,
>
> select substr('abcdef',1,2) val1, substr('abcdef',0,2) val2,
> substr('abcdef',-1,2) val3;
>
> The above query in Postgres returns expected result for val1 ('ab') but for
> val2 and val3, it returns 'a' and '' respectively. Oracle and most of other
> RDBMS systems would return 'ab' for val1 and val2. When the start position
> is -ve, then the start position will be considered from end of input string.
> The start position can be considered either as 0 or 1 and substring from
> that position can be returned by considering the max length of the input
> string, which is logically correct. But when -ve value given for start
> position and returning empty string '', which does not seem logically
> correct. Kindly consider this case and let me know your thoughts.
>
> Regards,
> Vimal
>