Início / coding / Perguntas / 79043716
Accepted
user1034461
Asked: 2024-10-01 23:01:57 +0800 CST

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

  • 772

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:

insira a descrição da imagem aqui

sql

1 respostas

  1. Best Answer

    O modo SQL ONLY_FULL_GROUP_BYafeta como sua consulta é executada.

    https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html diz:

    ... uma consulta que tem DISTINCTe ORDER BYé rejeitada como inválida se qualquer ORDER BYexpressão não satisfaz pelo menos uma destas condições:

    • A expressão é igual a um na lista de seleção

    • Todas as colunas referenciadas pela expressão e pertencentes às tabelas selecionadas da consulta são elementos da lista de seleção

    Leia a página do manual que vinculei para obter mais explicações e exemplos do porquê essa regra existe.

    Esta regra não terá efeito se o seu modo SQL omitir ONLY_FULL_GROUP_BY, mas isso faz com que suas consultas tenham resultados arbitrários.

    Recomendo deixar o ONLY_FULL_GROUP_BYmodo em vigor e restaurar essa opção na sua instância do Windows. Ela é habilitada por padrão desde o MySQL 5.7.5. Isso é importante para que o MySQL se comporte de acordo com o SQL padrão.

    A maioria das outras marcas de SQL aplicam a mesma regra, sem opção para desativá-la.

    Você pode corrigir sua consulta incluindo na lista de seleção as colunas referenciadas em ORDER BY:

    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`,
`priority`, `is_parent`  <-- add these columns
...
    • 1

relate perguntas