Complex SQL query which works with precompiled 0.13.2, returns syntax error with latest compiled code

[dardhal]

lnav version
Issue occurs with "lnav 0.14.0-a2e2380-dirty" compiled from source, but it does not with "lnav 0.13.2" downloaded as statically linked binary from GitHub.

Describe the bug
I have lists of files in a format which is like below:

name    mtime   fileid  size    seg_bytes       seg_count       redun_seg_count pre_lc_size     post_lc_size    tier    placement_time
/file/path/file01   1577573998671406000     17      8192    8236    2       2       0       0       ECS     Sun Mar 28 09:48:19 2021
/file/path/file02   1577574000016210000     18      32768   32868   4       4       0       0       ECS     Sun Mar 28 09:48:19 2021
/file/path/file03   1577574000978268000     19      8192    8236    2       2       0       0       ECS     Sun Mar 28 09:48:19 2021

Sometimes I need to do some data analysis from "placement_time", but this being a string, I have to convert it first to another which is still a string but in a format which allows easy searching or comparisons. Hence use a canned SQL query like below to create a view so that "placement_time" is turned into a better timestamp:

;CREATE VIEW location_report_ptime AS
SELECT *,
    CASE
        WHEN ptime IS NOT NULL THEN
            -- Normalize input like "Sun Mar 9 23:41:59 2025"
            -- Remove weekday (first 4 characters)
            (
                WITH parts AS (
                    SELECT
                        substr(ptime, 5) AS dt -- "Mar 9 23:41:59 2025"
                )
                SELECT
                    printf(
                        '%04d-%02d-%02d %s',
                        CAST(substr(dt, length(dt) - 3, 4) AS INT), -- Year (last 4 chars)
                        CASE substr(dt, 1, 3)                        -- Month abbreviation
                            WHEN 'Jan' THEN 1
                            WHEN 'Feb' THEN 2
                            WHEN 'Mar' THEN 3
                            WHEN 'Apr' THEN 4
                            WHEN 'May' THEN 5
                            WHEN 'Jun' THEN 6
                            WHEN 'Jul' THEN 7
                            WHEN 'Aug' THEN 8
                            WHEN 'Sep' THEN 9
                            WHEN 'Oct' THEN 10
                            WHEN 'Nov' THEN 11
                            WHEN 'Dec' THEN 12
                        END,
                        -- Day part: between month and time
                        CAST(trim(substr(dt, 5, instr(dt, ':') - 8)) AS INT),
                        -- Time part
                        substr(dt, instr(dt, ':') - 2, 8)
                    )
                FROM parts
            )
        ELSE NULL
    END AS iso_date_ptime
FROM location_report;

Which results in another field called "iso_date_ptime" having a date / time string which I can use for things like searching all the files matching the timestamp for a given period of time, ie:

;SELECT path,log_time,ptime,iso_date_ptime FROM location_report_ptime WHERE iso_date_ptime > '2021-01-01' AND iso_date_ptime < '2022-01-01' ORDER BY iso_date_ptime DESC LIMIT 20

Note there may be much simpler and effective ways of achieving the same result, and my approach may be too brute-force.

Fact is, the SQL VIEW above does get created fine and hence I can query the created "location_report_ptime" view fine with pre-compiled lnav 0.13.2, but I can not with my own compiled copy of current code (0.14.0-a2e2380-dirty).

With my compiled version the CREATE VIEW appears to work, but querying the view returns it does not exist, revisiting the CREATE query in SQL mode the result is (info below taken from debug log but lnav UI shows the same):

2025-11-17T07:32:59.612114+01:00 E t0 sql_util.cc:1122 (1) near "parts": syntax error in "CREATE VIEW location_report_ptime AS
SELECT
    *,
    CASE
        WHEN ptime IS NOT NULL THEN -- Normalize input like "Sun Mar 9 23:41:59 2025"
            -- Remove weekd

To Reproduce
As described above.

I understand this is a problem from the underlying SQLite library versions being used (lnav passes the SQL down to the SQLite library and returns whatever it gets from below), and not related to lnav at all, but logging here just in case.

[tstack]

I understand this is a problem from the underlying SQLite library versions being used

What version of SQLite is on the system? The lnav debug log will include a line like sqlite=3.50.4, if you want to make sure what is actually being used. The version built into the binaries on the release page should be v3.50.4.

