SQL Engine constraints

Owned by Pablo Beltran (Unlicensed)
Last updated: Nov 18, 2020 by Pablo Beltran
3 min read

The trusted-users must understand these constraints in order to write SQL+JQL queries efficiently:

Constraint One: full scans on Issues

Try to run this query from the console:

SELECT * FROM ISSUES

 

Bump! You get an error!

Imagine that it is a production environment and there are millions of issues on your Jira instance. This simple query would compromise Jira’s reliability and performance. However, it is syntactically and grammatically correct so it should run!. But does it have any sene running so many massive-data queries from an app? No.

Full scans searching for issues are not allowed and the user’s query will be aborted.

On the other hand, the error message displays some useful information: the indexed columns on the table.

¿What is an indexed column? It is a column supported by Atlassian’s public Jira API to search for issues.

In this example, Atlassian’s API supports searching for issues by the Issue ID, the issue KEY, or an arbitrary JQL query.

Users must use provide input data for one index at least to work with 99% of the tables (there are a few exceptions)

So, how do users input data for the indexes? Via conditions in the WHERE and JOIN clauses:

 

SELECT * FROM ISSUES WHERE ID = 10000 // <- Input data

 

The SQL engine will take the ID = 10000 as input data to invoke the Jira API to find the issue and populate the table columns with the issues fields accordingly.

Constraint two: Searching for null values (really full scans)

Run this query:

SELECT * FROM ISSUES I WHERE I.ID = NULL

 

¿Issues with null ids? There are no issues with null ID values in Jira. Why you do want to search for that?

Bump! again.

What is the problem now? Well, SQL+JQL could return no records in this case instead of firing an error.

The SQL+JQL engine is unable to distinguish among both uses cases:

  • missing conditions to use the index properly (that the user did not provide by mistake)

  • null values as conditions set explicitly by the user (it has not to sense in Jira because the Jira API does not support searching for null values in many cases)

Therefore SQL+JQL engine fires an error regardless the query is syntactically correct and no empty recordset is returned as most databases would work:

Null values are not welcome

Many times, null values are passed implicitly via outer joins. Be careful about avoiding nulls by using some of the built-in functions and convert them into a not null value accepted by the underlying JIra API.

I.e:

nvl(numeric_column, -1)

The H2 nvl() function above will return -1 when numeric_column is null.

Constraint three: JQL and SQL in the same sentence

Run:

The query above is very interesting as it takes a JQL as input:

' reporter = currentUser()'

which is executed by the SQL engine and the JQL resulting issues transformed into SQL records.

Pay attention that the currentUser() function in a JQL is also supported in SQL+JQL!

Constraint five: the currentuser()

The SQL engine is only able to return results if the user’s HTTP session is detected (logged-in Jira user).

Constraint five: Read-only

SQL+JQL is read-only. UPDATE, INSERT, and DELETE commands are always aborted.

 

Next steps

You have learned about the engine constraints. Now it's time to learn the basics of working with JQL and SQL in the same query.