NULL values in BOOLEAN / DATETIME / DATE / TIMESTAMP columns are sent on the wire as zero-valued non-NULL types

[antoineleclair]

When a column declared as BOOLEAN, DATETIME, DATE, or TIMESTAMP
contains NULL, dqlite encodes the response with the wrong type.

Declared type	Type/value dqlite returns	Expected
INTEGER	NULL (5) / (none)	NULL (5) / (none) ✓
TEXT	NULL (5) / (none)	NULL (5) / (none) ✓
REAL	NULL (5) / (none)	NULL (5) / (none) ✓
BLOB	NULL (5) / (none)	NULL (5) / (none) ✓
BOOLEAN	BOOLEAN (11) / 0	NULL (5) / (none) ✗
DATETIME	ISO8601 (10) / ""	NULL (5) / (none) ✗
DATE	ISO8601 (10) / ""	NULL (5) / (none) ✗
TIMESTAMP	ISO8601 (10) / ""	NULL (5) / (none) ✗

Instead of NULL (type code 5) it returns BOOLEAN (11) or
ISO8601 (10) followed by the zero value for that type (0 for booleans,
"" for ISO8601). Columns declared with other types (INTEGER, TEXT,
REAL, BLOB) are encoded correctly as NULL.

For example, the BOOLEAN 0 is indistinguishable from FALSE.

For comparison, SQLite returns SQLITE_NULL for a NULL value regardless of the column's declared type.

Disclosure: I bumped into the issue while working on a Python client for dqlite and
I used Claude Code to help identify the issue and to generate the examples below,
but I do believe that SQLite does it right and dqlite should have the same behavior.

Reproduction: C client

The program below starts an in-process dqlite_node, connects to it with
the in-tree C client (src/client/protocol.c, the same one used by the
existing integration tests), creates a table with one column per declared
type listed above, inserts a row of NULLs, and prints the wire-level
type tag the server sent back for each column. For comparison, it also
runs the same DDL/INSERT/SELECT against an in-memory plain SQLite database
and prints what sqlite3_column_type() reports there.

The internal client headers (src/client/protocol.h, src/server.h,
src/tuple.h) aren't part of the installed public API, so the program is
intended to be built against a checked-out dqlite source tree.

null_types_repro.c

/* Reproduction for: NULL values in BOOLEAN / DATETIME / DATE / TIMESTAMP
 * columns are returned with the wrong wire-level type code. */

#define _GNU_SOURCE
#include <inttypes.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/stat.h>
#include <unistd.h>

#include <sqlite3.h>

#include <dqlite.h>
#include <client/protocol.h>
#include <server.h>
#include <tuple.h>

#define BIND_ADDRESS "127.0.0.1:9011"

static const char *const CREATE_SQL =
    "CREATE TABLE t ("
    "  id INTEGER PRIMARY KEY,"
    "  int_col       INTEGER,"
    "  text_col      TEXT,"
    "  real_col      REAL,"
    "  blob_col      BLOB,"
    "  bool_col      BOOLEAN,"
    "  datetime_col  DATETIME,"
    "  date_col      DATE,"
    "  timestamp_col TIMESTAMP"
    ")";

static const char *const INSERT_SQL =
    "INSERT INTO t "
    "(int_col, text_col, real_col, blob_col, "
    " bool_col, datetime_col, date_col, timestamp_col) "
    "VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";

static const char *const SELECT_SQL =
    "SELECT int_col, text_col, real_col, blob_col, "
    "       bool_col, datetime_col, date_col, timestamp_col "
    "FROM t";

static const char *type_name(int type)
{
    switch (type) {
        case SQLITE_INTEGER:  return "INTEGER";
        case SQLITE_FLOAT:    return "FLOAT";
        case SQLITE_TEXT:     return "TEXT";
        case SQLITE_BLOB:     return "BLOB";
        case SQLITE_NULL:     return "NULL";
        case DQLITE_UNIXTIME: return "UNIXTIME";
        case DQLITE_ISO8601:  return "ISO8601";
        case DQLITE_BOOLEAN:  return "BOOLEAN";
        default:              return "?";
    }
}

static void print_column(const char *name, int type, const char *value_repr)
{
    if (type == SQLITE_NULL) {
        printf("  %-14s: NULL                              ok\n", name);
    } else {
        printf("  %-14s: %s(%d) value=%-15s  BUG: expected NULL(5)\n",
               name, type_name(type), type, value_repr);
    }
}

