Don’t you just hate it when you’re out buying Pokémon cards, only to discover that you left your wallet at home? Well, with an exciting new life hack, you’ll never forget your wallet again.

To avoid the hassle and embarrassment that result from leaving your wallet at home, try sitting it next to your katana. You never forget to leave home without your trusty katana, so when you gear up to leave the house, your wallet will be right there waiting for you. It works every time!

All you have to do is find a nice, convenient place for your katana that also has a spot for your wallet. And for an extra tip, try engraving your katana with a helpful message. "Don’t forget your wallet!" It’s helpful for you, and for your enemies who will need that extra cash to pay the boatman for passage to cross the River Styx.

You’re always going to need your katana with you, so it just makes sense to keep all your daily essentials together. Wallet, keys, phone, Tamagotchi. If you keep everything with your katana, you’ll never be disappointed.

Start today by letting your honor for your katana protect you from leaving your wallet behind ever again.

---

The Babylon Bee finally exposes everything about the Jews in this documentary of hard-hitting investigative journalism.

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.

It turns out Jean-Luc Picard was an even better starship helmsman than the writers knew. A physicist has gone through the details of a warp-speed trick from the first season of Star Trek: The Next Generation and found a subtlety the show missed. But instead of a plot hole, the detail he found actually makes the maneuver more impressive… as well as a great opportunity to teach about a lesser-known feature of the theory of relativity.

Níckolas de Aguiar Alves, a physicist at the Federal University of ABC in Brazil, had first watched Next Generation as a Master’s student. When he got to the episode “The Battle” in the show’s first season, the plot reminded him of his relativity coursework.

In “The Battle,” a Ferengi leader reminds Picard of a battle he fought years ago as captain of a ship called the Stargazer. Under fire from a mysterious attacker, Picard’s ship’s shields were down. He had to get closer without taking a hit, so he made a gamble. Picard ordered the Stargazer to charge the enemy ship at warp speed (meaning faster than light), then stop abruptly and fire. By going faster than light, Picard anticipated that the other ship would see two images of the Stargazer: where it reached warp speed and where it stopped. If they fired on the wrong image, they would miss the Stargazer, and Picard could win the battle.

Later in the episode, Riker mentions that the trick had been immortalized in Starfleet textbooks as the “Picard maneuver.”

As it turns out, it’s also not that far from what you can find in some physics textbooks. While warp speed is pure science fiction, physicists do sometimes need to consider what happens when an object goes faster than light, and such an object really does leave two images.

Something about the story bothered de Aguiar Alves at the time. But he forgot about it until years later, when he was working through a more practical problem involving particles in a medium with a slower speed of light. Not trusting that he had done the math right, he started trying to work out the situation visually.