Last week, I asked a famous LLM to write a piece of Oracle SQL time-window logic to fill in the gaps in a time series.

The data structure is simple:

CREATE TABLE test (
    t DATE,
    v NUMBER
)

The requirement is not complex:

For a data table containing a t field (time) and a v field (value), generate complete, continuous per-minute time windows, filling in any missing minute intervals, and calculate four statistical metrics for each window:

start_value is the window’s baseline starting value. For the first window, it takes the v value of the first record in that window; for all subsequent windows, it takes the start_value as the end_value of the preceding window.

For the end_value, it takes the v value of the last record in the current window; when the window has no data, end_value stays the same as start_value.

min is the minimum v value in the current window. When the window has no data, min is the same as start_value.

max is the maximum v value in the current window. When the window has no data, max is the same as start_value.

The final output should include a record for each window, consisting of start (the window’s start time), end (the window’s end time), and four statistical metrics.

I even gave it an extremely detailed set of “rules”: all logic must be split into same-level CTEs; each CTE can only do one thing; no subqueries and no merge operations are allowed. The point of this was to make every step of the SQL clear and readable, and to make later maintenance more convenient.

But what happened next completely shattered my last illusion that LLMs can directly write production-ready SQL”.

The failure at syntax level

The syntax crashed on the very first run.

The LLM first generated a set of 17-step CTE definitions, and I assembled them into a complete SQL query. I gave it no examples – just let it generate purely based on its own understanding. Then I ran the code in DBeaver, and it errored out immediately, failing before execution even completed.

The error came from the recursive CTE at step 16. It nested a “WITH ... SELECT ... FROM filled” inside an ordinary CTE, writing the recursion definition as a nested statement rather than a standard, flat recursive CTE declaration. Oracle simply doesn’t accept the syntax – it requires a recursive CTE to be fully defined in the outermost WITH clause, including the column alias list, and the recursive part. The LLM clearly remembered that recursive queries need WITH but confused where that syntax belongs. This error directly invalidated the entire SQL query – it didn’t even get through step one.

In other words, even when strictly following my “one step, one function” rule, the LLM’s first output was a syntactically non-compliant, flawed result. A SQL query that can’t even run isn’t even worth discussing – let alone evaluating its correctness.

Null values in the output

On the second attempt, I said: “I’ll give you another chance”, and it responded with all NULLs.

After fixing the syntax error, I gave it another chance and had it regenerate the entire SQL. This time it did rewrite the recursive CTE in standard form and added the column alias list. The entire query passed the syntax check and executed successfully. But the moment I saw the result set, my heart sank – the five returned rows all had NULL values for all four statistical metrics:

start	end	START_VALUE	END_VALUE	min	max
2026-04-01 10:10:00.000	2026-04-01 10:11:00.000	NULL	NULL	NULL	NULL
2026-04-01 10:11:00.000	2026-04-01 10:12:00.000	NULL	NULL	NULL	NULL
2026-04-01 10:12:00.000	2026-04-01 10:13:00.000	NULL	NULL	NULL	NULL
2026-04-01 10:13:00.000	2026-04-01 10:14:00.000	NULL	NULL	NULL	NULL
2026-04-01 10:14:00.000	2026-04-01 10:15:00.000	NULL	NULL	NULL	NULL

There clearly was data in the original table, and every window had a corresponding v value – yet all the statistical metrics still came back as NULL.

I traced the bug to the window boundaries. The LLM-generated window sequence used the original data’s minimum and maximum timestamps directly in the time calculation (min_t + (rn - 1) / 1440), without first truncating the two boundaries to whole minutes. Meanwhile, when grouping the original data, it used TRUNC(t, 'MI') – the window identifier already truncated to the minute level, to group the original data. One side ran on second-level boundaries while the other ran on minute-level ones. As a result, they could never align. So, every LEFT JOIN naturally matched nothing, and NULL values took over the result set.

Ironically, in its natural-language description, the two adjacent steps read: “calculate the start and end timestamps for each one-minute window” and “calculate the start timestamp of the one-minute window where each record belongs.” Reading those two sentences alone, you’d never guess it had skipped the truncation step. Not until you saw the screen full of NULLs did you realize that a tiny TRUNC was the crux of the entire query.

The logical errors

On the third generation, it ran – but the logic had drifted, quietly, off course.

After fixing the boundary error, I ran the query again. The NULLs were gone, the values were populated, and everything looked normal. The moment I compared the output with my expected results row by row, I knew something wasn’t right:

