APIJSON "join"语法和"key@"引用赋值语法冲突

环境信息

系统: macos 11.5.2 (20G95)
JDK: 1.8.0_261
数据库: MySQL 5.7.14
APIJSON: 4.7.2

问题描述 "join"语法和"key@"引用赋值语法冲突 join后会引用赋值语法也会根据返回数据的总条数继续做关联查询，返回的数据也不是以含join的sql解析出来的数据，而是引用复制生成sql返回的数据

当join语法中含有sum函数时不会返回sum结果，会返回引用赋值关联查询的数据

错误信息 以下为生成的4条sql

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 已生成 1 条 SQL execute startTime = 1637317650951 database = ; schema = ; sql = SELECT count(*) AS count FROM pre_sales_project_info AS PreSalesProjectInfo
LEFT JOIN ( SELECT project FROM code_info WHERE ( (del_flag = 0) AND (bound_type = 0) ) ) AS CodeInfo ON CodeInfo.project = PreSalesProjectInfo.id WHERE ( (PreSalesProjectInfo.deleted = 0) )

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 已生成 2 条 SQL execute startTime = 1637317651302 database = ; schema = ; sql = SELECT PreSalesProjectInfo.*, CodeInfo.project,CodeInfo.budget FROM pre_sales_project_info AS PreSalesProjectInfo
LEFT JOIN ( SELECT project,budget FROM code_info WHERE ( (del_flag = 0) AND (bound_type = 0) ) ) AS CodeInfo ON CodeInfo.project = PreSalesProjectInfo.id WHERE ( (PreSalesProjectInfo.deleted = 0) ) ORDER BY PreSalesProjectInfo.gmtCreate DESC LIMIT 2

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 已生成 3 条 SQL execute startTime = 1637317651343 database = ; schema = ; sql = SELECT project,budget FROM code_info WHERE ( (del_flag = 0) AND (bound_type = 0) AND (project = 1637054928436) ) LIMIT 1

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 已生成 4 条 SQL execute startTime = 1637317651356 database = ; schema = ; sql = SELECT project,budget FROM code_info WHERE ( (del_flag = 0) AND (bound_type = 0) AND (project = 1637051014468) ) LIMIT 1

Nov 19 '21 10:11 Shuai-S

第一个问题换最新版 4.8.0 再试试。 https://github.com/APIJSON/APIJSON-Demo/releases/tag/4.8.0

第二个没问题，只是 Postman 把 PresalesProjectInfo 和 CodeInfo 顺序倒过来了，用 APIAuto 就仍然保持顺序 http://apijson.cn/api/

Nov 20 '21 09:11 TommyLemon

第一个问题换最新版 4.8.0 再试试。 https://github.com/APIJSON/APIJSON-Demo/releases/tag/4.8.0

第二个没问题，只是 Postman 把 PresalesProjectInfo 和 CodeInfo 顺序倒过来了，用 APIAuto 就仍然保持顺序 http://apijson.cn/api/

你好，第一个问题4.8.0没有解决问题，还是会生成关联查询sql 一共会生成和执行一个count(*)的sql 一个left join的sql 和n条查询数据的关联查询sql

第二个问题可能是我描述的不清晰的问题，主要问题是返回的sum(budget) 不是根据left join查出来的budget，而是查询出来的progect=1的单条数据的budget

Nov 22 '21 03:11 Shuai-S

第一个问题我这边复现了 bug，当 JOIN 副表有 JOIN ON 外的条件时会导致用不上查询缓存：

去掉 "userId": 93793 这个条件后正常。

应该是这里处理有问题，导致没有把引用赋值键值对放在最前 https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractParser.java

目前的解决方法是把

				tableObj = newTableObj;
				request.put(tableKey, tableObj);

改成

				tableObj.clear();
				tableObj.putAll(newTableObj);

感谢反馈

Nov 22 '21 07:11 TommyLemon

导致用不上查询缓存：

你好，tableObj.clear(); tableObj.putAll(newTableObj);这个无效的：因为tableObj是无序的，替换成tableObj = newTableObj;后可以把引用赋值键值对放在最前,但是返回的关联数据一直是第一条，数据是重复的

第二个问题，去除副表查询条件后不返回sum(budget)字段

麻烦你们了，谢谢

Nov 22 '21 09:11 Shuai-S

com.alibaba.fastjson.JSONObject 构造方法可以传参 ordered = true 指定内部使用 LinkedHashMap 保证键值对顺序

