Re: [sqlite] Strange concatenation result
That’s the way I see it Jean-Luc.
From: Jean-Luc Hainaut<mailto:jean-luc.hain...@unamur.be>
Sent: 27 February 2018 09:56
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange concatenation result
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 , 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange concatenation result
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange concatenation result
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange concatenation result
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange concatenation result
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange concatenation result
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Strange concatenation result
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-322$ 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('' || (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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
