tstack
Complex SQL query which works with precompiled 0.13.2, returns syntax error with latest compiled code
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.
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.
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
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.
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.