WordPress meta_query: the silent FLOAT cast that ate GPS

The dev on call refreshed the staging map at 23:14 on a Tuesday and saw 6,400 parcels stacked in a kilometre-wide blob over the cooperative's parking lot. Mechelen, mid-June, day eight of a WordPress migration that was supposed to take five.

The cooperative is a 22-person operation outside Mechelen. They aggregate sugar beet, chicory, and seed potato across about 9,000 hectares for 340 members. The website is not the business, but it runs the business: every member parcel has a public page (a perceel-pagina) with crop history, soil type, last sample date, and a polygon drawn on a Leaflet map. The polygons are how the field agronomist finds anything in the morning.

The migration brief was unromantic. WordPress 6.5, PHP 8.2, MariaDB 10.6, a tower of plugins, three years of ACF Pro field churn, and an edit window so tight that a single schema change could cost a member half a hectare. Move it to Astro for the public site, Payload for the editor surface, keep the URLs, keep the maps, keep the agronomist's morning routine intact.

The first seven days were the boring part. URL-stable rewrites, image pipeline, search index, the usual.

Then the polygons collapsed.

The page that broke

The migration's parcel importer ran a paginated WP_Query against the perceel post type, pulled the ACF Pro repeater for boundary points, and pushed each row into Payload as an array of {lat, lng} objects. The agronomist's staging review was the first time anyone had loaded all 6,400 parcels onto a single map.

They were all on top of each other. Not "in the wrong country" wrong. Worse: in the same patch of gravel near the office, ninety metres across, every parcel snapped onto every other parcel.

The team's first instinct was the migration script. It got rewritten twice between 23:14 and 02:00. Each rewrite produced the same blob. They checked the GeoJSON output. Valid. They checked the Leaflet config. Fine. They wrote a script that diffed five sample parcels' coordinates against the legacy site live in production. Identical. The same five floats, copied byte for byte, rendered correctly on one site and wrongly on the other.

That was the moment it stopped being a migration bug.

What ACF Pro was actually storing

ACF Pro repeaters are stored as flat rows in wp_postmeta. A repeater called boundary with sub-fields lat and lng writes one row per coordinate per parcel. For a polygon with sixty vertices, that is 120 rows per parcel. For 6,400 parcels, around 768,000 rows. Acceptable, indexable, the way the plugin is meant to work.

That was not what was in the database.

What was in the database was a single repeater field called polygon_csv, with one row per parcel, holding a string that looked like this:

51.0231,4.4598,51.0234,4.4612,51.0245,4.4621,51.0249,4.4604,51.0231,4.4598

A predecessor agency had decided, three years ago, that a 60-vertex polygon was "too many rows" and replaced the repeater's individual lat/lng fields with a single textarea. They kept the field name and the ACF field key, so the editor UI still rendered as a repeater, but every row held a CSV blob of paired floats. A point-of-failure JavaScript hook on the admin save parsed the CSV back into a Leaflet polygon for the editor preview, and a matching template helper parsed it back out on the public site. Two functions, ninety lines of code, one undocumented contract.

That was fine. Sort of.

What WP_Query was doing

The migration script had a perfectly reasonable line in it. The team wanted to import parcels in geographic batches to keep the Payload bulk upserts manageable, so they filtered by bounding box.

$query = new WP_Query([
    'post_type'      => 'perceel',
    'posts_per_page' => 200,
    'meta_query'     => [
        'relation' => 'AND',
        [
            'key'     => 'polygon_csv',
            'value'   => [$bbox_min_lat, $bbox_max_lat],
            'compare' => 'BETWEEN',
            'type'    => 'DECIMAL(10,3)',
        ],
    ],
]);

This is the line that ate the week. Read the value of type. BETWEEN on a DECIMAL(10,3) cast.

MySQL's CAST(... AS DECIMAL(10,3)) does not return NULL when handed a string that starts with a number and then contains garbage. It returns the leading numeric portion, silently, rounded to the precision you asked for. So CAST('51.0231,4.4598,51.0234,...' AS DECIMAL(10,3)) returns 51.023. Not 51.0231. The fourth decimal, and everything after it, including the rest of the polygon's sixty vertices, vanished into the cast.

At three decimal degrees of latitude, you have a resolution of roughly 110 metres. Every parcel that fell inside a single 110-metre tile got the same coordinate. The bounding box batches still ran. The script still completed. The data still imported. The maps still rendered.

They just all rendered on top of one patch of gravel.

The thing that made this hard to see

The fix is one parameter, and we will get to it. The reason it took eight days is that nothing failed.

