[FIX] expression: 'select from not null' as subquery for x2many

Before this commit, the query to fetch records with/without relations would be
done in a separate query which results would be then passed to the main query.

This commit adds this query as a subquery of the main query to do it in the
same SQL query instead of separately.

task-2211013

closes odoo/odoo#47729

Signed-off-by: Jérémy Kersten (jke) <jke@openerp.com>
Signed-off-by: Romain Derie <rdeodoo@users.noreply.github.com>

1 files changed, 11 insertions, 14 deletions

diff --git a/odoo/osv/expression.py b/odoo/osv/expression.py
index 070396efb4d..ce1410b9225 100644
--- a/odoo/osv/expression.py
+++ b/odoo/osv/expression.py
@@ -453,9 +453,6 @@ def select_from_where(cr, select_field, from_table, where_field, where_ids, wher
                 res.extend([r[0] for r in cr.fetchall()])
     return res
 
-def select_distinct_from_where_not_null(cr, select_field, from_table):
-    cr.execute('SELECT distinct("%s") FROM "%s" where "%s" is not null' % (select_field, from_table, select_field))
-    return [r[0] for r in cr.fetchall()]
 
 def get_unaccent_wrapper(cr):
     if odoo.registry(cr.dbname).has_unaccent:
@@ -982,19 +979,21 @@ class expression(object):
                     push(create_substitution_leaf(leaf, ('id', op1, ids1), model))
 
                 else:
-                    # determine ids1 = records with lines
                     if comodel._fields[field.inverse_name].store and not (inverse_is_int and domain):
-                        ids1 = select_distinct_from_where_not_null(cr, field.inverse_name, comodel._table)
+                        # rewrite condition to match records with/without lines
+                        op1 = 'inselect' if operator in NEGATIVE_TERM_OPERATORS else 'not inselect'
+                        subquery = 'SELECT "%s" FROM "%s" where "%s" is not null' % (field.inverse_name, comodel._table, field.inverse_name)
+                        push(create_substitution_leaf(leaf, ('id', op1, (subquery, [])), internal=True))
                     else:
                         comodel_domain = [(field.inverse_name, '!=', False)]
                         if inverse_is_int and domain:
                             comodel_domain += domain
                         recs = comodel.search(comodel_domain).sudo().with_context(prefetch_fields=False)
+                        # determine ids1 = records with lines
                         ids1 = unwrap_inverse(recs.mapped(field.inverse_name))
-
-                    # rewrite condition to match records with/without lines
-                    op1 = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
-                    push(create_substitution_leaf(leaf, ('id', op1, ids1), model))
+                        # rewrite condition to match records with/without lines
+                        op1 = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
+                        push(create_substitution_leaf(leaf, ('id', op1, ids1), model))
 
             elif field.type == 'many2many':
                 rel_table, rel_id1, rel_id2 = field.relation, field.column1, field.column2
@@ -1031,12 +1030,10 @@ class expression(object):
                     push(create_substitution_leaf(leaf, ('id', subop, (subquery, [ids2])), internal=True))
 
                 else:
-                    # determine ids1 = records with relations
-                    ids1 = select_distinct_from_where_not_null(cr, rel_id1, rel_table)
-
                     # rewrite condition to match records with/without relations
-                    op1 = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
-                    push(create_substitution_leaf(leaf, ('id', op1, ids1), model))
+                    op1 = 'inselect' if operator in NEGATIVE_TERM_OPERATORS else 'not inselect'
+                    subquery = 'SELECT "%s" FROM "%s" where "%s" is not null' % (rel_id1, rel_table, rel_id1)
+                    push(create_substitution_leaf(leaf, ('id', op1, (subquery, [])), internal=True))
 
             elif field.type == 'many2one':
                 if operator in HIERARCHY_FUNCS: