Skip to content
The AI Agent Factory

19.3 Reading SQL: predict, then run

Approved by
Panaversity ·
23 min read

About twenty minutes. Read 19.2 first. This concept teaches SQL the way Chapters 13 and 14 taught TypeScript and Python: you read what an agent wrote, predict what it will do, then run it and compare. You never write SQL from memory. You learn to recognize what you see.

In everyday life. A new bookkeeper is handed a pile of filled-in forms. Nobody expects her to design the forms. Her job is to read each one, say what it will do once it is filed, and spot the one that would do damage. That might be the transfer with no account number, or the "pay all" box ticked by mistake. Reading forms is a smaller skill than designing them, and it is the one that catches mistakes.

What you will learn. How to read a query an agent wrote: predict what it returns, run it, and compare, with the five traps that cause real bugs.

Why it matters. A query can look right and still change every row, or find nothing, with no error. A prediction written down before the run is how you notice.

One question, start to finish. Luis asks the builder: "Which of our open invoices are the largest?" The builder asks a coding agent, which writes a query and shows it before running it. The builder reads it, writes down a guess ("three rows, the largest first, and the top one is over $4,000"), and only then lets it run. The guess was right. The builder now knows the query does what Luis asked, and can say why. That is the whole method of this concept, five times, with the traps that cause real bugs, and a sixth block, on reading errors, in field guide F1.

How does predict, then run work for SQL?

Chapter 13 called this method PRIMM-AI+: Predict, Run, Investigate, Modify, Make, with the coding agent as your reading partner. For SQL it works like this:

StepWhat you doWhat the agent does
PredictRead the query and write down what it will return: how many rows, and roughly what is in themShows the query before it runs, and waits
RunRun it for one company, read-only as ap_reader unless the block says otherwiseRuns it and shows the real output
InvestigateCompare your guess with the output. Where they differ, your picture of the query is wrong somewhereAnswers "explain line 3" or "why did this row come back?"
ModifyChange one thing, a condition or a column, and predict againMakes the change you ask for, and nothing else
MakeIn this book, the agent writes the SQL. Your "make" is the plain-words request it starts fromWrites the query from your request

Every output below was produced by running the block against the lab's data, with Cedar Ridge as the company. The blocks are in the lab download, in concept-blocks/sql/, and Lab 19, Task 4b, runs each one with you, after the data is loaded. Every block is always undone at the end, so nothing in the database changes. Write your prediction down before you read the output. The block does not teach you. Your wrong prediction does.

Which four words do the blocks use?

The lab's real tables hold several companies' rows side by side, so every row has a tenant_id column saying whose it is, such as org_cedar for Cedar Ridge Supply. Each block runs with one company set: the database is told "this request is for Cedar Ridge". Then it shows only Cedar Ridge's rows (19.8 and 19.9 explain how). Each block runs as a role, a database login with its own rights: ap_reader may only read, and ap_loader, the loader, may also change rows (19.8). And each block runs inside one transaction, a group of statements that is kept or undone together (19.9). Here it is always undone. The runner also sets a marker (a savepoint) before each block, so a block that fails is undone back to its marker, and the next block still runs. SQL does not care about capital letters in its own words, so COUNT and count are the same.

Block 1. Filter, sort, take the first few

SELECT invoice_number, vendor_id, amount_cents
  FROM invoices
 WHERE tenant_id = 'org_cedar' AND status = 'open'
 ORDER BY amount_cents DESC
 LIMIT 3;

SELECT '1290000' < '61875' AS compared_as_text,
        1290000  <  61875  AS compared_as_numbers;

Predict: how many rows does the first query return, and in what order? In the second, which comparison is true? (A SELECT can compute a value without reading any table, so it has no FROM.)

invoice_number | vendor_id | amount_cents
---------------+-----------+-------------
INV-6201       | V001      | 412075
INV-6210       | V001      | 325000
INV-6204       | V004      | 268800
(3 rows)

compared_as_text | compared_as_numbers
-----------------+--------------------
true             | false

In plain words: keep Cedar Ridge's open invoices, sort them by amount, largest first (DESC means descending, from high to low), and keep the first three. AS gives a column in the answer a name of your choice. The second query is Chapter 13's past-due report again, the one that sorted invoice amounts as text and put a $12,900.00 invoice near the bottom. As text, "1290000" comes before "61875", because text is compared one character at a time, and "1" comes before "6". As numbers, 1,290,000 is the larger. That is why the AP tables keep money in a number column, amount_cents bigint, never in text (19.4).

