[FIX] base: fix name_search on res_partner when searching on linked users

Currently name_search on res_partner holds code to be done by a customized SQL
query allowing to speedup the search [1]. However when dealing with given args
there may be a crash if there is a domain based on user_ids fields.

Indeed this field is defined as auto_join [2]. It means the result of get_sql
returns where parameters based on joined tables. Those tables are not available
in the from clause in the original query.

This commit fixes that issue by correctly taking the from_clause from get_sql.
That way joined tables are available. Small update of the query is necessary
as fields are now res_partner.{id/email/vat/reference} as there may be several
tables linked in the query.

A test has been added to avoid regression.

[1] See https://github.com/odoo/odoo/commit/05ec12692f99b69924765fd0ce384632547f03f9 for a recent modification
    and implementation of this query, even if it exists since a looooong time
[2] See https://github.com/odoo/odoo/commit/db8203c27a21acdbcad2cf1c394b6fea3cf13688 for the auto_join addition

closes odoo/odoo#29827

3 files changed, 28 insertions, 6 deletions

diff --git a/odoo/addons/base/res/res_partner.py b/odoo/addons/base/res/res_partner.py
index a222cbc8526..bfca29deff2 100644
--- a/odoo/addons/base/res/res_partner.py
+++ b/odoo/addons/base/res/res_partner.py
@@ -647,6 +647,7 @@ class Partner(models.Model, FormatAddress):
             where_query = self._where_calc(args)
             self._apply_ir_rules(where_query, 'read')
             from_clause, where_clause, where_clause_params = where_query.get_sql()
+            from_str = from_clause if from_clause else 'res_partner'
             where_str = where_clause and (" WHERE %s AND " % where_clause) or ' WHERE '
 
             # search on the name of the contacts and of its company
@@ -658,19 +659,20 @@ class Partner(models.Model, FormatAddress):
 
             unaccent = get_unaccent_wrapper(self.env.cr)
 
-            query = """SELECT id
-                         FROM res_partner
+            query = """SELECT res_partner.id
+                         FROM {from_str}
                       {where} ({email} {operator} {percent}
                            OR {display_name} {operator} {percent}
                            OR {reference} {operator} {percent})
                            -- don't panic, trust postgres bitmap
                      ORDER BY {display_name} {operator} {percent} desc,
                               {display_name}
-                    """.format(where=where_str,
+                    """.format(from_str=from_str,
+                               where=where_str,
                                operator=operator,
-                               email=unaccent('email'),
-                               display_name=unaccent('display_name'),
-                               reference=unaccent('ref'),
+                               email=unaccent('res_partner.email'),
+                               display_name=unaccent('res_partner.display_name'),
+                               reference=unaccent('res_partner.ref'),
                                percent=unaccent('%s'))
 
             where_clause_params += [search_name]*4
diff --git a/odoo/addons/base/tests/__init__.py b/odoo/addons/base/tests/__init__.py
index e40c2d042c3..0ca5c962f19 100644
--- a/odoo/addons/base/tests/__init__.py
+++ b/odoo/addons/base/tests/__init__.py
@@ -30,5 +30,6 @@ import test_user_has_group
 import test_view_validation
 import test_views
 import test_xmlrpc
+import test_res_partner
 import test_res_partner_bank
 import test_non_regression
diff --git a/odoo/addons/base/tests/test_res_partner.py b/odoo/addons/base/tests/test_res_partner.py
new file mode 100644
index 00000000000..2a6722553af
--- /dev/null
+++ b/odoo/addons/base/tests/test_res_partner.py
@@ -0,0 +1,19 @@
+# -*- coding: utf-8 -*-
+# Part of Odoo. See LICENSE file for full copyright and licensing details.
+
+from odoo.tests.common import TransactionCase
+
+
+class TestPartner(TransactionCase):
+
+    def test_name_search(self):
+        """ Check name_search on partner, especially with domain based on auto_join
+        user_ids field. Check specific SQL of name_search correctly handle joined tables. """
+        test_partner = self.env['res.partner'].create({'name': 'Vlad the Impaler'})
+        test_user = self.env['res.users'].create({'name': 'Vlad the Impaler', 'login': 'vlad', 'email': 'vlad.the.impaler@example.com'})
+
+        ns_res = self.env['res.partner'].name_search('Vlad', operator='ilike')
+        self.assertEqual(set(i[0] for i in ns_res), set((test_partner | test_user.partner_id).ids))
+
+        ns_res = self.env['res.partner'].name_search('Vlad', args=[('user_ids.email', 'ilike', 'vlad')])
+        self.assertEqual(set(i[0] for i in ns_res), set(test_user.partner_id.ids))