2.1.6版本函数返回值校验问题

Viewed 156
2.1

从2.0.x升级至2.1.6后，增加了函数返回值校验，导致旧版能运行的查询到新版本后执行不了
image.png

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能够正常执行

North Lin35
3 Answers

先看下用的是新优化器还是旧优化器，如果是旧优化器先切到新优化器

BiteTheDDDDt106

SET enable_nereids_planner=false;

不过貌似关闭了，其他地方又出问题了

林只先生16