# What causes the count function to return an empty result

**URL:** <https://discuss.elastic.co/t/what-causes-the-count-function-to-return-an-empty-result/276613>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [June 22, 2021, 7:17am UTC](https://discuss.elastic.co/t/what-causes-the-count-function-to-return-an-empty-result/276613 "2021-06-22T07:17:27Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![RhodesOfficial](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rhodesofficial/32/90633_2.png) [@RhodesOfficial](https://discuss.elastic.co/u/RhodesOfficial)\
**Post date:** [June 22, 2021, 7:17am UTC](https://discuss.elastic.co/t/what-causes-the-count-function-to-return-an-empty-result/276613/1 "2021-06-22T07:17:27Z")

</div>

I have a complex query statement（some data were desensitized）, but count (1) returns an empty result. NPE occurs in my code. So what causes this result？  
Thank you for answering my question！

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/5/0/5067408f5ad1c9f0a45f73fc03fb81d4731840e9.png)

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [June 22, 2021, 7:45am UTC](https://discuss.elastic.co/t/what-causes-the-count-function-to-return-an-empty-result/276613/2 "2021-06-22T07:45:53Z")

</div>

can you provide a full reproduction including index creation/mapping and query?

that would be extremely helpful for debugging!

---

<div class="post-metadata">

**Author:** ![RhodesOfficial](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rhodesofficial/32/90633_2.png) [@RhodesOfficial](https://discuss.elastic.co/u/RhodesOfficial)\
**Post date:** [June 22, 2021, 8:04am UTC](https://discuss.elastic.co/t/what-causes-the-count-function-to-return-an-empty-result/276613/3 "2021-06-22T08:04:48Z")

</div>

```auto
POST /_sql?format=json
{
  "query": "select * from user_selector_tag_increment data where ( data.asiuid_afi_repayment_day = 0 and data.pid_installment_loaning = 0 and data.asiuid_afi_repayment_day = 0 and data.pid_installment_loaning = 0 and data.pdl_current_bill = 32503564800000 and data.user_current_plat = 1 and ( data.uid_postfix >= 00 and data.uid_postfix <= 89 ) and ( data.pid_cash_credit_status = 1 and ( data.pdl_risk_level = '201' or data.pdl_risk_level = '203' or data.pdl_risk_level = '202' or data.pdl_risk_level = '204' or data.pdl_risk_level = '205' or data.pdl_risk_level = '206' or data.pdl_risk_level = '207' or data.pdl_risk_level = '208' or data.pdl_risk_level = '209' ) and ( data.pid_cash_loaned_times.pid_cash_loaned_180d_times >= 0 and data.pid_cash_loaned_times.pid_cash_loaned_180d_times <= 0 ) ) and ( data.pid_cash_credit_status = 1 and ( data.pdl_risk_level = '201' or data.pdl_risk_level = '202' or data.pdl_risk_level = '203' or data.pdl_risk_level = '204' or data.pdl_risk_level = '205' or data.pdl_risk_level = '206' or data.pdl_risk_level = '207' or data.pdl_risk_level = '208' or data.pdl_risk_level = '209' ) and ( data.pid_cash_loaned_times.pid_cash_loaned_180d_times >= 1 and data.pid_cash_loaned_times.pid_cash_loaned_180d_times <= 1000 ) and ( data.pdl_recent_pay_time <= '1621750439000' ) ) )",
  "columnar": true
}

```

---

<div class="post-metadata">

**Author:** ![RhodesOfficial](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rhodesofficial/32/90633_2.png) [@RhodesOfficial](https://discuss.elastic.co/u/RhodesOfficial)\
**Post date:** [June 22, 2021, 8:06am UTC](https://discuss.elastic.co/t/what-causes-the-count-function-to-return-an-empty-result/276613/4 "2021-06-22T08:06:47Z")

</div>

```auto
{
  "user_selector_tag_increment" : {
    "mappings" : {
      "properties" : {
        "active_time" : {
          "type" : "long"
        },
        "area_id" : {
          "type" : "long"
        },
        "asiuid_afi_cnt_apv" : {
          "type" : "long"
        },
        "asiuid_afi_credit_status" : {
          "type" : "integer"
        },
        "asiuid_afi_curr_repay_days" : {
          "type" : "long"
        },
        "asiuid_afi_first_apv_time" : {
          "type" : "long"
        },
        "asiuid_afi_is_success_invite_user_apv" : {
          "type" : "long"
        },
        "asiuid_afi_last_apv_time" : {
          "type" : "long"
        },
        "asiuid_afi_origin_credit_status" : {
          "type" : "integer"
        },
        "asiuid_afi_repayment_day" : {
          "type" : "long"
        },
        "asiuid_has_pending_pay_order" : {
          "type" : "integer"
        },
        "asiuid_is_cif_account" : {
          "type" : "long"
        },
        "asiuid_is_loan" : {
          "type" : "long"
        },
        "asiuid_is_open_bnc" : {
          "type" : "long"
        },
        "asiuid_is_social_security_card" : {
          "type" : "integer"
        },
        "asiuid_is_tax_card" : {
          "type" : "integer"
        },
        "asiuid_min_repayment_date" : {
          "type" : "long"
        },
        "asiuid_openpay_first_create_time" : {
          "type" : "long"
        },
        "asiuid_openpay_success_cnt" : {
          "type" : "long"
        },
        "asiuid_order_full_pay_success_first_time" : {
          "type" : "long"
        },
        "asiuid_order_installment_pay_last_order_type" : {
          "type" : "long"
        },
        "asiuid_order_installment_pay_success_first_time" : {
          "type" : "long"
        },
        "asiuid_order_last_pay_success_time" : {
          "type" : "long"
        },
        "asiuid_order_last_pay_type" : {
          "type" : "long"
        },
        "asiuid_pid_cash_installment_last_success_time" : {
          "type" : "long"
        },
        "asiuid_pid_cash_installment_success_cnt" : {
          "type" : "long"
        },
        "asiuid_pid_cash_load_cnt" : {
          "type" : "integer"
        },
        "asiuid_pid_cash_loan_last_success_time" : {
          "type" : "long"
        },
        "asiuid_pid_loan_curr_overdue" : {
          "type" : "integer"
        },
        "asiuid_transfer_success_cnt" : {
          "type" : "long"
        },
        "bnc_account_open_status" : {
          "type" : "long"
        },
        "bnc_account_open_time" : {
          "type" : "long"
        },
        "bnc_is_cif_customer" : {
          "type" : "long"
        },
        "bnc_is_pay" : {
          "type" : "long"
        },
        "bnc_is_transfer" : {
          "type" : "long"
        },
        "bnc_is_transfer_in" : {
          "type" : "long"
        },
        "bnc_is_vsip_balance_left" : {
          "type" : "long"
        },
        "bnc_last_pay_time" : {
          "type" : "long"
        },
        "bnc_last_transfer_in_time" : {
          "type" : "long"
        },
        "bnc_last_transfer_time" : {
          "type" : "long"
        },
        "category" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "consumption_installment_applied_times" : {
          "type" : "long"
        },
        "consumption_installment_credit_type" : {
          "type" : "long"
        },
        "country_id" : {
          "type" : "long"
        },
        "created_order_count" : {
          "properties" : {
            "full_pay_cnt" : {
              "type" : "long"
            },
            "installment_pay_cnt" : {
              "type" : "long"
            }
          }
        },
        "credit_limit" : {
          "type" : "long"
        },
        "data_balance" : {
          "type" : "long"
        },
        "docId" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "education_level" : {
          "type" : "long"
        },
        "first_cheap_buy_order_create_time" : {
          "type" : "long"
        },
        "first_order_create_time" : {
          "type" : "long"
        },
        "first_order_customer_price" : {
          "type" : "long"
        },
        "friend_invite" : {
          "properties" : {
            "asiuid_particpt_invt_cnt_15d" : {
              "type" : "long"
            },
            "asiuid_particpt_invt_cnt_180d" : {
              "type" : "long"
            },
            "asiuid_particpt_invt_cnt_1d" : {
              "type" : "long"
            },
            "asiuid_particpt_invt_cnt_30d" : {
              "type" : "long"
            },
            "asiuid_particpt_invt_cnt_60d" : {
              "type" : "long"
            },
            "asiuid_particpt_invt_cnt_7d" : {
              "type" : "long"
            },
            "asiuid_particpt_invt_cnt_90d" : {
              "type" : "long"
            }
          }
        },
        "friend_invite_credit" : {
          "properties" : {
            "asiuid_invt_succ_cnt_15d" : {
              "type" : "integer"
            },
            "asiuid_invt_succ_cnt_180d" : {
              "type" : "integer"
            },
            "asiuid_invt_succ_cnt_30d" : {
              "type" : "integer"
            },
            "asiuid_invt_succ_cnt_3d" : {
              "type" : "integer"
            },
            "asiuid_invt_succ_cnt_60d" : {
              "type" : "integer"
            },
            "asiuid_invt_succ_cnt_7d" : {
              "type" : "integer"
            },
            "asiuid_invt_succ_cnt_90d" : {
              "type" : "integer"
            }
          }
        },
        "gender" : {
          "type" : "boolean"
        },
        "get_current_repayment_day" : {
          "type" : "boolean"
        },
        "installment_payoff_periods" : {
          "type" : "long"
        },
        "kyc_grade" : {
          "type" : "long"
        },
        "last_access_date" : {
          "type" : "long"
        },
        "last_access_day" : {
          "properties" : {
            "last_1d_access_day_cnt" : {
              "type" : "long"
            },
            "last_30d_access_day_cnt" : {
              "type" : "long"
            },
            "last_3d_access_day_cnt" : {
              "type" : "long"
            },
            "last_60d_access_day_cnt" : {
              "type" : "long"
            },
            "last_7d_access_day_cnt" : {
              "type" : "long"
            }
          }
        },
        "last_order_create_time" : {
          "type" : "long"
        },
        "loan_market_user_level" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "occupation" : {
          "type" : "long"
        },
        "order_pay_method" : {
          "properties" : {
            "order_pay_method_10001_cnt" : {
              "type" : "long"
            },
            "order_pay_method_10002_cnt" : {
              "type" : "long"
            },
            "order_pay_method_10005_cnt" : {
              "type" : "long"
            },
            "order_pay_method_10010_cnt" : {
              "type" : "long"
            },
            "order_pay_method_10011_cnt" : {
              "type" : "long"
            },
            "order_pay_method_10012_cnt" : {
              "type" : "long"
            },
            "order_pay_method_20001_cnt" : {
              "type" : "long"
            },
            "order_pay_method_30000_cnt" : {
              "type" : "long"
            },
            "order_pay_method_30001_cnt" : {
              "type" : "long"
            },
            "order_pay_method_40000_cnt" : {
              "type" : "long"
            },
            "order_pay_method_40001_cnt" : {
              "type" : "long"
            },
            "order_pay_method_40002_cnt" : {
              "type" : "long"
            },
            "order_pay_method_40003_cnt" : {
              "type" : "long"
            },
            "order_pay_method_40006_cnt" : {
              "type" : "long"
            },
            "order_pay_method_40007_cnt" : {
              "type" : "long"
            }
          }
        },
        "order_total_customer_price" : {
          "type" : "long"
        },
        "pay_success_order" : {
          "properties" : {
            "full_pay_entity_success_order_cnt" : {
              "type" : "long"
            },
            "full_pay_virtual_success_order_cnt" : {
              "type" : "long"
            },
            "installment_pay_entity_success_order_cnt" : {
              "type" : "long"
            },
            "installment_pay_virtual_success_order_cnt" : {
              "type" : "long"
            }
          }
        },
        "pdl_avg_loan_period" : {
          "type" : "float"
        },
        "pdl_avg_mount" : {
          "type" : "long"
        },
        "pdl_current_bill" : {
          "type" : "long"
        },
        "pdl_last_amount" : {
          "type" : "long"
        },
        "pdl_periods" : {
          "type" : "long"
        },
        "pdl_recent_dpl_time" : {
          "type" : "long"
        },
        "pdl_recent_pay_time" : {
          "type" : "long"
        },
        "pdl_risk_level" : {
          "type" : "long"
        },
        "pid_cash_credit_applied_times" : {
          "type" : "long"
        },
        "pid_cash_credit_date" : {
          "type" : "long"
        },
        "pid_cash_credit_status" : {
          "type" : "long"
        },
        "pid_cash_last_loan_successful" : {
          "type" : "long"
        },
        "pid_cash_loan_applied_times" : {
          "type" : "long"
        },
        "pid_cash_loaned_times" : {
          "properties" : {
            "pid_cash_loaned_180d_times" : {
              "type" : "long"
            },
            "pid_cash_loaned_30d_times" : {
              "type" : "long"
            },
            "pid_cash_loaned_60d_times" : {
              "type" : "long"
            },
            "pid_cash_loaned_90d_times" : {
              "type" : "long"
            }
          }
        },
        "pid_cash_loaning" : {
          "type" : "long"
        },
        "pid_installment_last_loan_successful" : {
          "type" : "long"
        },
        "pid_installment_loaning" : {
          "type" : "long"
        },
        "pid_largeloan_available" : {
          "type" : "long"
        },
        "pid_shortloan_available" : {
          "type" : "long"
        },
        "referrer_uid" : {
          "type" : "boolean"
        },
        "register_time" : {
          "type" : "long"
        },
        "sign_continue_count" : {
          "properties" : {
            "asiuid_sign_continue_cnt_15d" : {
              "type" : "integer"
            },
            "asiuid_sign_continue_cnt_180d" : {
              "type" : "integer"
            },
            "asiuid_sign_continue_cnt_1d" : {
              "type" : "integer"
            },
            "asiuid_sign_continue_cnt_30d" : {
              "type" : "integer"
            },
            "asiuid_sign_continue_cnt_60d" : {
              "type" : "integer"
            },
            "asiuid_sign_continue_cnt_7d" : {
              "type" : "integer"
            },
            "asiuid_sign_continue_cnt_90d" : {
              "type" : "integer"
            }
          }
        },
        "status" : {
          "type" : "boolean"
        },
        "success_order_cnt" : {
          "type" : "long"
        },
        "uid" : {
          "type" : "long"
        },
        (too long to cut)

```

---

<div class="post-metadata">

**Author:** ![RhodesOfficial](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rhodesofficial/32/90633_2.png) [@RhodesOfficial](https://discuss.elastic.co/u/RhodesOfficial)\
**Post date:** [June 22, 2021, 8:07am UTC](https://discuss.elastic.co/t/what-causes-the-count-function-to-return-an-empty-result/276613/5 "2021-06-22T08:07:22Z")

</div>

```auto
"uid_postfix" : {
          "type" : "long"
        },
        "update_time" : {
          "type" : "long"
        },
        "user_channel" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "user_come_from_channel" : {
          "type" : "long"
        },
        "user_current_device_type" : {
          "type" : "long"
        },
        "user_current_plat" : {
          "type" : "long"
        },
        "user_current_uninstalled_app" : {
          "properties" : {
            "uninstalled_app_adakami" : {
              "type" : "long"
            },
            "uninstalled_app_cairin" : {
              "type" : "long"
            },
            "uninstalled_app_easycash" : {
              "type" : "long"
            },
            "uninstalled_app_kredinesia" : {
              "type" : "long"
            },
            "uninstalled_app_kta_kilat" : {
              "type" : "long"
            },
            "uninstalled_app_modal_duit" : {
              "type" : "long"
            },
            "uninstalled_app_one_hope" : {
              "type" : "long"
            },
            "uninstalled_app_rupiah_cepat" : {
              "type" : "long"
            },
            "uninstalled_app_solusikita" : {
              "type" : "long"
            },
            "uninstalled_app_tunai_cepat" : {
              "type" : "long"
            },
            "uninstalled_app_wadah_pinjaman" : {
              "type" : "long"
            }
          }
        }
      }
    }
  }
}

```

this is full query and mapping，thank you

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [June 22, 2021, 9:31am UTC](https://discuss.elastic.co/t/what-causes-the-count-function-to-return-an-empty-result/276613/6 "2021-06-22T09:31:42Z")

</div>

I am missing the sample documents that are being indexed. What I mean with a reproducible example are code snippets, that I can copy & paste into my local Kibana Dev Tools Console and reproduce your problem. I hope that clears it up. 🙂

---

<div class="post-metadata">

**Author:** ![RhodesOfficial](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rhodesofficial/32/90633_2.png) [@RhodesOfficial](https://discuss.elastic.co/u/RhodesOfficial)\
**Post date:** [June 22, 2021, 9:56am UTC](https://discuss.elastic.co/t/what-causes-the-count-function-to-return-an-empty-result/276613/7 "2021-06-22T09:56:13Z")

</div>

Thank you very much for your kind help. I'll look for some documents for you right now.  
Before providing documents, I would like to ask. I try to create a new index with the same mapping structure, and the previous SQL I provided to query still returns empty, you can try it, too.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/a/7/a7f8bb194f43d92f6d723a90f9ae11631c1500b1.png)

(My ElasticSearch's version is 7.7.1)  
According to my understanding, count function should return 0 instead of empty. Similar to MySQL does not use group by, the result of count should be always greater than or equal to zero, is my understanding wrong? 🤣

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [June 23, 2021, 11:59am UTC](https://discuss.elastic.co/t/what-causes-the-count-function-to-return-an-empty-result/276613/8 "2021-06-23T11:59:33Z")

</div>

I tested this under 7.13

```auto
POST _sql
{
  "query": "SELECT count(1) FROM test"
}

```

returns

```auto
{
  "columns" : [
    {
      "name" : "count(1)",
      "type" : "long"
    }
  ],
  "rows" : [
    [
      0
    ]
  ]
}

```

apparently the output seems to have changed in recent versions and this looks correct for an empty index

---

<div class="post-metadata">

**Author:** ![RhodesOfficial](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rhodesofficial/32/90633_2.png) [@RhodesOfficial](https://discuss.elastic.co/u/RhodesOfficial)\
**Post date:** [June 23, 2021, 12:28pm UTC](https://discuss.elastic.co/t/what-causes-the-count-function-to-return-an-empty-result/276613/9 "2021-06-23T12:28:45Z")

</div>

```auto
PUT user_selector_tag_increment_5
{
    "mappings" : {
      "properties" : {
        "active_time" : {
          "type" : "long"
        },
        "area_id" : {
          "type" : "long"
        },
        "asiuid_afi_cnt_apv" : {
          "type" : "long"
        },
        "asiuid_afi_credit_status" : {
          "type" : "integer"
        },
        "asiuid_afi_curr_repay_days" : {
          "type" : "long"
        },
        "asiuid_afi_first_apv_time" : {
          "type" : "long"
        },
        "asiuid_afi_is_success_invite_user_apv" : {
          "type" : "long"
        },
        "asiuid_afi_last_apv_time" : {
          "type" : "long"
        },
        "asiuid_afi_origin_credit_status" : {
          "type" : "integer"
        },
        "asiuid_afi_repayment_day" : {
          "type" : "long"
        },
        "asiuid_has_pending_pay_order" : {
          "type" : "integer"
        },
        "asiuid_is_cif_account" : {
          "type" : "long"
        },
        "asiuid_is_loan" : {
          "type" : "long"
        },
        "asiuid_is_open_bnc" : {
          "type" : "long"
        },
        "asiuid_is_social_security_card" : {
          "type" : "integer"
        },
        "asiuid_is_tax_card" : {
          "type" : "integer"
        },
        "asiuid_min_repayment_date" : {
          "type" : "long"
        },
        "asiuid_openpay_first_create_time" : {
          "type" : "long"
        },
        "asiuid_openpay_success_cnt" : {
          "type" : "long"
        },
        "asiuid_order_full_pay_success_first_time" : {
          "type" : "long"
        },
        "asiuid_order_installment_pay_last_order_type" : {
          "type" : "long"
        },
        "asiuid_order_installment_pay_success_first_time" : {
          "type" : "long"
        },
        "asiuid_order_last_pay_success_time" : {
          "type" : "long"
        },
        "asiuid_order_last_pay_type" : {
          "type" : "long"
        },
        "asiuid_pid_cash_installment_last_success_time" : {
          "type" : "long"
        },
        "asiuid_pid_cash_installment_success_cnt" : {
          "type" : "long"
        },
        "asiuid_pid_cash_load_cnt" : {
          "type" : "integer"
        },
        "asiuid_pid_cash_loan_last_success_time" : {
          "type" : "long"
        },
        "asiuid_pid_loan_curr_overdue" : {
          "type" : "integer"
        },
        "asiuid_transfer_success_cnt" : {
          "type" : "long"
        },
        "bnc_account_open_status" : {
          "type" : "long"
        },
        "bnc_account_open_time" : {
          "type" : "long"
        },
        "bnc_is_cif_customer" : {
          "type" : "long"
        },
        "bnc_is_pay" : {
          "type" : "long"
        },
        "bnc_is_transfer" : {
          "type" : "long"
        },
        "bnc_is_transfer_in" : {
          "type" : "long"
        },
        "bnc_is_vsip_balance_left" : {
          "type" : "long"
        },
        "bnc_last_pay_time" : {
          "type" : "long"
        },
        "bnc_last_transfer_in_time" : {
          "type" : "long"
        },
        "bnc_last_transfer_time" : {
          "type" : "long"
        },
        "category" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "consumption_installment_applied_times" : {
          "type" : "long"
        },
        "consumption_installment_credit_type" : {
          "type" : "long"
        },
        "country_id" : {
          "type" : "long"
        },
        "created_order_count" : {
          "properties" : {
            "full_pay_cnt" : {
              "type" : "long"
            },
            "installment_pay_cnt" : {
              "type" : "long"
            }
          }
        },
        "credit_limit" : {
          "type" : "long"
        },
        "data_balance" : {
          "type" : "long"
        },
        "docId" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "education_level" : {
          "type" : "long"
        },
        "first_cheap_buy_order_create_time" : {
          "type" : "long"
        },
        "first_order_create_time" : {
          "type" : "long"
        },
        "first_order_customer_price" : {
          "type" : "long"
        },
        "friend_invite" : {
          "properties" : {
            "asiuid_particpt_invt_cnt_15d" : {
              "type" : "long"
            },
            "asiuid_particpt_invt_cnt_180d" : {
              "type" : "long"
            },
            "asiuid_particpt_invt_cnt_1d" : {
              "type" : "long"
            },
            "asiuid_particpt_invt_cnt_30d" : {
              "type" : "long"
            },
            "asiuid_particpt_invt_cnt_60d" : {
              "type" : "long"
            },
            "asiuid_particpt_invt_cnt_7d" : {
              "type" : "long"
            },
            "asiuid_particpt_invt_cnt_90d" : {
              "type" : "long"
            }
          }
        },
        "friend_invite_credit" : {
          "properties" : {
            "asiuid_invt_succ_cnt_15d" : {
              "type" : "integer"
            },
            "asiuid_invt_succ_cnt_180d" : {
              "type" : "integer"
            },
            "asiuid_invt_succ_cnt_30d" : {
              "type" : "integer"
            },
            "asiuid_invt_succ_cnt_3d" : {
              "type" : "integer"
            },
            "asiuid_invt_succ_cnt_60d" : {
              "type" : "integer"
            },
            "asiuid_invt_succ_cnt_7d" : {
              "type" : "integer"
            },
            "asiuid_invt_succ_cnt_90d" : {
              "type" : "integer"
            }
          }
        },
        "gender" : {
          "type" : "boolean"
        },
        "get_current_repayment_day" : {
          "type" : "boolean"
        },
        "installment_payoff_periods" : {
          "type" : "long"
        },
        "kyc_grade" : {
          "type" : "long"
        },
        "last_access_date" : {
          "type" : "long"
        },
        "last_access_day" : {
          "properties" : {
            "last_1d_access_day_cnt" : {
              "type" : "long"
            },
            "last_30d_access_day_cnt" : {
              "type" : "long"
            },
            "last_3d_access_day_cnt" : {
              "type" : "long"
            },
            "last_60d_access_day_cnt" : {
              "type" : "long"
            },
            "last_7d_access_day_cnt" : {
              "type" : "long"
            }
          }
        },
        "last_order_create_time" : {
          "type" : "long"
        },
        "loan_market_user_level" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "occupation" : {
          "type" : "long"
        },
        "order_total_customer_price" : {
          "type" : "long"
        },
        "pay_success_order" : {
          "properties" : {
            "full_pay_entity_success_order_cnt" : {
              "type" : "long"
            },
            "full_pay_virtual_success_order_cnt" : {
              "type" : "long"
            },
            "installment_pay_entity_success_order_cnt" : {
              "type" : "long"
            },
            "installment_pay_virtual_success_order_cnt" : {
              "type" : "long"
            }
          }
        },
        "pdl_avg_loan_period" : {
          "type" : "float"
        },
        "pdl_avg_mount" : {
          "type" : "long"
        },
        "pdl_current_bill" : {
          "type" : "long"
        },
        "pdl_last_amount" : {
          "type" : "long"
        },
        "pdl_periods" : {
          "type" : "long"
        },
        "pdl_recent_dpl_time" : {
          "type" : "long"
        },
        "pdl_recent_pay_time" : {
          "type" : "long"
        },
        "pdl_risk_level" : {
          "type" : "long"
        },
        "pid_cash_credit_applied_times" : {
          "type" : "long"
        },
        "pid_cash_credit_date" : {
          "type" : "long"
        },
        "pid_cash_credit_status" : {
          "type" : "long"
        },
        "pid_cash_last_loan_successful" : {
          "type" : "long"
        },
        "pid_cash_loan_applied_times" : {
          "type" : "long"
        },
        "pid_cash_loaned_times" : {
          "properties" : {
            "pid_cash_loaned_180d_times" : {
              "type" : "long"
            },
            "pid_cash_loaned_30d_times" : {
              "type" : "long"
            },
            "pid_cash_loaned_60d_times" : {
              "type" : "long"
            },
            "pid_cash_loaned_90d_times" : {
              "type" : "long"
            }
          }
        },
        "pid_cash_loaning" : {
          "type" : "long"
        },
        "pid_installment_last_loan_successful" : {
          "type" : "long"
        },
        "pid_installment_loaning" : {
          "type" : "long"
        },
        "pid_largeloan_available" : {
          "type" : "long"
        },
        "pid_shortloan_available" : {
          "type" : "long"
        },
        "referrer_uid" : {
          "type" : "boolean"
        },
        "register_time" : {
          "type" : "long"
        },
        "sign_continue_count" : {
          "properties" : {
            "asiuid_sign_continue_cnt_15d" : {
              "type" : "integer"
            },
            "asiuid_sign_continue_cnt_180d" : {
              "type" : "integer"
            },
            "asiuid_sign_continue_cnt_1d" : {
              "type" : "integer"
            },
            "asiuid_sign_continue_cnt_30d" : {
              "type" : "integer"
            },
            "asiuid_sign_continue_cnt_60d" : {
              "type" : "integer"
            },
            "asiuid_sign_continue_cnt_7d" : {
              "type" : "integer"
            },
            "asiuid_sign_continue_cnt_90d" : {
              "type" : "integer"
            }
          }
        },
        "status" : {
          "type" : "boolean"
        },
        "success_order_cnt" : {
          "type" : "long"
        },
        "uid" : {
          "type" : "long"
        },
        "uid_postfix" : {
          "type" : "long"
        },
        "update_time" : {
          "type" : "long"
        },
        "user_channel" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "user_come_from_channel" : {
          "type" : "long"
        },
        "user_current_device_type" : {
          "type" : "long"
        },
        "user_current_plat" : {
          "type" : "long"
        },
        "user_current_uninstalled_app" : {
          "properties" : {
            "uninstalled_app_adakami" : {
              "type" : "long"
            },
            "uninstalled_app_cairin" : {
              "type" : "long"
            },
            "uninstalled_app_easycash" : {
              "type" : "long"
            },
            "uninstalled_app_kredinesia" : {
              "type" : "long"
            },
            "uninstalled_app_kta_kilat" : {
              "type" : "long"
            },
            "uninstalled_app_modal_duit" : {
              "type" : "long"
            },
            "uninstalled_app_one_hope" : {
              "type" : "long"
            },
            "uninstalled_app_rupiah_cepat" : {
              "type" : "long"
            },
            "uninstalled_app_solusikita" : {
              "type" : "long"
            },
            "uninstalled_app_tunai_cepat" : {
              "type" : "long"
            },
            "uninstalled_app_wadah_pinjaman" : {
              "type" : "long"
            }
          }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![RhodesOfficial](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rhodesofficial/32/90633_2.png) [@RhodesOfficial](https://discuss.elastic.co/u/RhodesOfficial)\
**Post date:** [June 23, 2021, 12:33pm UTC](https://discuss.elastic.co/t/what-causes-the-count-function-to-return-an-empty-result/276613/10 "2021-06-23T12:33:08Z")

</div>

```auto
GET /_sql?format=json
{
  "query": "select count(1) from user_selector_tag_increment_5 data where ( data.asiuid_afi_repayment_day = 0 and data.pid_installment_loaning = 0 and data.asiuid_afi_repayment_day = 0 and data.pid_installment_loaning = 0 and data.pdl_current_bill = 32503564800000 and data.user_current_plat = 1 and ( data.uid_postfix >= 00 and data.uid_postfix <= 89 ) and ( data.pid_cash_credit_status = 1 and ( data.pdl_risk_level = '201' or data.pdl_risk_level = '203' or data.pdl_risk_level = '202' or data.pdl_risk_level = '204' or data.pdl_risk_level = '205' or data.pdl_risk_level = '206' or data.pdl_risk_level = '207' or data.pdl_risk_level = '208' or data.pdl_risk_level = '209' ) and ( data.pid_cash_loaned_times.pid_cash_loaned_180d_times >= 0 and data.pid_cash_loaned_times.pid_cash_loaned_180d_times <= 0 ) ) and ( data.pid_cash_credit_status = 1 and ( data.pdl_risk_level = '201' or data.pdl_risk_level = '202' or data.pdl_risk_level = '203' or data.pdl_risk_level = '204' or data.pdl_risk_level = '205' or data.pdl_risk_level = '206' or data.pdl_risk_level = '207' or data.pdl_risk_level = '208' or data.pdl_risk_level = '209' ) and ( data.pid_cash_loaned_times.pid_cash_loaned_180d_times >= 1 and data.pid_cash_loaned_times.pid_cash_loaned_180d_times <= 1000 ) and ( data.pdl_recent_pay_time <= '1621750439000' ) ) )",
  "columnar": true
}

```

```auto
#! Elasticsearch built-in security features are not enabled. Without authentication, your cluster could be accessible to anyone. See https://www.elastic.co/guide/en/elasticsearch/reference/7.13/security-minimal-setup.html to enable security.
{
  "columns" : [
    {
      "name" : "count(1)",
      "type" : "long"
    }
  ],
  "values" : [
    []
  ]
}

```

I changed the version to the latest 7.13.2, but the query to an empty index's result is still empty.  
Above is my full reproduction including index creation/mapping and query.  
All of sentence can be executed directly on Kibana Dev Tools Console~

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 21, 2021, 12:33pm UTC](https://discuss.elastic.co/t/what-causes-the-count-function-to-return-an-empty-result/276613/11 "2021-07-21T12:33:52Z")

</div>

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