Skip to content

Commit 225cd1c

Browse files
youdaoSuyoudaoSu
committed
fix: include cross-JOIN ON referenced columns in count subquery for MySQL
When HEAD (count pagination) uses multiple JOINs, the subquery for each JOIN table only selected its own ON key (on.getKey()). If another JOIN's ON clause references a column of this table (e.g. Warehose_info.id@ = /Location_info/warehouse_id), that column was missing from the subquery, causing an Unknown column error on MySQL. Now, for MySQL-family databases, also collect the targetKey of any ON clause (from any JOIN in the list) whose target table/alias matches the current JOIN table. PostgreSQL keeps its SELECT * fallback. issue #824 fixes #824
1 parent dd1ef5a commit 225cd1c

2 files changed

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

0 commit comments

Comments
 (0)