Strange concatenation result

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

Strange concatenation result

Gary Briggs
Evening

I'm seeing a weird effect when concatenting things:
WITH q(tape,dp) AS (SELECT '04E', 1)
  SELECT SUBSTR(tape,1,dp-1) || SUBSTR(tape,dp,1)+1 || SUBSTR(tape,dp+1) AS expect_14E,
         SUBSTR(tape,1,dp-1) AS segment_1,
         SUBSTR(tape,dp,1)+1 AS segment_2,
         SUBSTR(tape,dp+1) AS segment_3
    FROM q;

expect_14E  segment_1   segment_2   segment_3
----------  ----------  ----------  ----------
14                      1           4E        

As the name of the column implies... I'm expecting '14E' from that
concatenation, it instead seems to be dropping the last character.

I originally found this in sqlite3 3.19.3 as distributed in 64-bit ubuntu
17.10; on that same system, I get the same effect in the amalgamation
3.22.0, compiled just now, thus:

sqlite-amalgamation-3220000$ gcc -o sqlite3 sqlite3.c shell.c -ldl -lpthread

For entertainment purposes only, I discovered this while trying to
implement a BF interpreter using CTEs, pasted below.

Have a good evening,
Gary


WITH RECURSIVE
  program AS (SELECT '+++.,>++++.,<<++++++.,' AS p, 'HELLO' AS input),
  jumpdepth AS (SELECT 0 AS idx, 0 AS jumpdepth, '' AS jumplist, NULL as jumpback, NULL AS direction, p || '0' AS p FROM program
          UNION ALL
        SELECT idx+1,
             CASE SUBSTR(p, idx+1, 1) WHEN '[' THEN jumpdepth+1 WHEN ']' THEN jumpdepth-1 ELSE jumpdepth END,
             CASE SUBSTR(p, idx+1, 1) WHEN '[' THEN SUBSTR('0000' || (idx+1), -4) || jumplist WHEN ']' THEN SUBSTR(jumplist,5) ELSE jumplist END,
             CASE SUBSTR(p, idx+1, 1) WHEN ']' THEN CAST(SUBSTR(jumplist,1,4) AS INTEGER) ELSE NULL END,
             CASE SUBSTR(p, idx+1, 1) WHEN '[' THEN 'L' WHEN ']' THEN 'R' ELSE NULL END, p
          FROM jumpdepth
          WHERE LENGTH(p)>=idx),
  jumptable(a,b,dir) AS
       (SELECT idx,jumpback,'L' FROM jumpdepth WHERE jumpback IS NOT NULL
          UNION ALL
        SELECT jumpback,idx+1,'R' FROM jumpdepth WHERE jumpback IS NOT NULL),
  bf AS (SELECT p, 1 AS ip, 1 AS dp, '' AS output, input, CAST('0' AS TEXT) AS tape
           FROM program
        UNION ALL
        SELECT p, CASE WHEN jumptable.b IS NOT NULL AND
                ((dir='L' AND SUBSTR(tape, dp, 1)=0) OR (dir='R' AND SUBSTR(tape,dp,1)!=0)) THEN jumptable.b ELSE ip+1 END,
        CASE SUBSTR(p, ip, 1) WHEN '>' THEN dp+1 WHEN '<' THEN MAX(dp-1,1) ELSE dp END,
        CASE WHEN SUBSTR(p, ip, 1)='.' THEN (output || SUBSTR(tape, dp, 1)) ELSE output END,
        CASE WHEN SUBSTR(p, ip, 1)=',' THEN SUBSTR(input, 2) ELSE input END,
        CASE SUBSTR(p, ip, 1)
                WHEN '<' THEN CASE WHEN dp=1 THEN '0' || tape ELSE tape END
                WHEN '>' THEN CASE WHEN dp=LENGTH(tape) THEN tape || '0' ELSE tape END
                WHEN '+' THEN SUBSTR(tape,1,dp-1) || SUBSTR(tape,dp,1)+1 || SUBSTR(tape,dp+1)
                WHEN '-' THEN SUBSTR(tape,1,dp-1) || SUBSTR(tape,dp,1)-1 || SUBSTR(tape,dp+1)
                WHEN ',' THEN SUBSTR(tape,1,dp-1) || SUBSTR(input,1,1) || SUBSTR(tape,dp+1)
                ELSE tape END
  FROM bf LEFT JOIN jumptable ON jumptable.a=ip WHERE LENGTH(p) >= ip)
