Run the query where you know the answer
Checking SQL by running it means trying a model's query on a small dataset whose answer you worked out by hand. Reading catches some faults; running catches the ones that look fine on the page.
Module 43 checks a person's query by its result rows. This lesson checks a model's candidates, and asks whether the data can tell right from wrong.
By the end of this lesson
Note
I can check a model's query by running it on data with a known answer.
Key words
| Word | What it means |
|---|---|
| Known answer | the result you worked out by hand |
| Candidate | one query offered as an answer |
| Result rows | what a query gives back |
| Fixture | small fixed data kept for checking |
| Tell apart | give different results for right and wrong |
The cells students fill in are left blank in this preview.
Compare rows with a known answer
Work out the answer by hand on a small fixture. Nine loans are few enough to count on paper. Then run each candidate and compare its rows with yours.
There are three verdicts. Pass means the rows match. Wrong rows means it ran and gave something else. Error means SQLite refused it.
Worked example: known answers for the library
| Question | Worked by hand | Known answer |
|---|---|---|
| Different members in month 3 | Loans 1 to 4: Asha, Asha, Ben, Chen | 3 |
| Books still out | Loans 3, 5, 6, 8 and 9 have returned = 0 | 5 |
| Titles member 2 borrowed | Loans 3, 6 and 8: books 3, 4 and 5 | A Map of Rain, Quiet Engines, Seven Kettles |
The cells students fill in are left blank in this preview.
Order only matters when the question asks
A list of titles in any order answers "which titles". Compare sorted copies of both lists, so a different order still passes.
If the question says "newest first", the order is part of the answer. Then compare the rows exactly as they come.
The data must tell right from wrong
Ask how many loans from month 5 are still out. Both loans from month 5 are out, so a query with no returned filter also gives 2.
That fixture cannot tell the two apart. Add one row that separates them, such as a returned loan in month 5, and check again.
A common mistake
Warning
Many people think a candidate that passes on test data is correct. It is only correct for that data. Check that the fixture gives a different answer for the likely wrong query, then trust the pass.
Write the model helper, part 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const base = process.env.AI_URL || \"http://ai:4000\";' 'function count(data, name) {' ' return data.usage && typeof data.usage[name] === \"number\" ? data.usage[name] : null;' '}' '// A reply may wrap the query in a fenced block: keep what is inside it.' 'function sqlOf(text) {' ' const parts = text.split(String.fromCharCode(96).repeat(3));' ' const inner = (parts.length > 2 ? parts[1] : text).trim();' ' return inner.toLowerCase().startsWith(\"sql\") ? inner.slice(3).trim() : inner;' '}' > model.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the model helper, part 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function ask(messages, sample, max) {' ' try {' ' const res = await fetch(base + \"/v1/chat/completions\", {' ' method: \"POST\",' ' headers: { \"content-type\": \"application/json\", authorization: \"Bearer lab\" },' ' body: JSON.stringify({ model: \"lab\", messages: messages, max_tokens: max || 100 }),' ' signal: AbortSignal.timeout(30000),' ' });' ' if (res.ok === false) throw new Error(\"status \" + res.status);' ' const data = await res.json();' ' const text = data.choices[0].message.content;' ' return { text, inTokens: count(data, \"prompt_tokens\"), outTokens: count(data, \"completion_tokens\"), recorded: false };' ' } catch (error) {' ' console.log(\"Recorded sample: no model answered (\" + error.message + \").\");' ' return { text: sample, inTokens: null, outTokens: null, recorded: true };' ' }' '}' 'module.exports = { ask, sqlOf };' >> model.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the small library database, part 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const { DatabaseSync } = require(\"node:sqlite\");' '// An invented library: six books, four members, nine loans.' 'const BOOKS = [[1, \"The Salt Road\", 2019], [2, \"Glass Harbour\", 2021], [3, \"A Map of Rain\", 2015],' ' [4, \"Quiet Engines\", 2023], [5, \"Seven Kettles\", 2018], [6, \"North of Noon\", 2022]];' 'const MEMBERS = [[1, \"Asha\"], [2, \"Ben\"], [3, \"Chen\"], [4, \"Dara\"]];' '// id, book_id, member_id, month, returned (1 back, 0 still out)' 'const LOANS = [[1, 1, 1, 3, 1], [2, 2, 1, 3, 1], [3, 3, 2, 3, 0], [4, 1, 3, 3, 1], [5, 1, 1, 4, 0],' ' [6, 4, 2, 4, 0], [7, 2, 3, 4, 1], [8, 5, 2, 5, 0], [9, 2, 4, 5, 0]];' > library.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the small library database, part 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'function put(db, table, rows) {' ' const marks = rows[0].map(function () { return \"?\"; }).join(\", \");' ' const insert = db.prepare(\"INSERT INTO \" + table + \" VALUES (\" + marks + \")\");' ' rows.forEach(function (row) { insert.run(...row); });' '}' 'function open(file) {' ' const db = new DatabaseSync(file || \":memory:\");' ' db.exec(\"CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, year INTEGER)\");' ' db.exec(\"CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT)\");' ' db.exec(\"CREATE TABLE loans (id INTEGER PRIMARY KEY, book_id INTEGER REFERENCES books(id), \"' ' + \"member_id INTEGER REFERENCES members(id), month INTEGER, returned INTEGER)\");' ' put(db, \"books\", BOOKS);' ' put(db, \"members\", MEMBERS);' ' put(db, \"loans\", LOANS);' ' return db;' '}' 'module.exports = { open };' >> library.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the checking script, part 1
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const { ask, sqlOf } = require(\"./model\");' 'const { open } = require(\"./library\");' 'const db = open();' 'function rowsOf(sql) {' ' try {' ' return db.prepare(sql).all().map(function (r) { return JSON.stringify(Object.values(r)); }).sort();' ' } catch (error) { return null; }' '}' 'function check(sql, want) {' ' const got = rowsOf(sql);' ' if (got === null) return \"error\";' ' const wanted = want.map(function (r) { return JSON.stringify(r); }).sort();' ' return got.join(\"|\") === wanted.join(\"|\") ? \"pass\" : \"wrong rows \" + got.join(\" \");' '}' > check.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the checking script, part 2
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'const L = \"SELECT COUNT(*) FROM loans WHERE \";' 'const T = \"SELECT b.title FROM loans l JOIN books b ON \";' 'const cases = [' ' { want: [[3]], sql: [\"SELECT COUNT(member_id) FROM loans WHERE month = 3\",' ' \"SELECT COUNT(DISTINCT member_id) FROM loans WHERE month = 3\",' ' \"SELECT COUNT(DISTINCT member) FROM loans WHERE month = 3\"] },' ' { want: [[5]], sql: [\"SELECT COUNT(*) FROM loans\", L + \"returned = 0\", L + \"returned = 1\"] },' ' { want: [[\"A Map of Rain\"], [\"Quiet Engines\"], [\"Seven Kettles\"]],' ' sql: [T + \"l.id = b.id WHERE l.member_id = 2\", T + \"l.book_id = b.id WHERE l.member_id = 2\",' ' T + \"l.book_id = b.id WHERE l.member_id = 2 ORDER BY b.title DESC\"] },' ' { want: [[2]], sql: [L + \"month = 5 AND returned = 0\", L + \"month = 5\"] },' '];' >> check.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the checking script, part 3
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function main() {' ' cases.forEach(function (c, i) {' ' c.sql.forEach(function (sql, j) { console.log((i + 1) + \"abc\"[j] + \": \" + check(sql, c.want)); });' ' });' ' db.exec(\"UPDATE loans SET returned = 1 WHERE id = 8\");' ' console.log(\"after loan 8 comes back, the answer to 4 is 1:\");' ' cases[3].sql.forEach(function (sql, j) { console.log(\"4\" + \"ab\"[j] + \": \" + check(sql, [[1]])); });' ' db.exec(\"UPDATE loans SET returned = 0 WHERE id = 8\");' ' await live();' '}' >> check.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Write the checking script, part 4
docker exec -w /home/node/project agent sh -c "printf '%s\n' 'async function live() {' ' const q = \"loans(id, book_id, member_id, month, returned)\nQuestion: How many different members borrowed a book in month 3?\";' ' const sample = \"SELECT COUNT(DISTINCT member_id) FROM loans WHERE month = 3;\";' ' const reply = await ask([{ role: \"system\", content: \"Answer with one SQLite SELECT query and nothing else.\" },' ' { role: \"user\", content: q }], sample, 120);' ' const sql = sqlOf(reply.text);' ' console.log(\"model wrote: \" + sql);' ' console.log(\" checked on known data: \" + check(sql, [[3]]));' '}' 'main();' >> check.js"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Run every candidate on known data
docker exec -w /home/node/project agent node --experimental-sqlite --no-warnings check.jsShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
What the Run prints
| Line | What it shows |
|---|---|
| 1a: wrong rows [4] | Counted Asha twice |
| 1c: error | No column called member |
| 2c: wrong rows [4] | Counted the books that came back |
| 3a: wrong rows ["A Map of Rain"] ["North of Noon"] | The id to id join |
| 3c: pass | A different order still passes |
| 4a: pass and 4b: pass | The data cannot tell them apart |
| 4b: wrong rows [2], after loan 8 comes back | One new row tells them apart |
The cells students fill in are left blank in this preview.