Serious problem filtering on annotations where one whishes to exlude a match

[Jari Häkkinen]

The problem is that when I use a filter like

<>string

then all items with annotation type == string are removed but also items without the annotation type. This is at best confusing, but removes items from the listing that should not be removed. We understand the difficulty to fix this with the current annotation filter system. However, as it is now, non-experienced users will have problems with <> filters.

A query that lists all items that has no value for a annotation type would be useful, or maybe forcing a dummy value to items that has no value for the annotation type filtered.

We, I and my confused users, cannot understand the behaviour of = and <> without strings to match, i.e. filter

=

excludes all items with no annotation value. Whereas filter

<>

keeps only items with no annotation value.

[Nicklas Nordborg]

This has nothing to with if you are searching on annotations or not. It is three-valued logic where NULL values never match anything. The same behavior can be seen when filtering on any any column that may have null values. For example:

1. Create an item that has 'foo' as it's description and one item that has an empty (=null) description.
2. Filter on '=foo', the first item will be found.
3. Filter on '<>foo', no item will be found.
4. Filter on '=', the second item will be found.
5. Filter on '<>', the first item will be found.

Searching on a specific value like 2 and 3 will never match any null values. 4 and 5 are special cases that only search matches null (or not null) values.

The same type of filter should work in the same way on annotations as on regular properties, but at the moment it seems like 4 and 5 have been switched, but that is another bug.

[Nicklas Nordborg]

#1206.

[Jari Häkkinen]

I still claim that there is a problem with the filtering. If one wants to find all items <>foo then null and non-values should be displayed since these are clearly not foo. This is what is natural to expect. 3 should include all items except foo. Which filter do you suggest to use to find all items not foo where also null values are included?

I think this ticket should not be closed but at least kept as a at least a BASE 3.0 item or BASE 2.x to remind us about his issue. The documentation must stress the current consequences of use case 3.

As long as this is not resolved to work as I describe then the user must annotate all items with a default value representing 'null'. The consequence is then that at least for annotations the core should require a value for each item. This is to resolve the for the user unexpected result of a filter <>foo. The workaround now is that users realize the filtering problem and adjusts their annotation to include default null representation.

[Nicklas Nordborg]

Keep it open if you like, but I think you will have a hard time to persuade database vendors to include null values in regular filters. Users will have to learn to live with this. If they need to find all <>foo that also includes null values they will have to do two queries: '<>foo' and '='.

[Nicklas Nordborg]

​http://en.wikipedia.org/wiki/Null_(SQL)#Controversy

[Jari Häkkinen]

Replying to nicklas:

Keep it open if you like, but I think you will have a hard time to persuade database vendors to include null values in regular filters. Users will have to learn to live with this. If they need to find all <>foo that also includes null values they will have to do two queries: '<>foo' and '='.

I have no whish to keep the ticket open, I'd like to see the issue resolved. I understand the SQL problem. However, the problem is that I think the two queries should be made by the programmers. When the users makes the query <>foo he is not thinking of SQL he expects to get all items not foo. Basically what I would like to see is a special treatment of <> filters.

If nothing else there is a need to document this issue. Remember the average user cannot even spell SQL.

[Nicklas Nordborg]

Fixed as part of #1932 and #1934. Turned out that for annotations it was really not related to three-valued logic. For regular columns, filters that use <> have been modified to explicitly add OR <property> IS NULL to the query (unless null is included in the filter).