Incorrect JQL return on fix/affected version due to deletion of version leaving link to 'Fix Versions' or 'Affected Versions in the database

Summary

When the customer creates a version, either in JIRA or JIRA Agile board, after deleting this version, all issues using it as fix or affected version will still have a reference to it in the database.

This results in some incorrect JQL searches for Fix Versions or Affected Versions. For example, if an issue ABC-123 had only the 'version1' as its fix version and this one is deleted (doesn't have a fix version anymore), this search won't return the issue when it should:

fixVersion is empty

Since that was the only fix version the issue had, now it should be null or empty, but the search fails as there's still a reference to the old version in the database.

This is also happening to affected versions.

Steps to Reproduce

1. Have a project in JIRA
2. Create a version (either in the board in case of Agile or not) and an issue
3. Add the version as the issue's fixVersion (or affectedVersion)
4. Delete the version without removing it as the fixVersion (or affectedVersion) in the issue itself:
   • Go to the versions page in the project
   • Click '...' >> 'Delete' >> 'Delete'

Expected Results

1. This JQL search should return the issue, as it was the only fixVersion:

   fixVersion is empty
   

Actual Results

Though the fixVersion is no longer visible in the issue, the following JQL won't return it even if it was the only fixVersion and it should:

fixVersion is empty

When deleting the version, even though the warning says the links will be removed, they're not completely.

Notes

Though the fixVersion/affectedVersion is not shown, there's still an entry in the 'nodeassociation' table to the non-existing version. They can all be found with these searches:
For FixVersion:

select distinct(n.sink_node_id) as fix_version_id from project p, jiraissue i, nodeassociation n where n.source_node_id = i.id and p.id = i.project and n.sink_node_id not in (select id from projectversion) and n.association_type = 'IssueFixVersion' order by fix_version_id asc;

For AffectedVersion:

select distinct(n.sink_node_id) as affected_version_id from project p, jiraissue i, nodeassociation n where n.source_node_id = i.id and p.id = i.project and n.sink_node_id not in (select id from projectversion) and n.association_type = 'IssueVersion' order by affected_version_id asc;

This was replicated in JIRA Cloud instances, but there are reports in JIRA Server instances as well.

Workaround

JIRA Server instances

1. Search for the orphan links as per above and copy their IDs
2. Remove it directly from the database using the IDs found in the search above
   • Fix Versions:

     delete from nodeassociation where association_type = 'IssueFixVersion' and sink_node_id in (<list of IDs>);
     

   • Affected Versions:

     delete from nodeassociation where association_type = 'IssueVersion' and sink_node_id in (<list of IDs>);
     

3. Reindex JIRA to be sure the changes are applied
4. To avoid this in the future, bulk edit issues to remove the fix/affected versions before deleting versions.

Cloud instances

1. Open a support request at Atlassian Support to have this workaround be applied