Blog /
The join problem
Published . Updated . Written for nlqueries-core 0.3.
joins text-to-sql knowledge-base
If you want to know why text-to-SQL systems that score well on benchmarks disappoint on real warehouses, joins are the shortest answer.
A question that touches one table is close to solved. Filter, aggregate, done. The moment a question spans two or more tables, the system has to decide how they relate, and the space of wrong decisions is large, mostly silent, and occasionally expensive.
Why guessing fails
The naive approach is to infer join paths from foreign keys and column names. orders.customer_id matches customers.id, so join on that. This works often enough to be dangerous.
It fails in specific ways:
There are too many paths. In a warehouse of any size, most pairs of tables are connected by several routes. Orders to regions might go through customers, or through shipping addresses, or through the sales rep assigned at the time. These are all valid joins. They give different answers, and which one is correct depends entirely on what the question meant.
Foreign keys are often absent. Plenty of analytical warehouses have no declared foreign keys at all, either because the loading process drops them or because nobody added them. The relationships exist; the database does not know about them.
Names lie. user_id on one table refers to the customer. user_id on another refers to the internal staff member who touched the record. They are the same name and they are not the same thing.
Cardinality is invisible. This is the important one.
Fan-out, the silent failure
Join a table to another table where the relationship is one-to-many, then sum a column from the first table, and every row on the "one" side is counted once per matching row on the "many" side.
An order with three line items contributes its total three times. Revenue is now inflated by an amount that depends on the average basket size, which means it looks wrong by a plausible factor rather than an obvious one. It does not error. It does not return zero rows. It returns a number that is the right order of magnitude and simply too big.
Everyone who has written SQL professionally has done this. The difference is that a person who has done it before is suspicious of a revenue number that jumped, and knows to check. A generated query that nobody reads has no such reflex attached to it.
This is the single strongest argument for showing the SQL with every answer. Fan-out is almost invisible in a result and almost obvious in a query, if you know to look at the join.
Making the path a decision, not an inference
The fix is to stop leaving the join path to be reconstructed from column names every time a question arrives, and to decide it once.
For the tables people actually ask about, write down:
- The default path between each pair of tables that several routes connect, and when the other routes apply.
- The cardinality of each hop, so that anyone summing across it knows whether a fan-out is waiting.
- The column a business term means. "Revenue" should resolve to the column your finance team means, not to whichever column has the word in its name.
- The handful of metrics the whole organisation quotes, defined once, so they are not recomputed differently each time.
None of this needs special tooling to start. What matters is that a person decided it. Candidate paths can be found mechanically, from names, types and overlapping values, and that is a good way to build the list. But the cost of a wrong entry is asymmetric. A path that nobody approved costs you one question that has to be answered another way. A path that was statistically plausible and semantically wrong, silently adopted, becomes the definition of how two tables relate and quietly corrupts every answer that uses it.
In practice the review is quick: most candidates are obviously right or obviously wrong to somebody who knows the warehouse, and the ambiguous ones are exactly the ones you want a human looking at.
What NLQueries does with joins in 0.3
- Declared foreign keys are read from the schema into the knowledge base and shown to the model. Where a warehouse declares none, this is empty.
- The joins your team already writes reach the model as query capsules mined from your query history. In 0.3 the prompt carries the most frequent capsules, up to five, rather than the ones closest to the question.
- The decisions above can be written into the knowledge base as business rules and glossary terms, which go into every prompt. A rule such as "sum revenue from
order_items; never sumorders.totalafter joiningorder_items" is exactly the kind of thing worth writing there. - Validation checks that every table and column the query names exists. It does not check the join path or its cardinality, so a fan-out query passes.
So on a wide warehouse the SQL on screen remains the main defence, which is why it comes back with every answer. Fan-out is hard to see in a number and easy to see in a JOIN.