【求助】MySQL8.0查询语句错误

[fei xu]

MySQL8.0

-- DDL

CREATE TABLE `a3_category` (
`ID` bigint NOT NULL AUTO_INCREMENT,
`NAME` varchar(64) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3

CREATE TABLE `a3_item` (
`ID` bigint NOT NULL DEFAULT '0',
`IS_ACTIVE` int NOT NULL DEFAULT '1',
`CATEGORY` bigint NOT NULL,
`ICON` mediumtext COMMENT,
`NAME` varchar(64) NOT NULL,
`CODE` varchar(36) DEFAULT NULL,
`MEMBER_ID` bigint DEFAULT NULL,
`IS_DELETED` int NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

-- DML

INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-10, 1, -1, '1', 'mock1', 'mock1', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-9, 1, -1, '2', 'mock2', 'mock2', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-8, 1, -1, '3', 'mock3', 'mock3', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-7, 1, -2, '4', 'mock4', 'mock4', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-6, 1, -1, '5', 'mock5', 'mock5', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-5, 1, -1, '6', 'mock6', 'mock6', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-4, 1, -1, '7', 'mock7', 'mock7', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-3, 1, -1, '8', 'mock8', 'mock8', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-2, 1, -1, '9', 'mock9', 'mock9', 0, 0);
INSERT INTO test.a3_item (ID, IS_ACTIVE, CATEGORY, ICON, NAME, CODE, MEMBER_ID, IS_DELETED) VALUES (-1, 1, -1, '0', 'mock10', 'mock10', 0, 0);

INSERT INTO test.a3_category (ID, NAME) VALUES (1, 'IT Service');
INSERT INTO test.a3_category (ID, NAME) VALUES (2, 'Asset Service');


-- QUERY SQL
select count(1)
from a3_item item
join (select max(ID) ID
from a3_item
where IS_DELETED = 0
and IS_ACTIVE = 1
group by code
union
select max(ID) ID
from a3_item
where IS_DELETED = 0
group by code
having max(IS_ACTIVE) = 0) latest on latest.ID = item.ID
join a3_category on item.CATEGORY = a3_category.ID
where item.IS_DELETED = 0
and item.CATEGORY = 1
and (item.MEMBER_ID = 1 or item.MEMBER_ID = 0);

-- ERROR INFO
[08S01] Communications link failure The last packet successfully received from the server was 58 milliseconds ago. The last packet sent successfully to the server was 58 milliseconds ago. Communications link fail ...