Use of ROWNUM in Oracle select statement incorrect

Symptoms

• You're using an Oracle database
• You're running JAG 6.4.x
• Creating a new issue is slow and the jira logs have the following warning message

  2014-07-02 09:11:26,257 http-bio-8080-exec-97 WARN xxxxxx 551x7454x6 1hz4f54 xxx.xxx.10.xxx,192.xxx.1.12 /secure/QuickCreateIssue.jspa [greenhopper.customfield.lexorank.LexoRankIndexer] Could not retrieve LexoRank value for issue[id=123317]. Indexing max LexoRank value 

Some Oracle configurations don't surface the problem however. To confirm your system is affected run these two queries:

 
SELECT * FROM "AO_60DB71_LEXORANK" WHERE "FIELD_ID" = *insert rank field id here* AND ROWNUM <=2 ORDER BY "RANK" DESC; 

 
SELECT "RANK" FROM (SELECT * FROM "AO_60DB71_LEXORANK" WHERE "FIELD_ID" = *insert rank field id here* ORDER BY "RANK" DESC) WHERE ROWNUM <=2 ; 

If both return the same 2 rows, then it is good. If they differ the ranking in JAG will not work properly.

To find the Rank field ID, follow the steps below:

1. Go to Administration > Issues > Custom Fields.
2. Find the Rank custom field.
3. Click on the Cog Icon > View.
4. The URL will change similarly to the example below:

    
   http://yourjira/secure/admin/ConfigureCustomField!default.jspa?customFieldId=10005 
   

   In this example the Rank Field ID is 10005.

Root cause

LexoRank code uses a custom SQL select statement to get rank rows and limits the results using a db specific clause. In Oracle's case this is the pseudocolumn ROWNUM.

We use this column to limit the results, but in a wrong way. We want a query to be sorted by the order by clause before limiting the results. The way we use it will mean we get the first n rows and then sort those rows based on the order by clause.

http://docs.oracle.com/cd/B28359_01/server.111/b28286/pseudocolumns009.htm#SQLRF00255

Workaround

Rollback the JIRA Agile add-on to 6.3.13.1 as per the below steps.

The only version that can be rolled back to is 6.3.13.1. Any other version will cause significant problems.

1. Unlock the rank fields as per How to unlock a Locked field.
2. From JIRA Administration > Issues > Custom Fields (left side menu), rename the rank fields to something else, for example Rank (LexoRank).
3. Rename the old (Obsolete) fields to their original name 'Rank'.
4. From JIRA Administration > Manage Add-ons, uninstall JIRA Agile.
5. Stop JIRA.
6. Roll-back the JIRA Agile version in the database with the following SQL:

    
   UPDATE propertynumber SET propertyvalue = 42 WHERE id = (SELECT id FROM propertyentry WHERE property_key = 'GreenHopper.Upgrade.Latest.Upgraded.Version'); 
   UPDATE propertystring SET propertyvalue = '42' WHERE id = (SELECT id FROM propertyentry WHERE property_key = 'com.pyxis.greenhopper.jira:build'); 
   

7. Start JIRA.
8. Install the previous version of JIRA Agile 6.3.13.1. From JIRA Administration > Add-ons > Manage Add-ons (left side menu), choose 'Upload Add-on' and provide the URL or upload from your computer.
9. Reindex JIRA.