Doltgres Reaches 99% Compliance on SQL Logic Tests

Two weeks ago, we announced that Doltgres 1.0 is coming August 6th. In that post, we laid out the four things we’re focused on to get there: correctness, storage format stability, performance, and compatibility. Correctness was measured by one very concrete number: 99% compliance on our SQL Logic Test suite. At the time we were sitting at a little over 96%. Today, we’ve hit our target: Doltgres passes 99% of the suite. That’s one more box checked on the road to 1.0. 🎉

SQL Logic Test#

SQL Logic Test is a test suite originally built for SQLite, containing millions of statements and queries that exercise SQL expressions, joins, aggregates, and type coercion rules. We forked it years ago and extended it with more tests to measure how correctly Dolt (and now Doltgres) execute SQL statements. The test suite in SQL Logic Test specifically stress tests the expression support in each engine. 99% represents millions of individual queries whose results have to match PostgreSQL exactly, down to the type and formatting of every returned value. This gives us a high confidence that Doltgres can correctly execute a wide range of statements and expressions.

Establishing a Baseline Against PostgreSQL#

Before we could chase down our own bugs, we needed to answer a more basic question: how many of these tests are even valid against PostgreSQL? The suite was originally written for SQLite, and over the years it’s been adapted and extended for MySQL as we’ve used it to test Dolt. Postgres has never been the primary target, so we couldn’t assume the entire test suite would execute cleanly against Postgres.

We pointed our test runner at a real PostgreSQL server and ran the full suite against stock PostgreSQL first, to establish a baseline of how compatible the tests actually were with Postgres. That baseline surfaced a number of places where the tests themselves (or in many cases, the runner’s expectations about results) were still encoding SQLite or MySQL behavior and not compatible with slightly different behavior in Postgres. Fixing those was a prerequisite before we could start figuring out what changes were needed in Doltgres.

A few examples of what we found and fixed:

• Integer vs. float schema types. The test format encodes an expected type for each result column (I for integer, R for float/real), based on SQLite’s type affinity rules. Postgres is stricter about numeric types than SQLite, so expressions SQLite treats as integers may legitimately come back as floats from Postgres, and vice versa. We updated the runner’s schema comparison to treat I and R as compatible in both directions, and to normalize whole-number floats (like 3.000) to integer formatting (3) so the value comparison succeeds when the underlying values genuinely match. Limiting this to whole-number floats only means we still detect correctness errors if the values don’t logically match, but we’re more flexible on the returned result type so that we can use the same tests to match against Postgres.
• Empty result sets. SQLite reports SQLITE_NULL as the type for every column when a query returns zero rows, which doesn’t correspond to anything meaningful in Postgres. We updated the runner to skip schema-type verification entirely when both the expected and actual result sets are empty, since there’s nothing to compare.
• Postgres-specific type names in the harness. Our test harness inspects the driver’s reported column types to decide how to parse and compare each value. It was written with MySQL’s type names in mind (INT, BIGINT, DECIMAL, and so on), so it didn’t know what to do with Postgres-specific names like BOOL, INT2, or FLOAT4. We filled in the missing cases so those types get parsed and compared correctly instead of falling through and failing.
• MySQL-only statements. Some tests exercise MySQL-specific syntax or behavior that has no Postgres equivalent at all. Rather than force those through, we added skip directives so they’re excluded when running against Doltgres, the same way we already skip SQLite-specific tests.

In addition to those improvements, we also invested in running the suite in parallel, spinning up a single shared Doltgres server and fanning test files out across concurrent workers, each against its own isolated database. At the scale of millions of test queries, that’s the difference between a test run that takes minutes and one that takes hours, which matters a lot when you’re iterating on fixes.

Bugs the Tests Found in Doltgres#

With those changes in place, we could now run the SQL Logic Tests against a real PostgreSQL server and get over 99% correctness. There are still some issues for the test suite to run 100% against PostgreSQL, and we’ll keep chipping away at those in future passes. After these improvements, the remaining test failures with Doltgres were much more likely to be real Doltgres bugs that we needed to dig into. The most interesting was in our COALESCE() implementation: when called with mixed numeric types (say, an int4 and an int8, or an int4 and a float8), it was using a generic type conversion instead of Postgres’ assignment cast rules to compute the common type. That’s an important distinction. Assignment casts are what Postgres itself uses to widen mixed-type arguments to a common type, and using the wrong conversion path meant we could return incorrectly typed or incorrectly rounded results for a fairly common pattern in real SQL.

On Track for August 6th#

Correctly executing 99% of the SQL Logic Test suite was our target for Doltgres’ 1.0 release. It gives us high confidence that a wide range of statements and SQL expressions are executing correctly in Doltgres. By baselining the test suite against PostgreSQL, we discovered that we were closer to this milestone than we initially expected. We thought we still had many remaining gaps to fill to reach that milestone, but it turned out that how the results were being processed by the test runner accounted for most of the gap.

Executing queries correctly, and returning identical results as PostgreSQL, is the foundation for our 1.0 launch. Without correct query execution, the other goals, like fast execution of queries and tool compatibility, just don’t matter. Overall, we’re making great progress on our 1.0 punch list and remain on track for August 6th.

If you’re running Doltgres and you hit a query that returns the wrong result, an error you don’t expect, or behavior that just doesn’t match Postgres, please send us a GitHub issue and let us know. We want to find and fix as many of these as possible before 1.0 ships, and customer-reported issues go straight to the top of our queue.

If you haven’t started using Doltgres yet, give it a shot! You can install Doltgres by running brew intall doltgres on a Mac with Homebrew, or you download a binary from our GitHub releases. Our dev team hangs out on the DoltHub Discord server every day, so feel free to come by and tell us how it’s going. We’re closing in on 1.0 and every bit of feedback helps us get there!