GeezerVison

The World Wide Web, restored to 800 pixels of glory.

GeezerVison is showing github.com — fix(sql): count groups for HEAD requests with @group by lnuxe · Pull Request #884 · APIJSON/APIJSON

Conversation

lnuxe commented Nov 11, 2026

Copy link

Copy Markdown

背景

issue #624 反馈：[] 里带 @group 时，total 拿到的是组内数量而不是分组后的总条数。

复现请求（issue 里的原始 JSON）：

{
  "[]": {
    "VehicleList_count": {
      "vehicleid$": "鄂QD24S8_0%",
      "sumdate%": "2023-10-15,2023-10-17",
      "@group": "vehicleid,sumdate",
      "@column": "vehicleid;sumdate;max(cnt):maxcnt",
      "@order": "maxcnt-"
    },
    "page": 0,
    "count": 5
  },
  "total@": "/[]/total"
}

原因

gainColumnString 在 HEAD 请求下把 SELECT 换成 count(*)，但 gainConditionString 对 HEAD 仍然拼接 GROUP BY，于是生成：

SELECT count(*) AS `count` FROM `sys`.`Vehicle` GROUP BY `vid`,`sdate`

这条 SQL 返回的是每一组的行数（多行），不是分组后的组数，所以 total 语义错位。Oracle 分支（isOracle() || isDameng() || isKingBaseOracle()）早就用外层 count(*) 包了一层子查询，其它库没有对应处理。

修复

在 gainSelectSQL 里对齐 Oracle 的写法：@group 非空且是 HEAD 方法时，把原语句包成子查询再 count(*)：

if (StringUtil.isNotEmpty(getGroup(), true) && RequestMethod.isHeadMethod(getMethod(), true)) {
    String sql = "SELECT " + (getCache() == JSONMap.CACHE_RAM ? "SQL_NO_CACHE " : "")
            + column + " FROM " + gainConditionString(tablePath, this);
    return explain + "SELECT count(*)" + gainAs() + getQuote() + JSONResponse.KEY_COUNT + getQuote()
            + " FROM (" + sql + ")" + gainAs() + gainSQLAliasWithQuote();
}

MySQL / SQL Server 要求 FROM 子查询有别名，用已有的 gainSQLAliasWithQuote() 补上，各库引号风格自动适配。

修复后：

-- MySQL
SELECT count(*) AS `count` FROM (SELECT count(*) AS `count` FROM `sys`.`Vehicle` GROUP BY `vid`,`sdate`) AS `Vehicle`
-- PostgreSQL
SELECT count(*) AS "count" FROM (SELECT count(*) AS "count" FROM "sys"."Vehicle" GROUP BY "vid","sdate") AS "Vehicle"

测试

新增 AbstractSQLConfigGroupCountTest，覆盖：

MySQL / PostgreSQL / SQL Server / DB2 分组 HEAD 都断言外层 count(*) + 子查询结构，且外层不再出现 GROUP BY
没有 @group 时保持原来的单层 count(*)，不包子查询
GET + @group 保持原样，不被改成 count(*)

双通道验证

修复前（git checkout 还原 AbstractSQLConfig.java）：

[ERROR] AbstractSQLConfigGroupCountTest.countsGroupsInsteadOfRowsInEachGroupForMySQL:87
    HEAD with @group on MYSQL: SELECT count(*) AS `count` FROM `sys`.`Vehicle` GROUP BY `vid`,`sdate`
[ERROR] Tests run: 6, Failures: 4, Errors: 0, Skipped: 0

修复后：

[INFO] Tests run: 6, Failures: 0, Errors: 0, Skipped: 0 -- in apijson.orm.AbstractSQLConfigGroupCountTest
[INFO] Tests run: 44, Failures: 0, Errors: 0, Skipped: 0
[INFO] BUILD SUCCESS

全量 mvn test 44 个用例全绿（原有 38 + 新增 6），无回归。

fix #624

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.

All reactions


          fix(sql): count groups for HEAD requests with @group

fd1f15b

HEAD requests replaced the SELECT with count(*) but kept GROUP BY, so
SELECT count(*) AS count FROM Vehicle GROUP BY vid,sdate returned one row per
group instead of the number of groups. That made total in grouped pagination
report the rows inside a group, as reported in issue 624.

Wrap the grouped statement in an outer count(*) subquery, the same way the
Oracle branch already does. MySQL and SQL Server require an alias on a FROM
subquery, so the subquery is aliased with gainSQLAliasWithQuote().

Tests assert the outer count(*) plus subquery shape for MySQL, PostgreSQL,
SQL Server and DB2, and keep the plain count(*) and plain GET paths
unchanged.

fix APIJSON#624

This branch has not been deployed

No deployments

Reviewers

No reviews

Uh oh!

There was an error while loading. Please reload this page.

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

分组后再分页，发现total数量不对，后台sql发现统计的是组内数量，请问有其他查询方法吗？

Uh oh!

There was an error while loading. Please reload this page.

1 participant

Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.

You are visitor 01402503 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.