Block 1's query written in a box, one clause per line: SELECT invoice_number, vendor_id, amount_cents, FROM invoices, WHERE tenant_id is org_cedar and status is open, ORDER BY amount_cents DESC, LIMIT 3. Beside each line, a numbered circle giving the order the database applies it: FROM 1, in red-orange, WHERE 2, SELECT 3, ORDER BY 4, LIMIT 5. Beside each, what it does: start from this table, keep only the rows that pass the test, keep only these columns, sort them largest first, keep the first three. Below: written order SELECT, FROM, WHERE, ORDER BY, LIMIT, and applied order FROM, WHERE, SELECT, ORDER BY, LIMIT. A note: read a query in the applied order, which table, which rows, which columns, in what order, how many.

Figure 19.6. A query is written starting with SELECT, but the database starts with FROM. Read it in the order it is applied.

Block 2. A join gives one row for every match

SELECT count(*) AS vendors FROM vendors WHERE tenant_id = 'org_cedar';

SELECT count(*) AS rows_after_join
  FROM vendors v
  JOIN invoices i ON i.tenant_id = v.tenant_id AND i.vendor_id = v.vendor_id
 WHERE v.tenant_id = 'org_cedar';

Predict: Cedar Ridge has 9 vendors and 20 invoices. How many rows does the join return?

vendors
-------
9

rows_after_join
---------------
20

In plain words: a join (19.2) gives one row for every pair that matches. So a vendor with three invoices appears three times, and a vendor with none does not appear at all. Cedar Ridge has 20 invoices, so the join has 20 rows. v and i are short names for the two tables, given in the FROM and JOIN lines. The trap: a report that "counts vendors" after a join is really counting invoices. When an agent's answer is bigger than you expected, ask it: "Which join multiplied the rows?"

Three columns. Left, three of Cedar Ridge's nine vendors: V001 Treasure Valley Lumber, V002 Snake River Fasteners, V103 Idaho Power, and a dashed, grayed V999, an imagined vendor with no invoices, labeled no match, so it would not appear at all. Middle, six invoices, each with its vendor id, and lines from each vendor to its invoices: three from V001, two from V002, one from V103. Right, the result, one row per match: Treasure Valley Lumber three times, with INV-6201, INV-6210 and INV-6218, then Snake River Fasteners twice and Idaho Power once. A note: V001 appears 3 times, once per invoice. Below: Cedar Ridge's 9 vendors and 20 invoices give 20 rows, and counting after a join counts matches, not vendors.

Figure 19.7. A join gives one row for every match, so 9 vendors and 20 invoices give 20 rows.

Block 3. Count rows, or count values

SELECT count(*) AS invoices, count(due_date) AS with_a_due_date
  FROM invoices
 WHERE tenant_id = 'org_cedar';

SELECT status, count(*) AS invoices, sum(amount_cents) AS total_cents
  FROM invoices
 WHERE tenant_id = 'org_cedar'
 GROUP BY status
 ORDER BY status;

Predict: will the two counts in the first query be the same? How many rows does the second return?

invoices | with_a_due_date
---------+----------------
20       | 18

status | invoices | total_cents
-------+----------+------------
open   | 16       | 2132900
paid   | 4        | 357700

In plain words: count(*) counts rows. count(due_date) counts only the rows where due_date has a value, and two of Cedar Ridge's invoices have none yet. GROUP BY status puts the rows in piles, one per status, and sum adds up each pile. Two numbers that should be equal and are not are often the first sign of missing data.

Block 4. NULL means "no value", and nothing equals it

SELECT po_number FROM purchase_orders
 WHERE tenant_id = 'org_cedar' AND invoice_number = NULL;

SELECT po_number FROM purchase_orders
 WHERE tenant_id = 'org_cedar' AND invoice_number IS NULL;

Predict: purchase_orders holds each purchase order, and its invoice_number names the invoice it matched, if any. The two queries differ by one word. Do they return the same rows?

po_number
---------
(0 rows)

po_number
---------
PO-3108
(1 row)

In plain words: NULL is how a database writes "no value here", like a blank cell. A purchase order with no invoice yet has NULL in invoice_number. A comparison with NULL is never true, not even NULL = NULL, so the first query finds nothing, with no error. The right test is IS NULL, or IS NOT NULL. If an agent's query uses = NULL, it is wrong, every time.

Two halves. Left, GROUP BY status: Cedar Ridge's 20 invoices drawn as slips in two piles, 16 open and 4 paid, with count of 16 and a sum of $21,329.00 under open, and a count of 4 and a sum of $3,577.00 under paid, one row per pile in the answer. Right, NULL, no value here: four invoices with their due dates, two of them blank and marked NULL, INV-6210 and INV-6219. Below, four lines with their results: count(*) gives 20, counts rows. count(due_date) gives 18, skips the 2 blanks. due_date = NULL gives 0 rows, nothing equals NULL. due_date IS NULL gives 2 rows, the right test.

Figure 19.8. GROUP BY makes one pile per value. NULL is a blank that counts skip and = never matches.

Block 5. The most dangerous line an agent can write

This block runs as the loader, the role that may change rows, and is undone at the end.

SELECT count(*) AS rows_it_would_touch
  FROM vendors WHERE tenant_id = 'org_cedar' AND vendor_id = 'V003';

UPDATE vendors SET terms = 'Net 45'
 WHERE tenant_id = 'org_cedar' AND vendor_id = 'V003';

UPDATE vendors SET terms = 'Net 45';

Predict: how many rows does each UPDATE change?

rows_it_would_touch
-------------------
1

UPDATE 1
UPDATE 9

In plain words: the first UPDATE changes one vendor's terms, the one its WHERE names. The second has no WHERE, so it changes every row it can reach. Row-level security (19.8) kept the damage inside Cedar Ridge, but all 9 of Cedar Ridge's vendors now say Net 45, and nothing warned anyone. The same is true of DELETE with no WHERE. Two habits follow. Before any UPDATE or DELETE, ask the agent to run the same WHERE as a SELECT count(*), as the first query here does, and check the count is the one you expect. And treat an UPDATE or DELETE with no WHERE as wrong until someone shows you why it is right.

How do you read an error?

When the database refuses something, read three things in order: the code, the message and the detail. Field guide F1 walks through the three refusals this chapter meets, with Block 6 to run, and a figure.

Where does SQL surprise you?

Only the differences that cause real bugs, as in Chapter 13:

What you might expectWhat SQL does
Numbers stored as text sort as numbersThey sort as text: "1290000" comes before "61875"
A join keeps one row per item on the leftIt keeps one row per match, so rows multiply or vanish
count(column) counts rowsIt counts values, and skips NULL
= NULL finds empty cellsIt finds nothing. Use IS NULL
An UPDATE asks "which rows?"With no WHERE, it changes every row it can reach
One = sets a value, as in TypeScriptOnly after SET. Inside WHERE, a single = compares, as === does. SQL has no ==

What do you ask the agent about any query?

Your reading partner answers these in a sentence each, and you check each answer against a run:

  • "Which rows does this touch, and how many?" For a change, "show me the SELECT count(*) first."
  • "Which company is it limited to, and where does that company come from?" (19.9)
  • "What would make it return nothing?" and "What would make it return too much?"
  • "Explain line 3."

Nothing the agent says is accepted on its word. It is accepted when a run, a test or a count agrees.

At work. In Lab 19, Task 4b, after the companies are loaded, the builder runs 19.2's three queries, these five blocks and field guide F1's Block 6 with Claude Code, predicting each answer first. Then comes the Modify step: Claude Code changes Block 4's IS NULL to IS NOT NULL, and Block 1's LIMIT 3 to LIMIT 5, and the builder predicts both answers before they run.

The operating rule: read every query before it runs, and predict its answer. Check counts before any change, treat a missing WHERE and a = NULL as wrong, read an error by its code first, and accept the agent's explanation only when a run agrees.

A worksheet drawn as four columns: the query, your guess, the real output, and a mark. Row one: an UPDATE with a WHERE naming one vendor, predicted 1 row, real UPDATE 1, a green tick. Row two: the same UPDATE with no WHERE, predicted 1 row, real UPDATE 9, a red cross, and a note saying every Cedar Ridge vendor changed. Row three: WHERE invoice_number = NULL, predicted 1 row, real 0 rows, a red cross, and a note saying nothing equals NULL, use IS NULL. Row four: a join of 9 vendors to their invoices, predicted 9 rows, real 20, a red cross, and a note saying one row per match. Beside the worksheet, the five steps of the method in a column: predict, run, investigate, modify, make. Below: write the guess down first, and where it differs from the output, the query does something you did not expect.

Figure 19.9. Predict, then run. Where your guess and the output differ, the query does something you did not expect.

Check yourself

Question 1 / 8

0 answered

In 19.3's method, what do you do before a query an agent wrote is run?

Owner
Panaversity

On this page