
Understand it
A task report joins tasks to projects and lists unfinished tasks in active projects. Joining by a coincidentally equal name or forgetting the done filter may pass a tiny happy-path fixture. Seed unrelated, archived and completed records so each predicate has observable consequences.
SQL does not promise result ordering without ORDER BY. Sort by project name and then task ID to break ties. A LEFT JOIN and an INNER JOIN have different contracts: this report includes only tasks with an existing active project. The task title does not participate in grouping or identity. Inspect the query plan after establishing correctness, then consider an index on the actual filter/join keys. An index is not permission to change which rows appear. The independent checker rebuilds fixtures and executes your query, allowing different correct SQL formulations.
The route project is Task Ledger: first keep a reproducible source history, then expose its data through a CLI and HTTP boundary, persist it in SQL, diagnose failures and prepare a release. This lesson owns one contract in that project. Run the worked example before changing the starter and explain which input makes the tempting shortcut fail.
Terminal practice: Run sqlite3 tasks.db .read reports/pending.sql; compare the output after completing a task and archiving its project.
The compiler lab is independent of the reading question. Edit solution.rs; the app supplies a separate caller with normal, empty and adversarial inputs. A successful unchanged demonstration does not prove your implementation. After the lab, apply the same contract to the route companion project. Its README separates the on-device adapter from the desktop Cargo/TCP profile.
Read the code
pub fn report_sql() -> &'static str {
"SELECT t.id,t.title,p.name FROM tasks t JOIN projects p ON p.id=t.project_id WHERE t.done=0 AND p.active=1 ORDER BY p.name,t.id"
}
fn main() {
println!("{}", report_sql());
}
This Rust reading prints a SQL statement. The independent compiler lab executes it against real SQLite.
Practice starter
pub fn report_sql() -> &'static str {
todo!("implement the contract")
}
Try it
Return a read-only query producing columns id,title,name for unfinished tasks in active projects. Tables are tasks(id,title,done,project_id) and projects(id,name,active); join on project_id=id, order by project name then task ID. Keep the public signature. Open the compiler lab and edit solution.rs; do not edit the private verification caller.
What success looks like: The separate caller accepts the changed solution and rejects unchanged TODO, a wrong implementation and a printed success message.
Check your understanding
What guarantees stable order when project names tie?
Reveal the answer
- ORDER BY project name and task ID
- The storage order of inserted rows
- The presence of any index
Neither insertion order nor a query plan is a contract for row ordering.
Go deeper
Common misconception: The storage order of inserted rows
Specify both the inclusion rule and deterministic ordering.
- Identify the boundary: Filter, join and order by the intended relationship. Write down its accepted inputs and observable output.
- Exercise the failure: Neither insertion order nor a query plan is a contract for row ordering.
- Check the independent caller: Return a read-only query producing columns id,title,name for unfinished tasks in active projects. Tables are tasks(id,title,done,project_id) and projects(id,name,active); join on project_id=id, order by project name then task ID.
Transfer challenge: Apply this contract to Task Ledger, introduce the failure described in the lesson, and show the failing check before fixing it.
Connections
- Make Invalid Rows Impossible: Enforce domain invariants in a real database
- Bind Values Instead of SQL Syntax: Keep untrusted text in a parameter slot