7.0: res.partner - name_search applies limit too early

Here is a yaml test illustrating the issue. Please make sure that the patch for the bug features the fix (and that the tests are green)

The steps to reproduce work as expected when I try them (i.e. no bug), and I think it's because the behavior of the limit-restricted name_search is not deterministic. This odd behavior is not caused by the limit being applied twice but by the absence of ORDER BY clause in the pure SQL pre-filtering. The YAML test proposed on the merge proposals also never failed when I tried it, for the same reasons.

This non-deterministic behavior is fixed in the third branch[1] linked to this bug, at the cost of a performance hit due to the extra ORDER BY clause. This sort operation seems unfortunately inevitable in order to apply the limit correctly, and that requires sorting the whole list of results - possibly the complete table if the search criterion is very wide, such as " ". From my tests the name_search still returns under 0.5s for a search on " " on a database with 150k partners, which is not totally unusable.

The point about the limit being applied twice is however valid, and there is a very good performance reason why it is so, as explained in comment #3 on MP[2]. One possible fix for this issue is discussed on MP[2] too.

[1]: https://code.launchpad.net/~openerp-dev/openobject-server/7.0-bug-1203727-order-by
[2]: https://code.launchpad.net/~tsabi/openobject-server/7.0-tsabi-res_partner_name_search_limit_fix/+merge/176050

The patch mentioned in previous comment (branch [1]) has been merged in server 7.0 at revision 5067 rev-id: <email address hidden>.
It should decrease a lot the chances of seeing inconsistent results from one keystroke to the next during many2one auto-completion.
The double limit application remains, but should have a much more limited impact now.

unfortunately, the bug is still there: on runbot, with the current setup, if I type "aa" in the creation step of a Sale Order, I see AAA10 but not "Best Designers, Ayaan Agarwal", which suddenly pops up if I type an additional "n".

in addition to my previous comment: lp:~openerp-dev/openobject-server/7.0-bug-1203727-order-by does not fix the issue

In the real case reported by my customer, the database has different subsidiary companies in the customers sharing common prefixes in their names. The impression that the users have is that the company they are looking for when typing the first letters of the name and getting a list of suggestions with only 1 element which is not the one they are looking for is that the partner does not exist in database. I can certainly tell them to type more letters but this is at best an ugly workaround for a real useability issue (what if the user does not remember enough of the name to properly type in more letters?)

On 09/17/2013 04:44 PM, Alexandre Fayolle - camptocamp wrote:
> unfortunately, the bug is still there: on runbot, with the current
> setup, if I type "aa" in the creation step of a Sale Order, I see AAA10
> but not "Best Designers, Ayaan Agarwal", which suddenly pops up if I
> type an additional "n".

Yes, that's why I explicitly mentioned that the patch does not solve the bug.
If anything, it actually makes the bug more consistently reproducible, while it
used to be semi-random.

I've highlighted some possible solutions to the fundamental problem in comment
#3 of MP
https://code.launchpad.net/~tsabi/openobject-server/7.0-tsabi-res_partner_name_search_limit_fix/+merge/176050

However the suggested patch is ugly and complex, and the person writing it
should be sure to understand the performance implication of any alteration of
the current code.

As a possible workaround, I've pushed an experimental fix in branch
lp:~openerp-dev/openobject-addons/7.0-bug-1203727-hack. It might sufficiently
decrease the chance of triggering the bug, without incurring a noticeable
performance hit on most databases, and without the complexity of the full
patch. It is still possible to trigger the bug with pathological cases, so
testing it on real customer databases should tell us if it is an acceptable
workaround for v7.0.
v8 will feature a complete fix, but this requires a change in the ORM system
that is not suitable for 7.0 stable.

Sorry, the branch mentioned in previous commit should read:
    lp:~openerp-dev/openobject-server/7.0-bug-1203727-hack

I figured some more problems with the current implementation.

- In the SQL query we should check for the active flag as Model.search() does.
- To let the web client display the 'Search more...' entry we should append empty entries to fill the result set up to the limit if there are more name-matching partners.
- To reduce the number of calls to name_search() we should use the default value (300msec) for many2one autocomplete widgets. (OpenERP is not Google).
- To reduce the number of empty entries in the result set we could do some more work in a loop. This is expensive for vague search expressions but the user gets what he or she expects.

The server part:
http://bazaar.launchpad.net/~redneck/+junk/7.0-server-res_partner_name_search_bug1203727-redneck/revision/
The web client part:
http://bazaar.launchpad.net/~redneck/+junk/7.0-web-many2one_autocomlete_default_delay-redneck/revision/

The server part still contains code for profiling. Just set profile in name_search() to True.

Hello guys,

I have created a patch applying Olivier's recommendations (execute one global sql query including the limit and the orm underlying search).
The name_search call will now return the correct number of records (and hopefully be faster).

Please note that this is not needed for trunk as display_name is stored.

revno: 5216 [merge]
revision-id: <email address hidden>

Hello Martin,

Thanks, this is great news.