static int run_sqlite_reference(void)
{
    sqlite3 *db;
    sqlite3_stmt *stmt;
    int rv;

    if ((rv = sqlite3_open(":memory:", &db)) != SQLITE_OK) return -1;
    if ((rv = sqlite3_exec(db, CREATE_SQL, NULL, NULL, NULL)) != SQLITE_OK) return -1;
    if ((rv = sqlite3_exec(db, INSERT_SQL, NULL, NULL, NULL)) != SQLITE_OK) return -1;
    if ((rv = sqlite3_prepare_v2(db, SELECT_SQL, -1, &stmt, NULL)) != SQLITE_OK) return -1;
    if ((rv = sqlite3_step(stmt)) != SQLITE_ROW) return -1;

    printf("=== SQLite (sqlite3_column_type for each column) ===\n");
    for (int i = 0; i < sqlite3_column_count(stmt); i++) {
        print_column(sqlite3_column_name(stmt, i),
                     sqlite3_column_type(stmt, i), "");
    }
    printf("\n");

    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return 0;
}

static int open_db(struct client_proto *c, dqlite_node *node)
{
    int rv;
    *c = (struct client_proto){
        .connect = node->connect_func,
        .connect_arg = node->connect_func_arg,
    };
    if ((rv = clientOpen(c, node->bind_address, node->config.id)) != 0) return rv;
    if ((rv = clientSendHandshake(c, NULL)) != 0) return rv;
    if ((rv = clientSendOpen(c, "test", NULL)) != 0) return rv;
    return clientRecvDb(c, NULL);
}

static int run_dqlite_check(dqlite_node *node)
{
    struct client_proto client;
    struct rows rows = {0};
    bool done;
    uint64_t last_insert_id, rows_affected;
    int rv;

    if ((rv = open_db(&client, node)) != 0) return -1;
    if ((rv = clientSendExecSQL(&client, CREATE_SQL, NULL, 0, NULL)) != 0) return -1;
    if ((rv = clientRecvResult(&client, &last_insert_id, &rows_affected, NULL)) != 0) return -1;
    if ((rv = clientSendExecSQL(&client, INSERT_SQL, NULL, 0, NULL)) != 0) return -1;
    if ((rv = clientRecvResult(&client, &last_insert_id, &rows_affected, NULL)) != 0) return -1;
    if ((rv = clientSendQuerySQL(&client, SELECT_SQL, NULL, 0, NULL)) != 0) return -1;
    if ((rv = clientRecvRows(&client, &rows, &done, NULL)) != 0) return -1;
    if (rows.next == NULL) return -1;

    printf("=== dqlite (wire-level type tag from server) ===\n");
    for (unsigned i = 0; i < rows.column_count; i++) {
        struct value *v = &rows.next->values[i];
        char repr[128] = "";
        switch (v->type) {
            case SQLITE_INTEGER:  snprintf(repr, sizeof repr, "%" PRId64, v->integer); break;
            case DQLITE_BOOLEAN:  snprintf(repr, sizeof repr, "%" PRIu64, v->boolean); break;
            case DQLITE_UNIXTIME: snprintf(repr, sizeof repr, "%" PRId64, v->unixtime); break;
            case SQLITE_FLOAT:    snprintf(repr, sizeof repr, "%g", v->real); break;
            case SQLITE_TEXT:     snprintf(repr, sizeof repr, "\"%s\"", v->text ? v->text : ""); break;
            case DQLITE_ISO8601:  snprintf(repr, sizeof repr, "\"%s\"", v->iso8601 ? v->iso8601 : ""); break;
            default: break;
        }
        print_column(rows.column_names[i], v->type, repr);
    }
    printf("\n");

    clientCloseRows(&rows);
    clientClose(&client);
    return 0;
}

int main(void)
{
    dqlite_node *node;
    const char *dir = "/tmp/null_types_repro_data";
    int rv;

    mkdir(dir, 0755);
    if ((rv = dqlite_node_create(1, "1", dir, &node)) != 0) return 1;
    if ((rv = dqlite_node_set_bind_address(node, BIND_ADDRESS)) != 0) return 1;
    if ((rv = dqlite_node_start(node)) != 0) return 1;

    run_sqlite_reference();
    run_dqlite_check(node);

    dqlite_node_stop(node);
    dqlite_node_destroy(node);
    return 0;
}

Build & run:

From a checked-out dqlite tree (after ./configure && make && sudo make install):

gcc -I include -I src null_types_repro.c -ldqlite -lsqlite3 -o null_types_repro
./null_types_repro

Output:

=== SQLite (sqlite3_column_type for each column) ===
  int_col       : NULL                              ok
  text_col      : NULL                              ok
  real_col      : NULL                              ok
  blob_col      : NULL                              ok
  bool_col      : NULL                              ok
  datetime_col  : NULL                              ok
  date_col      : NULL                              ok
  timestamp_col : NULL                              ok

