jql intersection

[Some Guy]

Hi,

I wanted to do a simple INTERSECT with JQL but a standard query will always perform a UNION. The keyword intersect does not work. It's quite an overhead (query and performance) to include/exclude filters and make proper intersection queries.

ATM I am doing it so

((filter = A AND filter = B AND filter = C) OR (filter = A AND filter = B) OR (filter = A AND filter = C) OR (filter = B AND filter = C))

Is there any way to make this more performant and readable?

Thank you

[Nic Brough -Adaptavist-]

JQL is not SQL, and the operators are focussed on helping non-programmers find the issues they are looking for.  It's aimed more at natural language use.  There is no "intersect", it's not needed.  Nor is there a Union for that matter, again, it's not needed.

Start by asking the question in natural language, you can more easily translate that into JQL than you can by trying to work out how to replicate union or intersect from SQL.

I'm looking for issues that are in projects A, B or C, and all have a label of Red or Amber, for example.

Also look at the reason behind the question.  Are you really looking for issues that appear in two or more filters?  How is that useful?  What does it tell your users?

(I can think of a couple of uses for it, but they all lead back to "people not understanding what they're doing or having mutiple ownership of issues", both of which are things you should be fixing, not filtering for)

[Some Guy]

One problem is we have multiple labels which are not generalized. I am searching for them with a wildcard search.

I have to find all issues, regarding three types

• Type A
• Type B
• Type C
• No Type given

There can also be an issue that's regarding two types. I have to query all combinations (A,B,C)(A,B)(A,C)(B,C)(A)(B)(C)(None) and while I don't want to have duplicates I need to exclude them from the other queries.

Example for Type A:

Project = SomeProject AND Type = SomeType AND (Version ~ "*A*" OR filter = label-wildcard-query*)

In this query I also exclude all the other Types because I would have duplicates. This is not preventable.

I am no admin nor are they "my" users, the Jira structure is big and a mess and I have to work around it and make the right queries (which would be intersect with SQL). The relational algebra workaround for intersection is not really performant.

*Label wildcard ScriptRunner workaround

issueFunction IN issueFieldMatch("Project = SomeProject AND Type = SomeType", labels, "(?i).*desiredLabel*")

[Nic Brough -Adaptavist-]

>while I don't want to have duplicates I need to exclude them from the other queries

JQL deduplicates automatically, it doesn't matter how many times you find an issue.  So there's no need to worry about it.

[Some Guy]

But I need only the issues which are present in 2 sets (intersection), and only them.

Also I have to exclude them in another filter on the same structure page, so I have no duplicates. (Set A - INTERSECT(Set A, Set B))