DEV Community: Judy

No More Rewriting the Same Logic for Every Database

Judy — Wed, 02 Sep 2026 06:16:31 +0000

First, let me ask you: how many databases does your team use?

MySQL for OLTP, PostgreSQL for OLAP, Snowflake for the data warehouse, and occasionally, running a report on some legacy Oracle system – that’s practically standard for every data team today.

Now comes the question: How many times do you need to implement the same business logic?

The “dialect tax” every database imposes

SQL has a subtle concept called “dialect.” Standard SQL is one thing, but every database has its own “accent”:

MySQL has LIMIT. Oracle has ROWNUM. SQL Server has TOP.

Date functions: MySQL has DATE_ADD. PostgreSQL has INTERVAL. Oracle has ADD_MONTHS.

String concatenation: MySQL has CONCAT. SQL Server has +. PostgreSQL has ||.

Window function support, recursive CTE syntax, and GROUP BY strictness — every database has its own way of doing things.

You spend a whole afternoon getting a complex analytical SQL query working on MySQL. It runs beautifully. Then comes the requirement: run the same logic on Snowflake. You open the editor, copy the SQL over, hit run — and it throws an error.

Not because the logic is wrong, but because you are using the wrong dialect.

So, you start fixing it: replace LIMIT with QUALIFY, rewrite every date function, change the string concatenation syntax… Eventually, it works. But what happens when the requirements change again? Rewrite everything all over again? And what if the next project uses a different database?

You spend more time translating between dialects than thinking through the logic.

This isn’t an isolated case. According to a Stack Overflow survey, developers spend a significant amount of their coding time dealing with environment setup and compatibility issues. And SQL dialect differences are one of the most subtle yet costly forms of “compatibility tax”.

Worse still, the cost compounds. Every new database you support means one more SQL code version to maintain. Three databases = three SQL code versions. The change of one business rule means three places to update. Miss one update = production data don’t match = a midnight page to fix bugs.

Why can’t we “write once, run everywhere”?

You might think: “Then why not just use standard SQL?”

In theory, yes. But in practice, standard SQL cannot cover real-world business requirements. Dialect differences in window function support, the many different approaches to date and time handling, and different implementations of string operations – these are either undefined or loosely defined in the standard SQL, leaving each database with its own implementation.

Think about it differently: you don’t need “one SQL running everywhere”; you need “one logic generating SQL everywhere.”

These are two very different concepts.

“One SQL running everywhere” means sending the same SQL text to every database – and this approach simply doesn’t work.

“One logic generating SQL for every database” means writing the logic once and letting a tool compile it into each database’s dialect – and this is a path that works.

SQLazy’s approach: Write logic once, adapt to every dialect automatically

SQLazy takes exactly the second approach.

You express your logic as a workflow – a readable, step-by-step sequence of operations. The compiler then translates it into native SQL for the target database.

For example, the logic of “calculating the longest streak of consecutive up days of a stock” can be expressed as the following workflow:

This workflow is database-independent. It describes the logic, not SQL syntax.

Then you tell the compiler the target database – MySQL, PostgreSQL, Oracle, Snowflake, or BigQuery, and it automatically generates native SQL for that database.

With the same workflow, simply switch the database option to generate SQL in a different dialect. You don’t need to rewrite anything.

The workflow stays the same because the logic stays the same. What changes is only the “accent” output by the compiler.

What does this mean?

First, you only need to maintain one version of the logic. When business requirements change, only update the workflow. The compiler regenerates native SQL for every database – no more “updating MySQL but forgetting Oracle”.

Second, it’s easier for new developers to get started. They don’t need to learn database dialect differences – they only need to understand the workflow, while the compiler handles the dialect translation. The workflow is for humans to read; SQL is for databases to execute.

Third, migration costs are nearly zero. Moving from MySQL to PostgreSQL? Just switch the database option, recompile, and the compiler automatically adapts to every SQL dialect. No need to modify code line by line, and no more dialect pitfalls.

Fourth, auditing becomes simpler. You audit the workflow – a readable representation of the logic, instead of hundreds of lines of SQL code versions scattered across different databases.

SQL dialect differences are not a minor inconvenience. They are the hidden tax data teams pay every day. Every additional database means more maintenance costs and a higher risk of errors.

SQLazy’s solution is simple: let compilers, instead of humans, translate dialects.

Your job is to write clear logic, and leave the rest to the compiler. You can also use an LLM to help write the logic. Natural language descriptions can then be standardized into SQLazy statements.

AI writes the logic. A compiler writes the SQL.

SQLazy: Search for Adjacent Records at a Specified Offset Within Groups

Judy — Wed, 26 Aug 2026 08:14:33 +0000

Problem Description

In a table, ProductionLine_Number is the grouping field, and within each group records are sorted by date_Time. The task is to search, within each group, all records whose Cardboard_Number equals a specified string, then take the records within a specified offset before and after each matched record, merge and remove duplicates before outputting.

Source Data

Expected Result

In the ProductionLine_Number=1 group, sorted by time, the ids are 2,4,5,6,7,8, where the row matching spL1ml82N4o is id=4. Taking 2 rows before and after id=4 gives ids 2,4,5,6.

In the ProductionLine_Number=2 group, sorted by time, the ids are 9,10,11,12, where the row matching spL1ml82N4o is id=11. Taking 2 rows before and after id=11 gives ids 9,10,11,12.

Merging and deduplicating both groups gives 2,4,5,6,9,10,11,12. Note that ids 7 and 8 are more than 2 rows away from the matched row id=4, so they are excluded.

SQLazy Step-by-Step Implementation

Core idea: After sorting by grouping field and time within groups, use compute to flag the rows matching the target value within each group, then use the relative-position range syntax flag[-2:2] to take a window of 2 rows before and after the current row, and check whether the window contains a matched row. If so, the current row falls within the result range. Finally filter and deduplicate.

[Click to run this example online]

The steps are explained below.

