ES-SQL 8.x版本当前在GROUP BY中使用substring(t.statis_date,0,8)，返回结果只返回10条，无法支持返回全部或LIMIT条数，能否帮忙解决。

[suncc800]

1、"SELECT t.statis_date AS CCOL1,SUM(t.amount2) AS CCOL2 FROM biz1d8vmyfwndbmrf9b7da2fohr5gh t WHERE 1=1 GROUP BY substring(t.statis_date,0,8) ORDER BY statis_date ASC LIMIT 100"; 在这段SQL中，有LIMIT当查询记录大于10条时只会返回10条记录，LIMIT不起作用
2、"SELECT t.statis_date AS CCOL1,SUM(t.amount2) AS CCOL2 FROM biz1d8vmyfwndbmrf9b7da2fohr5gh t WHERE 1=1 GROUP BY substring(t.statis_date,0,10) ORDER BY statis_date ASC LIMIT 2"; 在这段SQL中，有LIMIT当查询记录大于10条时只会返回10条记录，LIMIT不起作用
3、"SELECT t.statis_date AS CCOL1,SUM(t.amount2) AS CCOL2 FROM biz1d8vmyfwndbmrf9b7da2fohr5gh t WHERE 1=1 GROUP BY substring(t.statis_date,0,8) ORDER BY statis_date ASC"; 在这段SQL中，没有LIMIT，当查询实际结果大于10条时，也只返回10条记录。
4、当GROUP BY substring(t.statis_date,0,8)改成GROUP BY t.statis_date后查询正常，但是我们有业务需求需要只对YYYY-MM-DD HH:MM:SS这种日期只按照YYYY-MM-DD进行汇总的情况就搞不定了

5、执行日志
SELECT t.statis_date AS CCOL1,SUM(t.amount2) AS CCOL2 FROM bizocjte9ko5u9eim6o2ow76b72lzh t WHERE 1=1 GROUP BY substring(t.statis_date,0,10) LIMIT 100
ESSQL:********************************************
SELECT t.statis_date AS CCOL1,SUM(t.amount2) AS CCOL2 FROM bizocjte9ko5u9eim6o2ow76b72lzh t WHERE 1=1 GROUP BY substring(t.statis_date,0,10) LIMIT 100
12:52:16.712 [main] WARN o.a.c.httpclient.HttpMethodBase - Going to buffer response body of large or unknown size. Using getResponseBodyAsStream instead is recommended.
{"took":10,"timed_out":false,"_shards":{"total":6,"successful":6,"skipped":0,"failed":0},"hits":{"total":{"value":14,"relation":"eq"},"max_score":null,"hits":[]},"aggregations":{"field_1581081929":{"doc_count_error_upper_bound":0,"sum_other_doc_count":3,"buckets":[{"key":"2025-09-01","doc_count":2,"CCOL2":{"value":11001.0}},{"key":"2025-09-02","doc_count":1,"CCOL2":{"value":1002.0}},{"key":"2025-09-03","doc_count":1,"CCOL2":{"value":1003.0}},{"key":"2025-09-04","doc_count":1,"CCOL2":{"value":1004.0}},{"key":"2025-09-05","doc_count":1,"CCOL2":{"value":1005.0}},{"key":"2025-09-06","doc_count":1,"CCOL2":{"value":1006.0}},{"key":"2025-09-07","doc_count":1,"CCOL2":{"value":1007.0}},{"key":"2025-09-08","doc_count":1,"CCOL2":{"value":1008.0}},{"key":"2025-09-09","doc_count":1,"CCOL2":{"value":1009.0}},{"key":"2025-09-10","doc_count":1,"CCOL2":{"value":1010.0}}]}}}，查询出来理论有13条，但是查询结果只有10条，sum_other_doc_count":3显示还有3条未显示出来。

[shi-yuan]

terms中传入参数size

SELECT t.statis_date AS CCOL1,SUM(t.amount2) AS CCOL2 FROM biz1d8vmyfwndbmrf9b7da2fohr5gh t WHERE 1=1 GROUP BY terms(substring(t.statis_date,0,8),size=1000,alias='statis_date') ORDER BY statis_date ASC

{
  "from": 0,
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "must": [
              {
                "script": {
                  "script": {
                    "source": "1 == 1",
                    "lang": "painless"
                  },
                  "boost": 1
                }
              }
            ],
            "boost": 1
          }
        }
      ],
      "boost": 1
    }
  },
  "_source": {
    "includes": [
      "script",
      "SUM"
    ],
    "excludes": []
  },
  "stored_fields": "CCOL1",
  "script_fields": {
    "CCOL1": {
      "script": {
        "source": "doc['statis_date'].value",
        "lang": "painless"
      },
      "ignore_failure": false
    }
  },
  "sort": [
    {
      "statis_date": {
        "order": "asc"
      }
    }
  ],
  "aggregations": {
    "statis_date": {
      "terms": {
        "script": {
          "source": "def substring_507811161 = doc['statis_date'].value.substring(0,8);return substring_507811161;; return substring_507811161",
          "lang": "painless"
        },
        "size": 1000,
        "min_doc_count": 1,
        "shard_min_doc_count": 0,
        "show_term_doc_count_error": false,
        "order": [
          {
            "_count": "desc"
          },
          {
            "_key": "asc"
          }
        ]
      },
      "aggregations": {
        "CCOL2": {
          "sum": {
            "field": "amount2"
          }
        }
      }
    }
  }
}