SQLite Forum

Error: near line 3: parser stack overflow
Login

Error: near line 3: parser stack overflow

(1) By anonymous on 2020-07-06 09:43:50 [link]

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]

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]

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]

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 [link]

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]

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]

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

[Cunningham's Law](https://meta.wikimedia.org/wiki/Cunningham%27s_Law) in action.

(9) By anonymous on 2020-07-07 08:27:44 in reply to 8 [link]

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]

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