Step 1: Sort by grouping field and time within groups

sort ProductionLine_Number, date_Time asc

Sort the data by ProductionLine_Number to group records, and by date_Time ascending within each group, ensuring subsequent relative-position calculations follow the correct time order.

Step 2: Flag the rows matching the target value
compute (if (Cardboard_Number = “spL1ml82N4o” then 1)) , as flag; partition ProductionLine_Number
Within each ProductionLine_Number group, mark rows where Cardboard_Number equals the target string spL1ml82N4o as 1, leaving others empty. partition confines the flagging to each group independently.

Step 3: Use range syntax to check whether the window contains a match (core)
compute flag[-2:2] , max , as in_range; partition ProductionLine_Number
This is the core step, using SQLazy’s relative-position range syntax. flag[-2:2] takes the flag values in the window from 2 rows before to 2 rows after the current row, then aggregates with max: as long as the window contains a matched row (flag=1), the current row’s in_range is 1. Thus every record within the offset range of each matched row is covered.

Step 4: Filter records within the range
filter in_range = 1
Keep only rows where in_range is 1, i.e., records falling within the offset range before or after a matched row.

Step 5: Remove duplicate records
distinct id
When the offset ranges of multiple matched rows overlap, the same row may be selected more than once. Use distinct id to deduplicate and output the final result.

Generated SQL

After confirming the above steps, the SQLazy compiler automatically generates native SQL (MySQL syntax):

