Sitelet https://github.com/APIJSON/APIJSON/pull/885/files
Skip to content

fix(sql): skip TSQL paging for aggregates without @group #885

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
lnuxe wants to merge 1 commit into
base: main
Choose a base branch
from
Open

fix(sql): skip TSQL paging for aggregates without @group #885

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
42 changes: 41 additions & 1 deletion src/main/java/apijson/orm/AbstractSQLConfig.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2119,7 +2119,9 @@ public String gainOrderString(boolean hasPrefix) {
// return (hasPrefix ? " ORDER BY " : "") + StringUtil.concat(order, joinOrder, ", ");
// }

if (getCount() > 0 && (isSQLServer() || isKingBaseSQLServer() || isDb2())) {
// 聚合函数不带 GROUP BY 时只有一行，不能补 ORDER BY，否则 SQL Server/DB2 报列未包含在聚合函数内
if (getCount() > 0 && (isSQLServer() || isKingBaseSQLServer() || isDb2())
&& isAggregateWithoutGroup() == false) {
// Oracle, SQL Server, DB2 的 OFFSET 必须加 ORDER BY.去掉Oracle，Oracle里面没有offset关键字

// String[] ss = StringUtil.split(order);
Expand Down Expand Up @@ -3086,6 +3088,40 @@ public int getOffset() {
public static int getOffset(int page, int count) {
return page*count;
}
/**SELECT 是否只有一个不带 GROUP BY 的聚合函数，例如 @column:"max(date):date"
* 这种 SQL 永远只返回一行，不能再用 ORDER BY id + OFFSET/FETCH 分页，
* 否则 SQL Server/DB2 报 order by 子句列 id 无效，该列没有包含在聚合函数或 group by 子句中
* @return
*/
public boolean isAggregateWithoutGroup() {
if (StringUtil.isNotEmpty(getGroup(), true)) {
return false;
}

List<String> column = getColumn();
if (column == null || column.size() != 1) {
return false;
}

String expression = column.get(0);
if (StringUtil.isEmpty(expression, true)) {
return false;
}

int index = expression.lastIndexOf(":");
if (index > 0) {
expression = expression.substring(0, index); // 去掉 :alias
}

int start = expression.indexOf("(");
if (start <= 0) {
return false;
}

String fun = expression.substring(0, start).trim().toLowerCase();
return SQL_AGGREGATE_FUNCTION_MAP.containsKey(fun);
}

/**获取限制数量
* @return
*/
Expand All @@ -3098,6 +3134,10 @@ public String gainLimitString() {
return "";
}

if (isAggregateWithoutGroup()) { // 聚合函数不带 GROUP BY 永远只返回一行，分页会引入非法的 ORDER BY
return "";
}

boolean isSurrealDB = isSurrealDB();
boolean isQuestDB = isQuestDB();
if (isSurrealDB || isQuestDB || isMilvus) {
Expand Down
196 changes: 196 additions & 0 deletions src/test/java/apijson/orm/AbstractSQLConfigAggregatePagingTest.java
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,196 @@
package apijson.orm;

import apijson.JSON;
import apijson.JSONMap;
import apijson.JSONParser;
import apijson.RequestMethod;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import static org.junit.Assert.assertTrue;

/**
* 复现 issue #648：SQL Server 查询聚合函数出错。
*
* 请求 { "Moment": { "@column": "max(date):date" } } 只返回一行，
* 但 SQL Server/DB2 的 OFFSET FETCH 要求 ORDER BY，gainOrderString 会补上
* ORDER BY "id"，生成
* SELECT max("date") AS "date" FROM "sys"."Moment" ORDER BY "id" OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY
* 数据库报 order by 子句列 id 无效，因为该列没有包含在聚合函数或 group by 子句中。
*/
public class AbstractSQLConfigAggregatePagingTest {
private static final String TABLE = "Moment";
private static JSONParser<? extends Map<String, Object>, ? extends List<Object>> previousJSONParser;

@BeforeClass
public static void installJSONParser() {
previousJSONParser = JSON.DEFAULT_JSON_PARSER;
JSON.DEFAULT_JSON_PARSER = new JSONParser<Map<String, Object>, List<Object>>() {
@Override
public Map<String, Object> createJSONObject() {
return new LinkedHashMap<>();
}

@Override
public List<Object> createJSONArray() {
return new ArrayList<>();
}

@Override
public Object parse(Object json) {
return json;
}

@Override
@SuppressWarnings("unchecked")
public Map<String, Object> parseObject(Object json) {
return (Map<String, Object>) json;
}

@Override
public <T> T parseObject(Object json, Class<T> clazz) {
return clazz.cast(json);
}

@Override
@SuppressWarnings("unchecked")
public List<Object> parseArray(Object json) {
return (List<Object>) json;
}

@Override
@SuppressWarnings("unchecked")
public <T> List<T> parseArray(Object json, Class<T> clazz) {
return (List<T>) json;
}

@Override
public String toJSONString(Object obj, boolean format) {
return String.valueOf(obj);
}
};
}

@AfterClass
public static void restoreJSONParser() {
JSON.DEFAULT_JSON_PARSER = previousJSONParser;
}

@Test
public void skipsOrderByForSQLServerAggregate() throws Exception {
assertNoOrderBy(sql(SQLConfig.DATABASE_SQLSERVER, "2022", 10));
}

@Test
public void skipsOrderByForSQLServerAggregateWithDefaultPage() throws Exception {
assertNoOrderBy(sql(SQLConfig.DATABASE_SQLSERVER, "2022", 1));
}

@Test
public void skipsOrderByForDB2Aggregate() throws Exception {
assertNoOrderBy(sql(SQLConfig.DATABASE_DB2, "11.5", 10));
}

@Test
public void skipsOrderByForKingbaseSQLServerAggregate() throws Exception {
assertNoOrderBy(sql(SQLConfig.DATABASE_KINGBASE_SQLSERVER, "V8", 10));
}

@Test
public void keepsOrderByForSQLServerNormalColumn() throws Exception {
String sql = sqlWithColumn(SQLConfig.DATABASE_SQLSERVER, "2022", "date", 10);
System.out.println("SQL Server plain column: " + flat(sql));

assertTrue("普通字段分页仍必须补 ORDER BY，否则 OFFSET FETCH 非法: " + sql,
sql.contains("ORDER BY"));
assertTrue("普通字段分页仍应带 OFFSET FETCH: " + sql, sql.contains("OFFSET"));
}

@Test
public void keepsOrderByForSQLServerAggregateWithGroup() throws Exception {
String sql = sqlWithGroup(SQLConfig.DATABASE_SQLSERVER, "2022", 10);
System.out.println("SQL Server aggregate with @group: " + flat(sql));

assertTrue("带 GROUP BY 的聚合可以按分组字段排序，应保留 ORDER BY: " + sql, sql.contains("ORDER BY"));
}

private static void assertNoOrderBy(String sql) {
String flat = flat(sql);
System.out.println("aggregate paging: " + flat);

assertTrue("聚合函数不带 GROUP BY 时不应补 ORDER BY，否则列不在聚合函数内会报错: " + flat,
!flat.contains("ORDER BY"));
assertTrue("聚合函数不带 GROUP BY 时不应再拼 OFFSET FETCH: " + flat, !flat.contains("OFFSET"));
assertTrue("仍应保留聚合函数本身: " + flat, flat.contains("max("));
}

private static String flat(String sql) {
return sql.replaceAll("[\\r\\n]+", " ");
}

private static String sql(String database, String version, int count) throws Exception {
return sqlWithColumn(database, version, null, count);
}

private static String sqlWithColumn(String database, String version, String column, int count)
throws Exception {
Map<String, Object> request = new LinkedHashMap<>();
request.put(JSONMap.KEY_COLUMN, column == null ? "max(date):date" : column);

return build(request, database, version, count);
}

private static String sqlWithGroup(String database, String version, int count) throws Exception {
Map<String, Object> request = new LinkedHashMap<>();
request.put(JSONMap.KEY_COLUMN, "max(date):date");
request.put(JSONMap.KEY_GROUP, "userId");

return build(request, database, version, count);
}

private static String build(Map<String, Object> request, String database, String version, int count)
throws Exception {
AbstractSQLConfig.Callback<Long, Map<String, Object>, List<Object>> callback =
new AbstractSQLConfig.SimpleCallback<Long, Map<String, Object>, List<Object>>() {
@Override
public SQLConfig<Long, Map<String, Object>, List<Object>> getSQLConfig(RequestMethod method,
String database, String datasource, String namespace, String catalog, String schema,
String table) {
return new AbstractSQLConfig<Long, Map<String, Object>, List<Object>>(method, table) {
@Override
public String gainDBVersion() {
return version;
}

@Override
public String gainDBUri() {
return "jdbc:test://localhost/test";
}

@Override
public String gainDBAccount() {
return "test";
}

@Override
public String gainDBPassword() {
return "test";
}
};
}
};

SQLConfig<Long, Map<String, Object>, List<Object>> config =
AbstractSQLConfig.newSQLConfig(RequestMethod.GET, TABLE, null, request, null, false, callback);
config.setDatabase(database);
config.setCount(count);
config.setPage(0);
return config.gainSQL(false);
}
}