Negative Offsets

csn

I was playing around with negative offsets:

select from table1 order by col1 offset -5 limit 25;
select from table1 order by col1 offset -25 limit 25;
select * from table1 order by col1 offset -250 limit 25;

They all return the same resultset (offset 0). Is
there even any point in allowing negative offsets -
such as maybe someday they'll offset backwards?

Sxooter

Ahhh, you've found yet another bit of insanity inherent in the SQL spec. First, I think it's 1 based, not 0. So, you'd use select substring('abcdef',1,2); to get ab. Now, my guess is that several databases extant at the time the spec was first made were written by accountants or cobol programmers, and they didn't get the whole 0 based and negative offset idea of a substring, and the only compromise that would allow all the current big names to work according to the spec was to create the current abortion of justice that is the definition of how substring works right now.

And, of course, now we're stuck with the damned thing. 🙂

csn

Yeah, one of many things that can trip you up.

But those pale in comparison to encodings, for me anyway. I'm mostly clueless there - I think I just create databases/tables in LATIN1, ANSI, or something and hope for the best. Haven't had any problems really, though one news feed I parse ends up with wacky characters when I get it back from the db and display it - not sure what exactly the problem is there. I recently read an article about i18n, l10n, Unicode, etc. by Joel Spolsky, and it turns out that all that is indeed largely a mess.

Roger_Ramjet

Well the manual sez
" The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements)."

So it looks like the negaitive offset is ignored anr the LIMIT n syntax assumed.

Be interesting to find out what happens with a prepared statement. and a negative offset!

Sxooter

Please note that this particular part of the SQL spec can cause brain damage... Here's a quote on how the standard substring function should work:

3) If <character substring function> is specified, then:
   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 "character" shall be taken to mean "unit specified by <char
       length units>".
   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 either C, S, or 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 -- substring error.
   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 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.

Now, isn't the answer to your question perfectly clear? :glare:

csn

Roger Ramjet wrote:
Well the manual sez
" The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements)."

So it looks like the negaitive offset is ignored anr the LIMIT n syntax assumed.

Be interesting to find out what happens with a prepared statement. and a negative offset!

IMO, then, the db should raise an error if the offset is negative.

csn

Sxooter wrote:

Please note that this particular part of the SQL spec can cause brain damage... Here's a quote on how the standard substring function should work:

3) If <character substring function> is specified, then:
   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 "character" shall be taken to mean "unit specified by <char
       length units>".
   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 either C, S, or 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 -- substring error.
   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 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.

Now, isn't the answer to your question perfectly clear? :glare:

Heh, that's like tax code!