ElyraSQL 1.7.0: the release we didn't write

Four Laravel codebases, one contributor, two wrong-results bugs, and the first Apple Silicon binary.

Every project has a moment where it stops being a thing you make and starts being a thing other people use. You can usually date it precisely, because it arrives as a pull request from someone you've never met containing a bug you'd have sworn wasn't there.

Ours arrived last Friday. Both halves of 1.7.0 came in from @HelgeSverre, and this post is mostly about what he found, because the method matters more than the fixes.

Why: "compatible" is a claim you have to earn

ElyraSQL speaks the MySQL wire protocol. We've said from the start that Laravel works. And it did — against our test suite, which we wrote, informed by our understanding of what Laravel does.

Helge did something different. He took the migration and test suites from four commercial Laravel codebases and pointed them at isolated ElyraSQL instances. The largest had 469 migration files. Then, for every failure, instead of filing "doesn't work with app X", he reduced it to a generic MySQL reproduction, compared it against real MySQL 8.4 where the correct behaviour was ambiguous, and submitted it as a project-level regression test with no application code attached.

The result: two complete application suites passing 278 tests / 764 assertions and 169 tests / 762 assertions. And 103 new end-to-end tests through the wire protocol on our side.

Here's the thing that surprised us. Almost none of these were engine bugs. They were places where ElyraSQL computed the right answer and then presented it differently. A column label. A coercion under a session SQL mode. A version string. A metadata field. Each one is invisible in a unit test and becomes a driver-specific workaround in somebody's application.

How: three examples

1. The version string that suppressed your SQL

We reported ourselves as 8.0.0-ElyraSQL. Harmless-looking. But MySQL introduced window functions in 8.0.11, and version-gated clients — Laravel among them — check that number before deciding what SQL to generate. So clients were quietly downgrading queries we could answer perfectly well.

SELECT VERSION();
-- before: 8.0.0-ElyraSQL-1.6.0
-- now:    8.0.12-ElyraSQL-1.7.0

The .12 rather than .11 is deliberate: it keeps the branded suffix from being read as a prerelease older than 8.0.11 by semver comparators. That's the level of care in these commits.

2. SELECT * over a join

Given two tables that both have an id:

SELECT * FROM users JOIN posts ON posts.user_id = users.id;

MySQL returns five columns, two of them named plain id, with the qualifier living in the result metadata. We were returning invented labels — users.id, posts.id — which looks more helpful and is wrong. Client code that expects MySQL's collision behaviour got something it had never seen.

Measured through PDO with EMULATE_PREPARES = false:

columnCount() FETCH_ASSOC keys MySQL 8.4 5 4 ElyraSQL 1.6.0 5 5 ElyraSQL 1.7.0 5 4

Amusingly, our own CI test asserted the old behaviour, so this fix arrived as a red build. We had encoded our mistake as a requirement. It took measuring against MySQL to notice.

3. CREATE DATABASE now says no

ElyraSQL is one logical schema in one file. We used to accept CREATE DATABASE analytics and return success. Nothing was created. Every connection kept sharing elyra. A test harness that "created" three isolated databases got one shared namespace and mysteriously interfering tests.

CREATE DATABASE analytics;
-- ERROR 1235: CREATE DATABASE is not supported; ElyraSQL uses the single
--             `elyra` database (use CREATE DATABASE IF NOT EXISTS to make
--             this a no-op)
CREATE DATABASE IF NOT EXISTS analytics;   -- OK, honest no-op
DROP DATABASE IF EXISTS never_existed;     -- OK, honest no-op
DROP DATABASE elyra;                       -- ERROR: refuses to pretend

The distinction is the whole point. IF NOT EXISTS means "make sure this exists" — satisfiable, and it's what Laravel's MigrateCommand issues. A bare CREATE DATABASE means "give me a new, empty one" — not satisfiable, so we say so.

We got this wrong on the first attempt, incidentally: the initial commit refused both forms, which would have broken php artisan migrate in a release whose entire purpose was making Laravel work. It was caught by our own benchmark scripts failing in CI. Good reminder that the safest-looking change ("be stricter") has a blast radius too.

The bug the sweep found

With the harness unblocked, our threshold sweep — which replays a query battery against MySQL at every size that brackets an internal boundary — went red at exactly n=2048:

SELECT COUNT(*) FROM a WHERE k > (SELECT AVG(k) FROM a);
-- MySQL:    1024
-- ElyraSQL: 1023

At n=2048 the average is 1024.5. Reduced further, the whole bug fits on one line:

CREATE TABLE av (k INT PRIMARY KEY);   -- holding 1..4096

SELECT COUNT() FROM av WHERE k > 1024.5;    -- MySQL 3072, we said 3071
SELECT COUNT() FROM av WHERE k < 1024.5;    -- MySQL 1024, we said 1023
SELECT COUNT() FROM av WHERE k = 1024.5;    -- MySQL 0,    we said 1
SELECT COUNT() FROM av WHERE k+0 > 1024.5;  -- correct!

