Opened 13 years ago

Closed 6 years ago

#1205 closed defect (fixed)

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

Reported by: Jari Häkkinen Owned by: everyone
Priority: minor Milestone: BASE 3.5
Component: web Version: 2.8.4
Keywords: Cc:

Description (last modified by 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.

Change History (16)

comment:1 Changed 13 years ago by Jari Häkkinen

Description: modified (diff)

comment:2 Changed 13 years ago by Nicklas Nordborg

Resolution: invalid
Status: newclosed

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.

comment:3 Changed 13 years ago by Nicklas Nordborg

comment:4 Changed 13 years ago by Jari Häkkinen

Resolution: invalid
Status: closedreopened

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.

comment:5 Changed 13 years ago by 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 '='.

comment:7 in reply to:  5 Changed 13 years ago by 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.

comment:8 Changed 13 years ago by Jari Häkkinen

Summary: Serioius problem filtering on annotations where one whiches to exlude a matchSerious problem filtering on annotations where one whiches to exlude a match

comment:9 Changed 12 years ago by Jari Häkkinen

Milestone: BASE 2.x+
Summary: Serious problem filtering on annotations where one whiches to exlude a matchSerious problem filtering on annotations where one whishes to exlude a match

comment:10 Changed 8 years ago by Jari Häkkinen

Resolution: invalid
Status: reopenedclosed

comment:11 Changed 8 years ago by Nicklas Nordborg

Milestone: BASE Future Release

comment:12 Changed 6 years ago by Nicklas Nordborg

Milestone: BASE 3.5
Priority: majorminor

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).

comment:13 Changed 6 years ago by Nicklas Nordborg

Resolution: invalid
Status: closedreopened

comment:14 Changed 6 years ago by Nicklas Nordborg

Resolution: duplicate
Status: reopenedclosed

comment:15 Changed 6 years ago by Nicklas Nordborg

Resolution: duplicate
Status: closedreopened

comment:16 Changed 6 years ago by Nicklas Nordborg

Resolution: fixed
Status: reopenedclosed
Note: See TracTickets for help on using tickets.