When building on my mac, I use the SQLite from home brew by passing --with-sqlite3=/opt/homebrew/opt/sqlite3 to configure.

[dardhal]

Both the "sqlite3" binary as the libraries used to compile / link the "lnav" binary are the same version (running on a Fedora OS 42):

$ sqlite3 --version
3.47.2 2024-12-07 20:39:59 2aabe05e2e8cae4847a802ee2daddc1d7413d8fc560254d93ee3e72c1468alt1 (64-bit)

$ ldd `which lnav` | grep -i sqli
	libsqlite3.so.0 => /lib64/libsqlite3.so.0 (0x00007fd63b4de000)
$ rpm -qf /lib64/libsqlite3.so.0
sqlite-libs-3.47.2-5.fc42.x86_64

Running the compiled "lnav" with the debug log it indeed shows the same version:

2025-11-18T13:54:18.225183+01:00 I t0 lnav.cc:2849 opening main sqlite3 (3.47.2) DB
2025-11-18T13:54:18.226266+01:00 I t0 views_vtab.cc:1394 creating filter view: 
CREATE VIEW lnav_db.lnav_view_filters_and_stats AS

Using pre-compiled "lnav" 0.13.2 it does indeed inform version 3.50.4 being used:

2025-11-18T13:56:36.305+01:00 I t0 lnav.cc:3925   sqlite=3.50.4

[dardhal]

So did a test to try to narrow down where the error may be coming from, so what I did was:

• Took the input file CSV and slightly modified it so that the TAB separator is changed to comma (reason for this later on)
• Kicked off "sqlite3" from the CLI, and confirmed it is version 3.47.2:

sqlite> .version
SQLite 3.47.2 2024-12-07 20:39:59 2aabe05e2e8cae4847a802ee2daddc1d7413d8fc560254d93ee3e72c1468alt1
zlib version 1.3.1.zlib-ng
gcc-15.2.1 20250808 (Red Hat 15.2.1-1) (64-bit)