Nov 22 '21 09:11 TommyLemon

第二个问题我这边也复现了，JOIN 中副表有 SQL 函数时不返回 SQL 函数对应的值

应该是 AbstractSQLExecutor.onPutColumon 内部逻辑问题（大概率）或者 JDBC 未返回（小概率） https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLExecutor.java

Nov 22 '21 09:11 TommyLemon

您好，大佬，我想了解一下这两个问题的大概修复时间节点或者有什么临时解决办法吗？谢谢🙏

Nov 23 '21 08:11 Shuai-S

您好，大佬，我想了解一下这两个问题的大概修复时间节点或者有什么临时解决办法吗？谢谢🙏

预计下周末，顺便会让 JOIN ON 新增支持多个条件。

第二个问题，应该只要把

SELECT Table2.column1, Table2.column2..  FROM Table1 LEFT JOIN (SELECT column1, column2 FROM Table2 ..) AS Table2

改成

SELECT Table2.*  FROM Table1 LEFT JOIN (SELECT column1, column2 FROM Table2 ..) AS Table2

就可以解决，源码在 AbstractSQLConfig.getColumnString

Nov 23 '21 10:11 TommyLemon

com.alibaba.fastjson.JSONObject 构造方法可以传参 ordered = true 指定内部使用 LinkedHashMap 保证键值对顺序

ResultSetMetaData.getTableName 副表的返回值为 ""，导致 APIJSON 在 LEFT/RIGHT JOIN 的副表中不返回 SQL 函数执行值

8EB69FC4FF8EFFEA4B21B68E25EE1EC6

https://bugs.mysql.com/bug.php?id=94585

第一个问题也是这个问题导致，副表所有字段对应 ResultSetMetaData.getTableName 是 ""，导致没用上缓存，然后重新查询，所以执行的 SQL 次数增加。

Demo 中 mysql-connector-java 8.0.22 复现，改为最新版 8.0.27 仍然复现。 https://mvnrepository.com/artifact/mysql/mysql-connector-java

改用 8.0.0 前一个版本 6.0.6 问题更大

PostgreSQL 一样会对 SQL 函数返回 ResultSetMetaData.getTableName 是 ""，看来得调整下副表字段存放策略。

Dec 04 '21 17:12 TommyLemon

已解决 https://github.com/Tencent/APIJSON/commit/00dae1b6bfa0de09b3e2465f62319c278524f375

目前可用 4.8.2 的 jar 包 https://github.com/APIJSON/APIJSON-Demo/tree/master/APIJSON-Java-Server/APIJSONBoot-MultiDataSource/libs

Dec 07 '21 08:12 TommyLemon

好的，谢谢解决问题，我试试

Dec 09 '21 03:12 Shuai-S

第一个问题我这边复现了 bug，当 JOIN 副表有 JOIN ON 外的条件时会导致用不上查询缓存

大佬，您好，其他问题都已经解决，还这个问题没有解决，当 JOIN 副表有 JOIN ON 外的条件时会导致用不上查询缓存

希望能再看一下，谢谢

Dec 16 '21 08:12 Shuai-S

导致用不上查询缓存：

你好，tableObj.clear(); tableObj.putAll(newTableObj);这个无效的：因为tableObj是无序的，替换成tableObj = newTableObj;后可以把引用赋值键值对放在最前,但是返回的关联数据一直是第一条，数据是重复的

第二个问题，去除副表查询条件后不返回sum(budget)字段

麻烦你们了，谢谢

还是这样改下看看，截屏里主表出现重复数据是因为主表与副表是一对多关系，用 LEFT JOIN 查到的结果集本身就会重复，可以用 GROUP BY 主表 id 或 DISTINCT 主表 id 来去重。

上次调试时发现有时副表数据为空没有缓存空对象 {} 到 AbstractSQLExecutor 的 cacheMap，导致重新查副表，然后又查到空的对象，有了多余的的 SQL 执行

Dec 16 '21 16:12 TommyLemon

@Shuai-S 解决了吗？

Dec 24 '21 18:12 TommyLemon

您好，最近这段时间比较忙，刚刚试了一下，具体问题是当JOIN 副表有 JOIN ON 外的条件时，不会走缓存 1111

是因为副表中没有对应的数据，缓存为空导致执行了多余的sql查询副表吗？

Dec 28 '21 08:12 Shuai-S

