DEV Community: Judy

Compilable and Executable Pseudocode (spec) Solves AI Coding Hallucinations

Judy — Fri, 24 Jul 2026 03:56:21 +0000

The AI-generated SQL – no one dares to say they fully “understand” it
First, look at the following SQL query, which aims to find credit card customers whose transaction amount has increased for three consecutive months:

WITH ranked AS (
  SELECT customer_id, month, amount,
    LAG(amount,1) OVER(PARTITION BY customer_id ORDER BY month) as prev_1,
    LAG(amount,2) OVER(PARTITION BY customer_id ORDER BY month) as prev_2
  FROM transactions
)
SELECT customer_id, COUNT(*) FROM ranked
WHERE amount > prev_1 AND prev_1 > prev_2
AND prev_1 IS NOT NULL AND prev_2 IS NOT NULL
GROUP BY customer_id HAVING COUNT(*) >= 3

The syntax is correct, and it runs. But is the logic sound? No one can say for sure right away.

This is the daily routine of data analysts and developers today: they give a task to an AI, get dozens of lines of SQL in seconds, copy and paste the code, and it runs. But can they really trust the results? Modern AI can generate runnable SQL, but it is often not fully reliable.

AI-generated SQL is a black box. The code may look correct, run without errors, and return a clean result, but what it actually does may have nothing to do with what you intended. Worse, you only realize it’s wrong after it has already executed. During the review phase, you have to mentally execute the SQL yourself, effectively redoing the work that the AI was supposed to handle.

Even worse, even if the accuracy of AI-generated SQL reaches 90%, how can you know that this time they aren’t falling into the remaining 10%? Data-driven decisions should not become a game of chance. You get a SQL query that executes successfully and returns a clean result, but what it actually does is not what the user intended. This type of error cannot be caught by syntax checking. And the verification cost during the code review phase is extremely high: to confirm whether the logic is correct, you have to break down CTEs layer by layer, run them step by step, and compare the intermediate results.

What is the real problem?
In AI-assisted programming, there is a basic workflow: the user writes a clear specification and AI generates runnable code.

This is not a question of whether AI is “capable” or not, but a hard constraint under current technical conditions. If the user only sketches out a couple of lines of specification, no AI can generate runnable engineering-grade code out of thin air – unless the task is a common, well-defined one such as bubble sort. The actual AI-assisted programming workflow is: the user writes a spec → the AI generates code → the user reviews it → spec the refined → the AI regenerates the code. The spec is both the starting point and a constraint throughout the entire process.

But even with a perfectly clear specification, AI can still make mistakes – it’s a matter of probability. A poorly defined specification increases the chance that the AI will guess wrong, while a well-defined specification reduces that probability but can never eliminate it. This is because, at its core, AI is making probabilistic predictions. From input to output, it searches the space of possible answers for the most likely one, rather than logically deriving the only correct result.

Then what can we do? Is there any way to turn a “likely answer” to a “definitive one”?

The answer is: make the spec compliant enough to compile. Once the spec can be parsed and executed by a compiler, AI no longer needs to “guess” the code. The compiler is deterministic – the same input always produces the same output. No more “works this time, breaks the next”. This isn’t about “reducing the hallucination rate”; it’s about bypassing the stage where hallucinations can occur.

So, the overall logic chain is clear: AI-assisted programming requires a spec → a well-defined spec can reduce hallucination rate → but it cannot eliminate hallucinations → make the spec compliant enough to compile → use a compiler instead of AI to generate code →hallucinations disappear.

Compilable pseudocode: an advanced form of the specification
Traditionally, the requirements document that programmers create is intended for humans. It contains natural language, charts and diagrams, and pseudocode. In the era of AI, the spec can evolve into a new form with two characteristics:

Human-readable: No need to be familiar with the target language (SQL, Java, Python), you can grasp the logic at a glance.
Machine-compilable: The specification has a fixed syntactic structure and can be deterministically transformed into final code, without relying on AI guessing.