That last line is the tell. Defeat the index and everything is fine — so the comparison logic was never wrong. The index bound was.

Four lookup paths were calling coerce(), the function that prepares a value for storage, to prepare a literal for comparison. Those are different jobs. Storing 1024.5 in an INT column should round to 1025 — MySQL does. But WHERE k > 1024.5 means k >= 1025, and rounding the bound to 1025 while keeping the strict > silently drops row 1025.

The fix keeps the bound's meaning by flipping its inclusivity whenever coercion moved the value:

coercion moved the literal col > lit col >= lit col < lit col <= lit up (1024.5 → 1025) >= 1025 >= 1025 < 1025 < 1025 down (1024.4 → 1024) > 1024 > 1024 <= 1024 <= 1024

That's exact, not a heuristic: coercion rounds to the nearest representable value, so nothing sits strictly between the literal and its coerced form, and the two forms select the same rows by construction. =, IN and BETWEEN have nowhere to record a flipped inclusivity, so an inexact literal there declines the index and lets the sequential scan answer — which closes k = 1024.5 too.

Two honest notes. First, < and = were already wrong before this release — 1.6.0 undercounted k < 1024.5 and matched a row for k = 1024.5. Helge's change didn't introduce the family of bugs; it made the third member visible and got blamed for the other two. Second, this is the second release running where a wrong-results bug was found by differential testing against MySQL rather than by a user report. We've now wired that battery into every pull request, because clearly we can't find these by thinking harder.

Apple Silicon, at last

The other half of the release. From 1.7.0 every tagged release attaches a native aarch64-apple-darwin binary alongside the Linux musl builds:

VER=1.7.0
curl -L -o elyrasql.tar.gz "https://github.com/kwhorne/ElyraSQL/releases/download/v${VER}/elyrasql-${VER}-macos-aarch64.tar.gz"
tar xzf elyrasql.tar.gz
./elyrasql-${VER}-macos-aarch64/elyrasql serve

macOS 11+, Apple Silicon only, no Rosetta. CI builds and runs the entire workspace test suite on an arm64 macOS runner, then verifies the Mach-O architecture, the 11.0 deployment floor and the code signature, and finally extracts the published archive and executes the binary before the release goes out.

Two Linux assumptions fell out of that work:

SELECT @@version_compile_os, @@version_compile_machine;
-- before: Linux, x86_64   (on an M-series Mac, confidently)
-- now:    macOS, aarch64

And stale spill files from a SIGKILLed process were only reclaimable on Linux, because liveness was checked via /proc. It's a non-signalling kill(pid, 0) now — still treating anything ambiguous as alive, so a running instance's files are never deleted.

There was a third finding hidden in there, and it's the one I'd have been most embarrassed to ship: our documented MSRV of 1.82 was simply wrong. The locked dependency graph has required 1.88 for a while via time, so a --locked build on the claimed minimum could never have worked. A one-line diff that was really a bug report.

What we're not claiming

There's a page called limitations.md and it's linked from the README for a reason.

While auditing the compatibility docs for this release, we ran every claim in the "differences and gaps" list against the actual 1.7.0 binary rather than reading the code. Most of the list was stale in our favour — WITH RECURSIVE, explicit window frames, correlated subqueries over joins, INSERT ... SET, LOAD DATA LOCAL and ALTER ... ADD FOREIGN KEY all work and were documented as missing.

But the same audit found something genuinely broken:

CREATE VIEW vg AS
SELECT g, COUNT(*) AS c FROM u GROUP BY g;              -- fine

CREATE MATERIALIZED VIEW mvg AS
SELECT g, COUNT(*) AS c FROM u GROUP BY g;              -- ERROR: unknown column: g

A materialized view over an aggregate — which is the main reason to want one — is rejected while the identical plain view works. It's present in 1.6.0 too, so it isn't new, but it means the feature is effectively unavailable for its primary use case. It's ESQL-54 and it's in the docs as a known gap until it's fixed.

Also still open: our result metadata leaves the table field empty where MySQL names the source table. Since we now return MySQL's duplicate column names, a client can no longer disambiguate them by name or by metadata — only by position. That's a regression in capability created by a correctness fix, and it's the next thing we'd like to close.

Upgrading

No on-disk format change, no migration. A 1.5.x or 1.6.x database opens unchanged, and a 1.7.0 database still opens in either.

docker pull ghcr.io/kwhorne/elyrasql:1.7.0

Two behaviour changes to know about: CREATE DATABASE refuses unless conditional, and the advertised MySQL version is now 8.0.12, so version-gated clients may start generating window-function SQL they previously suppressed. That's the reason for a minor rather than a patch bump — both are observable from the outside, and semver is a promise about observable behaviour, not about how hard the change was.

Thank you

Nothing in this release came from a roadmap. It came from someone taking real applications, running them against our database, and doing the unglamorous work of turning each failure into something we could merge.

If you're running ElyraSQL against a real workload and something behaves differently from MySQL: that's a bug, we want it, and — as this release demonstrates — a reproduction measured against MySQL 8.4 is worth more to us than any amount of internal certainty.