Three Converter Bugs That Hide in Recursive CTEs

A pilot run two days ago put a 76-dataset failing set at 84.0% effective pass through ReportBridge's T-SQL to PostgreSQL conversion pipeline. A new run today put the same set at 89.6%. Three converter changes were responsible. None of them touched the model, the prompt, or the schema context. All three were deterministic scrubs that ran on the output of the AI step.

Each scrub closed a different failure shape. All three shapes cluster around recursive CTEs — the place where T-SQL's forgiving type system meets PostgreSQL's strict one and the converter has to bridge two languages that look almost identical but disagree about what counts as legal.

This is what the three bugs were, what the fixes look like, and why a deterministic scrub kept beating prompt engineering even after multiple rounds of trying.

Bug 1: Recursive-arm type drift

A recursive CTE in PostgreSQL has two arms joined by UNION ALL. The base arm seeds the recursion. The recursive arm references the CTE itself and adds the next row. PostgreSQL requires both arms to return columns of the same type. T-SQL also requires this in theory, but its type promotion is looser — it will frequently merge an integer base with a varchar recursive arm if the values look numeric.

The AI converter preserves the column shapes faithfully, which means the type drift gets carried into PostgreSQL output that then fails:

WITH RECURSIVE yl AS (
  SELECT (SELECT MIN(year) FROM tests) AS year   -- subquery returns integer
  UNION ALL
  SELECT yl.year + 1 FROM yl WHERE yl.year < 2025 -- arithmetic returns ??
)
SELECT * FROM yl

-- ERROR: UNION types integer and bigint cannot be matched

WITH RECURSIVE yl AS (
  SELECT (SELECT MIN(year) FROM tests)::INTEGER AS year
  UNION ALL
  SELECT (yl.year + 1)::INTEGER FROM yl WHERE yl.year < 2025
)
SELECT * FROM yl

The scrub looks at recursive CTEs whose base arm is either a scalar subquery or an explicit integer cast (the two shapes that cause type drift), then wraps both the base and the recursive-arm arithmetic in ::INTEGER. It's gated tightly so it doesn't fire on plain-literal bases where drift is impossible. Idempotent — running it twice produces identical output.

Why not handle this in the prompt? We tried. Two prompt rules telling the model to align UNION-arm types in recursive CTEs improved the pass rate by about a third of what the scrub achieved, with significant variance run-to-run. The model got it right most of the time. The scrub gets it right every time.

Bug 2: Year-int-to-date cast

A common T-SQL pattern is to compute a year as an integer and then cast it to a date for use as a range boundary. The model translates this verbatim into a PostgreSQL pattern that compiles but evaluates to nonsense:

-- AI emits this from T-SQL CAST(@year - 1 AS DATE):
SELECT CAST((CAST('2020' AS INTEGER) - 1) AS TEXT)::date

-- ERROR: invalid input syntax for type date: "2019"

-- Rewritten to PG-native MAKE_DATE
SELECT MAKE_DATE(CAST('2020' AS INTEGER) - 1, 1, 1)

The scrub recognizes the pattern CAST(<int-expr> AS TEXT)::date and rewrites it to MAKE_DATE(<expr>, 1, 1). PostgreSQL won't coerce a four-character year string to a date, but it will happily build a date from an integer year, month, and day. The scrub fired once on this pilot — and revealed an unrelated identifier bug underneath it that the date error had been masking.

That second-order benefit is one of the reasons we lean on deterministic scrubs: they unblock downstream errors quickly instead of hiding them behind a recurring first-line failure.

Bug 3: CTE-with-column-list parsing

This one wasn't in the AI step at all. It was in the tooling that runs around the AI step — specifically the "missing relations" check that decides which queries need migration work versus a converter fix.

T-SQL frequently uses a CTE-with-column-list shape that PostgreSQL also supports:

WITH MaxMailSendDateList (ScanDate, MaxMailSendDate) AS (...)
, UpdatedMailSendDate(CaseId, ScanDate, MailSendDate) AS (...)
,RapidPassData AS (...)              -- no space between , and name
SELECT * FROM RapidPassData

A naive parser looks for WITH name AS ( and , name AS (. It misses three things that real T-SQL throws at it:

Tightening one regex closed all three. The migration gap report for the same corpus dropped from 8 false-positive missing-table entries to zero. Datasets that had been mistakenly tagged NEEDS_MIGRATION rejoined the scorable pool and several passed on the next run.

The pattern: catch the shape in code, leave the rest to the model

Every one of these three fixes shares a structure. Look at the failures, not the passes. Group failures by error message. When a cluster has the same shape across multiple inputs, write a regex and a transformation that runs after the model. Add tests that assert both the positive case and the guard case (does the scrub fire on this shape, and does it leave that shape alone). Ship.

The scrubs are not a substitute for prompt rules. They're the layer below them. Prompt rules teach the model what to aim for. Scrubs catch the cases where the model misses anyway. The two together drive the floor up faster than either one alone.

The pipeline now has thirteen of these scrubs, each covering one recurring failure shape. Adding a fourteenth typically takes under an hour: write the failing test that captures the input and the desired output, write the regex, watch the test go green, run the full pilot to confirm no regressions. The full-pilot run is the slow part — usually 40 to 50 minutes — but the unit-test gate catches mistakes long before the pilot does.

What it adds up to

The three changes that drove this pilot from 84.0% to 89.6% shipped together as two commits. Each was small. Each was deterministic. Each was unit-tested before the pilot ran. The re-pilot took about 50 minutes; the code work took about three hours including tests.

The remaining gap is a mix of identifier hallucinations and unparenthesized recursive-arm shapes that the regex hasn't been widened to cover yet. Both are class-level fixes — the next two scrubs will close them. After that the residual is genuine schema work: tables that exist in the source database but not yet in the target.

None of this is glamorous. It's also the reason the pass rate keeps moving in the right direction.

FAQ