SQL Access strange results

Elastic Stack Elasticsearch
1

I have. a strange result in Kibana 7.4 (just upgraded to 7.5 - no change) elasticsearch results but I cannot reproduce it with test data.

Maybe someone can give me a hint how to pinpoint the issue?

The query is:

POST _sql?format=txt
{
  "query":"""
    select sum(total_volume), sum(content_user_filter) from myindex
    where customer_number = '0081'
  """
}

The issue here is that sum(content_user_filter) is null. But when I run

POST _sql?format=txt
{
  "query":"""
    select total_volume, content_user_filter from myindex
    where customer_number = '0081'
  """
}

I see only zeros (0) for content_user_filter.

What could be a reason that the sum is null?

2

With a quick try I couldn't reproduce it.
Is it possible to provide the mapping of your index and maybe some sample data?

3

Okay… I managed to create example data with the same behaviour:

PUT /myindex

PUT /myindex/_mapping
{
      "properties" : {
        "content_filter" : {
          "type" : "long"
        },
        "content_user_filter" : {
          "type" : "long"
        },
        "customer_number" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "date" : {
          "type" : "date"
        },
        "delisted" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "dkim_domain" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "domain" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "host" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "inbox" : {
          "type" : "long"
        },
        "ip" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "ip_status" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "mbp_brand" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "other" : {
          "type" : "long"
        },
        "spam_complaints" : {
          "type" : "long"
        },
        "tenant_name" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "total_volume" : {
          "type" : "long"
        },
        "traphit" : {
          "type" : "long"
        },
        "user_filter" : {
          "type" : "long"
        },
        "virus" : {
          "type" : "long"
        }
      }
}

POST _bulk
{ "index": {"_index" : "myindex"} }
{ "delisted" : "0", "content_user_filter" : 0, "user_filter" : 3086, "ip_status" : "Active", "virus" : 0, "date" : "2019-10-28", "host" : "XXX", "content_filter" : 0, "customer_number" : "0081", "domain" : "XXX", "total_volume" : 26845, "mbp_brand" : "XXX", "tenant_name" : "XXX", "other" : 0, "dkim_domain" : "XXX", "inbox" : 23759, "traphit" : 0, "spam_complaints" : 44, "ip" : "123.123.123.123"  }
{ "index": {"_index" : "myindex"} }
{ "delisted" : "0", "content_user_filter" : 0, "user_filter" : 5423, "ip_status" : "Active", "virus" : 0, "date" : "2019-10-28", "host" : "XXX", "content_filter" : 0, "customer_number" : "0081", "domain" : "XXX", "total_volume" : 28786, "mbp_brand" : "XXX", "tenant_name" : "XXX", "other" : 0, "dkim_domain" : "XXX", "inbox" : 23363, "traphit" : 0, "spam_complaints" : 13, "ip" : "123.123.123.123"  }
{ "index": {"_index" : "myindex"} }
{ "delisted" : "0", "content_user_filter" : 0, "user_filter" : 0, "ip_status" : "Active", "virus" : 0, "date" : "2019-10-28", "host" : "XXX", "content_filter" : 0, "customer_number" : "0081", "domain" : "XXX", "total_volume" : 0, "mbp_brand" : "XXX", "tenant_name" : "XXX", "other" : 0, "dkim_domain" : "XXX", "inbox" : 0, "traphit" : 0, "spam_complaints" : 5, "ip" : "123.123.123.123"  }
{ "index": {"_index" : "myindex"} }
{ "delisted" : "0", "content_user_filter" : 0, "user_filter" : 0, "ip_status" : "Active", "virus" : 0, "date" : "2019-10-28", "host" : "XXX", "content_filter" : 0, "customer_number" : "0081", "domain" : "XXX", "total_volume" : 0, "mbp_brand" : "XXX", "tenant_name" : "XXX", "other" : 0, "dkim_domain" : "XXX", "inbox" : 0, "traphit" : 0, "spam_complaints" : 5, "ip" : "123.123.123.123"  }
{ "index": {"_index" : "myindex"} }
{ "date" : "2019-10-28", "tenant_name" : "XXX", "mbp_brand" : "XXX", "domain" : "XXX", "total_volume" : 1366, "customer_number" : "0081", "content_filter" : 0, "host" : "XXX", "content_user_filter" : 0, "delisted" : "0", "virus" : 0, "ip_status" : "Active", "user_filter" : 547, "traphit" : 0, "inbox" : 819, "ip" : "123.123.123.123", "spam_complaints" : 0, "other" : 0, "dkim_domain" : "XXX"  }
{ "index": {"_index" : "myindex"} }
{ "date" : "2019-10-28", "tenant_name" : "XXX", "mbp_brand" : "XXX", "domain" : "XXX", "total_volume" : 1415, "customer_number" : "0081", "content_filter" : 0, "host" : "XXX", "content_user_filter" : 0, "delisted" : "0", "virus" : 0, "ip_status" : "Active", "user_filter" : 317, "traphit" : 0, "inbox" : 1098, "ip" : "123.123.123.123", "spam_complaints" : 0, "other" : 0, "dkim_domain" : "XXX"  }
{ "index": {"_index" : "myindex"} }
{ "spam_complaints" : 0, "ip" : "123.123.123.123", "inbox" : 1128, "traphit" : 0, "dkim_domain" : "XXX", "other" : 0, "content_filter" : 0, "customer_number" : "0081", "host" : "XXX", "total_volume" : 1460, "domain" : "XXX", "mbp_brand" : "XXX", "tenant_name" : "XXX", "date" : "2019-10-28", "ip_status" : "Active", "user_filter" : 332, "virus" : 0, "delisted" : "0", "content_user_filter" : 0  }
{ "index": {"_index" : "myindex"} }
{ "spam_complaints" : 2, "ip" : "123.123.123.123", "inbox" : 848, "traphit" : 0, "dkim_domain" : "XXX", "other" : 0, "content_filter" : 0, "customer_number" : "0081", "host" : "XXX", "total_volume" : 1429, "domain" : "XXX", "mbp_brand" : "XXX", "tenant_name" : "XXX", "date" : "2019-10-28", "ip_status" : "Active", "user_filter" : 581, "virus" : 0, "delisted" : "0", "content_user_filter" : 0  }
{ "index": {"_index" : "myindex"} }
{ "domain" : "XXX", "ip" : "123.123.123.123", "tenant_name" : "XXX", "customer_number" : "0081", "spam_complaints" : 0, "delisted" : "0", "other" : 0, "host" : "XXX", "traphit" : 0, "virus" : 0, "content_filter" : 0, "inbox" : 1083, "date" : "2019-11-10", "ip_status" : "Active", "content_user_filter" : 0, "mbp_brand" : "XXX", "total_volume" : 1401, "dkim_domain" : "XXX", "user_filter" : 318  }
{ "index": {"_index" : "myindex"} }
{ "domain" : "XXX", "ip" : "123.123.123.123", "tenant_name" : "XXX", "customer_number" : "0081", "spam_complaints" : 1, "delisted" : "0", "other" : 0, "host" : "XXX", "traphit" : 0, "virus" : 0, "content_filter" : 0, "inbox" : 1253, "date" : "2019-11-10", "ip_status" : "Active", "content_user_filter" : 0, "mbp_brand" : "XXX", "total_volume" : 2015, "dkim_domain" : "XXX", "user_filter" : 762 }
4

Thanks, indeed I reproduced it too with your data.
There is an open issue for this behavior: https://github.com/elastic/elasticsearch/issues/45251

5

Thanks for finding that…

Wondering how to solve this…

6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.