Error: near line 3: parser stack overflow
(1) By anonymous on 2020-07-06 09:43:50 [link] [source]
Not sure of the facts, I have a query run in the CLI returning the titled error and I just cannot seem to up the limit at runtime, though the .help command indicates to me it should be possible. Also the documentation states that making it 0 would allow the stack usage to just grow, though I still get the parser stack overflow error:
C:sqlite3 SQLite version 3.32.3 2020-06-18 14:00:33 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .limit expr_depth expr_depth 1000 sqlite> .limit expr_depth 2000 expr_depth 1000 sqlite> .limit expr_depth 900 expr_depth 900 sqlite> .limit expr_depth 0 expr_depth 0
Can't make it higher than 1000 and 0 still cause the error for me...
Is it possible to change it via CLI tool?
Philip
(2) By Richard Hipp (drh) on 2020-07-06 11:31:20 in reply to 1 [link] [source]
The parser stack is fixed, by default. (There are compile-time options to change that, but as they slow down the parser, they are not normally used.)
Can you share with us the SQL that is causing the parser stack overflow?
(3) By anonymous on 2020-07-06 13:03:28 in reply to 2 [link] [source]
I am trying to break down file paths into columns for it's different directory and file name parts. I am by no means an expert so all feedback apreciated: (It works fine up to level 6 or 7) CREATE TABLE T1(FilDat, FilTim, PathFileName, DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, DirLev6, DirLev7, DirLev8, FilNam); INSERT INTO T1 VALUES('1993-06-17', '09:54:59', 'DirLevOne\LevTwo\Three\lpk0924.mne', '', '', '', '', '', '', '', '', '', ''); SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, DirLev6, substr(DirLev7, 1, pos-1) AS DirLev7, substr(DirLev7, pos+1) AS DirLev8 FROM ( SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, DirLev6, DirLev7, instr(DirLev7, '\') AS pos FROM ( SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, substr(DirLev6, 1, pos-1) AS DirLev6, substr(DirLev6, pos+1) AS DirLev7 FROM ( SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, DirLev6, instr(DirLev6, '\') AS pos FROM ( SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, substr(DirLev5, 1, pos-1) AS DirLev5, substr(DirLev5, pos+1) AS DirLev6 FROM ( SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, instr(DirLev5, '\') AS pos FROM ( SELECT DirLev0, DirLev1, DirLev2, DirLev3, substr(DirLev4, 1, pos-1) AS DirLev4, substr(DirLev4, pos+1) AS DirLev5 FROM ( SELECT DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, instr(DirLev4, '\') AS pos FROM ( SELECT DirLev0, DirLev1, DirLev2, substr(DirLev3, 1, pos-1) AS DirLev3, substr(DirLev3, pos+1) AS DirLev4 FROM ( SELECT DirLev0, DirLev1, DirLev2, DirLev3, instr(DirLev3, '\') AS pos FROM ( SELECT DirLev0, DirLev1, substr(DirLev2, 1, pos-1) AS DirLev2, substr(DirLev2, pos+1) AS DirLev3 FROM ( SELECT DirLev0, DirLev1, DirLev2, instr(DirLev2, '\') AS pos FROM ( SELECT DirLev0, substr(DirLev2, 1, pos-1) AS DirLev1, substr(DirLev2, pos+1) AS DirLev2 FROM ( SELECT DirLev0, DirLev2, instr(DirLev2, '\') AS pos FROM ( SELECT substr(PathFileName, 1, pos-1) AS DirLev0, substr(PathFileName, pos+1) AS DirLev2 FROM ( SELECT *, instr(PathFileName,'\') AS pos FROM T1 ) ) ) ) ) ) ) ) ) ) ) ) ) ) )
(4) By Ryan Smith (cuz) on 2020-07-06 14:44:03 in reply to 3 [link] [source]
Well, that's a beautiful code tree!
Aside from the bug/fixing/problem, may I suggest a method slightly more sane:
This script makes the same table as you proposed, but I added some values that would be problematic and then some sections to do what you intended.
Only Section 3 is the one doing the real work, but the prior two sections are included to show the steps and what their data looks like.
- The first section only parse and pulls apart the path.
- The next section then amalgamates the parsed path bits into a kind of pivot-table to do what (I hope) you intended.
- The last section simply UPDATEs that into your original table and then displays its content.
PS: This is bad for so many reasons, this example shows HOW, but I'm not promoting this as a good thing to do. Path parsing should really be left to the end-system code.
-- Processing SQL in: D:\Documents\SQLiteAutoScript.sql
-- SQLite version 3.30.1 [ Release: 2019-10-10 ] on SQLitespeed version 2.1.3.11.
-- Script Items: 6 Parameter Count: 0
-- 2020-07-06 16:28:21.511 | [Info] Script Initialized, Started executing...
-- ================================================================================================
CREATE TABLE T1(FilDat, FilTim, PathFileName, DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, DirLev6, DirLev7, DirLev8, FilNam);
INSERT INTO T1 VALUES
('1993-06-17', '09:54:59', 'DirLevOne\LevTwo\Three\lpk0924.mne', '', '', '', '', '', '', '', '', '', '')
,('1993-06-18', '09:50:00', 'DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne', '', '', '', '', '', '', '', '', '', '')
,('1998-05-16', '09:51:01', 'DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne', '', '', '', '', '', '', '', '', '', '')
;
-- SECTION 1 - Parsing
WITH pathrec(i, l, d, c, r) AS (
SELECT t.rowid, 1, -1,
t.PathFileName||'\', ''
FROM T1 AS t
WHERE 1
UNION ALL
SELECT i,
instr( c, '\' ) AS vLength, d+1,
substr( c, instr( c, '\' ) + 1) AS vRemainder,
trim( substr( c, 1, instr( c, '\' ) - 1) ) AS vCSV
FROM pathrec
WHERE vLength > 0
)
SELECT t.FilDat, t.FilTim, t.PathFileName, cr.d, cr.r AS colPathSect FROM T1 AS t, pathrec AS cr
WHERE t.rowid = cr.i AND cr.r <> ''
ORDER BY t.rowid, cr.d
;
-- FilDat |FilTim |PathFileName | d |colPathSect
-- ------------|----------|----------------------------------------------------------|---|-------------
-- 1993-06-17 |09:54:59 |DirLevOne\LevTwo\Three\lpk0924.mne | 0 |DirLevOne
-- 1993-06-17 |09:54:59 |DirLevOne\LevTwo\Three\lpk0924.mne | 1 |LevTwo
-- 1993-06-17 |09:54:59 |DirLevOne\LevTwo\Three\lpk0924.mne | 2 |Three
-- 1993-06-17 |09:54:59 |DirLevOne\LevTwo\Three\lpk0924.mne | 3 |lpk0924.mne
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne | 0 |DirLevOne
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne | 1 |LevTwo
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne | 2 |Three
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne | 3 |Four
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne | 4 |Five
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne | 5 |lpk0923.mne
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 0 |DirLevOne
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 1 |LevTwo
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 2 |Three
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 3 |Four
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 4 |Five
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 5 |L6
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 6 |L7
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 7 |L8
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 8 |L9
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne | 9 |lpk0925.mne
-- SECTION 2 - Pivot
WITH pathrec(i, l, d, c, r) AS (
SELECT t.rowid, 1, -1,
t.PathFileName||'\', ''
FROM T1 AS t
WHERE 1
UNION ALL
SELECT i,
instr( c, '\' ) AS vLength, d+1,
substr( c, instr( c, '\' ) + 1) AS vRemainder,
trim( substr( c, 1, instr( c, '\' ) - 1) ) AS vCSV
FROM pathrec
WHERE vLength > 0
), paths AS (
SELECT cr.i, cr.d, cr.r
FROM pathrec AS cr
WHERE cr.r <> ''
GROUP BY cr.i, cr.d
), pathexp(i, DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, DirLev6, DirLev7, DirLev8, DirOver) AS (
SELECT i,
MAX(CASE WHEN d = 0 THEN r ELSE '' END),
MAX(CASE WHEN d = 1 THEN r ELSE '' END),
MAX(CASE WHEN d = 2 THEN r ELSE '' END),
MAX(CASE WHEN d = 3 THEN r ELSE '' END),
MAX(CASE WHEN d = 4 THEN r ELSE '' END),
MAX(CASE WHEN d = 5 THEN r ELSE '' END),
MAX(CASE WHEN d = 6 THEN r ELSE '' END),
MAX(CASE WHEN d = 7 THEN r ELSE '' END),
MAX(CASE WHEN d = 8 THEN r ELSE '' END),
GROUP_CONCAT(CASE WHEN d > 8 THEN r ELSE '' END,'')
FROM paths
GROUP BY i
)
SELECT * FROM pathexp
;
-- i |DirLev0 |DirLev1 |DirLev2|DirLev3 |DirLev4|DirLev5 |DirLev6|DirLev7|DirLev8|DirOver
-- ------------|-----------|--------|-------|-------------|-------|-------------|-------|-------|-------|-------------
-- 1 |DirLevOne |LevTwo |Three |lpk0924.mne | | | | | |
-- 2 |DirLevOne |LevTwo |Three |Four |Five |lpk0923.mne | | | |
-- 3 |DirLevOne |LevTwo |Three |Four |Five |L6 | L7 | L8 | L9 |lpk0925.mne
-- SECTION 3 - Populate Table
WITH pathrec(i, l, d, c, r) AS (
SELECT t.rowid, 1, -1,
t.PathFileName||'\', ''
FROM T1 AS t
WHERE 1
UNION ALL
SELECT i,
instr( c, '\' ) AS vLength, d+1,
substr( c, instr( c, '\' ) + 1) AS vRemainder,
trim( substr( c, 1, instr( c, '\' ) - 1) ) AS vCSV
FROM pathrec
WHERE vLength > 0
), paths AS (
SELECT cr.i, cr.d, cr.r
FROM pathrec AS cr
WHERE cr.r <> ''
GROUP BY cr.i, cr.d
), pathexp(i, D0, D1, D2, D3, D4, D5, D6, D7, D8, DEx) AS (
SELECT i,
MAX(CASE WHEN d = 0 THEN r ELSE '' END),
MAX(CASE WHEN d = 1 THEN r ELSE '' END),
MAX(CASE WHEN d = 2 THEN r ELSE '' END),
MAX(CASE WHEN d = 3 THEN r ELSE '' END),
MAX(CASE WHEN d = 4 THEN r ELSE '' END),
MAX(CASE WHEN d = 5 THEN r ELSE '' END),
MAX(CASE WHEN d = 6 THEN r ELSE '' END),
MAX(CASE WHEN d = 7 THEN r ELSE '' END),
MAX(CASE WHEN d = 8 THEN r ELSE '' END),
GROUP_CONCAT(CASE WHEN d > 8 THEN r ELSE '' END,'')
FROM paths
GROUP BY i
)
UPDATE T1
SET (DirLev0, DirLev1, DirLev2, DirLev3, DirLev4, DirLev5, DirLev6, DirLev7, DirLev8) =
(SELECT D0, D1, D2, D3, D4, D5, D6, D7, D8 FROM pathexp WHERE pathexp.i = T1.rowid),
FilNam = (SELECT CASE
WHEN DEx!= '' THEN DEx
WHEN D8 != '' THEN D8
WHEN D7 != '' THEN D7
WHEN D6 != '' THEN D6
WHEN D5 != '' THEN D5
WHEN D4 != '' THEN D4
WHEN D3 != '' THEN D3
WHEN D2 != '' THEN D2
WHEN D1 != '' THEN D1
ELSE D0
END FROM pathexp WHERE pathexp.i = T1.rowid)
WHERE 1
;
SELECT *
FROM T1
;
-- FilDat |FilTim |PathFileName |DirLev0 |DirLev1|DirLev2|DirLev3 |DirLev4|DirLev5 |DirLev6|DirLev7|DirLev8|FilNam
-- -----------|---------|---------------------------------------------------------|----------|-------|-------|-----------|-------|-----------|-------|-------|-------|-------------
-- 1993-06-17 |09:54:59 |DirLevOne\LevTwo\Three\lpk0924.mne |DirLevOne |LevTwo |Three |lpk0924.mne| | | | | |lpk0924.mne
-- 1993-06-18 |09:50:00 |DirLevOne\LevTwo\Three\Four\Five\lpk0923.mne |DirLevOne |LevTwo |Three |Four |Five |lpk0923.mne| | | |lpk0923.mne
-- 1998-05-16 |09:51:01 |DirLevOne\LevTwo\Three\Four\Five\L6\L7\L8\L9\lpk0925.mne |DirLevOne |LevTwo |Three |Four |Five |L6 | L7 | L8 | L9 |lpk0925.mne
-- Script Stats: Total Script Execution Time: 0d 00h 00m and 00.035s
-- Total Script Query Time: 0d 00h 00m and 00.003s
-- Total Database Rows Changed: 6
-- Total Virtual-Machine Steps: 10045
-- Last executed Item Index: 6
-- Last Script Error:
-- ------------------------------------------------------------------------------------------------
-- 2020-07-06 16:28:21.530 | [Success] Script Success.
-- ================================================================================================
Note: The original table does not leave a method for handling overflow (paths longer than 9 sections) but the parser can add it as the un-parsed remaining path (see second step)... Which is one of the reasons this is a bad idea in a DB.
At least it was fun to do :)
Ryan
(6) By anonymous on 2020-07-06 15:24:10 in reply to 4 [source]
Thanks a million! It's not part of something bigger so the alternative for me would have probably been a spreadsheet, but like you said, much more fun here / this way :) In the long run I wanna use it in part to change file folders, names, times etc. for some of my older projects being imported into Fossil. Philip
(5) By Igor Tandetnik (itandetnik) on 2020-07-06 15:20:44 in reply to 3 [link] [source]
Something along these lines, perhaps:
with recursive DirLevels as (
select rowid, 0 Level,
substr(PathFileName, 1, instr(PathFileName||'\', '\')-1) DirName,
substr(PathFileName, instr(PathFileName, '\') + 1) Tail
from T1
union all
select rowid, Level+1,
substr(Tail, 1, instr(Tail||'\', '\')-1) DirName,
case when instr(Tail, '\') then substr(Tail, instr(Tail, '\') + 1) else '' end
from DirLevels
where Tail != ''
)
select rowid,
max(case level when 0 then DirName end) DirLev1,
max(case level when 1 then DirName end) DirLev2,
max(case level when 2 then DirName end) DirLev3,
max(case level when 3 then DirName end) DirLev4,
max(case level when 4 then DirName end) DirLev5,
max(case level when 5 then DirName end) DirLev6
from DirLevels
group by rowid;
(7) By anonymous on 2020-07-06 15:35:54 in reply to 5 [link] [source]
I think the subject should be changed to how not to do it with EXPERT examples on how to do it... Thank you Igor! Philip
(8) By Igor Tandetnik (itandetnik) on 2020-07-06 15:55:36 in reply to 7 [link] [source]
Cunningham's Law in action.
(9) By anonymous on 2020-07-07 08:27:44 in reply to 8 [link] [source]
100% Applicable law!! My original script always returned the file names in the last column, (purely by accident!!) which is ideal. Is there a way with your script to accomplish the same other than to run a second select? Thank you! Philip
(10) By anonymous on 2020-07-07 10:05:27 in reply to 9 [link] [source]
Not to worry... You return the file names in the DirName column which is 100%. This is also much more faster compared to what I had!! Thanks again! Philip