Before accepting AI-written SQL that combines reports, decide what repeated rows mean. Two identical-looking output rows may represent separate events that both count. Two different-looking rows may be competing versions of one event. Choosing between UNION and UNION ALL cannot settle that business distinction by itself.
Ask the assistant to preserve the event identity and explain the intended number of occurrences before removing duplicates. Then test equal-valued events, an exact replay and a conflicting version separately. A query can execute correctly according to SQL semantics while producing the wrong business report.
This guide uses invented records. We executed the small cases locally with Python’s SQLite interface, using SQLite 3.53.1. We did not test an AI SQL assistant, a PostgreSQL deployment or production data. The review workflow is our editorial proposal.
Output equality is narrower than business identity
The PostgreSQL 18 documentation specifies that UNION eliminates duplicate result rows, while UNION ALL retains them. The inputs must return the same number of columns with compatible corresponding types. Neither operator guarantees the order in which rows are returned.
The selected columns therefore matter. If a query selects only a project and a quantity, separate events with the same project and quantity become equal output rows. Keeping an event identifier in the selection can preserve a distinction that disappears when that identifier is omitted.
Hans-Jürgen Schönig’s August 2013 CYBERTEC tutorial illustrates the difference between appending results and eliminating duplicates. It carries a September 2023 update pointing to further reading. This is historical practitioner instruction from a database consultancy, not evidence about the reliability of current AI-generated queries.
For review, translate “combine these exports and remove duplicates” into two questions: which source records describe the same event, and how many times should each event contribute? Do not let a convenient SQL operator become an unstated answer.
Three events can look like one output row
Imagine two extracts from one fictional event registry. The registry assigns globally unique event IDs. The two extracts are initially disjoint; their labels describe the batches, not separate identity namespaces. Every quantity is a known integer in the same unit.
| Extract | Event ID | Project | Units |
|---|---|---|---|
| East | E01 | P | 8 |
| East | E02 | P | 8 |
| West | W01 | P | 8 |
The report should count all three events, totaling 24 units. The following query instead returns one row, (P, 8), in our local fixture:
WITH east(event_id, project, units) AS (
VALUES ('E01', 'P', 8), ('E02', 'P', 8)
), west(event_id, project, units) AS (
VALUES ('W01', 'P', 8)
)
SELECT project, units FROM east
UNION
SELECT project, units FROM west;The engine has followed the request expressed in SQL. All three selected rows are identical, including the two originating in East. The problem is that the selected values no longer distinguish the events the business intended to count.
Replacing UNION with UNION ALL returned three occurrences of (P, 8), totaling 24. Selecting event_id, project, units with UNION also retained the three distinct events in this fixture. These observations are deliberately limited to the stated records and SQL subset.
Neither repair is a universal policy. The first preserves every input occurrence. The second removes only rows equal across all selected fields. Each can behave differently when extracts overlap or records change.
Replay and revision require different decisions
Now add an exact copy of event E01, with project P and eight units, to West. There are four input occurrences but still three business events under the registry’s stated identity rule.
UNION ALL over project and units returned four occurrences, totaling 32. UNION over event ID, project and units retained three rows and 24 units. That latter result happens to match the intended event count because the replay has identical selected fields.
Next, change West’s copy of E01 to nine units while East’s copy remains eight. A full-row UNION now retains both versions: four rows totaling 33. It does not know whether nine is a correction, a bad extract or a different interpretation of the quantity.
The appropriate outcome in this example is a conflict requiring resolution, rather than silently accepting 33 or arbitrarily choosing eight or nine. We executed a separate grouping check that identified E01 as having more than one distinct units value. That check is sufficient for this constructed conflict; a real comparison must cover every field material to the event definition.
| Fixture state | Project/units UNION ALL | Event/project/units UNION | Business interpretation |
|---|---|---|---|
| Three distinct, equal-valued events | 3 rows; 24 units | 3 rows; 24 units | Preserve all three events |
| Add an identical replay of E01 | 4 rows; 32 units | 3 rows; 24 units | Resolve replay using the identity rule |
| Replace the replay with E01 at nine units | 4 rows; 33 units | 4 rows; 33 units | Flag conflicting versions |
The table records local fixture results, not model outputs. The original project/units UNION returned one row and eight units before any replay was added.
Test occurrences, not just membership
Rigger and Su’s original OSDI 2020 paper on Pivoted Query Synthesis describes testing database engines by constructing queries expected to contain a chosen row. Its limitations explicitly distinguish row presence from multiplicity: the approach cannot generally detect mistakenly included or omitted duplicate rows or validate result cardinality.
That research concerns database-engine bugs, not AI-written business queries. We have not replicated its method. Its relevant caution is about the test itself: checking that an expected row appears is insufficient when the number of occurrences matters.
For our fixture, converting the result into a set of (project, units) pairs would make the one-row and three-row outputs look identical. Keep an occurrence count, or compare a collection that retains repeated rows. Also compare contributing event IDs before summarizing away those IDs.
Our existing join-fanout guide tests multiplication caused by joins. Here the distinct failure is loss or retention of occurrences when query results are combined. Both need explicit expected results, but a passing join fixture does not test set-operation behavior.
Keep provenance without changing the identity rule
Preserve an audit view of the incoming batch and source record alongside the business identity. Adding a batch name to a selected row changes which rows are equal under UNION; two copies of one event from different batches will then differ in that field.
That may be desirable for an audit view. It does not mean both copies should contribute to the business total. Keep ingestion provenance and event identity available for their different purposes.
The fictional registry supplied global IDs. Real systems may reuse identifiers across applications. In that case, establish whether an identity needs the originating system plus the event ID. A new export filename is not automatically a new event namespace. Our data-cleaning guide discusses establishing what an identifier actually denotes.
When versions conflict, use an approved authority or revision rule and preserve the reason for selection. Do not invent a “latest wins” rule from whichever timestamp happens to be available. An ingestion time and an event revision time can answer different questions.
Review semantics before tuning the combined query
Laurenz Albe’s December 2022 CYBERTEC article examines how differing data types in UNION ALL views can affect PostgreSQL execution plans. It is a specific practitioner investigation, not a speed guarantee for this example or today’s database configuration.
Treat performance as a separate check after the required records and quantities are established. A faster query returning the wrong event population is not an acceptable improvement. Our local tests establish no production performance result and no cross-database compatibility guarantee.
For the next generated query, retain the original extracts, record what identifies an event and ask for the expected effect of an equal-valued new event, a replay and a conflicting revision. Execute those cases on the target database with suitable access and limits. Accept the combined report only when its occurrences and identity decisions match the stated business rule.