[FIX] core: != / not ilike failing to match null records

As odoo makes little difference, one would expect `(field, !=, 'foo')`
or `(field, 'not ilike', 'foo')` to have the same behaviour when field is
empty, and provide the opposite result as the "positive" operators.

However because NULL <OP> <value> returns NULL which is generally
assimilated to false, the sections above would fail to match records
whose field is null (they would match records whose field is *empty*
though).

Fix this by wrapping the operation in a coalesce falling back
to (hopefully) the correct value if the result is null.

See also: odoo/odoo#22879

closes odoo/odoo#33959

Signed-off-by: Olivier Dony (odo) <odo@openerp.com>

2 files changed, 28 insertions, 4 deletions

diff --git a/odoo/addons/base/tests/test_expression.py b/odoo/addons/base/tests/test_expression.py
index adae9a6fe01..c87701da048 100644
--- a/odoo/addons/base/tests/test_expression.py
+++ b/odoo/addons/base/tests/test_expression.py
@@ -1015,3 +1015,27 @@ class TestAutoJoin(TransactionCase):
         # Test produced queries
         self.assertEqual(len(self.query_list), 1,
             "_auto_join on: ('child_ids.state_id.country_id.code', 'like', '..') number of queries incorrect")
+
+    def test_nullfields(self):
+        obj1 = self.env['res.bank'].create({'name': 'c0'})
+        obj2 = self.env['res.bank'].create({'name': 'c1', 'city': 'Ljósálfaheimr'})
+        obj3 = self.env['res.bank'].create({'name': 'c2', 'city': 'York'})
+        obj4 = self.env['res.bank'].create({'name': 'c3', 'city': 'Springfield'})
+
+        self.assertEqual(
+            self.env['res.bank'].search([
+                ('id', 'in', (obj1 | obj2 | obj3 | obj4).ids),
+                ('city', '!=', 'York'),
+            ]),
+            (obj1 | obj2 | obj4),
+            "Should have returned all banks whose city is not York"
+        )
+
+        self.assertEqual(
+            self.env['res.bank'].search([
+                ('id', 'in', (obj1 | obj2 | obj3 | obj4).ids),
+                ('city', 'not ilike', 'field'),
+            ]),
+            (obj1 | obj2 | obj3),
+            "Should have returned all banks whose city doesn't contain field"
+        )
diff --git a/odoo/osv/expression.py b/odoo/osv/expression.py
index f8a1c702758..ebf6c56f628 100644
--- a/odoo/osv/expression.py
+++ b/odoo/osv/expression.py
@@ -1264,11 +1264,11 @@ class expression(object):
             column = '%s.%s' % (table_alias, _quote(left))
             query = '(%s %s %s)' % (unaccent(column + cast), sql_operator, unaccent(format))
 
+            if (need_wildcard and not right) or (right and operator in NEGATIVE_TERM_OPERATORS):
+                query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
+
             if need_wildcard:
-                native_str = pycompat.to_text(right)
-                if not native_str:
-                    query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
-                params = ['%%%s%%' % native_str]
+                params = ['%%%s%%' % pycompat.to_text(right)]
             else:
                 field = model._fields[left]
                 params = [field.convert_to_column(right, model, validate=False)]