The following two queries gives me different results when some documents has missing fields:
Query1, SELECT * FROM table WHERE colE <= 9 OR colD >= 6
{
"query": {
"bool": {
"should": [
{
"range": {
"colE": {
"lte": "9"
}
}
},
{
"range": {
"colD": {
"gte": "6"
}
}
}
]
}
}
}
Query2, SELECT * FROM table WHERE NOT (colE > 9 AND colD < 6)
{
"query": {
"bool": {
"must_not": [
{
"bool": {
"filter": [
{
"range": {
"colE": {
"gt": "9"
}
}
},
{
"range": {
"colD": {
"lt": "6"
}
}
}
]
}
}
]
}
}
}
The difference is that the 2nd query will return one more document that does not have colD and w/ colE = 14. Can anyone provide some hints on the difference between these 2 queries (especially on how they deal with missing fields)? Thank you so much!