SmartPreload weight order

Hi — reporting a SQL bug in 2.0.2, with root cause, a minimal reproduction, and a one-word fix.

The error

It fires from the front-end output buffer on essentially every render. We accumulated 1,864 of these in our PHP error log:

WordPress database error BIGINT UNSIGNED value is out of range in 'cast(nullif(wp_easyfonts_fonts.weight,'') as unsigned) - 500' for query SELECT font_file FROM wp_easyfonts_fonts WHERE family = ... ORDER BY ... ABS( CAST( NULLIF(weight,'') AS UNSIGNED ) - 500 ) ASC, id ASC LIMIT 1 made by shutdown_action_hook, do_action('shutdown'), wp_ob_end_flush_all, ob_end_flush, EasyFonts\Frontend\OutputBuffer->process, EasyFonts\Frontend\SmartPreload->build, EasyFonts\Fonts\Registry->resolve_preload_file

Cause

src/Fonts/Registry.php line 533, inside resolve_preload_file():

ABS( CAST( NULLIF(weight,'') AS UNSIGNED ) - %d ) ASC,

MySQL/MariaDB evaluates the subtraction in unsigned arithmetic. Any candidate row whose weight is lower than the requested target weight produces a negative intermediate, which is out of range for an unsigned type, and the statement errors.

ABS() cannot rescue it — the error is raised while evaluating the subtraction, before ABS() is ever applied. The error text is the tell: it quotes the inner expression (... as unsigned) - 500), not the ABS() wrapper.

Minimal reproduction (plain MySQL/MariaDB, no WordPress needed)

SELECT ABS( CAST( NULLIF('400','') AS UNSIGNED ) - 500 );
→ ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in ‘(cast(nullif(‘400′,”) as unsigned) – 500)’

SELECT ABS( CAST( NULLIF('400','') AS SIGNED ) - 500 );
→ 100 ✓

Suggested fix — change UNSIGNED to SIGNED:

ABS( CAST( NULLIF(weight,'') AS SIGNED ) - %d ) ASC,

The intent (order candidates by absolute distance from the target weight) is then expressed correctly: the subtraction happens in signed arithmetic and ABS() does its job.

Scale

It triggers for every candidate row lighter than the requested weight, so any family with a normal weight spread hits it constantly. On our install 50 of the 130 rows in wp_easyfonts_fonts have a weight below 500 (weights 100/200/300/400).

Environment

• Easy Fonts 2.0.2
• WordPress 6.9.4
• PHP 8.2.31
• MariaDB 10.11.18
• sql_mode: ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Note the sql_mode does not include NO_UNSIGNED_SUBTRACTION — that’s stock configuration, so I’d expect any install without that mode explicitly set to hit this.

Workaround in the meantime

We’ve disabled Smart Preload (smart_preload = 0), which stops SmartPreload::build() from calling resolve_preload_file() and silences the errors completely. Font hosting and serving are unaffected (that path uses the disk-based lookup), but of course we lose the preload hints — which is why I’d love to see the fix land so we can turn it back on.

Thanks for the plugin!