DB Query Returns No Results While Raw SQLite Query Returns Data

[Kotodian]

Version: v1.21.2

SQLite version: 3.46.0

Problem Description

When executing a grouped time-series query via DB's Query function, no results are returned. However, when running the exact same SQL query directly in a SQLite client (using DB Browser for SQLite or sqlite3 CLI), the expected data is returned. This discrepancy occurs despite both methods querying the same database and table.

Table Schema

CREATE TABLE `dashboard` (
  `id` integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `time` integer NOT NULL,        -- Unix timestamp (seconds)
  `cpu` integer NOT NULL,         -- CPU usage percentage
  `memuse` integer NOT NULL,      -- Memory usage percentage
  `total_mem` integer NOT NULL    -- Total memory in bytes
);

Query SQL

SELECT
  strftime('%Y-%m-%d %H', time, 'unixepoch') AS hour,
  avg(cpu) as cpu_usage,
  avg(memuse) as memory_usage
FROM dashboard
WHERE time >= [START_TIMESTAMP] AND time <= [END_TIMESTAMP]  -- Replace with actual timestamps
GROUP BY hour
ORDER BY hour DESC;

Golang Code

package main

import (
        "database/sql"
        "fmt"

        _ "github.com/glebarez/go-sqlite"
)

func main() {
        db, err := sql.Open("sqlite3", "test.db?&_pragma=foreign_keys(1)")
        if err != nil {
                panic(err)
        }
        defer db.Close()

        rows, err := db.Query(`
                select
strftime('%Y-%m-%d %H', time, 'unixepoch') AS hour, avg(cpu) as cpu, avg(memuse) as memory
from dashboard
where time >= 1748242123 and time <= 1748414923
group by hour order by hour desc;
                `)
        if err != nil {
                panic(err)
        }

        defer rows.Close()
        fmt.Println(rows.Next())
}

Outputs

SQLite version 3.46.0 2024-05-23 13:25:27
Enter ".help" for usage hints.
sqlite> select
strftime('%Y-%m-%d %H', time, 'unixepoch') AS hour, avg(cpu) as cpu, avg(memuse) as memory
from dashboard
where time >= 1748242123 and time <= 1748414923
group by hour order by hour desc;
2025-05-28 06|35.563829787234|36.6808510638298
2025-05-28 05|36.3083333333333|34.15
2025-05-28 04|37.1416666666667|32.95
2025-05-28 03|35.1083333333333|36.225
2025-05-28 02|33.9083333333333|34.275
2025-05-28 01|34.65|33.975
2025-05-28 00|36.0083333333333|33.1083333333333
2025-05-27 23|32.3|35.1333333333333
2025-05-27 22|34.8083333333333|34.275
2025-05-27 21|37.5916666666667|36.4833333333333
2025-05-27 20|34.55|35.5166666666667
2025-05-27 19|36.6333333333333|34.0333333333333
2025-05-27 18|33.6583333333333|36.1833333333333
2025-05-27 17|36.4833333333333|34.2583333333333
2025-05-27 16|35.5333333333333|38.0833333333333
2025-05-27 15|34.7|35.1083333333333
2025-05-27 14|32.5083333333333|35.075
2025-05-27 13|35.6|35.7083333333333
2025-05-27 12|36.9916666666667|35.1833333333333
2025-05-27 11|33.7916666666667|34.2
2025-05-27 10|33.2833333333333|37.0083333333333
2025-05-27 09|34.1583333333333|33.575
2025-05-27 08|33.7583333333333|34.475
2025-05-27 07|32.45|34.8833333333333
2025-05-27 06|31.2222222222222|32.0

go build -o query main.go
./query
false

[glebarez]

Hello, can you try without trailing semicolon ?

[Kotodian]

Hello, can you try without trailing semicolon ?

Thank you for your reply, it indeed worked. However, I would like to know why the trailing semicolon cannot be added.

[glebarez]

Hello, can you try without trailing semicolon ?

Thank you for your reply, it indeed worked. However, I would like to know why the trailing semicolon cannot be added.

Design choice is that you can run multiple queries in a single call to driver, but only the result of last query is returned.
Semicolon is treated as a query separator.
In your case there was a rendering of a second query as an empty string.

This is a bug for sure.

[Kotodian]

Hello, can you try without trailing semicolon ?

Thank you for your reply, it indeed worked. However, I would like to know why the trailing semicolon cannot be added.

Design choice is that you can run multiple queries in a single call to driver, but only the result of last query is returned. Semicolon is treated as a query separator. In your case there was a rendering of a second query as an empty string.

This is a bug for sure.

I understand, thank you.