Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1rhk2k-00D8Yu-U3
	for pgsql-docs@arkaria.postgresql.org; Wed, 06 Mar 2024 05:42:19 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1rhk2h-005fYy-Ky
	for pgsql-docs@arkaria.postgresql.org; Wed, 06 Mar 2024 05:42:16 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1rhk2h-005fYq-Dt
	for pgsql-docs@lists.postgresql.org; Wed, 06 Mar 2024 05:42:15 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by magus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1rhk2e-0031ql-DW
	for pgsql-docs@lists.postgresql.org; Wed, 06 Mar 2024 05:42:15 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 4265g9x7072912;
	Wed, 6 Mar 2024 00:42:09 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruce Momjian <bruce@momjian.us>
cc: dansonlinepresence@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: substring start position behavior
In-reply-to: <Zee2K8FeRyT4NY5O@momjian.us>
References: <170905442373.643.11536838320909376197@wrigleys.postgresql.org>
 <Zee2K8FeRyT4NY5O@momjian.us>
Comments: In-reply-to Bruce Momjian <bruce@momjian.us>
	message dated "Tue, 05 Mar 2024 19:17:47 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <72910.1709703729.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 06 Mar 2024 00:42:09 -0500
Message-ID: <72911.1709703729@sss.pgh.pa.us>
List-Id: <pgsql-docs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-docs@lists.postgresql.org>
List-Owner: <mailto:pgsql-docs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-docs>
Archived-At: 
 <https://www.postgresql.org/message-id/72911.1709703729%40sss.pgh.pa.us>
Precedence: bulk

Bruce Momjian <bruce@momjian.us> writes:
> This web page explains the feature:
> 	https://stackoverflow.com/questions/33462061/sql-server-substring-posit=
ion-negative-value
> but also asks:
> 	now the only question that remains is, "why would anyone need it
> 	to behave this way?"

Yeah.  I believe our implementation adheres to the SQL spec, which
says this for <character substring function> (in SQL:2021 6.3.2):

    a) If the character encoding form of <character value expression>
    is UTF8, UTF16, or UTF32, then, in the remainder of this General
    Rule, the term =E2=80=9Ccharacter=E2=80=9D shall be taken to mean =E2=80=
=9Cunit specified
    by <char length units>=E2=80=9D.

    b) Let C be the value of the <character value expression>, let LC
    be the length in characters of C, and let S be the value of the
    <start position>.

    c) 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.

    d) If at least one of C, S, and L is the null value, then the
    result of the <character substring function> is the null value.

    e) If E is less than S, then an exception condition is raised:
    data exception =E2=80=94 substring error (22011).  [tgl note: given c)=
,
    this happens if and only if a negative <string length> is provided.]

    f) 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 the
      zero-length character 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=E2=80=93S1.

        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.

That's a pretty sterling example of standards-ese that is both
unreadable and devoid of any justification.  But if you trace through
the possible effects of a negative S value, it looks like

  (1) if L >=3D 0 is specified and S+L (E) is less than one, the result
  is an empty string per rule f)i).

  (2) if L >=3D 0 is specified and S+L (E) is at least one but less than
  LC+1, then E is the substring end+1 position.

  (3) otherwise, a negative S is disregarded and replaced by 1 so
  far as the substring end calculation is concerned.

  (4) in any case, a negative S is disregarded and replaced by 1 so
  far as the substring start calculation is concerned.

I'm kind of inclined to not document this weirdness.  I especially
don't think it's worth giving an example that neither explains the
"disregarded" bit nor highlights the dependency on L being given.

			regards, tom lane