title: Creating ElasticSearch 6.5 nested boolean queries from SQL
body: <p>I've this quite simple SQL query:</p>
  
  <pre><code>SELECT * FROM table
  WHERE 
  ((sphereMin&gt;=-17 AND sphereMax&lt;=5) OR sphereMin&lt;=-17)
  AND
  ((sphereMax&lt;=5 AND sphereMax&gt;=-17) OR sphereMax&gt;=5)
  </code></pre>
  
  <p>I have to translate that query to ES using high level API.
  This is the relevant code:</p>
  
  <pre><code> SearchSourceBuilder searchBuilder = new SearchSourceBuilder();
   BoolQueryBuilder boolQueryBuilder = boolQuery();
  
   BoolQueryBuilder booleanQuery1 = boolQuery();
   BoolQueryBuilder subQuery1a = boolQuery();
   booleanQuery1.must(subQuery1a);
   RangeQueryBuilder subQuery1b = null;
  
  subQuery1a.must(rangeQuery("sphereMin").gte(-17));
  subQuery1a.must(rangeQuery("sphereMax").lte(5));
  subQuery1b = rangeQuery("sphereMin").lte(-17);
  
  booleanQuery1.should(subQuery1b);
  
  BoolQueryBuilder booleanQuery2 = boolQuery();
  BoolQueryBuilder subQuery2a = boolQuery();
  booleanQuery2.must(subQuery2a);
  RangeQueryBuilder subQuery2b = null;
  
  subQuery2a.must(rangeQuery("sphereMax").gte(-17));
  subQuery2a.must(rangeQuery("sphereMax").lte(5));
  subQuery2b = rangeQuery("sphereMax").gte(5);
  
  booleanQuery2.should(subQuery2b);
  
  boolQueryBuilder.filter(booleanQuery1);
  boolQueryBuilder.filter(booleanQuery2);
  searchBuilder.query(boolQueryBuilder);
  SearchRequest searchRequest = Requests.searchRequest(indexName).allowPartialSearchResults(true)
                  .source(searchBuilder).routing(routing);
  </code></pre>
  
  <p>The JSON created from that code is:</p>
  
  <pre><code>{
    "bool" : {
      "filter" : [     
        {
          "bool" : {
            "must" : [
              {
                "bool" : {
                  "must" : [
                    {
                      "range" : {
                        "sphereMin" : {
                          "from" : "-17",
                          "to" : null,
                          "include_lower" : true,
                          "include_upper" : true,
                          "boost" : 1.0
                        }
                      }
                    },
                    {
                      "range" : {
                        "sphereMax" : {
                          "from" : null,
                          "to" : "5",
                          "include_lower" : true,
                          "include_upper" : true,
                          "boost" : 1.0
                        }
                      }
                    }
                  ],
                  "adjust_pure_negative" : true,
                  "boost" : 1.0
                }
              }
            ],
            "should" : [
              {
                "range" : {
                  "sphereMin" : {
                    "from" : null,
                    "to" : "-17",
                    "include_lower" : true,
                    "include_upper" : true,
                    "boost" : 1.0
                  }
                }
              }
            ],
            "adjust_pure_negative" : true,
            "boost" : 1.0
          }
        },
        {
          "bool" : {
            "must" : [
              {
                "bool" : {
                  "must" : [
                    {
                      "range" : {
                        "sphereMax" : {
                          "from" : "-17",
                          "to" : null,
                          "include_lower" : true,
                          "include_upper" : true,
                          "boost" : 1.0
                        }
                      }
                    },
                    {
                      "range" : {
                        "sphereMax" : {
                          "from" : null,
                          "to" : "5",
                          "include_lower" : true,
                          "include_upper" : true,
                          "boost" : 1.0
                        }
                      }
                    }
                  ],
                  "adjust_pure_negative" : true,
                  "boost" : 1.0
                }
              }
            ],
            "should" : [
              {
                "range" : {
                  "sphereMax" : {
                    "from" : "5",
                    "to" : null,
                    "include_lower" : true,
                    "include_upper" : true,
                    "boost" : 1.0
                  }
                }
              }
            ],
            "adjust_pure_negative" : true,
            "boost" : 1.0
          }
        }
      ],
      "adjust_pure_negative" : true,
      "boost" : 1.0
    }
  }
  </code></pre>
  
  <p>It looks right to me but that query doesn't return the same data of the SQL query.
  What am I doing wrong?</p>