start	end	START_VALUE	END_VALUE	min	max
2026-04-01 10:10:00.000	2026-04-01 10:11:00.000	3	3	3	3
2026-04-01 10:11:00.000	2026-04-01 10:12:00.000	4	4	4	4
2026-04-01 10:12:00.000	2026-04-01 10:13:00.000	4	4	4	4
2026-04-01 10:13:00.000	2026-04-01 10:14:00.000	5	8	5	9
2026-04-01 10:14:00.000	2026-04-01 10:15:00.000	2	2	2	2

According to the requirement, the START_VALUE in the second row should be equal to the END_VALUE in row 1(i.e., 3). In this result, however, START_VALUE in the second row is 4 – it simply takes the value of the current window’s first record. The reason lies in the value assignment logic of the recursive CTE:

COALESCE(t16.first_val, t17.end_val) AS start_val

The LLM’s interpretation was this: If the current window contains data, use the value from its first record; if the current window contains no data, use the end_value from the preceding row. On the surface, this seems “more reasonable”, but it completely deviates from the specification’s explicit requirement that every subsequent window should inherit the end_value from the preceding window. What makes the error particularly dangerous is that the numbers it produces are reasonable, non-null, even plausible-looking. Unless you compare the results against a pre-verified correct answer row by row, you’ll almost certainly be fooled.

Only after I manually wrote the recursive logic – setting the start_value of each non-first row as the end_value of the preceding row, did I finally get the correct result.

By this point, for a simple task that I could have completed by hand in a dozen minutes, I had already gone through six rounds of conversation with the LLM, three rounds of code generation, and two rounds of manual correction just to barely get it back on track.

Why is LLM-generated SQL so error-prone?

So, we simply ask AI to explain the reason itself — and, to its credit, it absolutely nailed it: the root of the problem is baked into the DNA of Text2SQL technology.

The longer the context, the more fragile the consistency

Across the 17 parallel CTEs, aliases from different sources, JOIN keys, and recursive references form a tangled web of dependencies. Within its extremely limited attention window, the LLM has to keep track of which field comes from which step while ensuring that every cross-step reference remains correct. Unfortunately, one typo in an alias, or a single data type mismatch is enough for the query to silently produce wrong results, just like the all-NULL output I encountered on my second attempt.

Runs successfully ≠ Logically correct

This is the most fundamental weakness. Text2SQL inherently lacks an executable testing environment, so the LLM can only cobble together “syntactically correct” SQL without being able to verify its semantics. Take COALESCE(t16.first_val, t17.end_val) as an example. To the LLM, it’s simply a reasonably use of a function for handling NULL values. But from the perspective of the business logic, it completely distorts the rule that the value should be inherited from the preceding row’s end_value”. Had I not already known the correct result, the third query, with its seemingly correct, smooth output, could easily have been accepted as the final output, quietly planting a hidden bug.

Natural language description can be deceptive

What’s even more terrifying is that if you ask the LLM to generate a natural-language description for each step, it will faithfully fabricate a plausible-sounding explanation based on the flawed SQL it wrote. Take the same faulty logic as an example. It would describe the step like this: “This step takes the value of the first record in the current window as start_value; if that value is NULL, it uses the end_value from the preceding window as a fallback. See? Even the explanation appears flawless. For decision-makers unfamiliar with SQL, this explanation reads as “trustworthy” evidence while the real logic flow stays hidden beneath the a seemingly perfect layer of words.

The debugging cost is far more than writing it manually

In this cycle of finding errors, regenerating SQL with the LLM, validating the output, and fixing mistakes, humans are no longer SQL writers, they become SQL reviewers. Reviewing machine-generated, lengthy, and highly decomposed SQL has heavier mental workload than writing it by hand. At least when I write SQL myself, I know exactly the reasoning behind every step. But when reviewing an LLM-generated query, I need to reverse-engineer its logic and figure out where it silently took a wrong turn.

The way forward

Let the LLM map out the logic, write commands with SQLazy, and compile.

After three failed attempts, I changed my approach. Since directly generating SQL with an LLM isn’t reliable, why not let it only map out the business logic and leave the actual SQL writing to a specialized tool?

I gave the same requirement to the LLM again, but this time I asked it to describe the solution steps in the natural language rather than generating SQL.

Step 1: Determine the data’s time range, identify the earliest and latest timestamps, and use them as the boundaries for generating subsequent time windows.

Step 2: Generate a continuous sequence of one-minute time windows covering the entire time range. Create a window for every minute from the beginning to the end, even if no data exists for that minute.

Step 3: Assign each row in the original data to the corresponding one-minute time window based on its timestamp.

Step 4: Aggregate the data in each one-minute time window – calculate the first value, last value, minimum value, and maximum value. Leave these values empty for windows with no data.

Step 5: Fill in the empty time one-minute windows that contain no data – use the end value from the preceding window as the start value, ensuring that the time series remains continuous.

Step 6: Calculate the final output values for each window.

start_value: For the first window, use the value from the first record; for every subsequent window, use the end value from the preceding window.

end_value: If the window contains data, use the value from the last record; otherwise, set it equal to start_value.

min: If the window contains data, use the minimum value; otherwise, set it equal to start_value.

max: If the window contains data, use the maximum value; otherwise, set it equal to start_value.

Step 7: Output the final result, including the start time, the end time and the four computed values for each one-minute time window.

The LLM outlined a clear seven-step solution. I then use the following commands to build the entire query in SQLazy based on those seven steps:

Name	Anchor	Statement
t1	test	compute datetime(t precise minute) as t_m
t2		summarize t_m min as min_window t_m max as max_window
t3		list from t2.min_window to t2.max_window step 1 minute as window_start
t4		join window_start with t1 t_m take t,v
t5		sort t
t6		summarize t argmin v max as first_val t argmax v max as last_val v min as min_val v max as max_val group window_start
t7		compute if (last_val isnull then last_val[-1] else last_val) as filled_last_val
t8		compute if (# = 1 then ifn(first_val, filled_last_val) else filled_last_val[-1]) as start_value
		derive window_start as 'start' (window_start elapse 1 minute) as 'end' start_value nvl(last_val, filled_last_val) as end_value nvl(min_val, start_value) as 'min' nvl(max_val, start_value) as 'max'

The LLM’s seven-step solution serves as a course-grained business blueprint, while SQLazy commands break that blueprint down into precise, executable atomic operations. Each business stage can be fully implemented through a small combination of several commands. Take the step of “determining the time range” as an example. The LLM summarizes it in a single sentence. But in SQLazy the actual implementation requires two commands – t1 truncates timestamps to whole minutes, and t2 determines the start and end boundaries of the truncated timestamps. Each command performs exactly one task. The boundaries between commands are clear, leaving no room for ambiguity. Consider another step: “computing specified values for each window”. Here, t5 sorts data, and t6 groups and summarizes data. The two actions are cleanly separated. In contrast, when generating SQL directly, the LLM intertwines sorting logic with window functions, which introduces hidden risks. This division of labor – blueprint plus execution – preservers the LLM’s flexibility in understanding requirements while eliminating the arbitrariness and inconsistency often found in generated SQL through the command layer’s strict syntactic constraints.

SQLazy’s syntax is specially designed to be human-readable. Take the task of retrieving values from the first and the last records in each window. In SQL, you need two window functions – FIRST_VALUE and LAST_VALUE – along with PARTITION BY, ORDER BY and ROWS BETWEEN. A dozen key words are piled together, and readers have to mentally reconstruct the execution flow before they can understand what the query is doing. In SQLazy, it takes just one line – summarize t argmin v max as first_val t argmax v max as last_val group window_start. Literally, argmin means “get the value of v corresponding to the minimum timestamp”, and argmax means “get the value of v corresponding to the maximum timestamp”. They express exactly the way you think about the problem. The same applies to cross-row reference. In SQL, you either use LAG together with NULL checks, or resort to a recursive CTE. Either way, the logic becomes convoluted. In SQLazy, it’s simply last_val[-1], which gets the value from the preceding row – as directly as writing an Excel formula. “sort” sorts the data, “compute” calculates a new column, and “summarize” groups and aggregates the data. Every command describes what you want to do, rather than how the database should execute it. Following the LLM’s step-by-step solution and writing SQLazy commands feels almost like translating natural language into precise programming expressions. There’s no need to mentally translate your ideas into SQL syntax first.

And that’s it. The job is done. SQLazy then compiles those commands into the SQL dialect of the target database. The Oracle CTE query that the LLM failed to generate correctly after three attempts is compiled by SQLazy into the following SQL:

WITH t1 AS (
    SELECT
        t,
        v,
        TRUNC(t, 'MI') AS t_m
    FROM
        t_1742353802112
),
t2 AS (
    SELECT
        MIN(t_m) AS min_window,
        MAX(t_m) AS max_window
    FROM
        t1
),
TEMP_TABLE__2(window_start) AS (
    SELECT
        (SELECT min_window FROM t2) AS window_start
    FROM
        DUAL
    UNION ALL
    SELECT
        window_start + NUMTODSINTERVAL(1, 'MINUTE')
    FROM
        TEMP_TABLE__2
    WHERE
        window_start < (SELECT max_window FROM t2)
),
t6 AS (
    SELECT
        window_start,
        MAX(v) KEEP (
            DENSE_RANK FIRST
            ORDER BY t
        ) AS first_val,
        MAX(v) KEEP (
            DENSE_RANK LAST
            ORDER BY t
        ) AS last_val,
        MIN(v) AS min_val,
        MAX(v) AS max_val
    FROM (
        SELECT
            TEMP_TABLE__2.window_start,
            t1.t,
            t1.v
        FROM
            TEMP_TABLE__2
        LEFT JOIN t1
            ON TEMP_TABLE__2.window_start = t1.t_m
        WHERE
            TEMP_TABLE__2.window_start >= TO_TIMESTAMP('2026-04-01 10:10:00', 'YYYY-MM-DD HH24:MI:SS')
            AND TEMP_TABLE__2.window_start <= TO_TIMESTAMP('2026-04-01 10:14:00', 'YYYY-MM-DD HH24:MI:SS')
    ) t_3
    GROUP BY
        window_start
),
t7 AS (
    SELECT
        window_start,
        first_val,
        last_val,
        min_val,
        max_val,
        CASE
            WHEN last_val IS NULL
                THEN LAG(last_val) OVER (ORDER BY window_start)
            ELSE last_val
        END AS filled_last_val
    FROM
        t6
),
t8 AS (
    SELECT
        window_start,
        first_val,
        last_val,
        min_val,
        max_val,
        filled_last_val,
        CASE
            WHEN ROW_NUMBER() OVER (ORDER BY window_start) = 1
                THEN COALESCE(first_val, filled_last_val)
            ELSE LAG(filled_last_val) OVER (ORDER BY window_start)
        END AS start_value
    FROM
        t7
)
SELECT
    window_start AS "start",
    window_start + NUMTODSINTERVAL(1, 'MINUTE') AS "end",
    start_value,
    COALESCE(
        NULLIF(CAST(last_val AS VARCHAR2(4000)), ''),
        NULLIF(CAST(filled_last_val AS VARCHAR2(4000)), '')
    ) AS end_value,
    COALESCE(
        NULLIF(CAST(min_val AS VARCHAR2(4000)), ''),
        NULLIF(CAST(start_value AS VARCHAR2(4000)), '')
    ) AS "min",
    COALESCE(
        NULLIF(CAST(max_val AS VARCHAR2(4000)), ''),
        NULLIF(CAST(start_value AS VARCHAR2(4000)), '')
    ) AS "max"
FROM
    t8;

Every operation in this block of code is produced through deterministic template expansion, guaranteeing syntactically correct SQL. Mistakes that LLMs repeatedly make – such as forgetting to truncate time boundaries or quietly drifting away from the intended cross-row reference logic – are eliminated. The generated SQL can be executed directly against the target database and produces results identical to those obtained during debugging in SQLazy.

Change the target database type, and SQLazy can compile SQL for other types of databases, such as MySQL, PostgreSQL, etc. If I relied on an LLM instead, who knows what new surprises it would cook up?

This is the truly practical path to Text2SQL. Instead of asking an LLM to generate the expected SQL in one shot, let it map out the solution, let humans translate it into SQLazy commands, and let SQLazy compile those commands into reliable SQL. Throughout the entire workflow, the LLM is responsible for deciding what needs to be done, while SQLazy ensures how those intentions are translated into SQL. The final deliverable is a syntactically correct, logically controlled SQL script that can be executed directly on the target database. Taking SQL generation out of the LLM’s hands and putting it back into the deterministic command writing and compiler – that’s the path that truly makes Text2SQL work.

Summary

Expecting LLMs to generate production-ready SQL in one shot is like asking programmers to write an operating system in a text editor without code completion tools or a compiler. They may be able to produce something, but every step could hide unexpected pitfalls. In real-world data analysis scenarios, timestamp handling, NULL handling, and cross-row reference are almost everywhere – the very areas where LLMs most likely to go wrong. Just because an LLM can write poetry does not mean it can automatically write reliable SQL.

My first attempt produced a flawed query with invalid syntax that could not even be executed.

On my second attempt, I said, “I’ll give you another chance.” It replied with a screen full of NULLs – its way of saying, “Sorry, still failed.”

On the third attempt, it finally generates numbers – only to quietly take a wrong turn in the recursive logic.

The next time someone tells you, “Just use AI to write SQL,” try giving it your most complex time-window requirement and let it give it a try. Then after you experience those three rounds of mistakes completely, you’ll understand – rather than expecting an LLM to write SQL, you’re better off having it help you work out the logic and leaving the actual code generation to SQLazy.