False NOT NULL constraint failure for table with true composite primary key #1467
Description
Background. This is a minimized version of a key logging database that collects various datasets of keystrokes from published researches on human-computer interaction. For every i-th keystroke of a session, the key log records key code, time of press, t_i, and the k-th order inter-key interval, defined as IKI_{k}(i) = t_{i} - t_{i - k} for i >= k and zero (or any non-NULL value) otherwise. As a dataset may be very large, the keystroke table should use (session_id,press_time) as the 'true' primary key, i.e. without the hidden ROWID column.
In this minimal test case, the keystroke record should be inserted successfully into the table.
It isn't.
Database structure and content after the false failure
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite> .tables
keystrokes sessions
sqlite> .schema
CREATE TABLE IF NOT EXISTS "keystrokes" ("session_id" INTEGER NOT NULL, "press_t
ime" INTEGER NOT NULL, "release_time" INTEGER NOT NULL, "iki1" INTEGER NOT NULL,
FOREIGN KEY("session_id") REFERENCES "sessions"("id"), PRIMARY KEY("session_id"
, "press_time")) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS "sessions" ("id" INTEGER PRIMARY KEY NOT NULL, "ident
ifier" TEXT NOT NULL);
sqlite> SELECT * FROM sessions;
1|test_session
sqlite> SELECT * FROM keystrokes;
sqlite> .quit
Source code
// SQLITE_ORM test
// issue 1: semantics of UNIQUE vs composite PRIMARY KEY without ROWID.
// I want this
//
// PRIMARY KEY(session_id, press_time) WITHOUT ROWID
//
// SQLITE_ORM seems to fail to do this.
// minimal import
#include <sqlite_orm/sqlite_orm.h>
#include <iostream>
struct MinimalSession {
int id;
std::string identifier;
};
struct MinimalKeystroke {
int session_id;
int press_time;
int iki1;
};
auto initMinimalStorage() {
using namespace sqlite_orm;
return make_storage("test_minimal.db",
make_table("sessions",
make_column("id", &MinimalSession::id, primary_key()),
make_column("identifier", &MinimalSession::identifier)
),
make_table("keystrokes",
make_column("session_id", &MinimalKeystroke::session_id),
make_column("press_time", &MinimalKeystroke::press_time),
make_column("iki1", &MinimalKeystroke::iki1),
foreign_key(&MinimalKeystroke::session_id).references(&MinimalSession::id),
primary_key(&MinimalKeystroke::session_id, &MinimalKeystroke::press_time)
).without_rowid()
);
}
void test_minimal_import() {
auto storage = initMinimalStorage();
storage.sync_schema();
storage.remove_all<MinimalKeystroke>();
storage.remove_all<MinimalSession>();
// Create one session
MinimalSession session{-1, "test_session"};
int session_id = storage.insert(session);
std::cout << "Created session with id: " << session_id << std::endl;
// Insert one keystroke
MinimalKeystroke keystroke{session_id, 100, 50};
storage.insert(keystroke);
std::cout << "Minimal test PASSED" << std::endl;
}
int main() {
std::cout << "=== SQLite ORM Bug Investigation ===" << std::endl;
std::cout << "Testing PRIMARY KEY + WITHOUT ROWID behavior" << std::endl;
try {
test_minimal_import();
std::cout << "SUCCESS: Test completed without exceptions" << std::endl;
return 0;
}
catch (const std::exception& e) {
std::cout << "\n!!! DIAGNOSIS REPORT !!!" << std::endl;
std::cout << "EXCEPTION: " << e.what() << std::endl;
std::cout << "\nROOT CAUSE ANALYSIS:" << std::endl;
std::cout << "1. Schema: PRIMARY KEY(session_id, press_time) + WITHOUT ROWID" << std::endl;
std::cout << "2. Session was successfully inserted with valid ID" << std::endl;
std::cout << "3. Keystroke object created with valid session_id" << std::endl;
std::cout << "4. BUT: NOT NULL constraint failed on keystrokes.session_id" << std::endl;
std::cout << "\nCONCLUSION: sqlite_orm bug in value binding for composite PK + WITHOUT ROWID" << std::endl;
std::cout << "The library is not properly passing the session_id value to SQLite." << std::endl;
return 1;
}
catch (...) {
std::cout << "\n!!! UNKNOWN EXCEPTION !!!" << std::endl;
std::cout << "Unexpected exception type caught" << std::endl;
return 2;
}
}