Support between/not between operator

[chloe-zh]

Issue #, if available: #1058

Description of changes:

• Supported between / not between operator in SQL following the PostgreSQL syntax and return type (boolean): https://www.postgresqltutorial.com/postgresql-between/
• Added tests
• Update user manual: https://github.com/chloe-zh/sql/blob/between-operator/docs/user/dql/expressions.rst#between-and
• Pushdown to range query: (NOT) BETWEEN ... AND ... operator is pushdown to range query when it's in a filter expression that can be pushed down. The explanation of optimized DSL is as follows: Query SELECT lastname, age FROM accounts WHERE age BETWEEN 20 AND 30:

{
  "from":0,
  "size":200,
  "timeout":"1m",
  "query":{
    "range":{
      "age":{
        "from":20,
        "to":30,
        "include_lower":true,
        "include_upper":true,
        "boost":1.0
      }
    }
  },
  "_source":{
    "includes":[
      "age",
      "lastname"
    ],
    "excludes":[
      
    ]
  },
  "sort":[
    {
      "_doc":{
        "order":"asc"
      }
    }
  ]
}

And query SELECT lastname, age FROM accounts WHERE age NOT BETWEEN 20 AND 30:

{
  "from":0,
  "size":200,
  "timeout":"1m",
  "query":{
    "bool":{
      "must_not":[
        {
          "range":{
            "age":{
              "from":20,
              "to":30,
              "include_lower":true,
              "include_upper":true,
              "boost":1.0
            }
          }
        }
      ],
      "adjust_pure_negative":true,
      "boost":1.0
    }
  },
  "_source":{
    "includes":[
      "age",
      "lastname"
    ],
    "excludes":[
      
    ]
  },
  "sort":[
    {
      "_doc":{
        "order":"asc"
      }
    }
  ]
}

Note: this PR does not include comparison test because of different behaviors for the testing DBs, they are returning integer type (1 as true and 0 as false).

By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license.

[codecov[bot]]

Codecov Report

Merging #1067 (1545188) into develop (c1107bb) will increase coverage by 0.00%. The diff coverage is 100.00%.

@@            Coverage Diff             @@
##             develop    #1067   +/-   ##
==========================================
  Coverage      99.89%   99.89%           
- Complexity      2408     2432   +24     
==========================================
  Files            236      236           
  Lines           5484     5537   +53     
  Branches         327      338   +11     
==========================================
+ Hits            5478     5531   +53     
  Misses             5        5           
  Partials           1        1           

Impacted Files	Coverage Δ	Complexity Δ
...elasticsearch/sql/analysis/ExpressionAnalyzer.java	100.00% <ø> (ø)	31.00 <0.00> (ø)
...arch/storage/script/filter/FilterQueryBuilder.java	100.00% <ø> (ø)	10.00 <0.00> (ø)
...opendistroforelasticsearch/sql/expression/DSL.java	100.00% <100.00%> (ø)	135.00 <2.00> (+2.00)
...h/sql/expression/function/BuiltinFunctionName.java	100.00% <100.00%> (ø)	3.00 <0.00> (ø)
...on/operator/predicate/BinaryPredicateOperator.java	100.00% <100.00%> (ø)	38.00 <1.00> (+1.00)
...istroforelasticsearch/sql/utils/OperatorUtils.java	100.00% <100.00%> (ø)	22.00 <10.00> (+10.00)
...arch/storage/script/filter/lucene/LuceneQuery.java	100.00% <100.00%> (ø)	12.00 <6.00> (+3.00)
...earch/storage/script/filter/lucene/RangeQuery.java	100.00% <100.00%> (ø)	7.00 <2.00> (+2.00)
...ticsearch/sql/sql/parser/AstExpressionBuilder.java	100.00% <100.00%> (ø)	53.00 <2.00> (+2.00)
...relasticsearch/sql/sql/domain/SQLQueryRequest.java	100.00% <0.00%> (ø)	21.00% <0.00%> (-1.00%)
... and 1 more

---

Continue to review full report at Codecov.

Legend - Click here to learn more Δ = absolute <relative> (impact), ø = not affected, ? = missing data Powered by Codecov. Last update c1107bb...1545188. Read the comment docs.

[kaioken]

@chloe-zh this is perfect we are needed this specific feature for one of our components, any idea when this will get a release?

Thanks

[kaioken]

@chloe-zh any update on this pull?