Visitar URL original
fix: include cross-JOIN ON referenced columns in count subquery for M… · APIJSON/APIJSON@813ced6 · GitHub
Skip to content

Commit 813ced6

Browse files
TommyLemonTommyLemon
authored
fix: include cross-JOIN ON referenced columns in count subquery for MySQL, thx to youdaoSu #878 
2 parents dd1ef5a + 498fcf5 commit 813ced6

2 files changed

Lines changed: 282 additions & 1 deletion

File tree

‎src/main/java/apijson/orm/AbstractSQLConfig.java‎

Lines changed: 22 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6585,9 +6585,30 @@ LEFT JOIN ( SELECT count(*) AS count FROM sys.Comment ) AS Comment ON Comment.m
65856585
///* SELECT count(*) AS count FROM sys.Moment AS Moment
65866586
// LEFT JOIN ( SELECT * FROM sys.Comment ) AS Comment ON Comment.momentId = Moment.id LIMIT 1 OFFSET 0 */
65876587
if (column != null && joinConfig.isMSQL()) { // 暂时这样兼容 PostgreSQL 等不支持 SELECT 中不包含对应 key 的隐式 ON 关联字段的数据库
6588+
// 当前副表自身的关联键（joinConfig 是当前副表）
6589+
Set<String> columnSet = new LinkedHashSet<>();
65886590
for (On on : onList) {
6589-
column.add(on.getKey()); // TODO PostgreSQL 等需要找到具体的 targetTable 对应 targetKey 来加到 SELECT，比直接 SELECT * 性能更好
6591+
columnSet.add(on.getKey());
65906592
}
6593+
// issue #824：多表 JOIN 时，其它副表的 ON 可能引用当前副表的字段（如
6594+
// Warehose_info.id@ = /Location_info/warehouse_id 引用 Location_info.warehouse_id）。
6595+
// 只收集当前副表自身的 on.getKey() 会漏掉被引用的字段，生成子查询缺列导致 Unknown column。
6596+
// 这里把所有 JOIN 的 ON 里 target 指向当前副表的 targetKey 也补进 SELECT 列。
6597+
for (Join<T, M, L> other : joinList) {
6598+
List<On> otherOnList = other.getOnList();
6599+
if (otherOnList == null) {
6600+
continue;
6601+
}
6602+
// 当前副表的别名（含主表引用自身场景）
6603+
String currentAlias = SQLConfig.gainSQLAlias(table, alias);
6604+
for (On otherOn : otherOnList) {
6605+
if (currentAlias.equals(SQLConfig.gainSQLAlias(otherOn.getTargetTable(), otherOn.getTargetAlias()))) {
6606+
columnSet.add(otherOn.getTargetKey());
6607+
}
6608+
}
6609+
}
6610+
column.addAll(columnSet);
6611+
// TODO PostgreSQL 等需要找到具体的 targetTable 对应 targetKey 来加到 SELECT，比直接 SELECT * 性能更好
65916612
}
65926613

65936614
joinConfig.setMethod(GET); // 子查询不能为 SELECT count(*) ，而应该是 SELECT momentId

‎src/test/java/apijson/orm/AbstractSQLConfigJoinCountTest.java‎