=== dqlite (wire-level type tag from server) ===
  int_col       : NULL                              ok
  text_col      : NULL                              ok
  real_col      : NULL                              ok
  blob_col      : NULL                              ok
  bool_col      : BOOLEAN(11) value=0                BUG: expected NULL(5)
  datetime_col  : ISO8601(10) value=""               BUG: expected NULL(5)
  date_col      : ISO8601(10) value=""               BUG: expected NULL(5)
  timestamp_col : ISO8601(10) value=""               BUG: expected NULL(5)

Reproduction: Go client

go-dqlite converts an empty ISO8601 string to nil at decode time.

case ISO8601:
	value := r.message.getString()
	if value == "" {
		dest[i] = nil
		break
	}

I think this is wrong (although a different issue, that should be addressed in go-dqlite).

It silently masks the DATETIME / DATE / TIMESTAMP cases — those
columns happen to look correct from Go even though the server sent the
wrong type tag.

There's no equivalent workaround for BOOLEAN, so a NULL
BOOLEAN surfaces as Go false and sql.NullBool.Valid ends up true,
indistinguishable from an actual FALSE value.

As a result, when the same test is run from Go using go-dqlite,
only the BOOLEAN case visibly fails.

go.mod

module dqlite-null-repro

go 1.21

require (
    github.com/canonical/go-dqlite v1.21.0
    github.com/mattn/go-sqlite3 v1.14.22
)

main.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"time"

	"github.com/canonical/go-dqlite/app"
	_ "github.com/mattn/go-sqlite3"
)

const createSQL = `CREATE TABLE t (
    id INTEGER PRIMARY KEY,
    int_col       INTEGER,
    text_col      TEXT,
    real_col      REAL,
    blob_col      BLOB,
    bool_col      BOOLEAN,
    datetime_col  DATETIME,
    date_col      DATE,
    timestamp_col TIMESTAMP
)`

const insertSQL = `INSERT INTO t (int_col, text_col, real_col, blob_col,
                       bool_col, datetime_col, date_col, timestamp_col)
VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)`

const selectSQL = `SELECT int_col, text_col, real_col, blob_col,
                          bool_col, datetime_col, date_col, timestamp_col
                   FROM t`

func runTest(label string, db *sql.DB) error {
	ctx := context.Background()

	if _, err := db.ExecContext(ctx, "DROP TABLE IF EXISTS t"); err != nil {
		return err
	}
	if _, err := db.ExecContext(ctx, createSQL); err != nil {
		return err
	}
	if _, err := db.ExecContext(ctx, insertSQL); err != nil {
		return err
	}

	rows, err := db.QueryContext(ctx, selectSQL)
	if err != nil {
		return err
	}
	defer rows.Close()

	cols, err := rows.Columns()
	if err != nil {
		return err
	}
	if !rows.Next() {
		return fmt.Errorf("no row returned")
	}

	values := make([]any, len(cols))
	scanArgs := make([]any, len(cols))
	for i := range values {
		scanArgs[i] = &values[i]
	}
	if err := rows.Scan(scanArgs...); err != nil {
		return err
	}

	fmt.Printf("=== %s ===\n", label)
	for i, name := range cols {
		v := values[i]
		if v == nil {
			fmt.Printf("  %-14s: NULL                       ok\n", name)
		} else {
			fmt.Printf("  %-14s: %-26s BUG: expected NULL\n", name,
				fmt.Sprintf("%T=%v", v, v))
		}
	}
	fmt.Println()
	return nil
}

func main() {
	sqliteDB, err := sql.Open("sqlite3", ":memory:")
	if err != nil { panic(err) }
	defer sqliteDB.Close()
	if err := runTest("SQLite", sqliteDB); err != nil { panic(err) }

	dir, err := os.MkdirTemp("", "dqlite-null-repro-*")
	if err != nil { panic(err) }
	defer os.RemoveAll(dir)

	ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
	defer cancel()

	node, err := app.New(dir, app.WithAddress("127.0.0.1:9011"))
	if err != nil { panic(err) }
	defer node.Close()
	if err := node.Ready(ctx); err != nil { panic(err) }

	dqliteDB, err := node.Open(ctx, "test")
	if err != nil { panic(err) }
	defer dqliteDB.Close()

	if err := runTest("dqlite", dqliteDB); err != nil { panic(err) }
}

Output against master:

=== SQLite ===
  int_col       : NULL                       ok
  text_col      : NULL                       ok
  real_col      : NULL                       ok
  blob_col      : NULL                       ok
  bool_col      : NULL                       ok
  datetime_col  : NULL                       ok
  date_col      : NULL                       ok
  timestamp_col : NULL                       ok

=== dqlite ===
  int_col       : NULL                       ok
  text_col      : NULL                       ok
  real_col      : NULL                       ok
  blob_col      : NULL                       ok
  bool_col      : bool=false                 BUG: expected NULL
  datetime_col  : NULL                       ok
  date_col      : NULL                       ok
  timestamp_col : NULL                       ok

