Search where one field != another field

[Lukas Rabe]

Hi,

can i create a Filter where for example created != updated?

How can i use the Value of a field for Filter?

Greetings Lukas Rabe

[Wojciech Seliga]

I am afraid it's not generally possible.

JIRA query language (JQL) uses underneath Apache Lucene as its indexing engine which does not allow such things easliy or with reasonable performance. Your search terms (aka "query values") basically need to be resolved (evaluated) before the search of the indexed data takes place, whereas in the situation you desire, search terms would be dynamically changing depending on the data searched ("created" and "updated" have different value for each issue).

I could create a plugin which could do such operations directly on JIRA DB (at least for system fields which would allow you to use SQL which makes such stuff easy).

You could also implement a custom field (here calculated custom field) which would store in index e.g. true or false depending on whether for given issue created date == updated date. Then you could easily use such custom field in a JQL query. The drawback is that you would have to create custom fields (and associated searcher logic) for each type of such query you would like to use - if you have many such requirements, that will drive you nuts and your index will be also very big.

[JamieA]

Do you think jira uses Lucene in something of a way that it perhaps wasn't designed for? It's a text-searching engine, yet often you want to do filters that are more like sql searches.

[Wojciech Seliga]

Honestly, I am not an expert in this field. I think however that Lucene was a great thing which happened to JIRA years ago. Thanks to it, JIRA could support custom fields (leading to quite nasty DB scheme which makes SQL searches quite painful) and at the same time have very fast search. I really don't know how would JIRA perform when it was using SQL directly.

And plain old SQL is not sexy anymore :) NoSQL, things like Hadoop, etc. - are now trendy.

[JamieA]

Yeah... I'm not saying really it should have been done using sql at the backend, although that would make things like clustering and grouping and what the OP wants much easier, just that Lucene seems to be being pushed beyond what it was intended for. Just an idle comment really...