数据库版本问题导致的查询bug
目录
数据库版本问题导致的查询bug
一个比较有意思的数据库查询bug，SQL如下：
SELECT agvo1.org_id org_ids, ptd.deadline,
t.*
, (SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'annualGoalValueId', atv.id,
'goalValue', atv.goal_value,
'orderNo', atv.order_no,
'attachments', atv.attachments,
'orgs', (SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'orgId', org.id,
'orgName', org.name
)
)
FROM org
INNER JOIN annual_goal_value_org atvo2 ON org.id = atvo2.org_id
AND atvo2.org_id = agvo1.org_id -- *这里报错！
WHERE atvo2.annual_goal_value_id = atv.id)
)
)
FROM annual_goal_value atv
WHERE atv.target_id = t.id
ORDER BY order_no) AS annual_goal_values_json
, pto.name office_name
, ts.scope_name scope_name
FROM annual_goal_value_org agvo1
LEFT JOIN annual_goal_value agv ON agvo1.annual_goal_value_id = agv.id
JOIN target t ON agv.target_id = t.id AND t.deleted_at IS NULL
AND t.status = 1 AND t.submit_type = 1
LEFT JOIN user u ON t.created_by = u.id
LEFT JOIN target_scope ts ON t.target_scope_id = ts.id
LEFT JOIN pt_office pto ON t.office_id = pto.id
JOIN pt_task_deadline ptd ON ptd.org_id = agvo1.org_id AND ptd.pt_task_id = (SELECT id
FROM pt_task
WHERE FIND_IN_SET(agvo1.org_id, org_ids))
WHERE t.year = ?
ORDER BY t.created_at DESC;标注的地方报错，
> 1054 - Unknown column 'agvo1.org_id' in 'on clause'
，我觉得难道是这里不能用外查询？就是子查询借用主查询中遍历的字段值。然后测试站没有报错，很奇怪，一样的SQL，一个报错一个不报错，唯一的变量就是MySQL的版本了，测试站是8.0.28，正式站是8.0.16，应该是版本问题。但是有个问题困扰着我，就是外查询这么通用的功能，怎么可能在这么小的版本里面进行更新修复或者开发出来，而且当我把这行注释掉，正式站又可以跑的通，可是我这个SQL又不止这个地方用到了外查询这个特性。太奇怪了。后面思考了一下，可能不是外查询的问题，我把过滤条件放在where语句就能跑的通，我很兴奋，原来是外查询放在join中是跑不通的。原本想就此结束，但是打这篇记录的时候发现，在后面的地方在join中也用到了外查询过滤，没有报错。所以问题到底是啥呢。
后续改成这个了：
SELECT agvo1.org_id org_ids, ptd.deadline, t.*
, (SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'annualGoalValueId', atv.id,
'goalValue', atv.goal_value,
'orderNo', atv.order_no,
'attachments', atv.attachments
,'orgs', (SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'orgId', org.id,
'orgName', org.name
)
)
FROM org
left JOIN annual_goal_value_org atvo2 ON org.id = atvo2.org_id
WHERE atvo2.annual_goal_value_id = atv.id
AND atvo2.org_id = agvo1.org_id -- 放入到where过滤
)
)
)
FROM annual_goal_value atv join shmec.annual_goal_value_org agvo3 ON atv.id = agvo3.annual_goal_value_id
WHERE atv.target_id = t.id
AND agvo3.org_id = agvo1.org_id -- 根据业务，增加了一个过滤
ORDER BY order_no) AS annual_goal_values_json -- 某高校在某任务下被分配的年度目标值
, pto.name office_name
, ts.scope_name scope_name
FROM annual_goal_value_org agvo1
LEFT JOIN annual_goal_value agv ON agvo1.annual_goal_value_id = agv.id
JOIN target t ON agv.target_id = t.id AND t.deleted_at IS NULL
AND t.status = 1 AND t.submit_type = 1
LEFT JOIN user u ON t.created_by = u.id
LEFT JOIN target_scope ts ON t.target_scope_id = ts.id
LEFT JOIN pt_office pto ON t.office_id = pto.id
JOIN pt_task_deadline ptd ON ptd.org_id = agvo1.org_id AND ptd.pt_task_id = (SELECT id
FROM pt_task
WHERE FIND_IN_SET(agvo1.org_id, org_ids))
WHERE t.year = 2024
ORDER BY t.created_at DESC;