What Is The True Meaning Of IS NOT BETWEEN?

[Ethan777100 (Ethan Chia)]

i just implemented a filter to find the count of values that are not within -300 to 300

But in my results, why do i still see 3 rows of 300?

I would interpret IS NOT BETWEEN to be inclusive of the range boundaries. I am indeed looking for the count of values starting frm +/-301

any advice?

[Ethan777100 (Ethan Chia)]

i did a further check, i think there is smth wrong with IS NOT BETWEEN FILTER.

I experimented with NOT BETWEEN -301 to 301

And look at the result. The negative case is correctly addressed: "-301" values are excluded in the results.

But the positive case is incorrect because "+301" is still shown even tho I already defined it in my range.

So what is the true essence of IS NOT BETWEEN then?

[flash1293 (Joe Reuter)]

"IS NOT BETWEEN" is producing the following clause in the underlying ES query (example is "bytes is not between 5 and 10"):

must_not": [
        {
          "range": {
            "bytes": {
              "gte": 5,
              "lt": 10
            }
          }
        }
      ]

As you can see here, for the lower bound "gte" (greater than equal) is used, while for the upper bound "lt" (lower than) is used. This means "IS NOT BETWEEN" will filter out everything 5 to up to 9 bytes (inclusive lower bound, exclusive upper bound)

[aaron_nimocks]

I know MySQL and MSSQL the NOT BETWEEN includes the boundaries. So if they follow the common interpretation it would be the same. It essentially says not between and including the min/max range.

I 100% agree the term is confusing.

[Ethan777100 (Ethan Chia)]

thanks so much of ur responses!

So can i ascertain there is indeed an issue with the filter?

[flash1293 (Joe Reuter)]

It's definitely worth creating an issue for this in the Github repo: https://github.com/elastic/kibana/issues/new/choose

It's not necessarily wrong, but I agree it's confusing behavior.

[Ethan777100 (Ethan Chia)]

shld it be a Bug Report? ur advice pls

[flash1293 (Joe Reuter)]

You can file it as a bug, if it's deemed expected behavior the team can change the labels afterwards.