This is “compilable pseudocode”, a more compliant spec. The difference between it and traditional pseudocode is this: traditional pseudocode won’t be compiled – it is intended only for humans to read; compilable pseudocode is both human-readable and machine-readable, so it can be compiled into target code.

The value of this kind of spec is that it squeezes AI’s “creative space” down to a minimum. AI doesn't need to “understand” the business logic, “design” an implementation plan, and “guess” the correct code – it only needs to translate the uncompliant spec into a compliant one. Translation leaves a far narrower margin for error than “generating code directly from natural language”.

More importantly, you can verify the correctness of the spec before it is compiled and executed. Each step of the logic can be verified independently, so errors are caught before execution rather than being debugged afterward.

SQLazy’s workflow: The compilable pseudocode
SQLazy brings this concept to the field of complex SQL development. Its workflow is essentially compilable pseudocode: developers describe data querying logic step by step in natural language, previewing the intermediate result at each step. Once the logic is confirmed correct, the compiler deterministically generates the SQL.

Still on the same requirement as above: finding the credit card customers whose transaction amount has increased for three consecutive months. Let’s implement this using SQLazy’s workflow:

You don’t need to know SQL – the four steps alone make the logic clear. At every step you can preview the intermediate result:

sort: Sort rows by customer and month.

segment: Mark records where the transaction amounts fall, and generate a number for each consecutively rising segment.

summarize: Count the number of months covered by each segment.

filter: Select customers whose transaction amounts increase for at least consecutive 3 months (>=3).

Here is another real-world example. A table stores time intervals for multiple accounts. Each account corresponds to multiple records, and the intervals may overlap. The goal is to merge all overlapping intervals within each account. With SQLazy, this can be implemented in just four steps:

sort account_id, start_date: Sort rows by account and start date.

compute end_date[:-1] max as prev_max: Calculate the maximum end date among all rows prior to the current row.

segment condition start_date > prev_max as gid: Segment rows based on the condition, and assign a group number to each segment.

summarize start_date min as start_date, end_date max as end_date: Aggregate by account and group number.

Once the above logic is confirmed correct, the SQLazy compiler automatically generates native SQL – 100% accurate, with zero hallucination risk. If you switch the target database from MySQL to Oracle, you only need to change one option and the compiler automatically adapts to the target SQL dialect.

SQLazy’s workflow is the spec, and the compiler is the last gate guaranteeing the code quality.

In this process, the role of AI is also redefined. Conventionally, AI is responsible for generating the final SQL directly from the natural language – an extremely difficult task with a high hallucination rate. With SQLazy, AI is charged with only one thing: translating users’ colloquial, step-by-step descriptions into formal workflow syntax. The former is “decision-making” while the latter is “translation”. Even if the AI’s translation is slightly off, you can spot it at a glance at the workflow level, and the cost of correction is nearly zero.

Use LLM to convert the natural language into SQLazy syntax

AI hallucinations in programming are a problem not because AI occasionally makes mistakes, but because of something more fundamental: under the conventional model, AI’s mistakes can only be discovered after execution. By the time you find the result is wrong, the code has already been generated – even executed – making the cost of correction extremely high.

SQLazy’s compilable pseudocode shifts the moment of discovering errors from after execution to before execution. Humans confirm the logic at the spec level, and the compiler generates the final code. AI’s room for error is squeezed down to the “translation” step alone, where the result can be verified instantly by humans – solving the hallucination problem in AI programming.

This does not mean programmers are going back to the era of “writing documentation”. Rather, it redivides the labor between humans and AI: humans handle design (describing “what to do” using structured spec); AI handles translation (converting colloquial spec into compliant spec); and the compiler handles execution (guaranteeing a deterministic output).

The next phase of AI programming is probably not about making AI generate longer code, but about enabling humans to write clearer spec.

SQLazy: Querying the Start Timestamp of the Next Group from the Event Table

Judy — Wed, 22 Jul 2026 07:24:00 +0000

