For the past month, I have been writing about the TEST Lab at National University of Singapore and the work they’ve done with Dolt over the years, including SQLancer++ and JIR. Today, I will be giving you all a sneak peek of WINE. While the paper on WINE has yet to be published, the TEST Lab has graciously shared with me a preprint.

I have previously mentioned WINE in my earlier blog posts. The main researcher on the project is Yibo Dong, who has filed 100 GitHub issues in the Dolt repository. That’s far more than anyone else outside of the DoltHub organization. Suyang Zhong, the author of SQLancer++, is at #2 with 52 issues, including the subissues of his megabug (more on that later). Congratulations!

Now let’s see how all those bugs were found!

You catch more bugs with WINE#

WINE is short for Window equivalence and is a testing approach for SQL window functions using a methodology similar to SQLancer and related projects, in that it generates window function queries and semantically equivalent queries without window functions and uses the two executions as test oracles to find correctness bugs. Yibo came up with the idea for the project last year and started work on it this past summer. The bulk of the issues filed with Dolt were reported in August.

Window functions in SQL allow for queries that perform calculations across a set of table rows related to the current row, without collapsing the result set into a single row. Unlike a simple aggregation, which squashes individual row details, window functions append calculations while retaining attributes of the original row. Window functions are particularly useful for analyzing data such as running totals, moving averages, and year/month-to-date aggregates.

Window functions can be hard to test due to the difficulty of determining the correct output for each row given that the context can vary. In other words, the window can shift throughout the table. For example, if you wanted to find a running total (SUM(amount) OVER (ORDER BY date)), the window for row 1 would just be the first row, but the window for row 5 would be rows 1 through 5, the window for row 10 would be rows 1 through 10, and so on. Compare this to a simple aggregation (SUM(amount)), where the context would simply be every row of the table. And in addition to cumulative windows, window functions can also use sliding windows, partitions, or ranges. Due to these complexities, it’s difficult to extend the semantic properties utilized by existing test oracles to window functions.

The big breakthroughs with WINE are the ways in which a window function query can be rewritten into non-window function queries, using subqueries to replicate the window context. The paper outlines a series of rules for how different window function types and window contexts are to be reconstructed. The paper includes an example of two queries in DuckDB that are semantically equivalent:

SELECT id,
  SUM ( DISTINCT amount ) OVER (
    PARTITION BY region
    ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING
    AND UNBOUNDED FOLLOWING
  ) AS total
FROM sales
ORDER BY id ;

SELECT o . id ,
  ( SELECT SUM ( DISTINCT i . amount )
  FROM sales AS i
  WHERE i . region = o . region
    OR ( i . region IS NULL
      AND o . region IS NULL )
  ) AS total
FROM sales AS o
ORDER BY o . id ;

The rest of the paper reads pretty similar to the other TEST Lab papers – dialect-aware SQL queries using window functions are randomly generated and then translated into equivalent queries without window functions, both queries are executed against a given database system, and the results are compared.

So is this AI?#

Yes, WINE was made with the help of AI! Though the paper does not mention any use of AI or LLMs, through my correspondence with Yibo, I’ve learned that coding agents were used to implement certain features and to scale testing. I’m not surprised – at this point, AI-usage is so commonplace in computer science and software engineering that perhaps it’s not even worth mentioning. That being said, Yibo was very cautious about not wanting to spam us with AI slop and verified all the bugs himself before filing any issues.

The scale of testing is certainly notable. While we were really impressed with the 20 bugs found by Argus last year, that was nothing compared to the 100 bugs found by WINE. So while the paper likens the implementation of WINE to that of SQLancer, its use of AI and the scale of bugs it was able to find reminds me of Argus, which built upon the ideas of SQLancer using LLM-generated queries and was published last year. And since then, AI has only gotten more advanced.

Impact on Dolt#

At the time the preprint was written, WINE had found 87 bugs in Dolt. This made up over half the 164 total bugs found by WINE. However, the paper did note that because the Dolt team was so prompt with responding to and confirming the bug reports, Dolt specifically underwent more rounds of testing. So this doesn’t mean Dolt has more bugs than other databases; this just means researchers, who want confirmation that their work is impactful and worthwhile, are more motivated to continue testing with Dolt due to our commitment to correctness. Of the 100 bugs filed on GitHub, we have fixed 89 of them and are grinding away at fixing all of them.

As mentioned before, the scale of bugs WINE was able to find was certainly notable. After the initial shock of the bug swarm, we have been rethinking how we wanted to collaborate with researchers and handle the bug-filing process. This has led us to piloting the “megabug”, where instead of filing an individual issue for each failing query, a researcher can file a single issue with links to various bug repros, from which the Dolt team can triage at our own pace. Recently, we’ve decided to try this idea out with Suyang, who filed a megabug containing 23 subissues.

We are always very appreciative of the work researchers have done in ensuring the correctness of Dolt. Window functions can be particularly tricky, both to implement and to test, and we are honored to have been chosen as one of the four databases tested by WINE. We wish Yibo good luck on getting his research published.

Are you an academic researcher working on a project with Dolt or another DoltHub database? Join our Discord community – we’d love to hear from you.