Search code examples
sqlmysql

winMySQL query fails on Ubuntu instance but works with Windows instance

How is it possible that the same query works on Windows MySQL instance but does not work on Ubuntu MySQL instance?

Instance versions are pretty close:

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

Windows: @@sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Ubuntu: @@sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION

The error on Ubuntu:

ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'p.priority' which is not in SELECT list; this is incompatible with DISTINCT

The query:

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;

Table is created this way:

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

Screenshot from Heidi on Windows; query copied form this SO question:

enter image description here

Solution

  • The SQL mode ONLY_FULL_GROUP_BY affects how your query is executed.

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

    ... a query that has DISTINCT and ORDER BY is rejected as invalid if any ORDER BY expression does not satisfy at least one of these conditions:

    • The expression is equal to one in the select list

    • All columns referenced by the expression and belonging to the query's selected tables are elements of the select list

    Read the manual page I linked to for more explanation and examples of why this rule exists.

    This rule is not in effect if your SQL mode omits ONLY_FULL_GROUP_BY, but this causes your queries to have arbitrary results.

    I recommend leaving the ONLY_FULL_GROUP_BY mode in effect, and restore this option on your Windows instance. It is enabled by default since MySQL 5.7.5. This is important for MySQL to behave according to standard SQL.

    Most other brands of SQL enforce the same rule, with no option to disable it.

    You can fix your query by including in the select-list the columns referenced in your 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
...