Built-in dynamic tables, column constraints and full scans

Please read this page above to avoid syntax errors and mark some specific features of our SQL Cloud dialect.

How many issues can be potentially stored on a Jira instance? Millions, of course!

Imagine how much information the engine needs to process in order to run this query.

select * from ISSUE

It will take a lot of time to execute all issues available, the engine will process numerous “calls” to Jira and as a result, we gonna face “informational congestion”. So the app introduces some column constraints to avoid them:

A Full scan error is raised by the engine, displaying the required columns to run: JQL and ID in this case.

Many built-in tables (AKA as dynamic tables) require some values to work. This might seem a bit annoying for users not used to this behavior at the beginning.

Tip: to discover the column constraints of a particular table, simple explore the table values as it shown in examples below (SQL Query Console, SQL Query Builder)

show columns from ISSUE

A full scan of numerous built-in tables is not allowed. Full scan means that the SQL engine requires to read all the data from the table to resolve the user’s query. This is not optimal and it may cause productivity and other reliability issues for the Jira server or user’s browser. As mentioned, the engine introduces the concept of column constraints to force users to set a limited scope for the table data, and the query will be aborted with a full scan error raised by the engine if a full scan is detected on a constrained dynamic table.

How to run a query against a constrained table? Simple: provide values for the required constrained columns. This can be done via WHERE clause or JOIN conditions as shown in the example below:

select * from ISSUE where JQL = ' project = SSP '

The results are displayed on a flat table.