SELECT ip,dp,input,output,tape FROM bf LIMIT 1000;



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Strange concatenation result

Igor Tandetnik-2
On 2/26/2018 12:23 AM, Gary Briggs wrote:
> Evening
>
> I'm seeing a weird effect when concatenting things:
> WITH q(tape,dp) AS (SELECT '04E', 1)
>    SELECT SUBSTR(tape,1,dp-1) || SUBSTR(tape,dp,1)+1 || SUBSTR(tape,dp+1) AS expect_14E,

|| has higher precedence than +. Your expression is an arithmetic sum of two values:

SUBSTR(tape,1,dp-1) || SUBSTR(tape,dp,1)  -- 0
+
1 || SUBSTR(tape,dp+1)  -- '14E', converted to integer 14

--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Strange concatenation result

Gary Briggs
On Mon, Feb 26, 2018 at 12:33:29AM -0500, Igor Tandetnik wrote:

> On 2/26/2018 12:23 AM, Gary Briggs wrote:
> >Evening
> >
> >I'm seeing a weird effect when concatenting things:
> >WITH q(tape,dp) AS (SELECT '04E', 1)
> >   SELECT SUBSTR(tape,1,dp-1) || SUBSTR(tape,dp,1)+1 || SUBSTR(tape,dp+1) AS expect_14E,
>
> || has higher precedence than +. Your expression is an arithmetic sum of two values:
>
> SUBSTR(tape,1,dp-1) || SUBSTR(tape,dp,1)  -- 0
> +
> 1 || SUBSTR(tape,dp+1)  -- '14E', converted to integer 14

Ah, that makes sense. Thanks!
Gary
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Strange concatenation result

Jean-Luc Hainaut
In reply to this post by Gary Briggs

About the "substr(X,Y,Z)" function, I observe a strange behaviour when Y
= 0.

If I execute this script:

select 'abcd',substr('abcd',0,1),substr('abcd',1,1),substr('abcd',2,1);
select 'abcd',substr('abcd',0,2),substr('abcd',1,2),substr('abcd',2,2);
select 'abcd',substr('abcd',0,9),substr('abcd',1,9),substr('abcd',2,9);
select 'abcd',substr('abcd',0),substr('abcd',1),substr('abcd',2);

It prints:

+--------+--------------------+--------------------+--------------------+
| 'abcd' | substr('abcd',0,1) | substr('abcd',1,1) | substr('abcd',2,1) |
+--------+--------------------+--------------------+--------------------+
| abcd   |                    | a                  | b                  |
+--------+--------------------+--------------------+--------------------+
+--------+--------------------+--------------------+--------------------+
| 'abcd' | substr('abcd',0,2) | substr('abcd',1,2) | substr('abcd',2,2) |
+--------+--------------------+--------------------+--------------------+
| abcd   | a                  | ab                 | bc                 |
+--------+--------------------+--------------------+--------------------+
+--------+--------------------+--------------------+--------------------+
| 'abcd' | substr('abcd',0,9) | substr('abcd',1,9) | substr('abcd',2,9) |
+--------+--------------------+--------------------+--------------------+
| abcd   | abcd               | abcd               | bcd                |
+--------+--------------------+--------------------+--------------------+
+--------+------------------+------------------+------------------+
| 'abcd' | substr('abcd',0) | substr('abcd',1) | substr('abcd',2) |
+--------+------------------+------------------+------------------+
| abcd   | abcd             | abcd             | bcd              |
+--------+------------------+------------------+------------------+

It seems that Y=0 denotes a fictitious empty position before the first
one (Y=1).Is it the intended behaviour?

The documentation (https://www.sqlite.org/lang_corefunc.html#substr),
says nothing about this specific pattern.

J-L Hainaut


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Strange concatenation result

Hick Gunter
"The left-most character of X is number 1. If Y is negative then the first character of the substring is found by counting from the right rather than the left."

The substr(x,y,z) function is defined only for nonzero values of y. SQlite can return whatever it feels like if you insist on providing invalid input. With "being nice to the user" and "making a best effort to return sensible data even for nonsense input" as design goals, mapping substr(x,0,z) to substr(x,1,z-1) seems quite a benign solution.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Jean-Luc Hainaut
Gesendet: Montag, 26. Februar 2018 10:53
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] Strange concatenation result


About the "substr(X,Y,Z)" function, I observe a strange behaviour when Y = 0.

If I execute this script:

select 'abcd',substr('abcd',0,1),substr('abcd',1,1),substr('abcd',2,1);
select 'abcd',substr('abcd',0,2),substr('abcd',1,2),substr('abcd',2,2);
select 'abcd',substr('abcd',0,9),substr('abcd',1,9),substr('abcd',2,9);
select 'abcd',substr('abcd',0),substr('abcd',1),substr('abcd',2);

It prints:

+--------+--------------------+--------------------+--------------------+
| 'abcd' | substr('abcd',0,1) | substr('abcd',1,1) | substr('abcd',2,1)
| |
+--------+--------------------+--------------------+--------------------+
| abcd   |                    | a                  | b                  |
+--------+--------------------+--------------------+--------------------+
+--------+--------------------+--------------------+--------------------+
| 'abcd' | substr('abcd',0,2) | substr('abcd',1,2) | substr('abcd',2,2)
| |
+--------+--------------------+--------------------+--------------------+
| abcd   | a                  | ab                 | bc                 |
+--------+--------------------+--------------------+--------------------+
+--------+--------------------+--------------------+--------------------+
| 'abcd' | substr('abcd',0,9) | substr('abcd',1,9) | substr('abcd',2,9)
| |
+--------+--------------------+--------------------+--------------------+
| abcd   | abcd               | abcd               | bcd                |
+--------+--------------------+--------------------+--------------------+
+--------+------------------+------------------+------------------+
| 'abcd' | substr('abcd',0) | substr('abcd',1) | substr('abcd',2) |
+--------+------------------+------------------+------------------+
| abcd   | abcd             | abcd             | bcd              |
+--------+------------------+------------------+------------------+

It seems that Y=0 denotes a fictitious empty position before the first one (Y=1).Is it the intended behaviour?

The documentation (https://www.sqlite.org/lang_corefunc.html#substr),
says nothing about this specific pattern.

J-L Hainaut


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Strange concatenation result

Cezary H. Noweta
In reply to this post by Jean-Luc Hainaut
Hello,
> It seems that Y=0 denotes a fictitious empty position before the first
> one (Y=1).Is it the intended behaviour?

> The documentation (https://www.sqlite.org/lang_corefunc.html#substr),
> says nothing about this specific pattern.

Even if it not intended, it will be very handy in some circumstances.
Treating 0 as a non-existing position is more flexible behavior then
generating errors, exceptions, roll--backs or a nuclear launch. Such
behavior saves (or can save at least) time and code space on both sides:
on the SQLite's side (eliminates checking against 0 and an effort
related to it) and on your code's side. Additionally, the behavior is
consistent and predictable -- it gives advantages only.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Strange concatenation result

Cezary H. Noweta
In reply to this post by Hick Gunter
Hello,

On 2018-02-26 11:38, Hick Gunter wrote:
> The substr(x,y,z) function is defined only for nonzero values of y. SQlite can return whatever it feels like if you insist on providing invalid input. With "being nice to the user" and "making a best effort to return sensible data even for nonsense input" as design goals, mapping substr(x,0,z) to substr(x,1,z-1) seems quite a benign solution.

... and as such, that design could be documented.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Strange concatenation result

Jean-Luc Hainaut
On 26/02/2018 12:19, Cezary H. Noweta wrote:

> Hello,
>
> On 2018-02-26 11:38, Hick Gunter wrote:
>> The substr(x,y,z) function is defined only for nonzero values of y.
>> SQlite can return whatever it feels like if you insist on providing
>> invalid input. With "being nice to the user" and "making a best
>> effort to return sensible data even for nonsense input" as design
>> goals, mapping substr(x,0,z) to substr(x,1,z-1) seems quite a benign
>> solution.
>
> ... and as such, that design could be documented.

I have been using SQLite for several years but I didn't know this
feature. I quite agree with your answers.

Thanks to both of you.

Just a personal comment: if we consider that the query, with Y=0, has no
legitimate answer (an empty string IS a legitimate answer), returning
"null" could also be a "user-friendly" answer, perhaps better at
alerting the user of the use of an invalid parameter.

J-L Hainaut

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Strange concatenation result

curmudgeon
There's nothing special about Y=0. The Y can be anywhere outwith the string.

e.g.

substr('abc', 6, -4) = 'bc'

substr('abc', -5, 3) = 'a'

All substr functions should work this way. I wrote a c++ function to emulate
it.

String substr(const String &Str, int Start, int Len)
{
        if (Str=="" || !Len) return "";
        String S;
        int StrLen = Str.Length();
        if (Start < 0) Start = StrLen + Start + 1;
        if (Len < 0) {Start += Len; Len = -Len;}
        for (int i = std::max(1, Start); i <= StrLen && i < Start+Len; i++) S +=
Str[i];
        return S;
}

// String is a windows wide string type

// I wrote it a while ago so it could probably be done gooder :-)




--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Strange concatenation result

Hick Gunter
Just to make things clear, I am not on the SQLite dev team, so I have no influence on the documentation. I am just extrapolating from experience.

What should substr('abcd',0,-2) return? 'cd' or just 'd'? Or maybe just an empty string?

If you start numbering at the left with 1, 0 is just left of the string. If you start numbering at the right with -1, 0 is just right of the string.

Or do you take on the stance of historians: There is no year 0, there is just 1BC immediately followed by 1AD?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von curmudgeon
Gesendet: Montag, 26. Februar 2018 19:35
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] Strange concatenation result

There's nothing special about Y=0. The Y can be anywhere outwith the string.

e.g.

substr('abc', 6, -4) = 'bc'

substr('abc', -5, 3) = 'a'

All substr functions should work this way. I wrote a c++ function to emulate it.

String substr(const String &Str, int Start, int Len) {
        if (Str=="" || !Len) return "";
        String S;
        int StrLen = Str.Length();
        if (Start < 0) Start = StrLen + Start + 1;
        if (Len < 0) {Start += Len; Len = -Len;}
        for (int i = std::max(1, Start); i <= StrLen && i < Start+Len; i++) S += Str[i];
        return S;
}

// String is a windows wide string type

// I wrote it a while ago so it could probably be done gooder :-)




--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Strange concatenation result

Simon Slavin-3


On 27 Feb 2018, at 7:01am, Hick Gunter <[hidden email]> wrote:

> What should substr('abcd',0,-2) return? 'cd' or just 'd'? Or maybe just an empty string?

NULL

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Strange concatenation result

Cezary H. Noweta
Hello,

On 2018-02-27 08:46, Simon Slavin wrote:
>> What should substr('abcd',0,-2) return? 'cd' or just 'd'? Or maybe just an empty string?

> NULL

Why?

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Strange concatenation result

Jean-Luc Hainaut

Let me suggest an interpretation that seems to comply with the current
implementation of "substr".

1. String X is stored in a (ficticious) infinite array, the cells of
which are indexed -*, ..., -2, -1, 0, 1, 2,.., +*.
2. String X is stored from cell 1 upward.
3. String 'abcd' is stored in cells [1,4]. Cells [-*,0] and [5,+*] are
empty.
4. Parameters X and Y specify a slice of the array.
5. Parameter Y, as described in the documentation, denotes any cell of
the array, even if it doesn't contain a character of X.
6. Parameter Z, as described in the documentation, denotes any slice of
the array, that may (but need not) include characters of X.
7. Function "substr" returns the contents of the non empty cells of this
slice.

Some examples:

select substr('abcd',1,2);   --> slice [1,2]
select substr('abcd',0,2);   --> slice [0,2]
select substr('abcd',0,-2);  --> slice [-2,-1]
select substr('abcd',5,-3);  --> slice [2,4]
select substr('abcd',5,2);   --> slice [5,6]
select substr('abcd',-3,3);  --> slice [2,4]
select substr('abcd',-4,3);  --> slice [1,3]
select substr('abcd',-5,3);  --> slice [0,2]
select substr('abcd',-6,3);  --> slice [-1,1]
select substr('abcd',-7,3);  --> slice [-2,0]
select substr('abcd',2,0);   --> empty slice
select substr('abcd',-5,0);  --> empty slice

+---------------------+
| substr('abcd',1,2)  |
+---------------------+
| ab                  |
+---------------------+
+---------------------+
| substr('abcd',0,2)  |
+---------------------+
| a                   |
+---------------------+
+---------------------+
| substr('abcd',0,-2) |
+---------------------+
|                     |
+---------------------+
+---------------------+
| substr('abcd',5,-3) |
+---------------------+
| bcd                 |
+---------------------+
+---------------------+
| substr('abcd',5,2)  |
+---------------------+
|                     |
+---------------------+
+---------------------+
| substr('abcd',-3,3) |
+---------------------+
| bcd                 |
+---------------------+
+---------------------+
| substr('abcd',-4,3) |
+---------------------+
| abc                 |
+---------------------+
+---------------------+
| substr('abcd',-5,3) |
+---------------------+
| ab                  |
+---------------------+
+---------------------+
| substr('abcd',-6,3) |
+---------------------+
| a                   |
+---------------------+
+---------------------+
| substr('abcd',-7,3) |
+---------------------+
|                     |
+---------------------+
+---------------------+
| substr('abcd',2,0)  |
+---------------------+
|                     |
+---------------------+
+---------------------+
| substr('abcd',-5,0) |
+---------------------+
|                     |
+---------------------+

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Strange concatenation result

