Skip to content
Interface language: English
Audit / Signal

AI writes SQL. The query runs, but what did it actually count?

Check joins, the unit being counted and small control cases before accepting an AI-assisted SQL result.

Article language: English

An illustration of a woman looking at an architectural model through a large lens.
In this article

A hypothetical service report shows four requests. The manager remembers three. The AI assistant displays a successfully executed query, so for a moment the conversation turns to unreliable human memory. Only when the data is examined does the team discover that the system counted rows joined to files. An extra photograph of a fault was promoted to an extra case.

SQL is a language for querying a database. An assistant can help write it, but successful execution primarily means the database could process the command. It does not establish that the command answered the team's question. When accepting the result, check what it means before the number enters a report and starts a life of its own in three presentations.

Three cases, four files, several possible numbers

Take a small example of our own: cases A, B and C were created in the period being examined. A has three files attached, B has none and C has one. The question is how many cases arrived. The answer is three. You can establish it without AI or knowledge of syntax by looking at the three identifiers.

A database may store information about cases and files in separate tables. A join connects them according to an agreed relationship, such as a file belonging to a case. PostgreSQL's documentation describes an INNER JOIN as producing a row for each matching combination. One case can therefore appear several times in the result.

In our data, a join containing only matching pairs produces three rows for A and one for C. B disappears because it has no file. Four rows do not mean four cases. Moreover, removing duplicate case identifiers from that result yields two cases. We have recovered a single A, but we have not restored the missing B.

This is a good moment to stop automatically issuing another instruction to "fix duplicates". First name the two independent problems: cases with several files are repeated, and cases without a file are omitted. One change can remove the first while leaving the second. The result will still be a plausible whole number, just an answer to a different question.

If we use a join that also keeps a case without a match, the example has five rows: three for A, one for B and one for C. B's row has no file identifier. The number of rows is again not the number of cases. You can count the example by hand, listing each pair before choosing an aggregation method, meaning how multiple values are combined into one result. The numbers for this data set were also checked locally in SQLite. That checks the illustration, not a particular assistant or a PostgreSQL deployment.

PostgreSQL's description of count distinguishes counting all rows with count(*) from counting non-NULL values of a specified expression. Here NULL means a missing value in the database. In this example an expression referring to the file yields four, while all rows yield five. Neither answer automatically replaces the number of cases.

There is no magic fix to memorise. Selecting a unique case identifier may be appropriate for this question, but first you need to know what identifies one case in the actual data. A customer name, a message subject or a shortened number shown on screen may not serve that role. Two different people can report a fault under the same title.

A similar error appears when analysing a spreadsheet with AI (in Polish) (in Polish). It is easy to calculate the correct sum of the wrong rows. Here the extra rows may be created by the query itself, so the original cases table can look entirely innocent.

Agree on the question before accepting the query

"Count October's requests" leaves several decisions open. Does it mean a case was created, a first message received or support completed? Is a reopened case a new one? Do we count test requests? The fact that a column is called date or status cannot answer those questions on its own.

Write the definition in one plain sentence. For example: one case has one stable identifier; include its creation date within the agreed period regardless of later closure; exclude records marked as tests. This is a proposal for our scenario, not a universal definition of a request. The process owner must accept it before the result is assessed.

Then connect the definition to the data. Which table contains the case, which field specifies creation and where is the test marker? Ask someone who knows the data model to confirm the relationships. When table names are similar, an assistant may select change history instead of the current list. The report will then count events in the life of a case.

The relationship description matters as much as the field name. Does a file belong to one case? Can a request have several people responsible for it? Can the history contain several entries for the same state? Understanding such relationships helps you read the result before aggregation. The broader question of the direction and basis of relationships is covered in checking a knowledge graph used by AI (in Polish) (in Polish).

In its documentation for generating queries with Gemini in BigQuery, Google recommends validating output before use and warns about responses that appear plausible but are incorrect. In practice, verification must cover the agreed meaning of the data. A query explanation generated by the same assistant is not an independent check.

If the question is ambiguous, clarify it before you start correcting the SQL. Changing the definition after seeing the number makes it harder to tell whether an error was fixed or the report was fitted to an expectation. Preserve the original question, the agreed interpretation and the version of the query that was checked. A later reader can then reconstruct what the number means.

A filter can change the meaning of a correct join

Suppose the report should cover all cases, but the assistant adds a condition about attachment format. Case B, with no file, can disappear even when a join preserving unmatched cases is used. Ask someone who knows SQL to inspect where the condition was applied. PostgreSQL's join documentation shows that, for an outer join, a restriction in ON and one in WHERE can produce different results.

Aeneia conceptual photography

