Every text-to-SQL system has a step nobody writes about. Before the model can generate anything, something has to decide which tables it gets to see, because you cannot put 260 tables in a prompt.
That step is usually treated as a retrieval problem: embed the schema, rank by similarity, take the top six. It works well enough on the tidy 40-table schema a demo uses.
So I built a schema designed to break it, and ran my own library against it. Here is what happened, including the number I would rather not publish.
The test schema
127 objects across three unrelated domains in one database: a healthcare claims warehouse, a finance and billing domain, and IoT telemetry. 90 tables, 37 real Oracle views, roughly 7 million server-generated rows, on a live Oracle Autonomous Database 26ai instance. Not SQLite pretending to be Oracle.
The point of mixing three domains is collision. member, account, device and event mean different things in each one, and a similarity ranker has no way to know which sense a question is about.
I also planted backup and staging copies - stg_member, member_bkp, fact_claim_line_20240101 - because real warehouses are full of them, and a selector that offers the model a staging table is worse than useless.
The numbers
Recall@6 over 30 questions:
| Cataloguing method | recall@6 |
|---|---|
| Identifiers only, no descriptions | 47% |
| OCI Gemini 2.5 Pro | 60% |
| Claude Sonnet | 80% |
On single-domain schemas the same pipeline hits 92%.
47% is the number I did not want. It says that if you point a schema selector at raw table and column names with no descriptions, on a genuinely messy schema, it is wrong more often than it is right. Half your answers get built on the wrong tables and nothing in the pipeline tells you.
It is also the most useful number in the table, because it is the baseline that shows what the cataloguing pass actually buys you. Identifiers to real descriptions is +33 points. That is the whole ballgame, and it is the part most people skip.
The 60 vs 80 gap is its own finding: the model you use to describe your schema matters as much as the model you use to write the SQL. Same selector, same questions, same schema - 20 points of difference from the cataloguing model alone.
What a live database found that 445 tests did not
Every test passed. The live run found five real bugs in an afternoon.
- Reflection returned 1,493 objects, because ADB ships with OML, ODI and OADC service schemas. Fixed by filtering on ALL_USERS.ORACLE_MAINTAINED.
- SQLAlchemy reported VECTOR columns as NULL instead of VECTOR(512, FLOAT32).
- python-oracledb 4 returns JSON already decoded, so json.loads() got a dict and raised TypeError.
- A Postgres-specific schema filter silently emptied the entire catalog for Postgres users. No error. Just nothing.
- The Terraform stack failed on an API validation that plan cannot detect: a route table cannot have an internet gateway and an all-services service gateway at the same time.
Number 4 is the one that stays with me. One line, an entire dialect broken, every test green, and the failure mode was silence.
Reviewing is not running. Until you have pointed it at a real database, "supports Oracle" is a to-do item wearing a checkmark.
The part it was actually built for
Shadow detection caught every backup and staging copy. And the selector filters the catalog by caller identity before it ranks - because schema selection happens before the query runs, which means it happens before row-level security can act.
An identity-blind selector hands the model a table the caller cannot read. The SQL is correct. RLS filters every row. The user is told "no records found."
That is not an access-denied message. It is a wrong answer in a confident tone, and the person reading it cannot tell the difference.
There is a demo that runs entirely in your browser, no signup and no database: https://ashishsinha1602.github.io/schemagate/ - ask something as an analyst, then add the payroll role and watch the restricted table appear in the prompt.
pip install schemagate - Apache-2.0, https://github.com/ashishsinha1602/schemagate
Happy to be told what else would break it. That is the useful feedback.
Top comments (0)