Problem description
A table stores status values of an object at different timestamps, with one record per timestamp. Segment rows based on changes in the status value and output the effective start timestamp and the effective end timestamp for each status segment – that is, merge consecutive records with the same status into a single time interval.

Source data:

Expected output (intervals where each value remains constant):

Take object 1 as an example:

The first three records contain the same value A, so they can be merged into a single interval: 2024-01-01 – 2024-01-04 (the timestamp when the next record begins).

Value B remains for record 4 and record 5 and they can be merged into a single interval: 2024-01-04 – 2024-01-06 (the timestamp when the next record begins).

Record 6 and record 7 have value A again, the same as before. However, since value B intervenes between them, a new group starts: 2024-01-06 – infinity (the end date of the last interval is 9999-12-31).

Step-by-step implementation with SQLazy
Key approach: sort rows by timestamp, and check whether the value in the current row has changed relative to the previous row. If it has changed, start a new group; otherwise, put it in the current group. Finally, take the minimum timestamp in each group as the start timestamp and the minimum timestamp in the next group as its end timestamp.

[Run this example online]

Let me walk through each step below:

Step 1: Sort rows in chronological order in ascending order

sort timestamp

Sort rows by timestamp in ascending order, ensuring intervals in each group are processed in chronological order.

Step 2: Start a new partition when value changes

segment value; change; as gid

This is the core step. The “segment” statement segment rows by the “value” field: each time the value changes, a new group is started and assigned a group number (gid). Within each group, the value remains unchanged.

Step 3: Aggregate by group number and value, and take the start timestamp

summarize timestamp first as effective_from, id first as id; group gid, value

Group rows by gid and value:

Take the timestamp of the first record in each group and use it as the effective start timestamp for that status.

id: Take the first value in each group.

Step 4: Calculate the effective end timestamp

compute nvl(effective_from[1], datetime("9999-12-31 00:00:00")) as effective_to

effective_from[1] takes the effective_from value of the next row after the current group as the end timestamp for the current group. If the current group is the last group (i.e., effective_from[1] is empty), use nvl to set it to a maximum date (9999-12-31), which represents “to present”.

Finally, remove the helper column gid and output the final result.

Compile the steps into SQL

Once the above steps are complete and verified, SQLazy’s compiler can automatically generate the equivalent native SQL (The Oracle syntax for this example):

WITH Value AS (
    SELECT
        id,
        value,
        timestamp
    FROM
        events
),
Value2 AS (
    SELECT
        gid,
        id AS id,
        value AS value,
        timestamp AS effective_from
    FROM
        (
            SELECT
                id,
                value,
                timestamp,
                SUM(
                    CASE
                        WHEN value <> col__5 THEN 1
                        ELSE 0
                    END
                ) OVER (
                    ORDER BY
                        CASE
                            WHEN timestamp IS NULL THEN 1
                            ELSE 0
                        END,
                        timestamp ASC
                ) + 1 AS gid
            FROM
                (
                    SELECT
                        Value.*,
                        LAG(value) OVER (
                            ORDER BY
                                CASE
                                    WHEN timestamp IS NULL THEN 1
                                    ELSE 0
                                END,
                                timestamp ASC
                        ) AS col__5
                    FROM
                        Value
                ) sub__6
        ) Value1
    GROUP BY
        gid
)
SELECT
    gid,
    id,
    value,
    effective_from,
    LEAD(
        effective_from,
        1,
        TO_DATE('9999-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
    ) OVER (
        ORDER BY
            gid
    ) AS effective_to
FROM
    Value2
ORDER BY
gid;

You only need to verify the logic of each of the above four steps – no need to manually debug the SQL – and the compiler generates the production-ready code.

SQLazy lets you describe logic using business language, rather than writing nested queries in SQL syntax. This example of “dividing time intervals based on value change” has one core statement: segment value; change, which directly corresponds to the business requirement of “starting a new group whenever the value changes”. To write SQL manually, you need to understand the window boundaries of LAG/LEAD, manually handle NULLs, and adjust for syntax differences across different databases. SQLazy, however, compresses this complex logic into 4 intuitive steps – sort, segment, aggregate, and take the the next row’s timestamp as the end timestamp. The intermediate result of each step can be verified independently, and the compiler generates the final runnable SQL. You just need to verify the business meaning of each step, and leave the rest to the compiler.

Try SQLazy online: sqlazy.com (Free to use, signup not required)
SQLazy project repository: github.com/SPLWare/SQLazy

SQLazy: Merging Overlapping Time Intervals

Judy — Thu, 16 Jul 2026 02:23:06 +0000

Problem description

A table stores multiple time intervals for each account, and the intervals may overlap.

Source data:

Expected output (merge overlapping intervals for each account):

Take account A as an example:

The first three intervals (6/20-6/29, 6/25-7/25, 7/20-8/26) overlap and can be merged into 6/20 - 8/26;

The nonoverlapping interval 12/25 - 1/25;

The overlapping intervals (4/27-7/27, 6/25-7/14, 7/10-8/14) can be merged into 4/27-8/14;

The nonoverlapping interval 9/10-11/12.

Apply the same merging operations to account B: merge any intervals that overlap.

Step-by-step implementation with SQLazy

Key approach: Check whether the current interval’s start date is later than the maximum end date among all previous intervals. If it is, the current interval does not overlap with any of the previous intervals and a new group should be started; otherwise, merge it into the current group.

[Run this example online]

Step 1: Sort rows by account and the start date

sort account_id, start_date

Sort rows by account_id and start_date in ascending order, ensuring intervals within each account are processed in chronological order.

Step 2: Calculate the maximum end date among all rows before the current row

compute end_date[:-1] max as prev_max; partition account_id

Within each account partition, calculate the maximum end_date value among all rows from the first row up to, but excluding, the current row, and record it as prev_max.

Step 3: Segment rows based on the condition and assign group numbers

segment condition start_date > prev_max as gid; partition account_id

Check each row in sequence: if start_date > prev_max, the current interval does not overlap with any of the previous intervals and a new group is started (gid+1); otherwise, the row is assigned to the current group.

Step 4: Aggregate by account and group number

summarize start_date min as start_date, end_date max as end_date; group account_id, gid

For each group, extract the earliest start date and the latest end date to form a merged interval.

Finally, remove the helper column gid.

Compile the steps into SQL

Once the above steps are complete and verified, SQLazy’s compiler can automatically generate the equivalent native SQL (using MySQL as an example):

WITH t2 AS (
    SELECT
        account_id,
        start_date,
        end_date,
        MAX(end_date) OVER (
            PARTITION BY account_id
            ORDER BY
                CASE WHEN account_id IS NULL THEN 1 ELSE 0 END,
                account_id ASC,
                CASE WHEN start_date IS NULL THEN 1 ELSE 0 END,
                start_date ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prev_max
    FROM
        acc
),
t3 AS (
    SELECT
        account_id,
        start_date,
        end_date,
        prev_max,
        1 + SUM(
            CASE
                WHEN start_date > prev_max THEN 1
                ELSE 0
            END
        ) OVER (
            PARTITION BY account_id
            ORDER BY
                CASE WHEN account_id IS NULL THEN 1 ELSE 0 END,
                account_id ASC,
                CASE WHEN start_date IS NULL THEN 1 ELSE 0 END,
                start_date ASC
        ) AS gid
    FROM
        t2
)
SELECT
    account_id,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM
    t3
GROUP BY
    account_id,
    gid
ORDER BY
    account_id,
    start_date;

You only need to verify the logic of each of the four steps – no need to understand or debug the SQL – and the compiler will generate the production-ready code.

Why SQLazy is more efficient

SQLazy lets you describe logic using business language, rather than writing nested queries in SQL syntax. This example of “merge overlapping intervals” is expressed clearly in just 4 steps – sort, calculate the maximum end date among the preceding rows, segment rows by condition, and aggregate. The compiler generates the final SQL for you, and you only need to verify the business meaning of each step.

Try SQLazy online: sqlazy.com (Free to use, signup not required)
SQLazy project repository: github.com/SPLWare/SQLazy

SQLazy: Account-based Grouping with Sequence Number Reset on Gaps Exceeding 1 Hour

Judy — Tue, 14 Jul 2026 02:50:39 +0000

Problem description
Group by account and reset the sequence number when the time interval between events exceeds 1 hour

A table consists of two fields: account_number and dt. Records within each account are ordered by time, and sequence numbers (Seq) need to be generated according to the following rules:

Group records by account and order them by datetime in ascending order.

If the interval between the current event time and the previous one exceeds 1 hour, reset the sequence number to 1.

Otherwise (the interval ≤ 1 hour), add 1 to the sequence number.

Source data:

account_number dt

19 2024-04-03 07:02:02

19 2024-04-03 07:02:41

19 2024-04-03 14:58:49

19 2024-04-03 19:58:49

19 2024-04-05 14:58:49

19 2024-04-05 14:59:31

19 2024-04-17 23:56:13

20 2024-04-17 23:59:13

19 2024-04-18 00:15:13

19 2024-04-18 14:56:13

20 2024-04-18 07:41:55

20 2024-04-18 19:41:55

20 2024-04-18 19:56:55

19 2024-04-19 07:41:55

19 2024-04-19 07:42:20

19 2024-04-19 08:41:20

Expected output:

19 2024-04-03 07:02:02 1

19 2024-04-03 07:02:41 2

19 2024-04-03 14:58:49 1

19 2024-04-03 19:58:49 1

19 2024-04-05 14:58:49 1

19 2024-04-05 14:59:31 2

19 2024-04-17 23:56:13 1

19 2024-04-18 00:15:13 2

19 2024-04-18 14:56:13 1

19 2024-04-19 07:41:55 1

19 2024-04-19 07:42:20 2

19 2024-04-19 08:41:20 3

20 2024-04-17 23:59:13 1

20 2024-04-18 07:41:55 1

20 2024-04-18 19:41:55 1

20 2024-04-18 19:56:55 2

Step-by-step implementation with SQLazy

[Run this example online]

Let me walk through each step below:

Step 1: Sort rows by account and datetime in ascending order

sort account_number asc, dt asc

Sort the original data by account_number and dt in ascending order. This step ensures that records within each account are processed in chronological order.

Step 2: Partition records, check whether the time interval exceeds 1 hour, and generate the group number

segment condition ((dt[-1] elapse 3600 second)<= dt) partition account_number as grp

Within each partition of account, check whether the interval between the current row’s dt and the previous row’s dt exceeds 1 hour (3,600 seconds). The condition (dt[-1] elapse 3600 second)<= dt means that after adding 3,600 seconds to the previous row’s timestamp, it is less than or equal to the current row’s timestamp. If so, the interval is greater than or equal to 1 hour, and a new group begins; otherwise, the current row remains in the same group as the previous row. In the end, a group number (grp) is assigned to each record.

Step 3: Generate sequence numbers within each subgroup, partitioned by account_number, grp

compute # as seq partition account_number, grp

“#” represents a sequence number automatically generated in order within each subgroup, starting from 1. Records are partitioned by account number and group number, and within each subgroup, sequence numbers (seq) are assigned in the existing order of the records (which have already been sorted).

Running this step produces the following output:

Finally, remove the helper column grp, or simply export the required columns, to obtain the final output.

Compile the steps into SQL

Once the above steps are complete, SQLazy’s compiler can generate the equivalent native SQL, without the need to write it manually.

Below is the generated SQL statement written in the target database dialect (MySQL in this example):

WITH t2 AS (
  SELECT
    account_number,
    dt
  FROM
    numEvents
),
t3 AS (
  SELECT
    account_number,
    dt,
    SUM(
      CASE
        WHEN (col__5 + INTERVAL 3600 SECOND <= dt) THEN 1
        ELSE 0
      END
    ) OVER (
      PARTITION BY account_number
      ORDER BY
        CASE
          WHEN account_number IS NULL THEN 1
          ELSE 0
        END,
        account_number ASC,
        CASE
          WHEN dt IS NULL THEN 1
          ELSE 0
        END,
        dt ASC
    ) + 1 AS grp
  FROM
    (
      SELECT
        t2.*,
        LAG(dt) OVER (
          PARTITION BY account_number
          ORDER BY
            CASE
              WHEN account_number IS NULL THEN 1
              ELSE 0
            END,
            account_number ASC,
            CASE
              WHEN dt IS NULL THEN 1
              ELSE 0
            END,
            dt ASC
        ) AS col__5
      FROM
        t2
    ) sub__6
)
SELECT
  account_number,
  dt,
  grp,
  ROW_NUMBER() OVER (
    PARTITION BY account_number,
    grp
    ORDER BY
      account_number,
      CASE
        WHEN account_number IS NULL THEN 1
        ELSE 0
      END,
      account_number ASC,
      CASE
        WHEN dt IS NULL THEN 1
        ELSE 0
      END,
      dt ASC
  ) AS seq
FROM
  t3
ORDER BY
  account_number,
  CASE
    WHEN account_number IS NULL THEN 1
    ELSE 0
  END,
  account_number ASC,
  CASE
    WHEN dt IS NULL THEN 1
    ELSE 0
  END,
  dt ASC

You only need to verify the logic of each of the three steps – no need to understand or debug the SQL – and the compiler will generate the production-ready code.

Let’s compare SQL and SQLazy in a table:

This example demonstrates how concisely SQLazy handles “time-interval-based session segmentation” problem – using a segment condition to directly express the rule “start a new group when the gap exceeds 1 hour”, combined with row numbers within each sub-partition. The method precisely maps to the business requirement.

Try SQLazy online: sqlazy.com (Free to use, signup not required)
SQLazy project repository: github.com/SPLWare/SQLazy

Compilable and Executable Pseudocode (spec) Solves AI Coding Hallucinations

Judy — Thu, 09 Jul 2026 03:08:21 +0000

The AI-generated SQL – no one dares to say they fully “understand” it

First, look at the following SQL query, which aims to find credit card customers whose transaction amount has increased for three consecutive months:

WITH ranked AS (
  SELECT customer_id, month, amount,
    LAG(amount,1) OVER(PARTITION BY customer_id ORDER BY month) as prev_1,
    LAG(amount,2) OVER(PARTITION BY customer_id ORDER BY month) as prev_2
  FROM transactions
)
SELECT customer_id, COUNT(*) FROM ranked
WHERE amount > prev_1 AND prev_1 > prev_2
AND prev_1 IS NOT NULL AND prev_2 IS NOT NULL
GROUP BY customer_id HAVING COUNT(*) >= 3

The syntax is correct, and it runs. But is the logic sound? No one can say for sure right away.

What is the real problem?
In AI-assisted programming, there is a basic workflow: the user writes a clear specification and AI generates runnable code.

Then what can we do? Is there any way to turn a “likely answer” to a “definitive one”?

Compilable pseudocode: an advanced form of the specification
Traditionally, the requirements document that programmers create is intended for humans. It contains natural language, charts and diagrams, and pseudocode. In the era of AI, the spec can evolve into a new form with two characteristics:

Human-readable: No need to be familiar with the target language (SQL, Java, Python), you can grasp the logic at a glance.
Machine-compilable: The specification has a fixed syntactic structure and can be deterministically transformed into final code, without relying on AI guessing.

SQLazy’s workflow: The compilable pseudocode
SQLazy brings this concept to the field of complex SQL development. Its workflow is essentially compilable pseudocode: developers describe data querying logic step by step in natural language, previewing the intermediate result at each step. Once the logic is confirmed correct, the compiler deterministically generates the SQL.