[SQL] 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

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:



Continue reading...