WITH t2 AS (
    SELECT id, Cardboard_Number, date_Time, ProductionLine_Number
        , CASE
            WHEN Cardboard_Number = 'spL1ml82N4o' THEN 1
            ELSE NULL
        END AS flag
    FROM table1
),
t3 AS (
    SELECT id, Cardboard_Number, date_Time, ProductionLine_Number, flag
        , MAX(flag) OVER (PARTITION BY ProductionLine_Number ORDER BY CASE
            WHEN ProductionLine_Number IS NULL THEN 1
            ELSE 0
        END, ProductionLine_Number ASC, CASE
            WHEN date_Time IS NULL THEN 1
            ELSE 0
        END, date_Time ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS in_range
    FROM t2
),
t4 AS (
    SELECT id, Cardboard_Number, date_Time, ProductionLine_Number, flag
        , in_range
    FROM t3
    WHERE in_range = 1
)
SELECT id, Cardboard_Number, date_Time, ProductionLine_Number, flag
    , in_range
FROM t4
GROUP BY id

SQLazy lets you describe logic in business language instead of writing nested SQL queries. In this example of searching adjacent records at a specified offset within groups, the highlight is the relative-position range syntax: flag[-2:2] expresses a 2-row window before and after with a simple index range, corresponding to the verbose ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING in SQL. Combined with compute's max aggregation, one line determines whether the window contains a match. partition keeps all calculations independent within each group, and step-by-step computation lets every intermediate result be verified.

Official Links
SQLazy Online Experience: sqlazy.com (free, no registration required)
SQLazy Repository: github.com/SPLWare/SQLazy

Trae + SQLazy: A Practical Guide to Writing Complex SQL

Judy — Mon, 24 Aug 2026 08:05:27 +0000

Introduction

AI-assisted data development is reshaping the way SQL is traditionally written. Today’s mainstream approaches can be broadly divided into two categories: one is the “end-to-end” approach, which directly generates native SQL; the other is the “layered” approach, which generates structured intermediate representations and then compiles them into SQL. The former is easy to get started with but difficult to handle complex business scenarios; the latter adds an abstraction layer, which enables engineering-grade guarantees of auditability, debuggability, and reproducibility.

The approach adopted in this article is a combination of Trae (ByteDance’s AI programming tool) as the “brain”, responsible for understanding requirements, clarifying ambiguities, and generating SQLazy step-by-step scripts (.nspl); and SQLazy’s dedicated IDE as the “execution layer”, responsible for syntax validation, step-by-step debugging, and cross-database compilation. Together, they form a closed loop of “AI planning + human review + deterministic engine execution”.

We selected four real-world cases, ranging from simple to complex, covering typical scenarios such as statistical aggregation, multi-table merging, cross-subgroup data filling, and amount allocation. The complete process – from requirement input to validation completion– is demonstrated, with emphasis on documenting the errors encountered and the reasoning behind corrections.

Tool Collaboration Model

2.1 Trae’s Roles and Capabilities

Trae plays three key roles in this workflow:

Automatically Load the Project Knowledge Base

Deploy the global knowledge base specification file (.md) described in Section 3.1 – Environment Setupto the project and register it as a centralized specification:

SQLazy script output format specifications (three tab-separated columns, one function per step)

Hard constraints (reserved word handling, cross-step reference rules, etc.)

Loading paths for function and feature documentation

When the user enters /sqlazy-plan followed by business requirements in the chat box, Trae automatically inherits all the rules above without needing to restate them.

Structured Four-Step Output

Trae is constrained to generate solutions following the four steps below instead of directly providing a final answer:

Capability Overview: List the functions and features involved in the task.
Requirement Decomposition: Break down business requirements into a sequence of data processing steps.
Feature Matching: Match each step with the appropriate SQLazy feature.
Code Generation: Generate the final step-by-step script (.nspl).

This enforced output process ensures the auditability of the solution – the rationale behind each step is clearly visible.

Proactive Requirement Clarification

Facing complex requirements, Trae proactively raises key questions, such as: How should the date range be defined? How should null values be handled? Is the grouping key unique? This prevents the AI from making unsupported assumptions about unstated conditions.

2.2 SQLazy's Core Value

SQLazy provides a dedicated IDE for writing and executing .nspl scripts. Its core value lies in three areas:

Clear Step Semantics, Low Audit Complexity

Taking "longest streak of consecutive up days for a stock" as an example, the .nspl script requires only 5 steps: filter → sort → segment →count → find the maximum. Reading the entire script feels like reading a business operation checklist, rather than parsing complex nested SQL. Each line represents one step; the output of each step becomes the input for the next, with the entire logic laid out explicitly.

Step-by-Step Execution, Quick Problem Identification

In the IDE, you can execute each step individually and inspect intermediate results in real time. Once a step's output doesn't meet expectations, you can immediately pinpoint the specific logic error without having to dig through dozens of lines of nested SQL.

One Script, Compiled Everywhere

After validation passes, one click compiles to native SQL for MySQL, PostgreSQL, Oracle and other mainstream databases. No rewriting per database.

Workflow

3.1 Environment Setup

The project adopts the following standard directory structure:

project_root/
├── plan.md # Global specification: format conventions, loading paths
├── sqlazy-plan.md # Command entry: /sqlazy-plan trigger, inherits all rules from plan.md
├── nspl/ # Delivery directory: .nspl scripts stored here
├── function/ # Function reference documentation (auto-loaded)
└── action/ # Action reference documentation (auto-loaded)

After creating a new project in Trae, copy sqlazy-plan.md, plan.md files, function/ and action/ directories from the SQLazy installation directory's LLM folder to the project root.

3.2 How to Trigger

Use the /sqlazy-plan command in the Trae chat box to trigger the task, followed by a complete business requirement description. It is best to specify the tables, fields, join relationships, grouping dimensions, time range, and output requirements – all in one go.

3.3 Validation and Correction

This is the most critical step in the entire workflow:

Construct a small set of representative test data and manually calculate the expected results.
Run the script step by step in the SQLazy IDE, comparing intermediate results against expected values.
When issues are found, modify the script directly or report them to Trae for regeneration.
After validation passes, compile to native SQL for the target database.
Hands-on Case Studies

The following four cases, from easy to hard, fully document the problem-solving process.

Case 1: Longest Streak of Consecutive Up Days for a Stock

Requirement:

/sqlazy-plan Stock price table stock contains three columns – CODE,DT (date), and CL (closing price). Calculate the maximum number of consecutive days the stock with code 100046 has been rising (i.e., each day’s closing price is higher than the previous day’s).

Analysis and Implementation:

This is the simplest type of statistical requirement. Trae outputs the following script following the four-step process:

Simple statistical requirement, generated correctly by AI in one attempt. Run and validate directly in the SQLazy IDE, then compile to native SQL for the target database.

Case 2: Merging Multiple Tables by ID into Single Rows

Requirement:

/sqlazy-plan There are four data tables, T1, T2, T3, and T4, with similar structures. Each table has two fields: the first field is an ID (named id, id2, id3, and id4 respectively), and the second field is named colA, colB, colC, and colD respectively. The goal is to merge these four tables by their ID values into a single result table with 9 columns: the first column, ID_main, stores the ID value, and the remaining 8 columns contain the fields from the four tables (i.e., all fields from T1, T2, T3, and T4). Each distinct ID appears as exactly one row in the merged table. If an ID is missing from any of the original tables, the corresponding columns from that table are set to NULL.

Analysis and Implementation:

The core challenge of this requirement is that the four tables have different ID field names (id, id2, id3, id4), and a method is needed to join them while ensuring no IDs are lost.

Trae designed a dual-track strategy of "full join + ID coalescing," outputting an 8-step script:

Same as Case 1, correct on first attempt.

Case 3: Cross-Group Sequential Field Value Filling

Requirement:

/sqlazy-plan Given a data table lines, where the first two columns, Group1 and Group2, are grouping columns, the third column, LineID, is a unique row identifier, and the fourth field, TargetField, is a numeric target column. After sorting by Group1, Group2, and LineID, within the same Group1, every Group2 has the same number of records; only the last Group2 (in sort order) has non-NULL values in TargetField, while all other Group2 groups are NULLs. The goal is to copy the TargetField values from the last Group2 group to the other groups within the same Group1, in the same sequential row order (i.e., matching by row positions in sorted order). The final output should be a table containing only the columns Group1, Group2, LineID, and TargetField, sorted in ascending order by the first three columns.

Analysis and Implementation (corrected after one iteration):

The core difficulty of this requirement is that LineID is unique across all rows and cannot be directly used as a cross-group join key. Trae's first version incorrectly used Group1 + LineID as the join key, causing mapping failures. Below is the complete iterative correction process.

First Version (Incorrect)

Trae's first attempt used Group1 + LineID as the join key to fill the TargetField from the last subgroup back to the whole table:

Problem: LineID is unique across all rows (e.g., 101, 105, 201, 205, 301, 305).

Different Group2 groups share no LineID values. Therefore, when using Group1 + LineID as the join key, only the last subgroup’s own rows can match; all other subgroups’ rows fail to match, TargetField remains NULL, and the fill logic completely fails.

Corrected Version: Using Row Number as Mapping Bridge

Instead of using LineID for joining, the “row number” (positional sequence 1, 2, 3, ... obtained by ranking within each Group2 by LineID) serves as the mapping bridge. Since every Group2 within the same Group1 has the same row count, the “Nth row” naturally corresponds across different Group2 groups.

Validation Example:

Assume the data is as follows (all LineID values are unique):

After t2 ranking, a row sequence number row_idx is generated: G2-1(101→1, 105→2), G2-2(201→1, 205→2), G2-3(301→1, 305→2). t3 takes the two records of G2-3. t4 creates a mapping table (A,1→100), (A,2→200). After t5’s backfill, the first row of every subgroup gets 100, and the second row gets 200.

When LineID is unique across the entire table, it cannot be directly used as a cross-group join key. You must first rank within each subgroup to obtain a row sequence number, and use the row number as the mapping bridge. The maximum-rank filter can directly select the last subgroup without requiring the additional aggregation and backfill steps.

Case 4: Invoice Amount Split by Account, with Total Preserved

Requirement:

/sqlazy-plan For the invoice table i (containing fields invoiceid, amount, projectid) and the project table p (containing fields id, projectid, accountcode), join them on projectid. For the joined result, add a new allocation field, splitamount, to implement a splitting logic that distributes the amount by the number of accounts under each project, while ensuring the total sum remains preserved. Within each group, sort by accountcode in ascending order. For the 2nd through the Nth account, calculate splitamount using amount/ total_number_of_accounts and round the result to 2 decimal places. The first account absorbs the rounding remainder; its splitamount equals the invoice’s original amount minus the sum of all other accounts’ splitamount values, so that the allocated amounts exactly match the original invoice amount.

Analysis and Implementation (finalized after two iterations):

This is the most complex of the four cases. Although Trae’s first version correctly identified the partitioned aggregation approach, there were issues with syntax details and step organization. It took two iterations to finalize.

First Iteration: Partitioned Aggregation (partially corrected)

Trae used a mathematically equivalent approach – partitioned aggregation to calculate the remainder – convert “first row’s remainder = total amount – sum of all the other rows’ allocated values” within the partition to amount - sum_temp_split + temp_split, where sum_temp_split is the sum of base allocated values across all rows in the partition.

Two issues were found after running:

Issue 1: Line 4 used * as the count formula, causing the SQLazy parser to report the error “logic error near [*]”. The current version does not support * as a count formula.

Issue 2: User feedback: “Complete the calculation directly in one step based on whether rank=1, without intermediate temporary columns” – they want to merge the base allocation calculation and the final conditional calculation into a single step, reducing the number of intermediate steps.

Second Iteration: Final Version

Two separate corrections were applied:

Changed * to the specific field projectid (counting projectid within a partition is equivalent to counting rows)

Combine the original t5 and t6 steps into one – within a single computed-column statement, create three derived columns separated by semicolons.

Below is the final script:

Validation Example:

projectid=1, amount=100.00, 3 accounts (accountcode=1, 2, 3)

The splitting logic is correct: the first account absorbs the rounding difference of 0.01, and the total is preserved. Three key lessons from this case: ① * is not supported in the current SQLazy environment; specific field names must be used; ② In computed-column statements, aggregate parameters and cross-row parameters are mutually exclusive and cannot be used simultaneously; ③ Reserved words (such as sum) used as names must be enclosed in single quotes.

Conclusion

The core value of the Trae + SQLazy combination does not lie in “letting AI write SQL automatically”, but rather in constraining AI’s uncertainty within an auditable, debuggable intermediate layer, then letting a deterministic engine handle the final execution.

In this workflow, the three parties have clear division of labor:

Trae handles understanding requirements, clarifying ambiguities, and generating the structured initial nspl draft – this is what AI does best: “structuring fuzzy problems”.

SQLazy IDE handles syntax validation, step-by-step debugging, and cross-database compilation — this is the reliable execution by a deterministic engine.

Humans are responsible for verifying business rules, validating test results, and correcting logic deviations – this is irreplaceable business judgment.

Reviewing the four cases: from the simple statistics that passed on first attempt, to the multi-table merge generated correctly in one pass, to the cross-group filling that required correcting the join key, to the invoice split finalized after two iterations – each step confirms the feasibility of the “AI assistance + human review + small-sample validation” approach. In practice, this workflow can be standardized to make AI a true productivity amplifier rather than a source of risk.

SQLazy: Fill Field Values by Sequence Across Sub-Groups

Judy — Wed, 19 Aug 2026 09:00:53 +0000

Problem Description

A table contains Group1 and Group2 as grouping fields, LineID as the sequence number within each group, and TargetField as the target field to fill. After sorting by Group1, Group2, and LineID, within the same Group1, each Group2 has the same number of records; only the last Group2 has TargetField values, while the others are empty. The goal is to copy the values from the last sub-group within each big group to fill the same-row positions of other sub-groups.

Source Data

Expected Result

Group1=2 has 2 sub-groups (Group2=4,5), each with 3 records. The last sub-group Group2=5 has TargetField [5,3,4]; these values are copied to the other sub-group Group2=4.

SQLazy Step-by-Step Implementation

Core idea: First use rank to generate row numbers rn within each (Group1, Group2), then leverage the fact that only the last sub-group has values. Use compute to summarize TargetField by (Group1, rn). Each (Group1, rn) group has only one non-null row, so sum returns that value, naturally copying the last sub-group's values to the same-row positions of other sub-groups.

[Click to run this example online]

The steps are explained below.

Step 1: Sort by grouping fields and sequence

sort Group1, Group2, LineID

Sort the data by Group1, Group2, LineID in ascending order to ensure records within each big group are processed in sub-group and row number order.

Step 2: Generate row numbers within each sub-group

rank as rn; partition Group1, Group2

Use rank within each (Group1, Group2) partition to generate row numbers rn. Since each sub-group has the same number of records, within the same big group, rn=1 rows come from each sub-group's first record, rn=2 from the second, and so on.

Step 3: Aggregate by row number to fill values (core)

compute TargetField sum as tempTarget; partition Group1, rn

This is the core step. Group by (Group1, rn) and use sum to aggregate TargetField. Since only the last sub-group has non-null TargetField values (others are NULL), sum ignores NULL. Each (Group1, rn) group has only one valid value, so sum returns that value, naturally copying the last sub-group's values to the same-row positions of other sub-groups.

Step 4: Select the final result columns

derive Group1, Group2, LineID, tempTarget as TargetField

Generated SQL

After confirming the above steps, the SQLazy compiler automatically generates native SQL (PostgreSQL syntax):

WITH t1 AS (
  SELECT Group1, Group2, LineID, TargetField
  FROM lines
),
sub__6 AS (
  SELECT
    sub__5.*,
    RANK() OVER (PARTITION BY Group1, Group2 ORDER BY Group1, Group2, LineID) AS rn
  FROM t1 sub__5
),
t3 AS (
  SELECT
    Group1, Group2, LineID, TargetField, rn,
    SUM(TargetField) OVER (PARTITION BY Group1, rn) AS tempTarget
  FROM sub__6
)
SELECT Group1, Group2, LineID, tempTarget AS TargetField
FROM t3

SQLazy lets you describe logic in business language instead of writing nested SQL queries.

The solution process is divided into 4 steps, each of which can independently verify intermediate results, reducing the probability of errors in complex logic. SQLazy's rank function generates row numbers directly within partitions, more concise than SQL's RANK() OVER. The compute function combined with sum naturally aggregates unique non-null values from the same row position across sub-groups, replacing explicit cross-row references.

Official Links
SQLazy Online Experience:sqlazy.com(free, no registration required)
SQLazy Repository:github.com/SPLWare/SQLazy

SQLazy: Identify Whether Differences Within Groups Come from Brand or Type Problem Description

Judy — Fri, 07 Aug 2026 06:53:47 +0000

The ID field of table tbl represents car categories, with each category further divided into Brand and Type. The task is to group by ID and determine the source of differences within each group: if a group has multiple brands, Difference is "Brand"; if a group has multiple types, Difference is "Type". The same ID may produce multiple records.

Source Data

Expected Result

ID=1 has both Honda and Jeep as brands, as well as Coupe and SUV as types, so Difference produces both Brand and Type.

ID=2 only has Ford as a brand, but has Sedan and Crossover as types, so only Type is produced.

SQLazy Step-by-Step Implementation

Core idea: First use summarize to group by ID and count distinct brands (cntBrand) and types (cntType), then use expand to cross-join the results with the dimensions ("Brand" and "Type"), and finally use filter to keep only the rows that satisfy the conditions.

[Click to run this example online]

The steps are explained below.

Step 1: Group by ID and count distinct brands and types

summarize Brand icount as cntBrand, Type icount as cntType; group ID

Use summarize to group by ID. The icount function counts distinct Brand and Type values within each group, recorded as cntBrand and cntType respectively. This step compresses each row of detail data into one row per ID, containing distinct counts.

Step 2: Expand the dimension list into rows

expand ["Brand","Type"] as Difference

The expand function unfolds the constant list ["Brand","Type"] into rows, cross-joining with each upstream row to generate the new column Difference. Each ID gets two rows: Difference="Brand" and Difference="Type", while retaining cntBrand and cntType fields.

Step 3: Filter dimensions that meet the conditions

filter (if ((Difference = "Brand") then cntBrand>1; (Difference = "Type") then cntType>1))

Use filter with conditional branching syntax: for rows where Difference="Brand", check cntBrand>1; for rows where Difference="Type", check cntType>1. Only rows with counts greater than 1 are kept. A single filter statement expresses different conditions for different branches, much more intuitive than SQL's nested CASE WHEN.

Step 4: Select the required columns from the result

derive ID, Difference

Generated SQL
After confirming the above steps, the SQLazy compiler automatically generates native SQL (PostgreSQL syntax):

WITH Value1 AS (
  SELECT
    ID,
    COUNT(DISTINCT Brand) AS cntBrand,
    COUNT(DISTINCT Type) AS cntType
  FROM (
    SELECT ID, Brand, Type FROM tbl
  ) tbl
  GROUP BY ID
),
Value2 AS (
  SELECT
    Value1.ID, Value1.cntBrand, Value1.cntType, Difference
  FROM Value1
  CROSS JOIN (
    SELECT 'Brand' AS Difference
    UNION ALL
    SELECT 'Type'
  ) T_1
)
SELECT ID, Difference FROM Value2
WHERE CASE
  WHEN (Difference = 'Brand') THEN cntBrand > 1
  WHEN (Difference = 'Type') THEN cntType > 1
  ELSE NULL
END

SQLazy lets you describe logic in business language instead of writing nested SQL queries. The solution process is divided into 4 steps, each can independently verify intermediate results, reducing the probability of errors in complex logic. The icount function of summarize can directly count distinct values, replacing SQL's COUNT(DISTINCT ...). The expand function expands a constant list into rows, completing in one line what requires CROSS JOIN with UNION ALL in SQL. The filter function's conditional branching syntax (if ... then ...; ... then ...) makes multi-condition filtering logic clear at a glance, clearer than SQL's nested CASE WHEN.

Official Links
SQLazy Online Experience: sqlazy.com (free, no registration required)
SQLazy Repository: github.com/SPLWare/SQLazy

SQLazy：Merge Multiple Tables into Single Rows by Common ID

Judy — Tue, 04 Aug 2026 06:57:22 +0000

Problem Description

Merge multiple structurally similar tables with different column names into a wide table using full outer joins by common ID. Four tables have similar structures, each with two fields. The fields have the same meaning but different names (id, id2, id3, id4 all represent ID). The goal is to merge the four tables into single rows by ID, with each ID appearing in exactly one row. When an ID is absent in a table, the corresponding columns take NULL.

Source Data
T1 table:

T2 table:

T3 table:

T4 table:

*Expected Result
*

For example, ID=555 appears in both T1 and T2 but not in T3 or T4, so id, colA, id2, colB have values, while id3/colC/id4/colD are NULL.

ID=222 only appears in T3, so only id3 and colC have values; all other columns are NULL.

ID=10 appears in T2 and T4 but not in T1 or T3, so id2, colB, id4, colD have values; all other columns are NULL.

SQLazy Step-by-Step Implementation

Core Idea: First use derive to unify the ID column names of each table to ID_main, making subsequent merging easier. Then start from the first table and perform full outer joins one by one: use join to full outer join the current result with the next table on ID_main, then use derive and nvl to merge the new ID into the ID_main column, appending tables one by one to get the final result.

[Click to run this example online]

The steps are explained below.

Steps 1-4: Unify ID Column Names Across Tables

derive id as ID_main, id, colA

Use derive on T1-T4 to rename their respective ID column names (id/id2/id3/id4) uniformly to ID_main.

Step 5: Full Outer Join T1 and T2

join ID_main; with t2; ID_main; take id2, colB; full

Use the join function to full outer join t1 and t2 on ID_main.

Step 6: Merge NULLs in ID Column

derive nvl(ID_main, id2) as ID_main, id, colA, id2, colB

If a record comes from t2 but is not present in t1, its ID_main is NULL. This step assigns t2.id2 to ID_main in such records, ensuring the ID_main column always has a value. Different SQL implementations use different syntax for this step - for example, Oracle uses nvl and SQL Server uses COALESCE - while NSPL uniformly uses the nvl function, which is automatically translated into the corresponding dialect during compilation based on the database type.

Steps 7-8: Append T3

join ID_main; with t3; ID_main; take id3, colC; full

derive nvl(ID_main, id3) as ID_main, id, colA, id2, colB, id3, colC

Repeat the join + derive pattern to full outer join T3 into the current result. After appending each table, use ifn to merge the new ID into the unified ID_main column.

Steps 9-10: Append T4 to Get Final Result

join ID_main; with t4; ID_main; take id4, colD; full

derive nvl(ID_main, id4) as ID_main, id, colA, id2, colB, id3, colC, id4, colD

Generated SQL
After confirming the above steps, the SQLazy compiler automatically generates native SQL (SQL Server syntax):

WITH t1 AS (
        SELECT id AS ID_main, id, colA
        FROM T1
    ),
    t2 AS (
        SELECT id2 AS ID_main, id2, colB
        FROM T2
    ),
    r1 AS (
        SELECT t1.ID_main, t1.id, t1.colA, t2.id2, t2.colB
        FROM t1
            FULL JOIN t2 ON ID_main = t2.ID_main
    ),
    r1b AS (
        SELECT COALESCE(NULLIF(CAST(ID_main AS VARCHAR(10)), ''), NULLIF(CAST(id2 AS VARCHAR(10)), '')) AS ID_main
            , id, colA, id2, colB
        FROM r1
    ),
    t3 AS (
        SELECT id3 AS ID_main, id3, colC
        FROM T3
    ),
    r2 AS (
        SELECT r1b.ID_main, r1b.id, r1b.colA, r1b.id2, r1b.colB
            , t3.id3, t3.colC
        FROM r1b
            FULL JOIN t3 ON ID_main = t3.ID_main
    ),
    r2b AS (
        SELECT COALESCE(NULLIF(CAST(ID_main AS VARCHAR(10)), ''), NULLIF(CAST(id3 AS VARCHAR(10)), '')) AS ID_main
            , id, colA, id2, colB, id3
            , colC
        FROM r2
    ),
    t4 AS (
        SELECT id4 AS ID_main, id4, colD
        FROM T4
    ),
    r3 AS (
        SELECT r2b.ID_main, r2b.id, r2b.colA, r2b.id2, r2b.colB
            , r2b.id3, r2b.colC, t4.id4, t4.colD
        FROM r2b
            FULL JOIN t4 ON ID_main = t4.ID_main
    )
SELECT COALESCE(NULLIF(CAST(ID_main AS VARCHAR(10)), ''), NULLIF(CAST(id4 AS VARCHAR(10)), '')) AS ID_main
    , id, colA, id2, colB, id3
    , colC, id4, colD
FROM r3

SQLazy lets you describe logic in business language instead of writing nested SQL queries. SQLazy's step-by-step computation breaks multi-table merging into independent steps such as unifying column names, performing full outer joins one table at a time, and merging IDs - each step can be independently verified for intermediate results. The join function, combined with derive and nvl, flexibly handles post-merge operations such as renaming columns and merging NULL values. This strategy of appending tables one by one is much clearer and more maintainable than writing deeply nested FULL JOIN + COALESCE queries in SQL.

Official Links
SQLazy Online Experience: sqlazy.com (free, no registration required)
SQLazy Repository: github.com/SPLWare/SQLazy

SQLazy：Forward Fill NULL Values

Judy — Fri, 31 Jul 2026 06:58:11 +0000

Problem Description

A table records employee information with three fields: id (sort key), name, and dept (department). The dept column contains NULL values that need to be forward filled—each NULL should be replaced with the most recent non-NULL value in the same column.

Source Data

Expected Result

For example, id=4–5 (dept=NULL): take the dept value Sales from id=3.

SQLazy Step-by-Step Implementation

Core idea: First create a logical grouping marker (grp) that increments each time dept is NOT NULL, grouping consecutive NULL rows with their preceding non-NULL row into the same partition. Then use grp as the partition key, taking the max dept value within each partition to forward fill the NULLs. This two-step strategy—create grouping marker first, then aggregate by partition—is SQLazy’s classic pattern for forward-fill problems.

[Click to run this example online]

The steps are explained below.

*Step 1: Sort by id in ascending order
*
sort id asc

Sort by id in ascending order to ensure records are processed in sequence; this is the prerequisite for subsequent grouping and filling.

Step 2: Create a logical grouping marker

compute if ((dept notnull) then 1 else 0) cum as grp

This is the most critical step. Use the computed column with the cum (running total) argument to cumulatively sum the condition if ((dept notnull) then 1 else 0). When dept is NOT NULL, it contributes 1 (starting a new group); when NULL, it contributes 0 (continuing the current group). The cumulative result grp increments by 1 each time dept is non-NULL, dividing the data into partitions.

Step 3: Forward fill by partition
compute dept max as filled_dept; partition grp
Within the grp partition, use the max aggregation to get the dept value. Each partition has only the first row’s dept as non-NULL, the rest are NULL. The MAX function automatically picks the non-NULL value, achieving forward fill.
The partition grp ensures that fills in different partitions do not interfere with each other. This replaces NULL values with the preceding non-NULL value in id order.

Step 4: Use derive to select the output fields
**
**Generated SQL
After confirming the logic of the above 4 steps, the SQLazy compiler automatically generates native SQL (MySQL syntax used here):

WITH t2 AS (
        SELECT id, name, dept
            , SUM(CASE
                WHEN dept IS NOT NULL THEN 1
                ELSE 0
            END) OVER (ORDER BY CASE
                WHEN id IS NULL THEN 1
                ELSE 0
            END, id ASC ROWS UNBOUNDED PRECEDING) AS grp
        FROM forwardFill
    )
    t3 AS (
        SELECT id, name, dept, grp
            , MAX(dept) OVER (PARTITION BY grp) AS filled_dept
        FROM t2
    )
SELECT id, name, filled_dept AS dept
FROM t3
ORDER BY CASE
    WHEN id IS NULL THEN 1
    ELSE 0
END, id ASC
;

SQLazy lets you describe logic in business language instead of writing nested SQL queries. Step-by-step calculation breaks forward fill into independent steps, each verifiable independently. The cum (conditional running total) automatically generates group numbers—this is cleaner than SQL’s window function approach for forward-fill problems.

Official Links

SQLazy Online Experience: sqlazy.com (free, no registration required)
SQLazy Repository: github.com/SPLWare/SQLazy

Code-as-Documentation: An SQL Development Paradigm for the AI Era

Judy — Wed, 29 Jul 2026 06:11:47 +0000

A “runnable but unmaintainable” SQL query

Let’s first look at the following SQL used in a production environment. The query aims to “merge all overlapping time intervals within each account”:

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

This SQL snippet uses window functions MAX()OVER and SUM() OVER for their cumulative-sum technique, along with ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING – syntax most developers would probably need to look up in the documentation. The syntax is completely correct, and the query runs and produces the expected output.

Six months later, the requirement changes – the merging condition shifts from “overlapping alone” to “intervals of no more than 3 days”. Modifying the code to fit this new rule is extremely difficult, basically amounting to a rewrite. You’d need to work out the logic from scratch, mentally re-running the window functions’ computations, just to figure out what to change and how. The code runs, but the cost of understanding it is about the same as rewriting it from scratch.

This is the reality of SQL development: code is written for machines to execute, not for humans to read.

Why self-documentation is particularly hard for SQL

SQL is a declarative language: it tells the database what to do, not how to do. This creates a problem –** business logic is encoded into the syntactic structure instead of being expressed explicitly.
**
A window function like LAG(amount, 1) OVER (PARTITION BY customer_id ORDER BY month) may correspond to the business concept of “calculating last month’s transaction amount”.

A cumulative-sum snippet like SUM(CASE WHEN ... THEN 1 ELSE 0 END) OVER (...) may correspond to the logic of “conditional segmentation”.

But these business semantics are implicit in SQL – readers need to infer for themselves what each snippet is actually doing.

Even worse, comments cannot solve this problem. Code changes, but comments don’t always follow. Over time, comments become less reliable than the code itself. And SQL’s nested structure is inherently resistant to comments. Wedged between multiple layers of parentheses, they only make readability worse.

AI-assisted coding doesn’t solve this problem either. You generate a SQL snippet with AI, execute and commit it – but you don’t put the prompts in the repository. All that’s left is the final SQL. When the requirement changes, you are back to the same SQL, and have to re-decode its logic from scratch. Even if the original spec written for AI is kept, there’s no guarantee the regeneration produces the same SQL – LLMs aren’t deterministic. Update the spec and regenerate, and you might get a completely different query, which means a re-audit is involved. That’s not a modification, that amounts to a rewrite.

Therefore, AI-assisted coding doesn’t solve the root problem: documentation and code falling out of sync.

Unless, spec and SQL are fused into one – permanently paired, so a spec change automatically updates the SQL.

“Code as documentation” is an ideal. SQL makes it further out of reach.

One misconception needs clearing up here: “code as documentation” doesn’t mean “writing lots of comments in the code”. Comments are a supplement. True “code as documentation” means three things:

The code structure itself expresses the logic. Reading the code is as clear as reading documentation – no extra explanation needed.

Code and documentation are the same thing. There’s no such problem as “the code changed but the documentation didn’t”.

When new hires take over, reading the code is enough to understand the business logic – no extra training and verbal handoffs from the last person needed.

In the SQL world, this means SQL itself needs to be self-descriptive. Every line of code should tell the reader clearly “what this step is doing”, not “what this syntax does”. But native SQL can’t do this. Multi-layer nesting is everywhere, and readability suffers badly.

So, the solution is this: make the spec both readable and executable. Spec is documentation; spec is code.

SQLazy’s solution: workflow as documentation

Here’s what SQLazy does: skip writing SQL directly. Instead, describe the logic as a workflow of steps. Each step is an atomic action. The steps combined are the complete business logic.

Take the example of “merging overlapping intervals” again. Here’s how SQLazy’s workflow expresses it:

These lines of workflow read almost like natural language: sort, calculate the maximum end date so far, segment rows by condition, aggregate to get the earliest and latest dates. You can guess what most actions mean without ever having to learn them. Only “segment” needs a bit of explanation – it means “group the data by condition”. But paired with the visual step-by-step execution, one glance at the intermediate result makes it click instantly.

Run this example online: https://www.sqlazy.com/?4Bs

This compilable workflow is itself the documentation:

Every step has clear business meaning: “sort” sorts, “compute” calculates helper values, “segment” groups the data by condition, and “summarize” aggregates.

The order of steps is the order of thinking. First sort, then compute, then segment, then aggregate – exactly how you’d work through this problem manually.

No extra comments are needed to explain “what this SQL is doing”. Every line of the workflow already says it clearly.

Six months later, when the requirement changes, just open the workflow and edit the corresponding step directly.

Code and documentation are one thing, not two. This is “code-as-documentation” actually made real.

Here’s another example: group by account, and reset the sequence number whenever the interval between activities exceeds one hour. Here’s how SQLazy’s workflow expresses it:

Three lines of workflow, business logic clear at a glance: “reset the sequence number whenever the interval exceeds one hour”. The equivalent hand-written SQL needs at least two layers of nested window functions, plus LAG-based time-gap calculations. With the workflow, you don’t need to take care of how the SQL is written – the compiler handles that for you.

The compiler guarantees: documentation and code will never get out of sync

Under the traditional model, documentation and code are separate, kept in sync only by hand – and manual sync is error-prone. Documentation gets written, then the code changes without the docs catching up; or the code changes but the documentation isn’t updated.

SQLazy’s model is this: workflow (documentation) → compiler → SQL (code).

Compile the workflow above into SQL (switchable across different databases):

Documentation is the source. Code is the compiled output. As long as the workflow stays the same, the generated SQL stays the same; when the workflow changes, the SQL regenerates automatically. There’s no more “documentation changed but code didn’t” or “code changed but documentation didn’t” – because they’re two sides of the same thing.

When you switch databases, just change the target option – from MySQL to Oracle or from PostgreSQL to Snowflake – and the compiler automatically generates SQL in the matching dialect. No need to modify the workflow. No need to rewrite the documentation.

In the SQL world, “Code-as-documentation” has always been a hard-to-reach ideal. Without a workflow mechanism like SQLazy’s, code and documentation stay two separate things. You write the SQL, then have to write separate documentation explaining what it does. Syncing the two falls entirely on manual work – and manual work means errors.

SQLazy changes that. It breaks complex SQL into clear steps, which themselves are the documentation, and the compiler guarantees code and documentation stay in sync, permanently.

This isn’t wrapping a shell around SQL. It’s changing the development paradigm from “writing code for machines to read” to “writing logic for humans to read and letting the machine translate it”. The workflow is for humans. The SQL is for the database to run. The compiler keeps the two in sync – no manual work required.

Next time you open a SQL file written three months ago and can’t make sense of it, ask yourself: if you’d written it as a workflow instead, would you still be in this much pain?

SQLazy：Conditional Running Total with Reset

Judy — Mon, 27 Jul 2026 07:32:56 +0000

Problem Description

Conditional running total with reset: restart accumulation when logic is 't'. An event table table_t1 records event sequences with three fields: id (sort key), logic (condition flag, values 't' or 'f'), and val (numeric value for accumulation). A computed column output needs to be added: when logic equals 't', output is set to 1; otherwise output equals the previous row's output plus the current row's val. This is a conditional running total that resets and restarts accumulation when logic is 't'.

Source Data

Expected Result

id=1 (logic=t): Start of a new segment, output = 1

id=2 to id=4 (logic=f): Accumulating row by row within the same segment, output = previous output + current val, yielding 3, 6, 10

id=5 (logic=t): Reset condition met, start a new segment, output = 1

SQLazy Step-by-Step Implementation

Core idea: First create a grouping marker, then accumulate by partition. Generate a logical grouping marker (logic_run) via a computed column; this marker increments each time logic is 't', dividing the data into independent segments. Then use logic_run as the partition key, applying the cum function for conditional accumulation within each partition. This two-step strategy—create grouping marker first, then accumulate by partition—is SQLazy's classic pattern for conditional reset accumulation problems.

[Click to run this example online]

The steps are explained below.

Step 1: Sort by id in ascending order

sort id asc

Sort the data by id in ascending order to ensure records are processed in chronological order; this is the prerequisite for subsequent segmentation and accumulation.

Step 2: Create a logical grouping marker

compute if (logic = 't' then 1 else 0) cum as logic_run

This is the most critical step. Use the computed column with the cum (running total) argument to cumulatively sum the conditional expression if (logic=‘t’ then 1 else 0). When logic is ‘t’, it contributes 1 (indicating the start of a new segment); when logic is ‘f’, it contributes 0 (continuing the current segment). The cumulative result logic_run increments by 1 each time logic is ‘t’, dividing the data into segments: the first 4 records (id=1-4) have logic_run=1, the last 3 records (id=5-7) have logic_run=2.

Step 3: Calculate running total by partition

compute if (logic = 't' then 1 else val) cum as op partition logic_run

Within the logic_run partition, use the cum (running total) function to cumulatively sum the conditional expression if (logic=‘t’ then 1 else val). Each partition is calculated independently: when logic is ‘t’, the cumulative value resets to 1; when logic is ‘f’, the cumulative value is the previous row’s accumulation plus the current row’s val. The partition logic_run ensures that accumulations in different partitions do not interfere with each other. This achieves the conditional running total with reset.

Unnecessary fields can be removed later using the derive function.

Generated SQL
After confirming the logic of the above 3 steps, the SQLazy compiler automatically generates native SQL (MySQL syntax used here):

WITH t3 AS (
        SELECT id, logic, val, SUM(CASE 
                WHEN logic = 't' THEN 1
                ELSE 0
            END) OVER (ORDER BY CASE 
                WHEN id IS NULL THEN 1
                ELSE 0
            END, id ASC ROWS UNBOUNDED PRECEDING) AS logic_run
        FROM t2
    )
SELECT id, logic, val, logic_run
    , SUM(CASE 
        WHEN logic = 't' THEN 1
        ELSE val
    END) OVER (PARTITION BY logic_run ORDER BY CASE 
        WHEN id IS NULL THEN 1
        ELSE 0
    END, id ASC ROWS UNBOUNDED PRECEDING) AS oput
FROM t3
ORDER BY CASE 
    WHEN id IS NULL THEN 1
    ELSE 0
END, id ASC

SQLazy lets you describe logic in business language instead of writing nested SQL queries. This example demonstrates two major features of SQLazy: first, step-by-step calculation that breaks conditional reset accumulation into three independent steps—sort, create grouping marker, accumulate by partition—each verifiable independently; second, the partition + cum syntax for partitioned running totals, which is more concise and intuitive than SQL’s window function syntax, directly expressing the business semantics of “reset on ‘t’, otherwise accumulate”.

Official Links
SQLazy Online Experience: sqlazy.com (free, no registration required)
SQLazy Repository: github.com/SPLWare/SQLazy

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