从2.0.x升级至2.1.6后,增加了函数返回值校验,导致旧版能运行的查询到新版本后执行不了
mysql原始表DDL:
CREATE TABLE test_db.`t1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`port` char(5) NOT NULL DEFAULT '',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1836251747245510660 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
CREATE TABLE test_db.`t2` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`state` tinyint(1) NOT NULL DEFAULT '0',
`history_id` bigint(20) NOT NULL DEFAULT '0',
`enterprise_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '企业ID',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_h_m_id` (`history_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1836251747270676483 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
CREATE TABLE test_db.`t3` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`state` tinyint(3) NOT NULL,
`bill_no` varchar(50) NOT NULL DEFAULT '',
`history_id` bigint(20) NOT NULL DEFAULT '0',
`receipt_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1836251747287453699 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
查询语句:
select
msr.enterprise_id,
concat(mgi.bill_no, '-', cast(mgi.history_id as varchar)) as union_id,
receipt_time
from
mysql_cat.test_db.t1 mr
left join mysql_cat.test_db.t2 msr on
mr.id = msr.history_id
left join mysql_cat.test_db.t3 mgi on
mgi.history_id = mr.id
where
-- mgi.state = 10
mgi.state in (10, 11, 12);
目前只发现mysql catalog中left join会出现校验失败的错误,换成inner join或者right join能够正常执行