WP_Query returned posts. The importer wrote rows. Payload accepted the inserts. The staging map drew valid GeoJSON for every parcel. The CSV-parsing JS hook on the legacy admin had been working fine for three years, because it never went near the database cast path. The legacy public site never used meta_query on this field, only get_post_meta(), which returns the raw string. The only test that would have caught this was "open the map with all 6,400 parcels visible at once", which is not a test anyone writes.

The discovery moment was geometric, not logical. The agronomist opened staging on his iPad to scroll for a member he was about to call, zoomed out, and said waar is alles. Where is everything.

Why MySQL casts the way it does

This is documented behaviour, and it has been for decades. The MySQL manual on type conversion says, in its quiet way, that if a string starts with a number, the converted value is the value of the leading numeric portion, and if it does not start with a number, the converted value is 0. See MySQL type conversion in expression evaluation. No warning. No NULL. A leading numeric prefix is good enough.

Combine that with the WordPress meta_query layer, which is a thin wrapper that emits the cast directly into the generated SQL, and you get a query that runs, returns sensible-looking row counts, and silently lies about every coordinate after the first comma. The WP_Query meta_query reference describes the type parameter as a hint to MySQL. The hint is taken literally.

There is a wider lesson hiding in this. An HN thread the same week argued the only scalable delete in Postgres is DROP TABLE: once a table is big enough, the operations that scale are the ones that ignore the row contents. WordPress's wp_postmeta is the inverse-shaped version of that problem. Once you have hundreds of thousands of opaque string blobs keyed by a meta_key, the operations that work are the ones that ignore the structure: index lookups on the key, returns of the raw value, parsing in application code. The moment you ask MySQL to reason about a string value's structure through meta_query type hints, you are at the mercy of whatever cast got written into the SQL.

The ninety-minute fix

The fix had two parts. The first was a one-line decision in the importer: bypass meta_query entirely and do the bounding-box filter in PHP after pulling the parsed coordinates.

$query = new WP_Query([
    'post_type'      => 'perceel',
    'posts_per_page' => -1,
    'fields'         => 'ids',
]);

foreach ($query->posts as $post_id) {
    $csv    = get_post_meta($post_id, 'polygon_csv', true);
    $coords = parse_polygon_csv($csv);
    if ($coords === null)                          continue;
    if (! polygon_intersects_bbox($coords, $bbox)) continue;
    enqueue_for_payload($post_id, $coords);
}

Slower, obviously. For 6,400 parcels the full import ran in eleven minutes. The agronomist looked at the map at 03:40 the next morning and saw 6,381 parcels in the right shape, with 19 broken CSVs flagged for manual review.

The second part of the fix was the part that mattered for the new system. In Payload, the parcel schema now stores boundary as a typed GeoJSON polygon field, validated on write, indexed on read.

{
  name: 'boundary',
  type: 'json',
  validate: (value) =>
    isValidGeoJSONPolygon(value)
      || 'Polygon must be a closed ring of [lng, lat] pairs.',
  admin: {
    components: { Field: PolygonMapEditor },
  },
}

No CSV. No meta_query. No silent casts. A parcel either has a valid polygon or the editor refuses to save it. The agronomist gets the same drag-the-vertices map he had before. The database gets a structure it can actually reason about.

The five-minute audit you can run today

If you run any WordPress site with custom field comparisons in queries, this audit takes five minutes and tells you whether you are sitting on the same bug.

Open a database client. Pick one custom field you query against with meta_query. Run this:

SELECT
  meta_value,
  CAST(meta_value AS DECIMAL(20,6)) AS cast_value
FROM wp_postmeta
WHERE meta_key = 'your_field_key'
ORDER BY RAND()
LIMIT 50;

Look at the two columns side by side. If cast_value differs from the leading digits of meta_value in ways you did not expect, or if meta_value contains anything other than digits, a decimal point, and a sign, your meta_query with a numeric type hint is lying to you. Quietly, every request, every cron, every export.

The fix in the legacy site is almost never "change the data". The data is the only thing the editors trust. The fix is to stop asking SQL to reason about string structure. Pull the rows, parse in PHP, filter in PHP. It is slower. It is also correct.

What we kept from this

When we built the Astro and Payload rebuild for the cooperative, the thing we ran into was that ACF's repeater shape can quietly drift from typed structure to opaque string, and downstream queries will not warn you. We ended up writing an importer that round-trips every parcel through a typed validator before it reaches the new database, and a Payload schema that refuses to store a polygon that cannot be parsed. If you have a WordPress site that is older than its current team, our legacy migration work always starts with a read-pass over the actual stored values, not the field labels.

The smallest thing worth doing today: pick one meta_query you wrote more than a year ago. Open the database. Look at what is actually in the column. The label and the contents are not the same thing.