MySQL spatial query doesn't always join rows that it should

Question

Using MySQL 8.0.35-0ubuntu0.23.04.1, the following query returns 116 rows with precincts where districtgroup='municipality' when this line is uncommented: -- used.districtgroup in ('municipality')

but with this line it only finds matching rows in precincts for rows where districtgroup='mem_city_council_super' and doesn't find any matching rows for 'municipality':

. -- used.districtgroup in ('municipality', 'mem_city_council_super')

How is this possible? It should be finding the municipal rows in addition to the mem_city_council_super rows.

SELECT 
        used.districtgroup, 
        used.district, 
        d.districtgroup,
        d.district,
        p.id AS precinct
    FROM  
        (SELECT 
            c.state, 
            c.county, 
            c.districtgroup, 
            c.district
         FROM contests c
         WHERE c.state = 'tn' AND c.county = 'shelby' AND c.electiondate = '2023-10-05'
         GROUP BY c.state, c.county, c.districtgroup, c.district
          ) used
    LEFT JOIN districts d ON used.state = d.state AND used.county = d.county AND used.districtgroup = d.districtgroup AND d.district = used.district 
    LEFT JOIN precincts p ON p.state = used.state AND p.county = used.county AND ST_Intersects(p.boundary, d.boundary)
     WHERE 
      -- used.districtgroup in ('municipality', 'mem_city_council_super')
      -- used.districtgroup in ('municipality')
    ORDER BY used.districtgroup, used.district, p.id

Also, if I only include this WHERE clause it only joins precincts where used.district='9' used.districtgroup = 'mem_city_council_super'

but if I use this where clause, it returns 68 rows with matching precincts where the district value is '8': used.districtgroup = 'mem_city_council_super' and used.district='8'

I did try increasing join_buffer_size but nothing changed.

Here are the CREATE statements for the districts and precincts tables:

CREATE TABLE `precincts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `state` varchar(16) DEFAULT NULL,
  `county` varchar(64) DEFAULT NULL,
  `key` varchar(16) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL,
  `pollingplace` varchar(64) DEFAULT NULL COMMENT 'the token of the last-used polling place for this precinct',
  `districts` json DEFAULT NULL,
  `boundary` geometry NOT NULL /*!80003 SRID 4326 */ DEFAULT (st_pointfromtext(_utf8mb4'POINT(0 0)',4326)),
  `lastmodified` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  SPATIAL KEY `boundary` (`boundary`)
) ENGINE=InnoDB AUTO_INCREMENT=1846 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `districts` (
  `state` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `county` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `tier` enum('state','county','municipal') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `counties` json DEFAULT NULL,
  `districtgroupname` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `districtgroup` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `districtname` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `district` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `datasourcename` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `boundary` geometry NOT NULL /*!80003 SRID 4326 */ DEFAULT (st_pointfromtext(_utf8mb4'POINT(0 0)',4326)),
  `description` varchar(4096) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `whenchanged` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `lastchangeby` int unsigned DEFAULT NULL,
  SPATIAL KEY `boundary` (`boundary`),
  KEY `state_counties` (`state`,(cast(`counties` as char(64) array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci