GreenHopper shouldn't allow sub task to be listed in the scrum board if the parent issue is excluded

In GreenHopper, it is possible for us to configure the a scrum board filter to include other projects, or only include certain issues of a project by attributes as needed. Because of this, there's a possibility that users may accidentally include a sub task to a Sprint while actually the parent task is excluded from the Sprint. When this scenario happen, the following error will be produced if we try to generate a Sprint Report:

A simple steps to reproduce the problem:

• Create a new project with 1 issue having 1 subtask
• Create a scrum board for the project
• Go to the Plan board and include the parent issue in a Sprint (subtask won't be displayed in the plan board). Start the Sprint
• Modify the board filter (Configure > Filter > View in Issue Navigator), exclude the parent task from the filter. Save.
• Back to the Scrum, try to generate Sprint Report and the problem is reproducible.

It'll be great if we have the feature to exclude a sub task if the parent issue is not included, or add a more meaningful message that may help customer to understand the root cause of the problem.

Workaround

Remove the subtask without the parent from the JIRA Agile (formerly GreenHopper) board. Identifying the problematic issues is not a trivial process, and can be done with either of the following workarounds:

Using JQL

1. Identify the board which is causing the problems and copy the filter from it in Board > Configure > Filter > Filter Query. For example:

   project = "Sample Scrum Project" ORDER BY Rank ASC
   

2. Add AND type not in subTaskIssueTypes() to it (see the below example) and then search for that filter in the Issue Navigator, so as to return all the parent issues.

   project = "Sample Scrum Project" AND type not in subTaskIssueTypes() ORDER BY Rank ASC
   

3. Those issuekeys will need be collected and collated for comparison against a filter again. This can either by done by exporting the results to CSV and extracting them from there, or by using the Javascript in step 4 & 5.
4. Using Chrome, open the Chrome Developer Tools, select the console and add the following Javascript to it. Make sure to press enter after adding the Javascript.

   AJS.$(document).ajaxSuccess(function(e, obj) { try{ console.log(JSON.parse(obj.responseText).issueTable.issueKeys.join(',')) } catch(e) {} })
   

   You should see the below appear after entering to confirm the JS went through:
   

5. Reload to filter and the issue keys will be put together in the console as below:
   
6. Take those issue keys and add them into the JQL query from step 2, put them inside the and parent not in () section and change the not in subTaskIssueTypes() to in subTaskIssueTypes(). See the below example:

   project = "Sample Scrum Project" AND type in subTaskIssueTypes() and parent not in (SSP-1,SSP-2,SSP-3,SSP-4,SSP-5,SSP-6,SSP-8,SSP-9,SSP-10,SSP-13,SSP-14,SSP-15,SSP-16,SSP-17,SSP-18,SSP-19,SSP-20,SSP-21,SSP-22,SSP-23) ORDER BY Rank ASC
   

   This will search for all parent issues that do not exist within the board.

7. Remove those subtasks from the board.

Using SQL

In order to find out all the subtasks without their parent, you may use the following queries:

1. Run the following query in the database and get the id of the subtask link:

   SELECT * 
   FROM   issuelinktype 
   WHERE  linkname LIKE '%subtask%'; 
   

2. After this run the following query in the database, substituting the value for 'Link Type Id' by the id derived from the above SQL:
   Before JIRA 6.1:

   SELECT ji2.pkey AS "parent issue key", 
          ji1.pkey AS "child issue key" 
   FROM   issuelink il 
          JOIN jiraissue ji1 
            ON ji1.id = il.destination 
          JOIN jiraissue ji2 
            ON ji2.id = il.source 
   WHERE  il.linktype = <Link Type Id>;
   

   After JIRA 6.1:

   SELECT p.pkey AS "parent project key",
       ji2.issuenum AS "parent issue num",
       q.pkey AS "child project key", 
          ji1.issuenum AS "child issue num" 
   FROM   issuelink il 
          JOIN jiraissue ji1 
            ON ji1.id = il.destination 
          JOIN jiraissue ji2 
            ON ji2.id = il.source 
          JOIN project p
           ON ji1.project = p.id
          JOIN project q
           ON ji2.project = q.id 
   WHERE  il.linktype = <Link Type Id>;
   

This query will list all the sub task type of issues with their parents. You can now compare the subtasks from your filter query and verify if their parents are present on the board by looking up their parent id from the query results.