Lines changed: 260 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,260 @@
1+
package apijson.orm;
2+
3+
import apijson.JSON;
4+
import apijson.JSONParser;
5+
import apijson.RequestMethod;
6+
import org.junit.AfterClass;
7+
import org.junit.BeforeClass;
8+
import org.junit.Test;
9+
10+
import java.util.ArrayList;
11+
import java.util.LinkedHashMap;
12+
import java.util.List;
13+
import java.util.Map;
14+
15+
import static org.junit.Assert.assertTrue;
16+
17+
/**
18+
* 复现 issue #824：多表 JOIN + HEAD(count 分页) 子查询字段缺失。
19+
* - PostgreSQL：LEFT JOIN 子查询回退 SELECT *（main 已修复原报告场景，此处作回归）
20+
* - MySQL：子查询只选自身关联键，缺被其它 ON 引用的字段，仍会报 Unknown column（本次修复）
21+
*/
22+
public class AbstractSQLConfigJoinCountTest {
23+
private static final String MAIN_TABLE = "Inventory";
24+
private static final String JOIN_TABLE = "Location_info";
25+
private static final String JOIN_TABLE2 = "Warehose_info";
26+
private static final String DATABASE = SQLConfig.DATABASE_POSTGRESQL;
27+
28+
private static JSONParser<? extends Map<String, Object>, ? extends List<Object>> previousJSONParser;
29+
30+
@BeforeClass
31+
public static void installJSONParser() {
32+
previousJSONParser = JSON.DEFAULT_JSON_PARSER;
33+
JSON.DEFAULT_JSON_PARSER = new JSONParser<Map<String, Object>, List<Object>>() {
34+
@Override
35+
public Map<String, Object> createJSONObject() {
36+
return new LinkedHashMap<>();
37+
}
38+
39+
@Override
40+
public List<Object> createJSONArray() {
41+
return new ArrayList<>();
42+
}
43+
44+
@Override
45+
public Object parse(Object json) {
46+
return json;
47+
}
48+
49+
@Override
50+
@SuppressWarnings("unchecked")
51+
public Map<String, Object> parseObject(Object json) {
52+
return (Map<String, Object>) json;
53+
}
54+
55+
@Override
56+
public <T> T parseObject(Object json, Class<T> clazz) {
57+
return clazz.cast(json);
58+
}
59+
60+
@Override
61+
@SuppressWarnings("unchecked")
62+
public List<Object> parseArray(Object json) {
63+
return (List<Object>) json;
64+
}
65+
66+
@Override
67+
@SuppressWarnings("unchecked")
68+
public <T> List<T> parseArray(Object json, Class<T> clazz) {
69+
return (List<T>) json;
70+
}
71+
72+
@Override
73+
public String toJSONString(Object obj, boolean format) {
74+
return String.valueOf(obj);
75+
}
76+
};
77+
}
78+
79+
@AfterClass
80+
public static void restoreJSONParser() {
81+
JSON.DEFAULT_JSON_PARSER = previousJSONParser;
82+
}
83+
84+
/**
85+
* 复现 issue #824 的 count 分页 SQL（PostgreSQL 场景）：
86+
* SELECT count(*) AS "count" FROM "Inventory" AS "Inventory"
87+
* LEFT JOIN ( SELECT * FROM "Location_info" ) AS "Location_info"
88+
* ON "Location_info"."code" = "Inventory"."location_code"
89+
* LEFT JOIN ( SELECT * FROM "Warehose_info" ) AS "Warehose_info"
90+
* ON "Warehose_info"."id" = "Location_info"."warehouse_id"
91+
*
92+
* main 分支已对 PostgreSQL 回退为 SELECT *（isMSQL 门控），子查询不缺列，PG 不再报错。
93+
* 本用例断言该行为（回归保护）。
94+
*/
95+
@Test
96+
public void headJoinCountPostgreSQLFallsBackToSelectStar() throws Exception {
97+
String sql = buildHeadJoinSQL();
98+
99+
System.out.println("Generated SQL:\n" + sql + "\n");
100+
101+
// 断言：LEFT JOIN 子查询结构存在
102+
int leftJoin1 = sql.indexOf("LEFT JOIN ( SELECT");
103+
assertTrue("应为 LEFT JOIN 子查询结构: " + sql, leftJoin1 >= 0);
104+
105+
// 副表1子查询部分（PG 应回退 SELECT *，天然包含 code + warehouse_id）
106+
String sub1 = extractSubquery(sql, "Location_info");
107+
System.out.println("Subquery 1: " + sub1);
108+
assertTrue("PG 下 Location_info 子查询应回退 SELECT *（含所有列，天然覆盖 ON 引用）: " + sub1,
109+
sub1.contains("SELECT *"));
110+
111+
// 副表2子查询部分
112+
String sub2 = extractSubquery(sql, "Warehose_info");
113+
System.out.println("Subquery 2: " + sub2);
114+
assertTrue("PG 下 Warehose_info 子查询应回退 SELECT *: " + sub2,
115+
sub2.contains("SELECT *"));
116+
117+
// ON 条件完整性
118+
assertTrue("ON 应引用 Location_info.code = Inventory.location_code: " + sql,
119+
sql.contains("\"Location_info\".\"code\" = \"Inventory\".\"location_code\""));
120+
assertTrue("ON 应引用 Warehose_info.id = Location_info.warehouse_id: " + sql,
121+
sql.contains("\"Warehose_info\".\"id\" = \"Location_info\".\"warehouse_id\""));
122+
}
123+
124+
/**
125+
* 回归：MySQL 下副表子查询只含自身关联键 code/id（isMSQL 门控逻辑）。
126+
* 本用例断言当前行为，暴露 MySQL 多 join 交叉引用 bug（第二个 ON 引用
127+
* Location_info.warehouse_id，但子查询没有该列 -> Unknown column）。
128+
*/
129+
@Test
130+
public void headJoinCountMySQLOnlySelectsSelfOnKey() throws Exception {
131+
String sql = buildHeadJoinSQLWithDatabase(SQLConfig.DATABASE_MYSQL);
132+
133+
System.out.println("MySQL SQL:\n" + sql + "\n");
134+
135+
String sub1 = extractSubquery(sql, "Location_info");
136+
assertTrue("MySQL 下 Location_info 子查询应包含 code: " + sub1, sub1.contains("`code`"));
137+
// 关键：MySQL 下子查询缺 warehouse_id，ON 引用它 → 复现 Unknown column
138+
assertTrue("MySQL 下 Location_info 子查询应包含 warehouse_id（issue #824 反向场景，被 ON 引用的列）: " + sub1,
139+
sub1.contains("`warehouse_id`"));
140+
}
141+
142+
private static String buildHeadJoinSQL() throws Exception {
143+
return buildHeadJoinSQLWithDatabase(DATABASE);
144+
}
145+
146+
private static String extractSubquery(String sql, String table) {
147+
String marker = "JOIN ( ";
148+
int joinIdx = sql.indexOf(marker);
149+
while (joinIdx >= 0) {
150+
int closeIdx = sql.indexOf(" ) ", joinIdx);
151+
if (closeIdx < 0) {
152+
break;
153+
}
154+
String sub = sql.substring(joinIdx + marker.length(), closeIdx);
155+
if (sub.contains(table)) {
156+
return sub;
157+
}
158+
joinIdx = sql.indexOf(marker, closeIdx);
159+
}
160+
return "";
161+
}
162+
163+
private static String buildHeadJoinSQLWithDatabase(String database) throws Exception {
164+
// 主表
165+
Map<String, Object> mainRequest = new LinkedHashMap<>();
166+
167+
// join 副表1：Location_info.code@ = /Inventory/location_code
168+
// 注意：request 中不能包含 @ 引用键（真实解析器把它们剥离进 onList），
169+
// 只把 onList 作为唯一关联信息来源（与 AbstractParser.onJoinParse 一致）
170+
Map<String, Object> joinRequest1 = new LinkedHashMap<>();
171+
172+
// join 副表2：Warehose_info.id@ = /Location_info/warehouse_id
173+
Map<String, Object> joinRequest2 = new LinkedHashMap<>();
174+
175+
// 组装 joinList（on 定义显式传入，与 AbstractParser.onJoinParse 剥离后的 onList 等价）
176+
List<Join<Long, Map<String, Object>, List<Object>>> joinList = new ArrayList<>();
177+
joinList.add(newJoin("</Location_info/code@", JOIN_TABLE, "code@", "/Inventory/location_code"));
178+
joinList.add(newJoin("</Warehose_info/id@", JOIN_TABLE2, "id@", "/Location_info/warehouse_id"));
179+
180+
AbstractSQLConfig.Callback<Long, Map<String, Object>, List<Object>> callback =
181+
new AbstractSQLConfig.SimpleCallback<Long, Map<String, Object>, List<Object>>() {
182+
@Override
183+
public SQLConfig<Long, Map<String, Object>, List<Object>> getSQLConfig(RequestMethod method,
184+
String database, String datasource, String namespace, String catalog, String schema,
185+
String table) {
186+
return new AbstractSQLConfig<Long, Map<String, Object>, List<Object>>(method, table) {
187+
@Override
188+
public String gainDBVersion() {
189+
return "16.0";
190+
}
191+
192+
@Override
193+
public String gainDBUri() {
194+
return "jdbc:postgresql://localhost/test";
195+
}
196+
197+
@Override
198+
public String gainDBAccount() {
199+
return "test";
200+
}
201+
202+
@Override
203+
public String gainDBPassword() {
204+
return "test";
205+
}
206+
};
207+
}
208+
};
209+
210+
SQLConfig<Long, Map<String, Object>, List<Object>> config =
211+
AbstractSQLConfig.newSQLConfig(RequestMethod.HEAD, MAIN_TABLE, null, mainRequest, null, false, callback);
212+
config.setDatabase(database);
213+
214+
config = AbstractSQLConfig.parseJoin(RequestMethod.HEAD, config, joinList, callback);
215+
216+
return config.gainSQL(false);
217+
}
218+
219+
/**手工构建 Join（模拟 AbstractParser.onJoinParse 剥离 @ 引用键后的产物）
220+
* @param path join 路径 </Table/key@
221+
* @param table 副表
222+
* @param originKey 关联键（含 @）
223+
* @param targetPath 引用目标 /TargetTable/targetKey
224+
*/
225+
private static Join<Long, Map<String, Object>, List<Object>> newJoin(String path, String table,
226+
String originKey, String targetPath) throws Exception {
227+
Join<Long, Map<String, Object>, List<Object>> j = new Join<>();
228+
j.setPath(path);
229+
j.setJoinType("<");
230+
j.setTable(table);
231+
j.setRequest(new LinkedHashMap<>()); // 真实解析器剥离 @ 引用键，request 为空
232+
233+
List<Join.On> onList = new ArrayList<>();
234+
Join.On on = new Join.On();
235+
// originKey = "code@" -> key = "code"（setKeyAndType 会剥掉 @）
236+
on.setKeyAndType("<", table, originKey);
237+
on.setOriginKey(originKey);
238+
on.setOriginValue(targetPath);
239+
240+
// 解析 /TargetTable/targetKey
241+
String path0 = targetPath.startsWith("/") ? targetPath.substring(1) : targetPath;
242+
int idx = path0.lastIndexOf("/");
243+
String targetKey = idx < 0 ? path0 : path0.substring(idx + 1);
244+
String targetTableKey = idx < 0 ? "" : path0.substring(0, idx);
245+
// targetTableKey 可能带别名 TargetTable:alias
246+
int cIdx = targetTableKey.indexOf(":");
247+
String targetTable = cIdx < 0 ? targetTableKey : targetTableKey.substring(0, cIdx);
248+
String targetAlias = cIdx < 0 ? null : targetTableKey.substring(cIdx + 1);
249+
250+
on.setTargetTableKey(targetTableKey);
251+
on.setTargetTable(targetTable);
252+
on.setTargetAlias(targetAlias);
253+
on.setTargetKey(targetKey);
254+
255+
onList.add(on);
256+
j.setOnList(onList);
257+
258+
return j;
259+
}
260+
}

0 commit comments

Comments
 (0)