A consulta winMySQL falha na instância do Ubuntu, mas funciona com a instância do Windows [duplicado]

Como é possível que a mesma consulta funcione na instância MySQL do Windows, mas não funcione na instância MySQL do Ubuntu?

As versões de instância são bem próximas:

• Janelas: 8.0.16
• Ubuntu: 8.0.39-0ubuntu0.24.04.2

Windows: @@sql_mode: TABELAS_DE_TRANS_TRIBUIÇÃO_TRIBUÍDAS, SEM_SUBSTITUIÇÃO_DO_ENGINE

Ubuntu: @@sql_mode: SOMENTE_GRUPO_COMPLETO_POR, TABELAS_DE_TRANSFERÊNCIA_ESTRITAS, SEM_DATA_ZERO, SEM_DATA_ZERO, ERRO_PARA_DIVISÃO_POR_ZERO, SEM_SUBSTITUIÇÃO_DO_MECANISMO

O erro no Ubuntu:

ERRO 3065 (HY000): A expressão nº 1 da cláusula ORDER BY não está na lista SELECT, faz referência à coluna 'p.priority' que não está na lista SELECT; isso é incompatível com DISTINCT

A pergunta:

SELECT DISTINCT 
    `id`, `date_created`, `date_last_update`, `text_id_map`, `address_search_aid`, `lat`, `lng`, 
    `street_line1`, `street_line2`, `zip`, `company_status`, `compassion_level`, `emails`, 
    `phones`, `social_media`, `urls`, `description`, `importance`, `name`, `object_status`, 
    `search_aid`, `short_name`, `created_by_id`, `last_update_by_id`, `alternate_country_id`,
    `city_id`, `parent_id`, `city_region_id`, `autocomplete`, `sitemap_xml`
FROM (
    
    SELECT p.*, 0 AS `priority`, CASE WHEN `parent_id` IS NULL THEN 1 ELSE 0 END AS `is_parent`
    FROM `provider` p 
    CROSS JOIN JSON_TABLE( JSON_KEYS(`name`), '$[*]' COLUMNS (locale VARCHAR(10) PATH '$') ) AS l 
    CROSS JOIN JSON_TABLE( JSON_EXTRACT(`name`, CONCAT('$.', l.locale)), '$' COLUMNS (`jsonval` VARCHAR(2048) PATH '$') ) AS j
    WHERE
        INSTR( CONCAT(' ', REPLACE(j.jsonval, '-', ' '), ' '), CONCAT(' ', 'web', ' ') ) > 0
        AND
        INSTR( CONCAT(' ', REPLACE(j.jsonval, '-', ' '), ' '), CONCAT(' ', 'kon', ' ') ) > 0 
    
    UNION
    
    SELECT *, 
        CASE 
            WHEN 
                INSTR( CONCAT(' ', `autocomplete`, ' '), CONCAT(' ', 'web', ' ') ) > 0 
                AND 
                INSTR( CONCAT(' ', `autocomplete`, ' '), CONCAT(' ', 'kon', ' ') ) > 0 
            THEN 1 
            WHEN INSTR( `autocomplete`, 'web' ) > 0 AND INSTR( `autocomplete`, 'kon' ) > 0 THEN 2 
            ELSE 3 
        END `priority`, 
        CASE 
            WHEN parent_id IS NULL THEN 1 
            ELSE 0 
        END AS `is_parent`
    FROM `provider`
    
) p
WHERE `priority` < 3
ORDER BY `priority`, `is_parent` DESC
LIMIT 10;

A tabela é criada desta forma:

CREATE TABLE `provider` (
  `id` int NOT NULL,
  `date_created` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_last_update` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `text_id_map` text COLLATE utf8mb4_unicode_ci,
  `address_search_aid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lat` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lng` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `street_line1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `street_line2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `zip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `company_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `compassion_level` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `emails` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phones` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `social_media` text COLLATE utf8mb4_unicode_ci,
  `urls` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `importance` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` text COLLATE utf8mb4_unicode_ci,
  `object_status` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `search_aid` text COLLATE utf8mb4_unicode_ci,
  `short_name` text COLLATE utf8mb4_unicode_ci,
  `created_by_id` int DEFAULT NULL,
  `last_update_by_id` int DEFAULT NULL,
  `alternate_country_id` int DEFAULT NULL,
  `city_id` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  `city_region_id` int DEFAULT NULL,
  `autocomplete` text COLLATE utf8mb4_unicode_ci,
  `sitemap_xml` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `FKotfd80u3xayhxxjrvtpclqwe6` (`created_by_id`),
  KEY `FK7gqnbcylk4ghwciki0779g32u` (`last_update_by_id`),
  KEY `FKk6jlj4kwu3hi5kl4a4qqso7j3` (`alternate_country_id`),
  KEY `FKrlh8uhcluf8si4vfbgdw3w6p1` (`city_id`),
  KEY `FK6psluxn6a0b64bcxggfuum2l0` (`parent_id`),
  KEY `FKegsn16mm766i6j5du01dlvn6` (`city_region_id`),
  CONSTRAINT `FK6psluxn6a0b64bcxggfuum2l0` FOREIGN KEY (`parent_id`) REFERENCES `provider` (`id`),
  CONSTRAINT `FK7gqnbcylk4ghwciki0779g32u` FOREIGN KEY (`last_update_by_id`) REFERENCES `veg_user` (`id`),
  CONSTRAINT `FKegsn16mm766i6j5du01dlvn6` FOREIGN KEY (`city_region_id`) REFERENCES `city_region` (`id`),
  CONSTRAINT `FKk6jlj4kwu3hi5kl4a4qqso7j3` FOREIGN KEY (`alternate_country_id`) REFERENCES `country` (`id`),
  CONSTRAINT `FKotfd80u3xayhxxjrvtpclqwe6` FOREIGN KEY (`created_by_id`) REFERENCES `veg_user` (`id`),
  CONSTRAINT `FKrlh8uhcluf8si4vfbgdw3w6p1` FOREIGN KEY (`city_id`) REFERENCES `city` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Captura de tela de Heidi no Windows; consulta copiada desta pergunta do SO: