数据库版本问题导致的查询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;