curmudgeon
That’s the way I see it Jean-Luc.

From: Jean-Luc Hainaut<mailto:[hidden email]>
Sent: 27 February 2018 09:56
To: SQLite mailing list<mailto:[hidden email]>
Subject: Re: [sqlite] Strange concatenation result


Let me suggest an interpretation that seems to comply with the current
implementation of "substr".

1. String X is stored in a (ficticious) infinite array, the cells of
which are indexed -*, ..., -2, -1, 0, 1, 2,.., +*.
2. String X is stored from cell 1 upward.
3. String 'abcd' is stored in cells [1,4]. Cells [-*,0] and [5,+*] are
empty.
4. Parameters X and Y specify a slice of the array.
5. Parameter Y, as described in the documentation, denotes any cell of
the array, even if it doesn't contain a character of X.
6. Parameter Z, as described in the documentation, denotes any slice of
the array, that may (but need not) include characters of X.
7. Function "substr" returns the contents of the non empty cells of this
slice.

Some examples:

select substr('abcd',1,2);   --> slice [1,2]
select substr('abcd',0,2);   --> slice [0,2]
select substr('abcd',0,-2);  --> slice [-2,-1]
select substr('abcd',5,-3);  --> slice [2,4]
select substr('abcd',5,2);   --> slice [5,6]
select substr('abcd',-3,3);  --> slice [2,4]
select substr('abcd',-4,3);  --> slice [1,3]
select substr('abcd',-5,3);  --> slice [0,2]
select substr('abcd',-6,3);  --> slice [-1,1]
select substr('abcd',-7,3);  --> slice [-2,0]
select substr('abcd',2,0);   --> empty slice
select substr('abcd',-5,0);  --> empty slice

+---------------------+
| substr('abcd',1,2)  |
+---------------------+
| ab                  |
+---------------------+
+---------------------+
| substr('abcd',0,2)  |
+---------------------+
| a                   |
+---------------------+
+---------------------+
| substr('abcd',0,-2) |
+---------------------+
|                     |
+---------------------+
+---------------------+
| substr('abcd',5,-3) |
+---------------------+
| bcd                 |
+---------------------+
+---------------------+
| substr('abcd',5,2)  |
+---------------------+
|                     |
+---------------------+
+---------------------+
| substr('abcd',-3,3) |
+---------------------+
| bcd                 |
+---------------------+
+---------------------+
| substr('abcd',-4,3) |
+---------------------+
| abc                 |
+---------------------+
+---------------------+
| substr('abcd',-5,3) |
+---------------------+
| ab                  |
+---------------------+
+---------------------+
| substr('abcd',-6,3) |
+---------------------+
| a                   |
+---------------------+
+---------------------+
| substr('abcd',-7,3) |
+---------------------+
|                     |
+---------------------+
+---------------------+
| substr('abcd',2,0)  |
+---------------------+
|                     |
+---------------------+
+---------------------+
| substr('abcd',-5,0) |
+---------------------+
|                     |
+---------------------+

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Strange concatenation result

Bart Smissaert
In reply to this post by Simon Slavin-3
If anything I would prefer it to return an empty string as returning Null
could mess up the detection of the column data type.

RBS



On Tue, Feb 27, 2018 at 7:46 AM, Simon Slavin <[hidden email]> wrote:

>
>
> On 27 Feb 2018, at 7:01am, Hick Gunter <[hidden email]> wrote:
>
> > What should substr('abcd',0,-2) return? 'cd' or just 'd'? Or maybe just
> an empty string?
>
> NULL
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Strange concatenation result

curmudgeon
In reply to this post by Hick Gunter
>What should substr('abcd',0,-2) return? 'cd' or just 'd'? Or maybe just an empty string?

You’re starting at position 0 (in the imaginary infinite array) and taking the two characters to the left of that which are ‘\0’, so an empty string.

substr(‘abcd’,0,2) on the other hand should return array[0]+array[1] = ‘a’

Seems fairly consistent to me.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users