The DATETIME/DATE/TIMESTAMP rows above only "look correct" because of
go-dqlite's empty-ISO8601-to-nil conversion linked above; the server is
still sending the wrong type tag for those columns, as the C reproducer
shows.

Reproduction: Python client

I discovered the issue while working on a Python client:

• dqlite-wire (pure
  Python wire-protocol codec),
• dqlite-client
  (async client built on top of it),
• dqlite-dbapi
  (PEP 249 wrapper) and
• sqlalchemy-dqlite
  (SQLAlchemy 2.0 dialect).

The bug shows up the same way the C reproducer above shows it.

The script needs a running dqlite node reachable at DQLITE_ADDRESS
(default 127.0.0.1:9001).

Install:

pip install \
    git+https://github.com/letsdiscodev/python-dqlite-wire@main \
    git+https://github.com/letsdiscodev/python-dqlite-client@main

null_types_repro.py

"""Connect to a dqlite node and print the per-column wire-level type tag
the server sent back for a row of NULLs.
"""

import asyncio
import os
import sqlite3

from dqliteclient import DqliteConnection

CREATE_SQL = """
CREATE TABLE t (
    id INTEGER PRIMARY KEY,
    int_col       INTEGER,
    text_col      TEXT,
    real_col      REAL,
    blob_col      BLOB,
    bool_col      BOOLEAN,
    datetime_col  DATETIME,
    date_col      DATE,
    timestamp_col TIMESTAMP
)
"""

INSERT_SQL = """
INSERT INTO t (int_col, text_col, real_col, blob_col,
               bool_col, datetime_col, date_col, timestamp_col)
VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
"""

SELECT_SQL = """
SELECT int_col, text_col, real_col, blob_col,
       bool_col, datetime_col, date_col, timestamp_col
FROM t
"""

VALUE_TYPE_NAMES = {
    1: "INTEGER", 2: "FLOAT", 3: "TEXT", 4: "BLOB", 5: "NULL",
    9: "UNIXTIME", 10: "ISO8601", 11: "BOOLEAN",
}


def print_column(name: str, wire_type: int, value_repr: str) -> None:
    if wire_type == 5:  # NULL
        print(f"  {name:<14}: NULL                              ok")
    else:
        tname = VALUE_TYPE_NAMES.get(wire_type, "?")
        print(
            f"  {name:<14}: {tname}({wire_type}) value={value_repr:<15}  "
            f"BUG: expected NULL(5)"
        )


def run_sqlite_reference() -> None:
    db = sqlite3.connect(":memory:")
    db.execute(CREATE_SQL)
    db.execute(INSERT_SQL)
    cur = db.execute(SELECT_SQL)
    row = cur.fetchone()
    print("=== SQLite (stdlib sqlite3) ===")
    for i, col in enumerate(cur.description):
        print_column(col[0], 5 if row[i] is None else 0, "")
    print()
    db.close()


async def run_dqlite_check(address: str) -> None:
    conn = DqliteConnection(address=address, database="test.db")
    await conn.connect()
    try:
        await conn.execute("DROP TABLE IF EXISTS t")
        await conn.execute(CREATE_SQL)
        await conn.execute(INSERT_SQL)
        names, _, row_types, rows = await conn.query_raw_typed(SELECT_SQL)
        if not rows:
            raise RuntimeError("no rows returned")
        print("=== dqlite (wire-level type tag from server) ===")
        for i, name in enumerate(names):
            wire_type = int(row_types[0][i])
            value = rows[0][i]
            value_repr = repr(value) if value is not None else ""
            print_column(name, wire_type, value_repr)
        print()
    finally:
        await conn.close()


def main() -> None:
    address = os.environ.get("DQLITE_ADDRESS", "127.0.0.1:9001")
    run_sqlite_reference()
    asyncio.run(run_dqlite_check(address))


if __name__ == "__main__":
    main()

Run:

DQLITE_ADDRESS=127.0.0.1:9001 python null_types_repro.py

Output:

=== SQLite (stdlib sqlite3) ===
  int_col       : NULL                              ok
  text_col      : NULL                              ok
  real_col      : NULL                              ok
  blob_col      : NULL                              ok
  bool_col      : NULL                              ok
  datetime_col  : NULL                              ok
  date_col      : NULL                              ok
  timestamp_col : NULL                              ok

=== dqlite (wire-level type tag from server) ===
  int_col       : NULL                              ok
  text_col      : NULL                              ok
  real_col      : NULL                              ok
  blob_col      : NULL                              ok
  bool_col      : BOOLEAN(11) value=False            BUG: expected NULL(5)
  datetime_col  : ISO8601(10) value=''               BUG: expected NULL(5)
  date_col      : ISO8601(10) value=''               BUG: expected NULL(5)
  timestamp_col : ISO8601(10) value=''               BUG: expected NULL(5)