Doltgres, the world’s first version-controlled Postgres-compatible database, just hit 1.0, meaning that it’s ready for production use. We want Doltgres to be a drop-in replacement for Postgres so that customers can use the entire ecosystem of Postgres-compatible tools and libraries, or port their existing database application to Doltgres without changing any code. This means getting all the nuanced semantics of Postgres’s behavior correct in our emulation. And we think we’ve done pretty well here — our compatibility tests encompass over two dozen tools and languages.

But Doltgres shares the same SQL engine Dolt uses, which was built to emulate MySQL semantics. For most queries this works fine, but MySQL plays famously fast and loose with the SQL standard, while Postgres takes it much more seriously. And because we take client compatibility very, very seriously, that means that we need an engine that reproduces all of MySQL’s wacky non-standard behavior for Dolt and Postgres’s dignified, correct behavior for Doltgres.

Today’s blog is a case study of one area where the engine’s behavior differs to match the emulation target, and a look under the hood for how we manage these differences internally in our interfaces.

UPDATE with column values from the same row#

This issue was brought to our attention by an early adopter customer: Doltgres had the wrong behavior when an UPDATE statement referenced table columns in its update expressions.

CREATE TABLE t_seq (a int, b int);
INSERT INTO t_seq VALUES (1, 0);
UPDATE t_seq SET a = 2, b = CASE WHEN a = 1 THEN 100 ELSE -1 END;
SELECT a, b FROM t_seq;

The SQL standard says that an UPDATE statement that references column values should use the value from the pre-update row, in all cases. So the SELECT query in the above block should return this:

 a |  b
---+-----
 2 | 100    -- per the SQL standard, every assignment reads the pre-update row

But MySQL doesn’t behave this way for an UPDATE. It ignores the SQL standard and uses the new, updated column values in every UPDATE expression as it executes them one by one, left to right, on each row. So in MySQL, and Dolt, the above select returns this:

 a |  b
---+-----
 2 |  -1    -- the CASE saw the NEW value of a (=2)

And until earlier this week, Doltgres behaved this way too. But that’s wrong, and breaks client expectations for Postgres application developers. We needed to change this behavior in the engine, but only when running in Postgres emulation mode.

How do we do that?

Introducing engine overrides#

During development of Doltgres, we experimented with a lot of different mechanisms to vary the engine’s behavior for Doltgres, either to reflect needed differences for Postgres compatibility or to implement features that MySQL doesn’t have. These include new rules during query analysis, new plan nodes that wrap or otherwise alter existing ones, as well as more hacky fixes like swapping function pointers during program init. For something like this divergence in behavior, there wasn’t an existing extension point in the query engine. We designed the engine to make the database backend swappable, as well as some of the query planning logic. But for something as fundamental as applying updates to a row, we had not bothered to make the behavior pluggable.

Our current approach in this kind of situation is to provide the engine with a set of well-defined behavioral extension points at construction. Unlike the interfaces that define tables, databases, functions, etc. that allow integrators to implement a custom database storage backend, these extension points alter the query-time behavior of the engine itself, independent of the storage backend. They’re currently stored in a struct called EngineOverrides. To solve this particular problem, we introduced the new UpdateExpressionApplier interface at the bottom of the struct.

type EngineOverrides struct {
	// Builder contains functions and variables that can replace, supplement, or override functionality within the builder.
	Builder BuilderOverrides
	// SchemaFormatter is the formatter for schema string creation. If nil, this will format in MySQL's style.
	SchemaFormatter SchemaFormatter
	// Hooks contain various hooks that are called within a statement's lifecycle.
	Hooks ExecutionHooks
	// CostedIndexScanExpressionFilter is used to walk expression trees in order to apply index scans based on
	// filter expressions. Some expressions may need to be modified or skipped in order to properly apply indexes
	// for all integrators.
	CostedIndexScanExpressionFilter ExpressionTreeFilter
	// UpdateExpressionApplier evaluates UPDATE assignments. If nil, the engine uses
	// MySQL's sequential assignment evaluation and IGNORE conversion handling.
	UpdateExpressionApplier UpdateExpressionApplier
}

The new interface looks like this:

// UpdateExpressionApplier evaluates the assignments for a row in an UPDATE statement.
// It does not apply to procedural SET or INSERT ON DUPLICATE KEY UPDATE statements.
type UpdateExpressionApplier interface {
	ApplyRowUpdate(ctx *Context, updateExprs *UpdateExprs, tableSchema Schema, oldRow Row, ignore bool) (Row, error)
}

For MySQL behavior, we have a simple interface that applies updates the same way it always has (matching MySQL, not Postgres). For Doltgres, we implemented a new one that we plug in at engine construction time.

func (UpdateExpressionApplier) ApplyRowUpdate(ctx *sql.Context, updateExprs *sql.UpdateExprs, tableSchema sql.Schema, oldRow sql.Row, _ bool) (sql.Row, error) {
	newRow := oldRow.Copy()
	for _, expr := range updateExprs.ExplicitUpdateExprs() {
		assignment, ok := expr.(*gmsexpression.SetField)
		if !ok {
			return nil, fmt.Errorf("UPDATE: expected SetField, found %T", expr)
		}
		// SetField performs assignment conversion and returns a copy of oldRow.
		// Merge only its target, so later assignments cannot undo earlier writes.
		value, err := assignment.Eval(ctx, oldRow)
		if err != nil {
			return nil, err
		}
		...
}

Now Doltgres returns the expected result, the same as Postgres.

a |  b
---+-----
2 | 100    -- per the SQL standard, every assignment reads the pre-update row

Check out these two PRs for the full details.

Conclusion#

Doltgres 1.0 already launched, but Doltgres’s compatibility story is definitely not over. Keep the issues coming and we’ll keep knocking them down in 24 hours.

Have a divergence in Postgres behavior to report? Want to learn more about Doltgres? Visit us on the DoltHub Discord where our engineering team hangs out all day. Hope to see you there.