Обсуждение: substr negative indexes

Does the spec leave implementation of indexes on the substr() function
less than one undefined?  By mistake, I had a substr() invocation with
an initial index of zero (would be nice if all the computer languages of
the world could agree to a single definition.)  Oracle silently treats
this the same as 1 (one), while I just learned that PG treats indexes
less than 1 as a sliding window off the left of the string.  Oracle
defines negative indexes to mean "from the end of the string."

So, given a string "abcdefg":

Oracle substr('abcdefg', 1, 4) = 'abcd'
Oracle substr('abcdefg', 0, 4) = 'abcd'
Oracle substr('abcdefg', -1, 4) = 'd'
PG substr('abcdefg', 1, 4) = 'abcd'
PG substr('abcdefg', 0, 4) = 'abc'
PG substr('abcdefg', -1, 4) = 'ab'

--
Guy Rouillier

Guy Rouillier <guyr-ml1@burntmail.com> writes:
> Does the spec leave implementation of indexes on the substr() function
> less than one undefined?

SQL99 defines the result of

         <character substring function> ::=
              SUBSTRING <left paren> <character value expression> FROM <start position>
                          [ FOR <string length> ] <right paren>

as

            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.

and unless I'm mistaken, our behavior conforms to the spec and Oracle's
doesn't.

Strictly speaking, the spec doesn't define the behavior of "SUBSTR" at
all, only "SUBSTRING" with this weird FROM/FOR argument syntax.  But
PG treats SUBSTR(x,y,z), SUBSTRING(x,y,z) and SUBSTRING(x FROM y FOR z)
all the same.  Possibly Oracle conforms to spec for SUBSTRING but
their SUBSTR acts differently?

            regards, tom lane

Tom Lane wrote:
> and unless I'm mistaken, our behavior conforms to the spec and Oracle's
> doesn't.
>
> Strictly speaking, the spec doesn't define the behavior of "SUBSTR" at
> all, only "SUBSTRING" with this weird FROM/FOR argument syntax.  But
> PG treats SUBSTR(x,y,z), SUBSTRING(x,y,z) and SUBSTRING(x FROM y FOR z)
> all the same.  Possibly Oracle conforms to spec for SUBSTRING but
> their SUBSTR acts differently?

Thanks, Tom, I agree that PG's substr() appears to be following the spec
(thank goodness I don't have to read that whole thing ;).  Oracle does
not implement substring() at all (up through release 9.2, the latest I
have to work with.  Just checked the online documentation for 10g
Release 2 (latest available) and it doesn't have it either.  Says this
under conformance: "E021-06, SUBSTRING function: use SUBSTR function
instead". Sigh...

--
Guy Rouillier