• Imported the CSV from SQLite3 itself (that's why I had to replace the TABS with commas, to allow sqlite3 import to work):

sqlite> .import output-comma.csv --csv location_report

sqlite> .schema
CREATE TABLE IF NOT EXISTS "location_report"(
"name" TEXT, "mtime" TEXT, "fileid" TEXT, "size" TEXT,
 "seg_bytes" TEXT, "seg_count" TEXT, "redun_seg_count" TEXT, "pre_lc_size" TEXT,
 "post_lc_size" TEXT, "tier" TEXT, "ptime" TEXT);

• Then did the "CREATE VIEW location_report_ptime AS" by copy / pasting into the SQLite3 CLI to create the view
• Finally run SQL queries against the view and all of them worked fine, for example:

sqlite> SELECT COUNT(*) FROM location_report_ptime;
828950

So it is as if underlying "sqlite3" does not have a problem with my "CREATE VIEW" command, and it could be somehow passing the query from "lnav" down to "sqlite3" what may have changed for me since 0.13.2, rather than the underlying sqlite3 provider being a particular version.

[dardhal]

Did some additional testing. Long story short: turning the "CREATE VIEW" into a "CREATE TABLE" works with the most recent version.

Long story. Initial bug report is correct but I failed to properly redact some details, and there was an inconsistency (placement_time and ptime should have been the same).

Used the input file in the first message as an example:

$ cat input-ptime.csv
/file/path/file01   1577573998671406000     17      8192    8236    2       2       0       0       ECS     Sun Mar 28 09:48:19 2021
/file/path/file02   1577574000016210000     18      32768   32868   4       4       0       0       ECS     Sun Mar 28 09:48:19 2021
/file/path/file03   1577574000978268000     19      8192    8236    2       2       0       0       ECS     Sun Mar 28 09:48:19 2021

The input data matches one of my own formats (ddos_location_report), which has a "ptime" text field I want to turn into an standard ISO date / time format, hence the query at the first message.

When using "CREATE VIEW", recent "lnav" fails to work. Used the CLI and -f option to clearly show why:

$ cat query.sql
;SELECT * FROM ddos_location_report
:write-table-to /dev/stdout

;CREATE VIEW location_report_ptime AS
SELECT *,
    CASE
        WHEN ptime IS NOT NULL THEN
            -- Normalize input like "Sun Mar 9 23:41:59 2025"
            -- Remove weekday (first 4 characters)
            (
                WITH parts AS (
                    SELECT
                        substr(ptime, 5) AS dt -- "Mar 9 23:41:59 2025"
                )
                SELECT
                    printf(
                        '%04d-%02d-%02d %s',
                        CAST(substr(dt, length(dt) - 3, 4) AS INT), -- Year (last 4 chars)
                        CASE substr(dt, 1, 3)                        -- Month abbreviation
                            WHEN 'Jan' THEN 1
                            WHEN 'Feb' THEN 2
                            WHEN 'Mar' THEN 3
                            WHEN 'Apr' THEN 4
                            WHEN 'May' THEN 5
                            WHEN 'Jun' THEN 6
                            WHEN 'Jul' THEN 7
                            WHEN 'Aug' THEN 8
                            WHEN 'Sep' THEN 9
                            WHEN 'Oct' THEN 10
                            WHEN 'Nov' THEN 11
                            WHEN 'Dec' THEN 12
                        END,
                        -- Day part: between month and time
                        CAST(trim(substr(dt, 5, instr(dt, ':') - 8)) AS INT),
                        -- Time part
                        substr(dt, instr(dt, ':') - 2, 8)
                    )
                FROM parts
            )
        ELSE NULL
    END AS iso_date_ptime
FROM ddos_location_report

;SELECT * FROM location_report_ptime
:write-table-to /dev/stdout

Running this from the CLI fails as below:

$ lnav -n -f query.sql input-ptime.csv 
┏━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┳━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━┓
┃log_line┃         log_time         ┃log_level┃  fileid  ┃       path        ┃post_lc_size┃pre_lc_size┃         ptime          ┃redun_seg_count┃seg_bytes ┃seg_count ┃   size   ┃tier┃log_part┃log_idle_msecs┃log_mark┃log_comment┃log_tags┃log_annotations┃log_filters┃
┡━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━╇━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━┩
│       0│2019-12-28 23:59:58.671406│info     │        17│/file/path/file01  │           0│          0│Sun Mar 28 09:48:19 2021│              2│      8236│         2│      8192│ECS │<NULL>  │             0│       0│<NULL>     │<NULL>  │<NULL>         │<NULL>     │
│       1│2019-12-29 00:00:00.016210│info     │        18│/file/path/file02  │           0│          0│Sun Mar 28 09:48:19 2021│              4│     32868│         4│     32768│ECS │<NULL>  │          1345│       0│<NULL>     │<NULL>  │<NULL>         │<NULL>     │
│       2│2019-12-29 00:00:00.978268│info     │        19│/file/path/file03  │           0│          0│Sun Mar 28 09:48:19 2021│              2│      8236│         2│      8192│ECS │<NULL>  │           962│       0│<NULL>     │<NULL>  │<NULL>         │<NULL>     │
└━━━━━━━━┴━━━━━━━━━━━━━━━━━━━━━━━━━━┴━━━━━━━━━┴━━━━━━━━━━┴━━━━━━━━━━━━━━━━━━━┴━━━━━━━━━━━━┴━━━━━━━━━━━┴━━━━━━━━━━━━━━━━━━━━━━━━┴━━━━━━━━━━━━━━━┴━━━━━━━━━━┴━━━━━━━━━━┴━━━━━━━━━━┴━━━━┴━━━━━━━━┴━━━━━━━━━━━━━━┴━━━━━━━━┴━━━━━━━━━━━┴━━━━━━━━┴━━━━━━━━━━━━━━━┴━━━━━━━━━━━┘
✘ error: failed to compile SQL statement
 reason: no such table: main.ddos_location_report
 --> command-option:1
 | |query.sql                              
 --> /tmp/query.sql:44
 | SELECT * FROM location_report_ptime   

Whereas changing the "CREATE VIEW" to a "CREATE TABLE" works as below:

$ lnav -n -f query.sql input-ptime.csv 
┏━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┳━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━┓
┃log_line┃         log_time         ┃log_level┃  fileid  ┃       path        ┃post_lc_size┃pre_lc_size┃         ptime          ┃redun_seg_count┃seg_bytes ┃seg_count ┃   size   ┃tier┃log_part┃log_idle_msecs┃log_mark┃log_comment┃log_tags┃log_annotations┃log_filters┃
┡━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━╇━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━┩
│       0│2019-12-28 23:59:58.671406│info     │        17│/file/path/file01  │           0│          0│Sun Mar 28 09:48:19 2021│              2│      8236│         2│      8192│ECS │<NULL>  │             0│       0│<NULL>     │<NULL>  │<NULL>         │<NULL>     │
│       1│2019-12-29 00:00:00.016210│info     │        18│/file/path/file02  │           0│          0│Sun Mar 28 09:48:19 2021│              4│     32868│         4│     32768│ECS │<NULL>  │          1345│       0│<NULL>     │<NULL>  │<NULL>         │<NULL>     │
│       2│2019-12-29 00:00:00.978268│info     │        19│/file/path/file03  │           0│          0│Sun Mar 28 09:48:19 2021│              2│      8236│         2│      8192│ECS │<NULL>  │           962│       0│<NULL>     │<NULL>  │<NULL>         │<NULL>     │
└━━━━━━━━┴━━━━━━━━━━━━━━━━━━━━━━━━━━┴━━━━━━━━━┴━━━━━━━━━━┴━━━━━━━━━━━━━━━━━━━┴━━━━━━━━━━━━┴━━━━━━━━━━━┴━━━━━━━━━━━━━━━━━━━━━━━━┴━━━━━━━━━━━━━━━┴━━━━━━━━━━┴━━━━━━━━━━┴━━━━━━━━━━┴━━━━┴━━━━━━━━┴━━━━━━━━━━━━━━┴━━━━━━━━┴━━━━━━━━━━━┴━━━━━━━━┴━━━━━━━━━━━━━━━┴━━━━━━━━━━━┘
┏━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┳━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┓
┃log_line┃         log_time         ┃log_level┃  fileid  ┃       path        ┃post_lc_size┃pre_lc_size┃         ptime          ┃redun_seg_count┃seg_bytes ┃seg_count ┃   size   ┃tier┃log_part┃log_idle_msecs┃log_mark┃log_comment┃log_tags┃log_annotations┃log_filters┃  iso_date_ptime   ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━╇━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━┩
│       0│2019-12-28 23:59:58.671406│info     │        17│/file/path/file01  │           0│          0│Sun Mar 28 09:48:19 2021│              2│      8236│         2│      8192│ECS │<NULL>  │             0│       0│<NULL>     │<NULL>  │<NULL>         │<NULL>     │2021-03-28 09:48:19│
│       1│2019-12-29 00:00:00.016210│info     │        18│/file/path/file02  │           0│          0│Sun Mar 28 09:48:19 2021│              4│     32868│         4│     32768│ECS │<NULL>  │          1345│       0│<NULL>     │<NULL>  │<NULL>         │<NULL>     │2021-03-28 09:48:19│
│       2│2019-12-29 00:00:00.978268│info     │        19│/file/path/file03  │           0│          0│Sun Mar 28 09:48:19 2021│              2│      8236│         2│      8192│ECS │<NULL>  │           962│       0│<NULL>     │<NULL>  │<NULL>         │<NULL>     │2021-03-28 09:48:19│
└━━━━━━━━┴━━━━━━━━━━━━━━━━━━━━━━━━━━┴━━━━━━━━━┴━━━━━━━━━━┴━━━━━━━━━━━━━━━━━━━┴━━━━━━━━━━━━┴━━━━━━━━━━━┴━━━━━━━━━━━━━━━━━━━━━━━━┴━━━━━━━━━━━━━━━┴━━━━━━━━━━┴━━━━━━━━━━┴━━━━━━━━━━┴━━━━┴━━━━━━━━┴━━━━━━━━━━━━━━┴━━━━━━━━┴━━━━━━━━━━━┴━━━━━━━━┴━━━━━━━━━━━━━━━┴━━━━━━━━━━━┴━━━━━━━━━━━━━━━━━━━┘

Obviously, I can turn my VIEW query into a TABLE query (and considering I am not processing huge amounts of data, I understand the impact on memory usage will be negligible), but it seems the difference in behavior is a consequence of some of the recent changes to make user tables to be attached to a database different to the main one, or so I guess from the error.

Hope this is helpful and saves you time fixing this if appropriate.