Debugging My SQL Query

working on a project i call naily (or just "numbers") and i had an sql problem :(

i was trying to load a page that ran an sql query. it was this:

SELECT creation_date, state, game, result
FROM double_dodge
WHERE player_id = ?1 AND state = ?2;

the error in my terminal read: no such column: creation_date. so i went and looked at my create table. it looked fine! the relevant bits:

CREATE TABLE IF NOT EXISTS double_dodge (
	...,
	creation_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
	...,
);

so i tried not selecting the creation_date column, but then it got mad at me about the state column. i can assure you that state, too, was a real column.

i opened the database in sqlite3. like this, sqlite3 onl.db. after trying to do \d double_dodge and it telling me that it didn't know what \ meant, i ran .help like it suggested i did.

haha, no i didn't. i went to google and searched, apparently, "sqlite cli" which pulled up the Command Line Shell For SQLite page. i scrolled for a few dozen seconds and saw .schema which looked promising.

sqlite> sqlite> .schema double_dodge
CREATE TABLE double_dodge (
	(lines redacted for brevity)
	creation_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,

see, it is real. i'm not loosing it. well, i might be.

next i tried to not run that sql query at all until there was data in it. would that matter? i didn't know, i'd never used sqlite before, but i thought it wouldn't matter. mostly because that wouldn't make sense for it too, but also because i did something greatly similar elsewhere in the project.

i commented the code out and was able to use the webpage to put data into the table without issue. more evidence it was real.

i then uncommented the code and it started to panic again. and it was still poisoning the mutex that held my database connection (not great probably, the mutex, but).

does it just say that if it doesn't think the table is real? surely the table is real. we have so much evidence it is. i changed the table name.

SELECT creation_date, state, game, result FROM awoo;

Some("no such table: awoo")

okay, so it knows the table is real. it just doesn't think any of the columns are real. i tried removing my where clause.

aha! it ran.

what's wrong with my where clause? let's look at it again

SELECT creation_date, state, game, result FROM double_dodge WHERE player_id = ?1 AND state = ?2

those are all columns that exist, even if sqlite does not believe me. oh, i'm not selecting player_id. that's probably not good.

SELECT player_id, creation_date, state, game, result FROM double_dodge WHERE player_id = ?1 AND state = ?2;

i am normally good about remembering to select what i where, but i forgot this time. i wish it gave me an error that made sense, though, as the where was for player_id which was not anything it was telling me about.