Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-96022

Sorting a Jira Issue Macro with a Filter/JQL Query containing an ORDER BY Clause can fail

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Low Low
    • None
    • 8.5.7, 8.5.9, 8.5.11
    • Macros - Jira Macros
    • None

      Problem

      Sorting a Jira Issue Macro that contains an ORDER BY Clause in the JQL query performed to retrieve the Jira data can fail.

      Environment

      Reproduced in Confluence Data Center LTS 8.5

      Steps to Reproduce

      1. Create a Confluence 8.5 instance
      2. Link Confluence to a Jira instance via application link
      3. Populate Jira project (example MYS) so that JQL query below returns some results

        project = MYS AND "Epic Name" ~"Test Epic Name" order by "Epic Name"

      4. Create a new Confluence Page with a new Jira Issue Macro using the JQL above
      5. Publish the page and then attempt to Sort the Jira Issues results by Assignee

      Expected Results

      • The Jira results should be preserved by the Sort Operation

      Actual Results

      • The Jira results break in the macro with message Jira project doesn't exist or you don't have permission to view it.

      Workaround

      • Use single quote in the ORDER BY Clause in JQL e.g.

        project = MYS AND "Epic Name" ~"Test Epic Name" order by 'Epic Name'

      • Or refine the Query for instance in this specific scenario the below will display the same data with no need of using the Order By (because there is only one epic name to search)

        project = MYS AND "Epic Name"="Test Epic Name"

      Notes

      Recording attached to illustrate the issue reproduced in a Local Lab instance

      • Enabling HTTP debug logs in Confluence highlight the 400 getting returned during the sort operation
        atlassian-confluence.log
        [...]
2024-06-24 11:00:14,369 DEBUG [http-nio-6859-exec-1 url: /rest/jiraanywhere/1.0/jira/renderTable; user: admin] [org.apache.http.wire] wire http-outgoing-11922 >> \"GET /j8226/sr/jira.issueviews:searchrequest-xml/temp/SearchRequest.xml?tempMax=20&returnMax=true&jqlQuery=project+%3D+MYS+AND+%22Epic+Name%22+%7E%22Test+Epic+Name%22+order+by+%22assignee%22+ASC%2CEpic+Name&field=issuekey&field=summary&field=issuetype&field=created&field=updated&field=duedate&field=assignee&field=reporter&field=priority&field=status&field=resolution&field=link&field=type&xoauth_requestor_id=admin HTTP/1.1[\\r][\\n]\"
[...]
2024-06-24 11:00:14,388 DEBUG [http-nio-6859-exec-1 url: /rest/jiraanywhere/1.0/jira/renderTable; user: admin] [org.apache.http.wire] wire http-outgoing-11922 << \"<!doctype html><html lang=\"en\"><head><title>HTTP Status 400 [0xe2][0x80][0x93] Bad Request</title><style type=\"text/css\">body {font-family:Tahoma,Arial,sans-serif;} h1, h2, h3, b {color:white;background-color:#525D76;} h1 {font-size:22px;} h2 {font-size:16px;} h3 {font-size:14px;} p {font-size:12px;} a {color:black;} .line {height:1px;background-color:#525D76;border:none;}</style></head><body><h1>HTTP Status 400 [0xe2][0x80][0x93] Bad Request</h1><hr class=\"line\" /><p><b>Type</b> Status Report</p><p><b>Message</b> Error in the JQL Query: Expecting either &#39;ASC&#39; or &#39;DESC&#39; but got &#39;Name&#39;. (line 1, character 78)</p><p><b>Description</b> The server cannot or will not process the request due to something that is perceived to be a client error (e.g., malformed request syntax, invalid request message framing, or deceptive request routing).</p><hr class=\"line\" /><h3>Apache Tomcat/8.5.81</h3></body></html>\"
2024-06-24 11:00:14,388 DEBUG [http-nio-6859-exec-1 url: /rest/jiraanywhere/1.0/jira/renderTable; user: admin] [org.apache.http.headers] onResponseReceived http-outgoing-11922 << HTTP/1.1 400 
2024-06-24 11:00:14,388 DEBUG [http-nio-6859-exec-1 url: /rest/jiraanywhere/1.0/jira/renderTable; user: admin] [org.apache.http.headers] onResponseReceived http-outgoing-11922 << X-AREQUESTID: 660x427x1

[...]
        • We can see the JQL performed after the Sort and if we decode it it gives us project = MYS AND "Epic Name" ~"Test Epic Name" order by "assignee" ASC,Epic Name
        • We can see the details of the Error getting picked as well and Jira responds as HTTP 400 with the following error:

          Error in the JQL Query: Expecting either 'ASC' or 'DESC' but got 'Name'. (line 1, character 78)

      • Now if We use Single Quote in the Order By that is JQL project = MYS AND "Epic Name" ~"Test Epic Name" order by 'Epic Name' and attempt to Sort by Assignee, it's working well and the sort operation is working
          • Checking the backend logs with HTTP Debug enabled in Confluence we see how the JQL is different with the GET HTTP Request executed below
            atlassian-confluence.log
            [...]
2024-06-24 11:21:00,244 DEBUG [http-nio-6859-exec-2 url: /rest/jiraanywhere/1.0/jira/renderTable; user: admin] [org.apache.http.headers] onRequestSubmitted http-outgoing-11999 >> GET /j8226/sr/jira.issueviews:searchrequest-xml/temp/SearchRequest.xml?tempMax=20&returnMax=true&jqlQuery=project+%3D+MYS+AND+%22Epic+Name%22+%7E%22Test+Epic+Name%22+order+by+%22assignee%22+ASC%2C%27Epic+Name%27&field=issuekey&field=summary&field=issuetype&field=created&field=updated&field=duedate&field=assignee&field=reporter&field=priority&field=status&field=resolution&field=link&field=type&xoauth_requestor_id=admin HTTP/1.1
[...]
        • Decoded JQL is project = MYS AND "Epic Name" ~"Test Epic Name" order by "assignee" ASC,'Epic Name'

        1. Sort Operation can break Jira Issue macro results when using Double Quoted ORDER BY JQL.mov
          9.98 MB

            Unassigned Unassigned
            2e857505f334 Pascal Oberle
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated: