---
template=post
title=Debugging My SQL Query
style=/styles/post.css
style=writing.css

published=2024-11-13 7:24pm CST

description=Tiny retelling about debugging a weird SQL problem.
art=images/awoo.png
art_alt=Some("no such table: awoo")
---

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

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

<pre><code>SELECT creation_date, state, game, result
FROM double_dodge
WHERE player_id = ?1 AND state = ?2;</code></pre>

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

<pre><code>CREATE TABLE IF NOT EXISTS double_dodge (
	...,
	creation_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
	...,
);</code></pre>

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

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

haha, no i didn't. i went to google and searched, apparently, "sqlite cli" which pulled up the
<a href="https://sqlite.org/cli.html">Command Line Shell For SQLite</a> page. i scrolled for a
few dozen seconds and saw <code>.schema</code> which looked promising.
<pre><code>sqlite> sqlite> .schema double_dodge
CREATE TABLE double_dodge (
	(lines redacted for brevity)
	creation_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,</code></pre>

see, it <i>is</i> 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 <i>(not great probably, the mutex, but)</i>.

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.
<pre><code>SELECT creation_date, state, game, result
FROM awoo;</code></pre>

<pre><code>Some("no such table: awoo")</code></pre>

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
<pre><code>SELECT creation_date, state, game, result
FROM double_dodge
WHERE player_id = ?1 AND state = ?2</code></pre>

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

<pre><code>SELECT player_id, creation_date, state, game, result
FROM double_dodge
WHERE player_id = ?1 AND state = ?2;</code></pre>

<b><i>success :)</i></b>

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
<code>player_id</code> which was not anything it was telling me about.