So, first of all, I'm by no means an expert of SQLite, and I just learned about SQLite's flexible typing.

A few days ago I noticed that this AbuseFilter test was failing on SQLite (see e.g. this console output), whereas it used to pass on MySQL.

It took me a decent amount of time (and tests using the CI) to find out the cause. The abuse_filter_log table definition for SQLite has:

CREATE TABLE /*$wgDBprefix*/abuse_filter_log (
	...
	afl_filter varbinary(64) NOT NULL DEFAULT '',

the test does

INSERT INTO abuse_filter_log ( ... afl_filter ... ) values ( ... 1 ...

i.e. it's inserting a numeric 1. Then we do something like

SELECT  1  FROM unittest_abuse_filter_log WHERE afl_filter = '1'

and this cannot find the row inserted above! Instead, it works as expected if you remove the quotes from '1'.

The docs above don't explicitly mention what happens if you try to insert an integer in a varbinary column, but apparently, no cast is taking place (unlike when inserting strings in an integer field). Moreover, SELECT queries are not flexibly typed, which explains why the test was failing. Needles to say, this is a big drift from MySQL and a potential source of sneaky issues.

I don't know whether this has already caused problems in the past, and/or it's already being mitigated somehow, but I think it's very important that we do something about that, even just putting a warning on mw.org.