是因为副表中没有对应的数据，缓存为空导致执行了多余的sql查询副表吗？

看起来不像这个原因（虽然我之前看到这个原因导致额外的 SQL 查询），因为图中 SQL 缓存了 1 条，但返回的有数据的副表记录缺不止一条，图中就有 3 条了。

Dec 28 '21 14:12 TommyLemon

导致用不上查询缓存：

你好，tableObj.clear(); tableObj.putAll(newTableObj);这个无效的：因为tableObj是无序的，替换成tableObj = newTableObj;后可以把引用赋值键值对放在最前,但是返回的关联数据一直是第一条，数据是重复的

第二个问题，去除副表查询条件后不返回sum(budget)字段

麻烦你们了，谢谢

				tableObj = newTableObj;
				request.put(tableKey, tableObj);

改成

				tableObj.clear();
				tableObj.putAll(newTableObj);

之前你这样改过后，缓存的 SQL 就变多了，执行的 SQL 变少了。可以再看看

WHERE ( (del_flag = 0) AND (bound_type = 0) AND (project = 1637054928436) )

中 project = 1637054928436 是不是提到最前了

WHERE (  (project = 1637054928436) AND (del_flag = 0) AND (bound_type = 0) )

是这样的话就对了。

当然不改的话数据一对一、多对一应该没问题，只是额外多了一些不必要的性能开销；一对多、多对多且不用 GROUP BY/DISTINCT 去重的话就可能导致副表数据重复

Dec 28 '21 14:12 TommyLemon

@Shuai-S 已解决 https://github.com/Tencent/APIJSON/commit/a8bad69e007e76ca5bc85634053363fc010c58c9

可以用 4.8.3 的 jar 包 https://github.com/APIJSON/APIJSON-Demo/tree/master/APIJSON-Java-Server/APIJSONBoot-MultiDataSource/libs

{
    "[]": {
        "query": 2,
        "page": 0,
        "count": 20,
        "join": "</Moment/id@",
        "Comment": {
            "@order": "date-"
        },
        "Moment": {
            "@column": "id,userId,content",
            "id@": "/Comment/userId",
            "userId": 93793
        }
    },
    "info@": "/[]/info",
    "@explain": true
}

Jan 02 '22 16:01 TommyLemon

已发布 4.8.3 https://github.com/Tencent/APIJSON/releases/tag/4.8.3 https://github.com/APIJSON/apijson-framework/releases/tag/4.8.3 https://github.com/APIJSON/apijson-column/releases/tag/1.1.5 https://github.com/APIJSON/APIJSON-Demo/releases/tag/4.8.3

Jan 04 '22 17:01 TommyLemon

谢谢大佬，我升级版本试一下

Jan 06 '22 02:01 Shuai-S

您好，大佬，我想了解一下这两个问题的大概修复时间节点或者有什么临时解决办法吗？谢谢🙏

预计下周末，顺便会让 JOIN ON 新增支持多个条件。

第二个问题，应该只要把
SELECT Table2.column1, Table2.column2..  FROM Table1 LEFT JOIN (SELECT column1, column2 FROM Table2 ..) AS Table2
改成
SELECT Table2.*  FROM Table1 LEFT JOIN (SELECT column1, column2 FROM Table2 ..) AS Table2
就可以解决，源码在 AbstractSQLConfig.getColumnString

顺便

已支持 JOIN 带多个 ON 条件 https://github.com/Tencent/APIJSON/commit/f7b82fd90907b954b9ef90a4bfb8b8f05c72f513

{
    "[]": {
        "join": "</User",
        "Comment": {},
        "User": {
            "sex@": "/Comment/toId",
            "id@": "/Comment/userId"
        }
    },
    "@explain": true
}

FBA0502D-FFEC-4A68-90A3-9BDC43EC16EE

{
    "[]": {
        "count": 3,
        "join": {
            "&/User": {
                "name$": "%a%",
                "tag*~": "a",
                "@combine": "name$,tag*~",
                "@column": null,
                "@group": "id",
                "@having": "(id)%7!=0",
                "@order": "id+"
            }
        },
        "Comment": {
            "@column": "userId"
        },
        "User": {
            "id@": "/Comment/userId",
            "@column": "id;count(1)",
            "@group": "id",
            "@having": "(id)%5>1",
            "@order": "id-"
        }
    },
    "@explain": true
}

Mar 06 '22 12:03 TommyLemon