【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】5.7.25-OceanBase_CE-v4.5.0.0
【问题描述】聚合派生表及其等价子查询写法在 JOIN 条件中均返回错误结果，物化到实体表后才与 MySQL 5.7 一致
【复现路径】
-- Found a subquery issue in OceanBase (5.7.25-OceanBase_CE-v4.5.0.0) with complex SQL,
-- where the behavior is inconsistent with MySQL (5.7.44). This should be a BUG, please follow up.
-- Test case data preparation: please create the following tables and insert the initial data
-- ----------------------------
-- Records of bs_pt_max
-- ----------------------------
INSERT INTO `bs_pt_max` VALUES ('232680563414822912', '2026-10-05 09:40:00');
INSERT INTO `bs_pt_max` VALUES ('232690045981192192', '2026-10-05 09:40:00');
-- ----------------------------
-- Table structure for bs_run
-- ----------------------------
DROP TABLE IF EXISTS `bs_run`;
CREATE TABLE `bs_run` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`schedule_id` bigint(20) NOT NULL COMMENT 'Schedule plan ID',
`schedule_name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
`target_type` tinyint(1) NOT NULL COMMENT 'Plan type: 1-task plan, 2-task group plan',
`target_id` bigint(20) NOT NULL COMMENT 'Target ID: task ID or task group ID',
`schedule_trigger_time` datetime NOT NULL COMMENT 'Execution time',
`misfire_window` int(11) NOT NULL COMMENT 'Misfire tolerance window (seconds)',
`max_execution_seconds` int(11) NOT NULL DEFAULT '-1' COMMENT 'Max job run time (seconds), -1=unlimited',
`max_total_seconds` int(11) NOT NULL DEFAULT '-1' COMMENT 'Overall timeout from trigger to completion (seconds), -1=unlimited',
`cancel_reason` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Cancel reason: MISFIRE/TOTAL_TIMEOUT',
`paused` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Pause flag: 0-normal, 1-paused (only blocks the triggering and dispatch of undispatched tasks)',
`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Status: 0-pending, 1-triggered, 2-cancelled, 3-running, 4-partial failure, 5-success, 6-misfired, 9-triggering',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_plan_execute` (`schedule_id`,`target_type`,`target_id`,`schedule_trigger_time`),
KEY `idx_plan_id` (`schedule_id`),
KEY `idx_execute_time` (`schedule_trigger_time`),
KEY `idx_status` (`status`)
) COMMENT='Task run instance table';
-- ----------------------------
-- Records of bs_run
-- ----------------------------
-- ----------------------------
-- Table structure for bs_schedule
-- ----------------------------
DROP TABLE IF EXISTS `bs_schedule`;
CREATE TABLE `bs_schedule` (
`id` bigint(20) NOT NULL,
`schedule_name` varchar(100) COLLATE utf8mb4_bin NOT NULL COMMENT 'Schedule plan name',
`target_type` tinyint(1) NOT NULL COMMENT 'Plan type: 1-task plan, 2-task group plan',
`target_id` bigint(20) NOT NULL COMMENT 'Target ID: task ID or task group ID',
`second` varchar(100) COLLATE utf8mb4_bin NOT NULL DEFAULT '*' COMMENT 'Second, supports * wildcard and comma-separated multiple values',
`minute` varchar(100) COLLATE utf8mb4_bin NOT NULL DEFAULT '*' COMMENT 'Minute, supports * wildcard and comma-separated multiple values',
`hour` varchar(100) COLLATE utf8mb4_bin NOT NULL DEFAULT '*' COMMENT 'Hour, supports * wildcard and comma-separated multiple values',
`day` varchar(100) COLLATE utf8mb4_bin NOT NULL DEFAULT '*' COMMENT 'Day, supports * wildcard and comma-separated multiple values',
`month` varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '*' COMMENT 'Month, supports * wildcard and comma-separated multiple values',
`week` varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '*' COMMENT 'Weekday, supports * wildcard and comma-separated multiple values',
`workday_of_month` varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '*' COMMENT 'Nth workday of the month, supports * wildcard and comma-separated multiple values',
`last_workday_of_month` varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '*' COMMENT 'Nth-to-last workday of the month, supports * wildcard and comma-separated multiple values',
`schedule_type` tinyint(1) NOT NULL DEFAULT '2' COMMENT 'Schedule type: 1-single-shot, 2-recurring',
`run_time` datetime DEFAULT NULL COMMENT 'Single-shot run time',
`description` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Schedule time description',
`is_workday_only` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Run only on workdays: 1-yes, 0-no (recurring plans only)',
`is_holiday_only` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Run only on holidays: 1-yes, 0-no (recurring plans only)',
`misfire_window` int(11) NOT NULL DEFAULT '180' COMMENT 'Misfire tolerance window (seconds)',
`max_total_seconds` int(11) NOT NULL DEFAULT '-1' COMMENT 'Overall timeout from trigger to completion (seconds), -1=unlimited',
`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Status: 0-draft, 1-active, 2-disabled, 3-frozen',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
`create_user` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Create user',
`create_user_name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Create user name',
`update_user` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Update user',
`update_user_name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Update user name',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_plan_name` (`schedule_name`)
) COMMENT='Schedule plan table';
-- ----------------------------
-- Records of bs_schedule
-- ----------------------------
INSERT INTO `bs_schedule` VALUES ('232680563414822912', 'OB Test A', '1', '232680299945422848', '0', '2,12,22,32,42,52', '*', '*', '*', '*', '*', '*', '2', null, 'Every Ten Minutes', '0', '0', '300', '7200', '1', '2026-10-05 09:50:43', '2026-10-05 09:50:43', 'guojing', 'guojing', null, null);
INSERT INTO `bs_schedule` VALUES ('232690045981192192', 'Deep Flow Test', '2', '232688057071595520', '30', '0,30', '*', '*', '*', '*', '*', '*', '2', null, 'Every Half Hour', '0', '0', '300', '7200', '1', '2026-10-05 10:28:44', '2026-10-05 10:28:44', 'admin', 'admin', null, null);
INSERT INTO `bs_schedule` VALUES ('233110326772133888', 'Broadcast By Instance Count', '1', '233109807550853120', '*', '*', '*', '*', '*', '*', '*', '*', '1', '2026-10-06 14:20:56', 'Run Once At Specified Time', '0', '0', '300', '7200', '1', '2026-10-06 14:18:55', '2026-10-06 14:18:55', 'guojing', 'guojing', null, null);
INSERT INTO `bs_schedule` VALUES ('233132870753480704', 'Single-shot Plan Test', '2', '232688057071595520', '*', '*', '*', '*', '*', '*', '*', '*', '1', '2026-10-06 15:52:32', 'Single-shot Plan', '0', '0', '300', '7200', '1', '2026-10-06 15:48:18', '2026-10-06 15:48:18', 'guojing', 'guojing', null, null);
-- ----------------------------
-- Table structure for bs_time
-- ----------------------------
DROP TABLE IF EXISTS `bs_time`;
CREATE TABLE `bs_time` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`time_value` datetime DEFAULT NULL,
`date_value` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Date',
`second` smallint(5) DEFAULT NULL,
`minute` smallint(5) DEFAULT NULL,
`hour` smallint(5) DEFAULT NULL,
`day` smallint(5) DEFAULT NULL,
`month` smallint(5) DEFAULT NULL,
`weekday` smallint(5) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_time_value` (`time_value`)
) COMMENT='Time window table';
-- ----------------------------
-- Records of bs_time
-- ----------------------------
INSERT INTO `bs_time` VALUES ('79063', '2026-10-05 09:40:00', '2026-10-05', '0', '40', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79064', '2026-10-05 09:40:30', '2026-10-05', '30', '40', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79065', '2026-10-05 09:41:00', '2026-10-05', '0', '41', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79066', '2026-10-05 09:41:30', '2026-10-05', '30', '41', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79067', '2026-10-05 09:42:00', '2026-10-05', '0', '42', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79068', '2026-10-05 09:42:30', '2026-10-05', '30', '42', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79069', '2026-10-05 09:43:00', '2026-10-05', '0', '43', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79070', '2026-10-05 09:43:30', '2026-10-05', '30', '43', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79071', '2026-10-05 09:44:00', '2026-10-05', '0', '44', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79072', '2026-10-05 09:44:30', '2026-10-05', '30', '44', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79073', '2026-10-05 09:45:00', '2026-10-05', '0', '45', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79074', '2026-10-05 09:45:30', '2026-10-05', '30', '45', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79075', '2026-10-05 09:46:00', '2026-10-05', '0', '46', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79076', '2026-10-05 09:46:30', '2026-10-05', '30', '46', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79077', '2026-10-05 09:47:00', '2026-10-05', '0', '47', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79078', '2026-10-05 09:47:30', '2026-10-05', '30', '47', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79079', '2026-10-05 09:48:00', '2026-10-05', '0', '48', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79080', '2026-10-05 09:48:30', '2026-10-05', '30', '48', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79081', '2026-10-05 09:49:00', '2026-10-05', '0', '49', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79082', '2026-10-05 09:49:30', '2026-10-05', '30', '49', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79083', '2026-10-05 09:50:00', '2026-10-05', '0', '50', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79084', '2026-10-05 09:50:30', '2026-10-05', '30', '50', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79085', '2026-10-05 09:51:00', '2026-10-05', '0', '51', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79086', '2026-10-05 09:51:30', '2026-10-05', '30', '51', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79087', '2026-10-05 09:52:00', '2026-10-05', '0', '52', '9', '5', '10', '1');
INSERT INTO `bs_time` VALUES ('79088', '2026-10-05 10:00:30', '2026-10-05', '30', '0', '10', '5', '10', '1');
-- Approach 1: subquery inside a function argument: COALESCE((SELECT GREATEST(MAX( ...
-- MySQL (5.7.44) and OceanBase (5.7.25-OceanBase_CE-v4.5.0.0) return different result sets
SELECT
ep.`id`,
ep.`schedule_name`,
ep.`target_type`,
ep.`target_id`,
t.`time_value`,
ep.`misfire_window`,
300 as max_execution_seconds,
ep.`max_total_seconds`,
0
FROM `bs_schedule` ep
INNER JOIN `bs_time` t
ON t.`time_value` > COALESCE(
(SELECT GREATEST(MAX(pt.`schedule_trigger_time`), STR_TO_DATE('2026-10-05 09:40:00', '%Y-%m-%d %H:%i:%s')) -- latest schedule trigger time of existing data, generate new data after it
FROM `bs_run` pt
WHERE pt.`schedule_id` = ep.`id`),
STR_TO_DATE('2026-10-05 09:40:00', '%Y-%m-%d %H:%i:%s')
)
WHERE ep.`status` = 1 and ep.schedule_type = 2 -- schedule type 2: recurring
-- Second-level match (using precomputed column t.second instead of SECOND(t.time_value)) to reduce computation and improve efficiency
AND (ep.`second` = '*' OR FIND_IN_SET(t.`second`, REPLACE(ep.`second`, ' ', '')))
-- Minute-level match
AND (ep.`minute` = '*' OR FIND_IN_SET(t.`minute`, REPLACE(ep.`minute`, ' ', '')))
-- Hour-level match
AND (ep.`hour` = '*' OR FIND_IN_SET(t.`hour`, REPLACE(ep.`hour`, ' ', '')))
-- Day-level match
AND (ep.`day` = '*' OR FIND_IN_SET(t.`day`, REPLACE(ep.`day`, ' ', '')))
-- Month-level match
AND (ep.`month` = '*' OR FIND_IN_SET(t.`month`, REPLACE(ep.`month`, ' ', '')))
-- Week-level match
AND (ep.`week` = '*' OR FIND_IN_SET(t.`weekday`, REPLACE(ep.`week`, ' ', '')));
-- MySQL result set, 3 records
232680563414822912 OB Test A 1 232680299945422848 2026-10-05 09:42:00 300 300 7200 0
232680563414822912 OB Test A 1 232680299945422848 2026-10-05 09:52:00 300 300 7200 0
232690045981192192 Deep Flow Test 2 232688057071595520 2026-10-05 10:00:30 300 300 7200 0
-- OceanBase result set, 4 records
"id","schedule_name","target_type","target_id","time_value","misfire_window","max_execution_seconds","max_total_seconds","0"
232680563414822912,"OB Test A",1,232680299945422848,"2026-10-05 09:42:00",300,300,7200,0
232680563414822912,"OB Test A",1,232680299945422848,"2026-10-05 09:52:00",300,300,7200,0
232690045981192192,"Deep Flow Test",2,232688057071595520,"2026-10-05 09:42:00",300,300,7200,0
232690045981192192,"Deep Flow Test",2,232688057071595520,"2026-10-05 09:52:00",300,300,7200,0
-- Approach 2: rewritten as a LEFT JOIN aggregate derived table pt_max.
-- MySQL returns the same result set as Approach 1, but OceanBase still returns the wrong result set
SELECT
ep.`id`,
ep.`schedule_name`,
ep.`target_type`,
ep.`target_id`,
t.`time_value`,
ep.`misfire_window`,
300 as max_execution_seconds,
ep.`max_total_seconds`,
0
FROM `bs_schedule` ep
LEFT JOIN (
SELECT schedule_id, MAX(schedule_trigger_time) AS max_trigger_time
FROM bs_run
GROUP BY schedule_id
) pt_max ON pt_max.schedule_id = ep.id
INNER JOIN `bs_time` t
ON t.`time_value` > COALESCE(GREATEST(pt_max.max_trigger_time, STR_TO_DATE('2026-10-05 09:40:00', '%Y-%m-%d %H:%i:%s')), STR_TO_DATE('2026-10-05 09:40:00', '%Y-%m-%d %H:%i:%s'))
WHERE ep.`status` = 1 and ep.schedule_type = 2 -- schedule type 2: recurring
-- Second-level match (using precomputed column t.second instead of SECOND(t.time_value)) to reduce computation and improve efficiency
AND (ep.`second` = '*' OR FIND_IN_SET(t.`second`, REPLACE(ep.`second`, ' ', '')))
-- Minute-level match
AND (ep.`minute` = '*' OR FIND_IN_SET(t.`minute`, REPLACE(ep.`minute`, ' ', '')))
-- Hour-level match
AND (ep.`hour` = '*' OR FIND_IN_SET(t.`hour`, REPLACE(ep.`hour`, ' ', '')))
-- Day-level match
AND (ep.`day` = '*' OR FIND_IN_SET(t.`day`, REPLACE(ep.`day`, ' ', '')))
-- Month-level match
AND (ep.`month` = '*' OR FIND_IN_SET(t.`month`, REPLACE(ep.`month`, ' ', '')))
-- Week-level match
AND (ep.`week` = '*' OR FIND_IN_SET(t.`weekday`, REPLACE(ep.`week`, ' ', '')));
-- Approach 3: materialize the aggregate derived table pt_max into a table; then OceanBase and MySQL return consistent result sets.
DROP TABLE IF EXISTS `bs_pt_max`;
CREATE TABLE bs_pt_max as
select s.id as schedule_id, COALESCE(GREATEST(pt_max.max_trigger_time, STR_TO_DATE('2026-10-05 09:40:00', '%Y-%m-%d %H:%i:%s')),
STR_TO_DATE('2026-10-05 09:40:00', '%Y-%m-%d %H:%i:%s')) AS max_trigger_time
from bs_schedule s
left join (
SELECT schedule_id, MAX(schedule_trigger_time) AS max_trigger_time
FROM bs_run
GROUP BY schedule_id
) pt_max on s.id = pt_max.schedule_id
where s.`status` = 1 and s.`schedule_type` = 2;
SELECT
ep.`id`,
ep.`schedule_name`,
ep.`target_type`,
ep.`target_id`,
t.`time_value`,
ep.`misfire_window`,
300 as max_execution_seconds,
ep.`max_total_seconds`,
0
FROM `bs_schedule` ep
LEFT JOIN bs_pt_max pt_max ON pt_max.schedule_id = ep.id
INNER JOIN `bs_time` t
ON t.`time_value` > pt_max.max_trigger_time
WHERE ep.`status` = 1 and ep.schedule_type = 2 -- schedule type 2: recurring
-- Second-level match (using precomputed column t.second instead of SECOND(t.time_value)) to reduce computation and improve efficiency
AND (ep.`second` = '*' OR FIND_IN_SET(t.`second`, REPLACE(ep.`second`, ' ', '')))
-- Minute-level match
AND (ep.`minute` = '*' OR FIND_IN_SET(t.`minute`, REPLACE(ep.`minute`, ' ', '')))
-- Hour-level match
AND (ep.`hour` = '*' OR FIND_IN_SET(t.`hour`, REPLACE(ep.`hour`, ' ', '')))
-- Day-level match
AND (ep.`day` = '*' OR FIND_IN_SET(t.`day`, REPLACE(ep.`day`, ' ', '')))
-- Month-level match
AND (ep.`month` = '*' OR FIND_IN_SET(t.`month`, REPLACE(ep.`month`, ' ', '')))
-- Week-level match
AND (ep.`week` = '*' OR FIND_IN_SET(t.`weekday`, REPLACE(ep.`week`, ' ', '')));