[FIX] core: searching through o2m with auto_join

Replaces the auto_join scheme which is pretty fast but doesn't quite
work (it's *completely* broken for read_group) by a subquery which is
slower but has the same semantics as the ORM version.

Ideally we'd use `EXISTS (select 1 from ...)` but that has no support
inside the SQL compiler right now, so use `id in (select inverse_field
from ...)`.

Here are some measures of a `read_group` on a simple object with lines
filtered on line values, similar to

    read_group([('order_line.product_uom', '=', xxx)],
               fields=['total_amount'], groupby=['user_id'])

    +-------+-------+-------+--------+
    |       |   ORM |  join |subquery|
    +-------+-------+-------+--------+
    | large |   160 |    10 |     20 |
    +-------+-------+-------+--------+
    |  many | 36000 |    50 |    180 |
    +-------+-------+-------+--------+

* values of data fields (filtering, grouping and aggregating) randomly
picked between 10 different values
* "large" is 1000 objects with 1000~15000 lines each
* "many" is 1000000 objects with 1~40 lines each

closes odoo/odoo#48494

Signed-off-by: Raphael Collet (rco) <rco@openerp.com>

1 files changed, 7 insertions, 10 deletions

diff --git a/odoo/osv/expression.py b/odoo/osv/expression.py
index eba82d9950c..732b49523ae 100644
--- a/odoo/osv/expression.py
+++ b/odoo/osv/expression.py
@@ -744,17 +744,14 @@ class expression(object):
                 push((path[1], operator, right), comodel, coalias)
 
             elif len(path) > 1 and field.store and field.type == 'one2many' and field.auto_join:
-                # res_partner.id = res_partner__bank_ids.partner_id
-                coalias, _ = self.query.add_join(
-                    (alias, comodel._table, 'id', field.inverse_name, path[0]),
-                    implicit=False, outer=True,
+                # use a subquery bypassing access rules and business logic
+                domain = [(path[1], operator, right)] + field.get_domain_list(model)
+                query = comodel.with_context(**field.context)._where_calc(domain)
+                subfrom, subwhere, subparams = query.get_sql()
+                subquery = 'SELECT "{}"."{}" FROM {} WHERE {}'.format(
+                    comodel._table, field.inverse_name, subfrom, subwhere,
                 )
-                domain = field.get_domain_list(model)
-                if domain:
-                    push(AND_OPERATOR, comodel, coalias)
-                    for elem in normalize_domain(domain):
-                        push(elem, comodel, coalias)
-                push((path[1], operator, right), comodel, coalias)
+                push(('id', 'inselect', (subquery, subparams)), model, alias, internal=True)
 
             elif len(path) > 1 and field.store and field.auto_join:
                 raise NotImplementedError('auto_join attribute not supported on field %s' % field)