how does must_not work in queries?

General Discussions - English
1

As part of my work, I needed to exclude a combination of attributes from the search results
a combination was needed because I use a combined primary key in my work

and then I ran into a problem that I can’t understand
Below I will show a series of queries to the test index

create table test(a string, b string);
insert into test (a, b) values ('0', '0');
insert into test (a, b) values ('0', '1');
insert into test (a, b) values ('1', '0');
insert into test (a, b) values ('1', '1');

Let’s imagine that I need the search result to not include a=1, b=1

the first attempt that I considered obvious was this

{

    "index": "test",
    "query": {
        "bool": {
            "must_not": [
                
                {
                    "equals": {
                        "a": "1"
                    }
                },
                {
                    "equals": {
                        "b": "1"
                    }
                }
            ]
        }
    }
}
--------------------------------------------

"hits": [
            {
                "_id": "7208260601569607687",
                "_score": 1,
                "_source": {
                    "a": "0",
                    "b": "0"
                }
            }
        ]

Quite quickly I realized that for some reason - when using must_not, “or” is placed between the conditions and not “and”, which is almost directly stated in the documentation.

then I decided to use a nested condition, since I can’t get by with the option above.
not(a and b)

{

    "index": "test",
    "query": {
        "bool": {
            "must_not": [
                {
                    "bool": {
                        "must": [
                            {
                                "equals": {
                                    "a": "1"
                                }
                            },
                            {
                                "equals": {
                                    "b": "1"
                                }
                            }
                        ]
                    }
                }
            ]
        }
    }
}

----------------------------------------------------------------------------
"hits": [
            {
                "_id": "7208260601569607687",
                "_score": 1,
                "_source": {
                    "a": "0",
                    "b": "0"
                }
            }
        ]

but for some reason the answer turned out to be as if “must” works like “should”.

on the 3rd attempt I finally managed to get the result I needed, but I completely stopped understanding how “must_not” works.
not(a or b)???

{

    "index": "test",
    "query": {
        "bool": {
            "must_not": [
                {
                    "bool": {
                        "should": [
                            {
                                "equals": {
                                    "a": "1"
                                }
                            },
                            {
                                "equals": {
                                    "b": "1"
                                }
                            }
                        ]
                    }
                }
            ]
        }
    }
}

-------------------------------------------------------

"hits": [
            {
                "_id": "7208260601569607687",
                "_score": 1,
                "_source": {
                    "a": "0",
                    "b": "0"
                }
            },
            {
                "_id": "7208260601569607688",
                "_score": 1,
                "_source": {
                    "a": "0",
                    "b": "1"
                }
            },
            {
                "_id": "7208260601569607686",
                "_score": 1,
                "_source": {
                    "a": "1",
                    "b": "0"
                }
            }
        ]

and the actual question
Am I not understanding how it should work or is this a bug?
and if I don’t understand, could you please explain?

2

you need to use De Morgan laws to invert logical operations.

You also need to use searchd.not_terms_only_allowed option if you use single negation at your query.

3 
"must_not": [
                
                {
                    "equals": {
                        "a": "1"
                    }
                },
                {
                    "equals": {
                        "b": "1"
                    }
                }
            ]

is equivalent to a!='1' and b!='1', there should be no OR. If you believe there’s OR, please let me know how you figured that? As for a!='1' and b!='1' via SQL in Manticore or even Mysql you’ll get the same result:

mysql Ver 8.0.35-0ubuntu0.22.04.1:

mysql> drop table if exists test; create table test(a varchar(255), b varchar(255)); insert into test (a, b) values ('0', '0'); insert into test (a, b) values ('0', '1'); insert into test (a, b) values ('1', '0'); insert into test (a, b) values ('1', '1'); select * from test where a!=1 and b!=1;
--------------
drop table if exists test
--------------

Query OK, 0 rows affected (1.36 sec)

--------------
create table test(a varchar(255), b varchar(255))
--------------

Query OK, 0 rows affected (1.52 sec)

--------------
insert into test (a, b) values ('0', '0')
--------------

Query OK, 1 row affected (0.28 sec)

--------------
insert into test (a, b) values ('0', '1')
--------------

Query OK, 1 row affected (0.22 sec)

--------------
insert into test (a, b) values ('1', '0')
--------------

Query OK, 1 row affected (0.18 sec)

--------------
insert into test (a, b) values ('1', '1')
--------------

Query OK, 1 row affected (0.12 sec)

--------------
select * from test where a!=1 and b!=1
--------------

+------+------+
| a    | b    |
+------+------+
| 0    | 0    |
+------+------+
1 row in set (0.00 sec)

Here Manticore Search Manual: Searching > Filters it’s said:

If several queries are specified under must_not , the document matches if none of them match.

In your case there’s only one document (a=0, b=0) which satisfies the none of them match condition.

then I decided to use a nested condition, since I can’t get by with the option above.
not(a and b)

Indeed looks like a bug. Reproduced and filed an issue - must_not doesn't work as NOT · Issue #1751 · manticoresoftware/manticoresearch · GitHub

on the 3rd attempt I finally managed to get the result I needed, but I completely stopped understanding how “must_not” works. not(a or b)???

No. I think in this example must_not works wrong too since it doesn’t invert what’s inside it. The problem may be that you think that the query for result to not include a=1, b=1 should be a!=1 and b!=1 while it actually has to be a!=1 or b!=1. Then the JSON query is:

curl -v -sX POST http://localhost:9308/search -d '
{
    "index": "test",
    "query": {
        "bool": {
            "should": [
                {
                    "bool": {
                        "must_not": {
                            "equals": {
                                "a": "1"
                            }
                        }
                    }
                }, 
                {
                    "bool": {
                        "must_not": {
                            "equals": {
                                "b": "1"
                            }
                        }
                    }
                }
            ]
        }
    }
}'|jq .

and it finds what you are looking for:

{
  "took": 109,
  "timed_out": false,
  "hits": {
    "total": 3,
    "total_relation": "eq",
    "hits": [
      {
        "_id": "1515714768311156774",
        "_score": 1,
        "_source": {
          "a": "0",
          "b": "1"
        }
      },
      {
        "_id": "1515714768311156775",
        "_score": 1,
        "_source": {
          "a": "1",
          "b": "0"
        }
      },
      {
        "_id": "1515714768311156773",
        "_score": 1,
        "_source": {
          "a": "0",
          "b": "0"
        }
      }
    ]
  }
}