The process owner should decide whether the question concerns all cases or only those with a particular file. Both are sensible analyses, but they need different names. Do not approve a changed definition just because the report stopped displaying duplicates after a fix. Removing some cases can hide the problem instead of solving it.

Also check which event the filtered date belongs to. A file uploaded in November may belong to a case created in October. If you are asking for the case creation date, the later attachment should not by itself move the case to another month. Prepare that example in the test set instead of relying on the column name in the assistant's explanation.

For the boundary of the period, write down the expected hours and the time zone used by the data. "Through 31 October" can be ambiguous when the field also stores a time. Include late evening on the last day and the start of the next month in the check. The result should match the agreed period, not an accidental interpretation of "through".

A small data set should expose the difference

A control set should contain cases that force an incorrect query to produce a different result. Three cases with exactly one file each will not reveal this problem. There will be as many joined rows as cases. The trial will pass because the data gave it no opportunity to recognise the error.

In our example A, with three files, and B, with none, create the difference. Also add two cases with the same title if someone proposes counting titles. For the period under examination, prepare cases just before the start, in the middle and at the end boundary. Write the expected result before running the query being assessed, not from its answer.

You can prepare the data by hand. If you use a generator, check relationship consistency in synthetic data (in Polish) (in Polish). A file pointing to a nonexistent case or two supposedly unique entities sharing an identifier will change what the trial examines. The test should assess the query under known conditions, not solve an accidentally generated puzzle.

After obtaining the correct result, change one thing that should not affect it. Attach another file to A. The number of cases remains three. If the report rises, you know it still depends on the number of attachments. Then add a new case without a file. This time the result should rise by one. The two changes test different parts of the meaning.

Add a record with a missing test marker if that state occurs in the data. Agree whether it should be excluded, included or sent for clarification. A missing marker is not automatic proof that a case is real or a test. A written rule makes it possible to assess the result even when the database contains less carefully prepared data than the demonstration.

Also review the list of identifiers making up the result. The same total can hide two errors that happen to cancel out: one omitted case and one counted twice. Only checking the members of the set reveals that. A small acceptance example has the advantage that every record can be explained.

Ask the provider to show the stages if the result still does not agree. A list of cases before the join, after the join and after the filter reveals where the discrepancy arose faster than another elaborate AI comment. Not every reader needs to see the full syntax. The person checking does need a way to establish where the specific cases came from.

Aeneia Audit: Let us check whether the way AI is used for analysis answers the team's real questions. We will organise acceptance criteria, control cases and evidence of a correct result so that executing a query does not end the assessment. Discuss acceptance of AI-assisted analyses.

A number still needs a description after checking

After accepting the query, preserve its version, the case definition, the data scope and the expected trial results. Record which copy of the data was checked and when it came from. If new cases were added between the check and a comparison with the report, a difference in numbers alone does not prove a query error. First relate both answers to the same data set.

If the database model or the way test requests are marked later changes, someone should know which assumption needs checking again. It is not enough to leave a note saying "SQL checked" without stating what that assessment refers to.

Separate the query result from an explanation added by the model. If the table contains three cases but the summary says four customers, checking the SQL does not approve that sentence. The unit has changed. It is the same kind of discrepancy as well-formed JSON data with the wrong meaning (in Polish) (in Polish): a technically accepted result may describe something different from what was agreed.

The metric name should retain the definition. "Cases created in October" is more precise than "service activity" if only new cases were counted. Do not extend the result to service quality, number of people or workload without additional data. One measure cannot answer every question the manager has.

Include checking time when assessing the tool's benefit. When measuring work with AI (in Polish) (in Polish), also record clarification of the question, query corrections and result verification. Spending less time writing SQL can still be useful, but it should not erase the work needed to accept the report.

At Aeneia Audit, assessment of this use concerns the process of working with AI and the evidence needed to accept it. Depending on the solution, analysis of the database itself should involve the person responsible for the data. A good result specifies which analyses the team can accept after the agreed checks and which still need further scrutiny.

Three cases, with a clear scope, can enter the report in our example. A's three photographs remain three photographs of A. If adding a fourth suddenly increases the number of requests, return to the query before the manager starts planning another support position.

Aeneia / Aeneia editorial

Editorial article. Source links are included in the text.

About Aeneia

Brands we have worked with

  • L’Oréal
  • Garnier
  • Netflix
  • Carrefour
  • Warner Bros. Discovery
  • Biedronka
  • HBO Max
  • Wolt
  • Żabka
  • COSRX
  • Embassy of China in Poland
  • Mixa
Aeneia / Initium

From knowledge to a decision

Tell us what you want to put in order. We will agree on where to start.

Contact Aeneia

We partner with and use solutions from:

  • Google
  • OpenAI
  • Microsoft
  • Stripe
  • Anthropic
  • Linear