执行Jira递归SQL查询时遇path列数据过长错误求助

错误根源 #

报错Data truncation: Data too long for column 'path' at row 2的核心原因是递归CTE中path字段的初始长度限制不足：

• 初始查询里将path定义为cast(j.id as char(100))，仅分配了100字符的固定空间
• 递归过程中每次都会拼接新的内容（日期字符串+Issue ID），随着递归层级增加，path的总长度会快速突破100字符的上限，导致数据截断

解决方法 #

将path字段的初始类型改为更长的可变长度字符类型，比如varchar(1000)（可根据实际递归层级预估足够长度），或者直接使用不限长度的text类型（MySQL、PostgreSQL等主流数据库均支持）。

修改后的关键代码片段 #

把初始查询中的path定义替换为：

cast(j.id as varchar(1000)) AS path

若数据库支持text类型，也可简化为：

cast(j.id as text) AS path

完整修改后的SQL #

with recursive links as( select
j.id,
concat(p.pkey,'-',j.issuenum) as Task,
(select pname from jiradb.issuetype t where j.issuetype = t.id) Type,
cast(j.id as varchar(1000)) AS path, -- 此处调整了长度限制
coalesce((SELECT cfv.stringvalue
FROM   jiradb.customfieldvalue cfv
LEFT   JOIN jiradb.customfield cf ON cf.id = cfv.customfield
WHERE  cf.cfname = 'Epic Name'
AND    cfv.issue = j.id),'No Epic Name') EpicName
from jiradb.jiraissue j
-- project
join jiradb.project p on j.project = p.id
-- issuetype
join jiradb.issuetype t on j.issuetype = t.id and t.pname = 'Epic'
where instr('(E)139899','(E)') and j.id = replace('(E)139899','(E)','')

union all

select
i.destination as id,
concat(p.pkey,'-',j.issuenum) as Task,
(select pname from jiradb.issuetype t where j.issuetype = t.id) Type,
concat(l.path, '/', coalesce((SELECT DATE(cfv.datevalue)
FROM   jiradb.customfieldvalue cfv
LEFT   JOIN jiradb.customfield cf ON cf.id = cfv.customfield
WHERE  cf.cfname = 'Planed start date'
AND    cfv.issue = i.destination),date('9999-12-31')), '/',
coalesce((select date(j.duedate) from jiradb.jiraissue j where j.id = i.destination),date('9999-12-31')), '/', i.destination)  AS path,
l.EpicName
from (  -- get the links for epic, tasks and subtasks
select source as source, destination as destination from jiradb.issuelink where linktype in (10200,10100)
and destination not in (select source from jiradb.issuelink where linktype = 10401)
union
-- get the links for stories
select destination as source, source as destination from jiradb.issuelink where linktype = 10401
) i
-- links
join links l on i.source = l.id
-- Jiraissue
join jiradb.jiraissue j on i.destination = j.id
-- project
join jiradb.project p on j.project = p.id
)
 select * from links 

内容的提问来源于stack exchange，提问作者Cosmin Novac