| Below is what I’d copy into your SQLEnv design doc as the “OpenEnv-native environment spec + reward shaping playbook”, based on how OpenEnv environments are structured **and** how recent interactive Text-to-SQL / NL2Query RL work avoids sparse, end-only rewards. |
|
|
| --- |
|
|
| ## 1) What OpenEnv actually expects from an environment (so SQLEnv “fits” cleanly) |
|
|
| OpenEnv’s “unit of integration” is a **typed** environment served over a **persistent WebSocket session** (per episode / per client), with: |
|
|
| * **Pydantic Action model** (what the agent sends) |
| * **Pydantic Observation model** (what the agent receives) |
| * An `Environment` with `reset()` and `step(action)` |
| * A `State` with at least `episode_id` + `step_count` |
| * Each observation includes `done` and `reward` (so reward can be stepwise, not just terminal) |
|
|
| That’s not just convention—the official “Building an Environment” guide shows exactly this pattern: define `MyAction(Action)` + `MyObservation(Observation)`, implement `reset/step`, and expose it via `create_app(...)`; the client returns a `StepResult` with `observation/reward/done`. ([Meta PyTorch][1]) |
|
|
| **Implication for SQLEnv:** if you want dense reward, you can compute **and return** reward on every `QUERY` step (and also on `DESCRIBE` / `SAMPLE` if you choose), without fighting the framework. |
|
|
| --- |
|
|
| ## 2) Make SQLEnv a POMDP on purpose (it will help your reward design) |
|
|
| Interactive DB work is naturally a **partially observable MDP**: |
|
|
| * The full database is the hidden state. |
| * The agent only sees schema + sampled previews + query results (truncated). |
| * The agent’s *belief* improves as it explores. |
|
|
| This is exactly how **QueryGym** frames NL2Query: observations include schema metadata, previews, errors; actions include exploration probes + “manipulations”; the episode ends when the current intermediate result matches the target. ([arXiv][2]) |
|
|
| **Takeaway:** don’t feel obligated to dump everything into the observation. Instead: |
|
|
| * Keep observations “what a real analyst would see” |
| * Keep reward “what the oracle knows” (gold answer / equivalence), but *shape it* so learning isn’t end-only. |
|
|
| --- |
|
|
| ## 3) How other projects avoid “reward only at the end” (patterns you can steal) |
|
|
| ### Pattern A — Reward panels (multi-signal scoring, not just correctness) |
|
|
| **SQL-TRAIL (2026)** explicitly argues binary execution reward is too sparse and introduces a **multi-term reward panel**: execution correctness + turn-budget efficiency + schema/format/syntax/bigram shaping; they even show an example weighting like `5*exec + 2*turns + schema + bigram + syntax + format`. ([arXiv][3]) |
|
|
| **What to steal for SQLEnv:** |
|
|
| * Keep **terminal correctness** as the main signal |
| * Add a few **low-leak**, “behavioral” signals: |
|
|
| * syntax success / runtime success |
| * redundancy penalties |
| * “finished within budget” bonus (difficulty-aware) |
|
|
| ### Pattern B — Partial-match execution rewards (graded correctness) |
|
|
| **PaVeRL-SQL (2025)** uses a *fractional execution accuracy* reward: instead of 0/1, it scores based on partial matches (e.g., fraction of correct columns), explicitly to reduce sparsity and improve stability; it also notes pitfalls with strongly negative rewards. ([arXiv][4]) |
|
|
| **What to steal:** |
|
|
| * Turn correctness into a **smooth “progress” measure** whenever possible (subset/superset, partial set overlap, relative error on aggregates). |
|
|
| ### Pattern C — Stepwise rewards from feedback signals |
|
|
| OpenEnv’s own Wordle GRPO tutorial computes multiple reward components from intermediate feedback (greens/yellows) plus repetition penalties. Even if the environment returns only the “correct” signal, the trainer can compute additional reward terms from observations. ([Meta PyTorch][5]) |
|
|
| **What to steal:** |
|
|
| * Many “intermediate reward” signals can be computed from the **observation stream** (query executed? error? rowcount changed? new columns discovered?) without referencing gold at each step. |
|
|
| ### Pattern D — Stepwise evaluation of intermediate structure (CTEs / subqueries) |
|
|
| **Graph-Reward-SQL (EMNLP Findings 2025)**: provides intermediate supervision by evaluating **CTE subqueries stepwise**, instead of only scoring the final SQL. |
|
|
| **What to steal:** |
|
|
| * If you allow (or encourage) CTE-style multi-step querying, you can score partial subqueries (even coarsely). |
|
|
| ### Pattern E — “Subset/superset” intermediate rewards (classic but effective) |
|
|
| QueryGym ends with a big terminal reward, but gives a smaller reward when the intermediate result is a **subset or superset** of the target (rows/cols). ([arXiv][2]) |
|
|
| **What to steal:** |
|
|
| * For list-type answers (top-k rows, grouped outputs), subset/superset rewards are very natural and robust. |
|
|
| --- |
|
|
| ## 4) A reward design that’s dense, meaningful, and hard to game |
|
|
| Your current doc has a good high-level weighting, but I’d change *how* you implement intermediate reward so it teaches the right skills without leaking the answer or being exploitable. |
|
|
| ### 4.1 Split reward into 3 layers (this is the key) |
|
|
| #### Layer 1 — **Operational validity** (stepwise, low leak) |
|
|
| Reward “being able to operate the tool” and not waste steps: |
|
|
| * `r_exec_ok`: +ε when a query executes successfully (no syntax/runtime error) |
| * `r_new_info`: +ε when the action reveals *new* info (new table described, new column seen, new value distribution sampled) |
| * `r_repeat`: small penalty (or zero reward) for repeating identical queries or identical observations |
| * `r_cost`: tiny step cost (keeps trajectories short) |
|
|
| This is similar in spirit to the “syntax / format / turns” components in SQL-TRAIL’s panel. ([arXiv][3]) |
|
|
| **Important tweak:** prefer **small positive signals** + “no reward” over heavy negative penalties. Strong negative shaping often makes agents risk-averse and can destabilize learning (PaVeRL-SQL explicitly designs around sparsity/instability concerns). ([arXiv][4]) |
|
|
| #### Layer 2 — **Progress-to-target** (stepwise, oracle-based but *coarsened*) |
|
|
| This is where you densify correctness *without waiting for ANSWER*. |
|
|
| Maintain a hidden “best progress so far” metric. After each `QUERY`, compute: |
|
|
| * If the question is an **aggregate** (count/sum/avg): |
|
|
| * `progress = 1 - min(1, |pred - gold| / denom)` (choose denom robustly; e.g., max(1, |gold|)) |
| * If the expected answer is a **set/list**: |
|
|
| * `progress = Jaccard(pred_set, gold_set)` or overlap@k |
| * If the expected answer is a **table**: |
|
|
| * score column overlap + row overlap separately (like fractional exec accuracy; PaVeRL uses column-based partial reward). ([arXiv][4]) |
| * If you have **gold SQL** available: |
|
|
| * you can additionally compute coarse structural signals (tables referenced, join graph overlap), but be careful: lexical overlap (e.g., bigrams) can overfit and may reward “copying style” rather than semantics (SQL-TRAIL still uses a bigram term, but I’d keep it very low weight). ([arXiv][3]) |
|
|
| Then define step reward as **improvement**: |
|
|
| * `r_progress = clamp(progress_t - best_progress, 0, 1)` |
| * `best_progress = max(best_progress, progress_t)` |
|
|
| This avoids agents “farming” reward by oscillating. |
|
|
| This is the same core idea as QueryGym’s subset/superset intermediate reward and terminal equivalence check, just adapted to your action space. ([arXiv][2]) |
|
|
| #### Layer 3 — **Terminal correctness** (big) |
|
|
| When the agent uses `ANSWER`, compute final correctness (multi-db test suite etc.) and give a large terminal reward. |
|
|
| SQL-TRAIL still keeps binary execution correctness as the primary term (weighted highest), and you should too. ([arXiv][3]) |
|
|
| --- |
|
|
| ### 4.2 A concrete reward recipe that will actually train |
|
|
| Here’s a version that’s dense but not messy: |
|
|
| * Terminal: |
|
|
| * `R_final = 1.0` if answer matches across test DBs else `0.0` |
| * Stepwise (each `QUERY`): |
|
|
| * `+0.02` if executed successfully |
| * `+0.05 * (progress_t - best_progress)+` (only improvements) |
| * `+0.01` if reveals new schema info / new column stats / new table sampled (capped per episode) |
| * `-0.005` step cost |
| * `-0.01` if exact same SQL was already run (hash match), or if observation identical N times |
|
|
| Then **cap total shaping** so it can’t exceed the value of being correct, e.g.: |
|
|
| * `sum(step_rewards) <= 0.5` |
| * `R_episode = 1.5*R_final + sum(step_rewards)` |
|
|
| Why caps matter: otherwise the agent learns to “explore forever” and harvest shaping. |
|
|
| This is conceptually aligned with “reward panel” thinking (multiple terms, correctness dominates), but implemented in a way that produces *incremental credit assignment* during exploration. ([arXiv][3]) |
|
|
| --- |
|
|
| ## 5) Make the environment “general” by widening actions carefully (don’t just dump raw SQL) |
|
|
| Your current action set is fine, but if you want generality *and* better learning dynamics, copy QueryGym’s strongest idea: |
|
|
| ### 5.1 Two-tier action space (beginner → advanced) |
|
|
| **Tier 1 (Structured / engine-agnostic):** |
|
|
| * Exploration actions (preview table, sample values, get schema) |
| * **Relational algebra ops** (filter/join/group/union…) that build an intermediate table/CTE |
|
|
| QueryGym uses exactly this: exploration + relational algebra operations, and it’s explicitly designed to be engine-agnostic and RL-friendly. ([arXiv][2]) |
|
|
| **Tier 2 (Raw SQL):** |
|
|
| * Free-form SQL once the agent has learned the “tool use” basics |
|
|
| This gives you: |
|
|
| * easier reward shaping early (subset/superset signals on intermediate tables) |
| * less syntax failure early |
| * dialect portability |
|
|
| ### 5.2 Observation classes (copy QueryGym’s 4 buckets) |
|
|
| QueryGym categorizes observations into: overview, exploration result, intermediate table info, error feedback. ([arXiv][2]) |
|
|
| Do the same; it keeps prompts stable and makes training easier. |
|
|
| **Crucial practical detail:** always include *structured summaries* (columns, rowcount, sample rows) rather than dumping full query results. SQL-TRAIL explicitly mentions truncated dataframe previews / error messages as observation content in their loop. ([arXiv][3]) |
|
|
| --- |
|
|
| ## 6) “Dense reward” gotchas (where these environments usually fail) |
|
|
| 1. **Reward leakage (agent learns to infer gold from reward)** |
|
|
| * If you give very specific progress signals, the agent can “hill-climb” without truly reasoning. |
| * Mitigation: coarsen progress into bins (0, 0.25, 0.5, 0.75, 1), and keep step rewards small vs terminal. |
|
|
| 2. **Reward farming via exploration** |
|
|
| * If `DESCRIBE` always pays, the agent will describe everything. |
| * Mitigation: novelty reward must be **capped** and/or **relevance-weighted** (e.g., only reward describing tables whose names overlap with question tokens, or limit “new info” reward to first N actions). |
|
|
| 3. **Over-penalizing errors** |
|
|
| * Heavy negative penalties can make the agent stop exploring; PaVeRL-style partial rewards are popular partly because they reduce that brittleness. ([arXiv][4]) |
| * Mitigation: prefer “no reward” for invalid steps + small step cost. |
|
|
| 4. **Lexical similarity rewards that overfit** |
|
|
| * Bigram/keyword overlap encourages copying gold SQL style, not semantics. |
| * SQL-TRAIL includes a bigram term, but treat this as a stabilizer, not a main objective. ([arXiv][3]) |
|
|
| 5. **Equivalence checking is tricky** |
|
|
| * Multi-db test suite helps, but adds complexity/compute. (You already captured this well.) |
| * A cheaper intermediate option is QueryGym’s subset/superset checks on intermediate result tables. ([arXiv][2]) |
|
|
| --- |
|
|
| ## 7) What I would change in your current SQLEnv doc (best-judgment critique) |
|
|
| * Your “Query 3+ quadratic penalty” is *directionally* right, but I’d replace it with: |
|
|
| * a tiny constant step cost **plus** |
| * a redundancy penalty (repeat SQL / repeat observation) |
| * **plus** a progress-improvement reward |
| This trains “efficient exploration” *because it makes progress*, not because it fears punishment. |
| |
| * “Query Quality” as “appropriate JOINs” is subjective and invites reward hacking unless you make it purely rule-based. |
|
|
| * Instead, score **objective properties**: executed successfully, reduced error count, improved progress metric, fewer repeated actions. |
|
|
| * Consider adding a structured “RA/CTE mode” (QueryGym-style) as an optional tier: |
|
|
| * it makes intermediate rewards far easier (subset/superset / partial table match) |
| * it makes your environment more “general” across engines/dialects. ([arXiv][2]) |
|
|
| --- |
|
|
| If you want a single north-star sentence for the environment: |
|
|
| **“Reward *improvement* during exploration (progress-to-target + operational validity), but make correctness dominate at termination.”** |
|
|
| That combination (reward panel mindset + partial-match progress + stepwise observation-derived signals) is exactly where the best recent work is converging for interactive Text-to-SQL RL. ([arXiv][3]) |
|
|
| [1]: https://meta-pytorch.org/OpenEnv/environment-builder/ "Building an Environment - OpenEnv Documentation" |
| [2]: https://www.arxiv.org/pdf/2509.21674 "QueryGym: Step-by-Step Interaction with Relational Databases" |
| [3]: https://www.arxiv.org/pdf/2601.17699 "SQL-Trail: Multi-Turn Reinforcement Learning with Interleaved Feedback for Text-to-SQL" |
| [4]: https://arxiv.org/html/2509.07159v1 "PaVeRL-SQL: Text–to–SQL via Partial–Match Rewards and Verbal Reinforcement Learning" |
| [5]: https://meta-pytorch.org/OpenEnv/tutorials/wordle-grpo/ "Wordle GRPO with TRL - OpenEnv Documentation" |
|
|
| Semantic correctness: do we care about “correct SQL”, or just “correct results”? |
|
|
| For an interactive analytics environment, I’d prioritize correct results over “SQL string correctness” most of the time. |
|
|
| Why “result-correct” is usually the right target |
|
|
| Real analysts don’t care how you wrote it if the answer is right. |
|
|
| It avoids penalizing semantically equivalent queries (different syntax, same meaning). |
|
|
| It fits RL best: the environment is about tool use + reasoning, not code style. |
|
|
| It’s easier to scale across agents/models — you’re evaluating behavior, not aesthetics. |
|
|
| The main risk: “accidental correctness” |
|
|
| A query can return the right answer on one dataset for the wrong reasons (e.g., relying on artifacts, duplicates, missing joins). This is the only serious reason to care beyond results. |
|
|
| Best-judgment recommendation |
|
|
| Use result correctness as the primary objective, but defend against accidental correctness with one of these (pick based on MVP scope): |
|
|
| Multiple test DB variants (best, if feasible) |
|
|
| Same schema, different seeded data distributions / edge cases. |
|
|
| If it’s correct across variants, it’s very likely semantically correct. |
|
|
| Metamorphic / invariance tests (cheap and powerful) |
|
|
| Perturb data in ways that shouldn’t change the answer (or should change it predictably). |
|
|
| Example: add irrelevant rows, shuffle row order, insert distractor entities, duplicate non-key rows, etc. |
|
|
| If the “right answer” flips unexpectedly, the query logic is suspect. |
|
|
| Result-only with guardrails (MVP-fast) |
|
|
| Accept correct result on one DB, but: |
|
|
| penalize “suspicious” patterns (e.g., selecting from wrong tables, cartesian products, missing join keys) lightly |
|
|
| keep these as small shaping signals, not hard fail conditions. |
|
|
| What I would do for the challenge |
|
|
| MVP: result correctness on one DB + strong truncation/sandboxing + mild heuristics (to prevent degenerate solutions). |
|
|
| If time allows: add a second “edge-case” DB per task (even one extra variant gives a big robustness jump). |
|
|
| Stretch: full multi-DB suite. |
|
|
| So: yes, it’s totally reasonable to not care about “SQL correctness” per se — but you should care about generalization, and that’s why at least some robustness check (multi-DB or metamorphic) is worth it. |
|
|
| If you want, I can propose 8–10 concrete metamorphic tests that work well for common SQL question types (counts, top-k, averages, group-bys) without making the implementation obvious. |
|
|
| yes please do |
| Thought for 40s |
|
|
| Here are 10 metamorphic tests that work really well for SQL/analytics tasks, without requiring you to “grade SQL style”. Each test is a database mutation (or a pair of mutations) you apply, then you check whether the agent’s final answer stays the same (invariance) or changes in a predictable way. |
|
|
| A good default rule: don’t hand-derive the expected outcome. Instead, if you have a gold query / oracle, compute the expected answer on the mutated DB too, and compare the agent to that. Metamorphic tests then become “robustness checks” against accidental correctness. |
|
|
| 10 metamorphic tests for interactive SQL / analytics |
| 1) Row-order permutation invariance |
|
|
| Mutation: randomly shuffle row order in one or more tables. |
|
|
| Should hold for: almost everything. |
|
|
| Catches: agents that implicitly depend on “first row”, missing ORDER BY when needed, or UI/truncation artifacts. |
|
|
| 2) Irrelevant row injection (out-of-scope records) |
|
|
| Mutation: insert records that are guaranteed to fall outside the question’s filter (e.g., dates outside the range, categories not referenced, foreign keys that won’t match). |
|
|
| Should hold for: filtered counts/sums/top-k within a defined subset. |
|
|
| Catches: missing filters, wrong date logic, join mistakes that pull in unrelated data. |
|
|
| 3) Add “dangling entities” that shouldn’t affect results |
|
|
| Mutation: add new dimension entities that have no matching facts (e.g., customers with no orders, products never sold). |
|
|
| Should hold for: most aggregates and top-k over fact tables. |
|
|
| Catches: incorrect outer joins, incorrect join direction, counting entities instead of events. |
|
|
| 4) Key re-encoding invariance (ID remapping) |
|
|
| Mutation: apply a bijection to primary keys (e.g., add a constant offset, or permute IDs) and update foreign keys accordingly. |
|
|
| Should hold for: everything. |
|
|
| Catches: hard-coded IDs, brittle assumptions, accidental reliance on numeric magnitudes of IDs. |
|
|
| 5) Duplicate-bridge / join-multiplicity trap |
|
|
| Mutation: introduce duplicates in a bridge / mapping table (or duplicate a subset of fact rows that shouldn’t be double-counted if the query is correct). |
|
|
| Should hold for: questions where the “semantic unit” is unique (e.g., count of orders, unique customers). |
|
|
| Catches: missing DISTINCT, incorrect join keys, cartesian explosions that still look plausible on one dataset. |
|
|
| (This is one of the strongest tests for “accidental correctness”.) |
|
|
| 6) NULL perturbation in non-key attributes |
|
|
| Mutation: turn some non-essential attribute values into NULL (or introduce NULLs in columns commonly mishandled). |
|
|
| Should hold for: counts that should be COUNT(*) not COUNT(col), joins that should rely on keys not nullable fields. |
| |
| Catches: silent drops from COUNT(col), unexpected filtering due to NULL comparisons, incorrect join columns. |
| |
| 7) Unit scaling (predictable transformation) |
| |
| Mutation: multiply a numeric measure by a constant factor (e.g., amounts × 10), optionally also adjust a “unit” column if present. |
| |
| Expected change: sums/max/avg should scale by the factor; counts should not. |
| |
| Catches: mixing up measure columns, wrong aggregation, or accidentally answering a count-like proxy. |
| |
| (You can use this even without gold SQL if the question type is known.) |
| |
| 8) Add “noise facts” outside the join path |
| |
| Mutation: insert new fact rows that reference nonexistent dimension keys (or keys in a separate, unused region of the keyspace). |
| |
| Should hold for: questions that correctly enforce referential joins. |
| |
| Catches: queries that don’t join properly (using only fact table attributes), or that implicitly treat orphaned facts as valid. |
| |
| 9) Tie-injection for top-k (robustness to ties) |
| |
| Mutation: create ties around the k-th boundary (e.g., two products with equal revenue). |
| |
| Expected behavior: results may be non-unique unless a deterministic tiebreak is specified. |
| |
| Catches: brittle evaluation + agent reliance on incidental ordering. |
| |
| Practical fix: grade as set match or accept multiple valid orderings unless tie-break is explicitly in the task. |
| |
| 10) Counterfactual “swap labels, preserve structure” |
| |
| Mutation: permute category/string labels via a bijection (rename categories, regions, etc.) while preserving relationships. |
| |
| Should hold for: numeric aggregates; for label-returning tasks, the identity changes but the structure of the answer should remain consistent with the oracle on the mutated DB. |
| |
| Catches: agents keying off surface strings, shortcut heuristics (“always pick category X”) that coincidentally worked. |
| |
| How to use these in evaluation without overcomplicating things |
| |
| Keep your primary scoring “result correctness”. |
| |
| Add metamorphic checks as a robustness score: |
| |
| Run the same “agent-produced method” on N mutated DB variants |
| |
| Compare to the oracle on those variants |
| |
| Give a small bonus/penalty or use it as a “confidence grade” (e.g., pass ≥ 3/5 variants) |
| |
| A nice MVP pattern: |
| |
| 1 base DB + 2 mutated DBs per task (chosen from the list above) |
| |
| Pick mutations that are cheap + high-signal: (2) irrelevant injection, (5) join multiplicity trap, (4) ID remap |
| |
| If you tell me which answer types you’ll support in MVP (count / avg / top-k / list / table), I can recommend the best 3–4 tests per type and how strict the comparison should be (exact / tolerance / set-match). |
| |
