Missing data...two SQL queries that should return identical responses, but don't?!?

One of our BI team reported an issue to me where an SQL query he was running seemed to be returning incorrect data (they know the data well enough that it just count be right)…we figured out a work around, but there’s clearly something odd happening, and more importantly we don’t know what other queries might be affected where the issue hasn’t been spotted.

I’ve added an anonymised index mapping and a sample document below, but the following two queries should surely give the same result, shouldn’t they?

select n from the_test where n = 12345;
select n from the_test where n in (12345);

The first return 0, the second 12345 as I’d expect?!?

Is this a bug, or expected behaviour?

Probably worth a mention, this is running on 7.10.2

PUT the_test
{
  "settings": {
    "index.number_of_shards": 1,
    "index.number_of_replicas": 1
  },
  "mappings" : {
    "properties" : {
      "a" : {
        "type" : "keyword"
      },
      "b" : {
        "type" : "boolean"
      },
      "c" : {
        "properties" : {
          "ca" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          },
          "cb" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          },
          "cc" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          }
        }
      },
      "d" : {
        "type" : "keyword"
      },
      "e" : {
        "type" : "keyword"
      },
      "f" : {
        "type" : "text",
        "fields" : {
          "keyword" : {
            "type" : "keyword",
            "ignore_above" : 256
          }
        }
      },
      "g" : {
        "type" : "date"
      },
      "h" : {
        "type" : "keyword"
      },
      "i" : {
        "type" : "keyword"
      },
      "j" : {
        "type" : "boolean"
      },
      "k" : {
        "type" : "integer"
      },
      "l" : {
        "properties" : {
          "la" : {
            "type" : "long"
          },
          "lb" : {
            "type" : "keyword"
          },
          "lc" : {
            "type" : "integer"
          }
        }
      },
      "m" : {
        "type" : "date"
      },
      "n" : {
        "type" : "long"
      },
      "o" : {
        "type" : "keyword"
      },
      "p" : {
        "type" : "long"
      },
      "q" : {
        "properties" : {
          "qa" : {
            "properties" : {
              "qaa" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qab" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qac" : {
                "type" : "float"
              },
              "qad" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "qb" : {
            "properties" : {
              "qba" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qbb" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "qc" : {
            "properties" : {
              "qca" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qcb" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qcc" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qcd" : {
                "properties" : {
                  "qcda" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qcdb" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qcdc" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qcdd" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  }
                }
              },
              "qce" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qcf" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "qd" : {
            "properties" : {
              "qda" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qdb" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qdc" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qdd" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "qe" : {
            "properties" : {
              "qea" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qeb" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qec" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "qf" : {
            "properties" : {
              "qfa" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qfb" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qfc" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qfd" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qfe" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "qg" : {
            "type" : "object"
          },
          "qh" : {
            "properties" : {
              "qha" : {
                "type" : "date"
              },
              "qhb" : {
                "properties" : {
                  "qhba" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qhbb" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qhbc" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  }
                }
              }
            }
          },
          "qi" : {
            "type" : "object"
          },
          "qj" : {
            "properties" : {
              "qja" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qjb" : {
                "properties" : {
                  "qjba" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qjbb" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qjbc" : {
                    "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                  },
                  "qjbd" : {
                    "type" : "boolean"
                  }
                }
              }
            }
          },
          "qk" : {
            "properties" : {
              "qka" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qkb" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              },
              "qkc" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          },
          "ql" : {
            "type" : "object"
          }
        }
      },
      "r" : {
        "type" : "keyword"
      },
      "s" : {
        "properties" : {
          "sa" : {
            "properties" : {
              "saa" : {
                "type" : "text",
                "fields" : {
                  "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                  }
                }
              }
            }
          }
        }
      },
      "t" : {
        "type" : "keyword"
      },
      "u" : {
        "type" : "integer"
      },
      "v" : {
        "type" : "long"
      },
      "w" : {
        "type" : "keyword"
      },
      "x" : {
        "properties" : {
          "xa" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          },
          "xb" : {
            "type" : "date"
          },
          "xc" : {
            "type" : "date"
          },
          "xd" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          }
        }
      },
      "y" : {
        "type" : "text",
        "fields" : {
          "keyword" : {
            "type" : "keyword",
            "ignore_above" : 256
          }
        }
      },
      "z" : {
        "type" : "text"
      },
      "aa" : {
        "type" : "text"
      },
      "bb" : {
        "type" : "date"
      },
      "cc" : {
        "type" : "integer"
      }
    }
  }
}
POST /the_test/_doc/999
{
  "aa" : "",
  "v" : "0",
  "z" : "",
  "o" : "PROFILE",
  "n" : "12345",
  "i" : "999",
  "j" : false,
  "g" : "2020-10-11T03:37:42.855Z",
  "u" : 1,
  "b" : true,
  "r" : "AB12345=T",
  "p" : "0",
  "d" : "",
  "h" : "TEST",
  "x" : [
  {
    "xa" : "0",
    "xc" : "2020-11-11T03:37:42.855Z",
    "xb" : "2020-11-11T03:37:42.855Z",
    "xd" : "Creating metadata record"
  }
  ],
  "t" : "e9a4c0a9-5ccc-427b-9e2c-bf505794191e",
  "e" : "ABC",
  "k" : 0,
  "a" : "SYNC",
  "c" : { },
  "m" : "2020-11-11T03:37:42.855Z",
  "bb" : 1605065862855,
  "l" : {
    "lb" : "",
    "lc" : 567,
    "la" : "987654"
  },
  "w" : "COMPLETE"
}

Copyright ©2021, Oracle and/or its affiliates

How does the result of the translate look? That should provide some insights.

Translate? Not sure what you mean…

The Explain plans for the queries are totally different though

For n= it’s this

{
  "root": {
    "name": "ProjectOperator",
    "description": {
      "fields": "[n]"
    },
    "children": [
      {
        "name": "ElasticsearchIndexScan",
        "description": {
          "request": "ElasticsearchQueryRequest(indexName=the_test, sourceBuilder={\"from\":0,\"size\":200,\"timeout\":\"1m\",\"query\":{\"term\":{\"n\":{\"value\":12345,\"boost\":1.0}}},\"_source\":{\"includes\":[\"n\"],\"excludes\":[]},\"sort\":[{\"_doc\":{\"order\":\"asc\"}}]}, searchDone=false)"
        },
        "children": []
      }
    ]
  }
}

For n IN it’s this

{
  "from": 0,
  "size": 200,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "must": [
              {
                "bool": {
                  "should": [
                    {
                      "term": {
                        "n": {
                          "value": 12345,
                          "boost": 1
                        }
                      }
                    }
                  ],
                  "adjust_pure_negative": true,
                  "boost": 1
                }
              }
            ],
            "adjust_pure_negative": true,
            "boost": 1
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "_source": {
    "includes": [
      "n"
    ],
    "excludes": []
  }
}

Yeah, sorry. The old UI used “Translate” instead of “Explain” in the query workbench (see: SQL - OpenSearch documentation).

So, I played around with your test case and it is interesting.
select n from the_test where n = 12345 … weird result (n = 0)
select h from the_test where h = 'TEST' … expected result (h = TEST)
select n from the_test where h = 'TEST' … weird result (n = 0)

Furthermore, looking the explain results, the query that select n from the_test where n = 12345 is being translated to is returning documents as expected:

GET /the_test/_search
{"from":0,"size":200,"timeout":"1m","query":{"term":{"n":{"value":12345,"boost":1.0}}},"_source":{"includes":["n"],"excludes":[]},"sort":[{"_doc":{"order":"asc"}}]}

results in:

{
  "took" : 8,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [
      {
        "_index" : "the_test",
        "_type" : "_doc",
        "_id" : "999",
        "_score" : null,
        "_source" : {
          "n" : "12345"
        },
        "sort" : [
          0
        ]
      }
    ]
  }
}

All this being said, it looks like a bug how longs are converted to SQL results. Please put in a bug report on the SQL plugin.

Will do…thanks. I was hoping that someone would verify the issue before I added a bug :slight_smile:

It’s an v interesting case - at first glance I thought it must something else.

Is it possible to get a version number for the SQL plugin? I can only see the ES version 7.10.2 in the UI?

I’ve added a bug here, [BUG] SELECT in SQL returns 0 rather than expected data · Issue #226 · opensearch-project/sql · GitHub

GET /_cat/plugins in Dev Tools should do it.

FWIW, it’s confirmed in opensearch-sql 1.1.0.0, so you can add that into your issue.

Thanks again for the help!

1 Like