Created attachment 52645 [details]
On Ubuntu Oneiric 11.10 with distrib provided LibO 3.4.3
Steps to reproduce :
1) Open the sample db provided.
2) Open the form.
3) Click on the Filter button, the Filter Navigator and Toolbar appear.
4) Try entering a filter string in a field of the form, e.g. T*
5) Apply filter
Expected result :
Form should display all results filtered according to the criteria given.
Actual result :
No results are displayed at all.
Filter criteria are not applied or incorrectly applied.
There's an authentification required so I couldn't try to reproduce it.
Created attachment 52678 [details]
The first attachment I submitted was connected to a mysql db with authentication, which of course was inaccessible for everyone else, my bad, sorry.
This file is a native hsqldb ODB. Use this one for testing instead. The results are the same.
Alex in filters, the form filters is same, you most use some SQL compliant filtering condition.
The 'T*' is not that, usage of LIKE 'T*' will give a results what you look for.
What is interesting, "Auto filter" adds this LIKE '' to search string, and "Form based filters" not adds, so nothing can find.
Expected: function on button "Form based filters" should add LIKE '' around search string automatically if not present, or understand search string without it.
Reproduced in 3.3.4 and 3.5.2 on Fedora 64 bit
Changing version to 3.3.4 as most early reproducible
The "form by filter" works exactly like the "condition" lines of the query designer. That is, the default operator is "="; if you type a string like "T*" in field "fname" it means you are filtering for records that have exactly "T*" in fname, and not "anything that begins with T".
If you want to use a pattern, you need to write "LIKE 'T*'" (without the surrounding double quotes, but with the internal single quotes).
You may argue that both should automagically recognise patterns (I'm not sure I would agree), but that is at most an enhancement request, not a bug. In my opinion, this should be consistent between the query designer (condition column) and "filter by form". So if we change one, we should change both.
(In reply to comment #4)
> What is interesting, "Auto filter" adds this LIKE '' to search string,
I would consider this a bug. It should definitely use equality (or IS NULL for NULL values). Auto Filter asks for records with the same value in the specified field. This way of doing it mixes up the notion of pattern and of value: the field contains a *value*, not a pattern!
As per request in comment 4 of bug 48807, changing to enhancement request. Here's my summary of what the enhancement could look like:
- Introduce a way so that values typed in a field in "filter form by form" or in a "condition" line of query's design mode are treated as patterns to match instead of as values to match. That is, if one types "A", the generated filter (query WHERE clause) is "colName LIKE 'A'" and not "colName = 'A'".
- That way should be *optional*. Should it be a global preference (in tools/options/base)? A per-form/query checkbox somewhere (e.g. next to "execute query directly" and in the navigator, respectively) ?
- IMO the only sane way is that the decision to treat as value or as pattern is made at the time the value is typed; if the checkbox is changed, all typed values stay as they are. That is, if the option is enabled, and one types "A", then it is immediately (and visibly) changed to "LIKE 'A'".
- Should this happen for all column (field) types or e.g. only for (var)char columns? Does it make much sense to filter a numeric column by pattern?
My opinion about this enhancement request: if people want it, I'm not really opposing it, but I'm also not quite championing it.
I *strongly* require that "filter by form" and "request design mode" behave coherently, that is the change should not be done for only one of them.
Also, could be an EasyHack.
Let's just set it to new as a RFE.
I agree with you though, Lionel, about making the behaviour consistent.
> - Should this happen for all column (field) types
IMHO no. If I enter 12, it means =12 and not LIKE '12*'. So, for numbers should be "=" added. And similar for date/time. But from other hand, if date is seen so:
12 jan 2012 , then when user entered "jan", expected that will be found all dates in January. But it more simply to add warning for user for not do such experiments with dates/times.
> Also, could be an EasyHack.
Then developers should enumerate all source files, where changes should/may be done for implementing this feature
> if people want it, I'm not really opposing it ...
usually CC field used as indicator how much people (roughly) interesting in feature. Currently CC=3 (may be original bugreporter not takes in account).
In order to limit the confusion between ProposedEasyHack and EasyHack and to make queries much easier we are changing ProposedEasyHack to NeedsDevEval.
Thank you and apologies for the noise
Adding self to CC if not already on
Migrating Whiteboard tags to Keywords: (needsDevEval difficultyInteresting skillCpp)