多表连接出现错误结果

[GITWEIZ]

环境信息

• 系统:Windows 10
• JDK: 1.8.0_17
• 数据库:MySQL 8.0.28
• APIJSON: 4.9.1

问题描述

在进行多表连接时，不知道为什么其它表的字段会加入那个表中。
数据库结构：

执行的apijson表达式：

{
  "[]": {
    "join": "&/Lrx_score:t2/sid@,&/Lrx_score:t3/sid@,&/Lrx_score:t4/sid@",
    "Lrx_student": {
    },
    "Lrx_score:t2": {
      "@column": "sid:t2_sid,cid:t2_cid,grade:t2_grade",
      "sid@": "/Lrx_student/sid",
      "cid": "01"
    },
    "Lrx_score:t3": {
      "@column": "sid:t3_sid,cid:t3_cid,grade:t3_grade",
      "sid@": "/Lrx_student/sid",
      "cid": "02"
    },
    "Lrx_score:t4": {
      "@column": "sid:t4_sid,cid:t4_cid,grade:t4_grade",
      "sid@": "/Lrx_student/sid",
      "cid": "03"
    }
  }
}

错误信息

红色框内出现其它表的数据
完整结果如下：

{
  "[]": [
    {
      "Lrx_student": {
        "sid": "01",
        "name": "赵雷",
        "age": "1990-01-01T00:00:00",
        "sex": "男"
      },
      "Lrx_score:t2": {
        "t2_sid": "01",
        "t2_cid": "01",
        "t2_grade": 80.0,
        "t3_sid": "01",
        "t3_cid": "02",
        "t3_grade": 90.0,
        "t4_sid": "01",
        "t4_cid": "03",
        "t4_grade": 99.0
      },
      "Lrx_score:t3": {
        "t3_sid": "01",
        "t3_cid": "02",
        "t3_grade": 90.0
      },
      "Lrx_score:t4": {
        "t4_sid": "01",
        "t4_cid": "03",
        "t4_grade": 99.0
      }
    },
    {
      "Lrx_student": {
        "sid": "02",
        "name": "钱电",
        "age": "1990-12-21T00:00:00",
        "sex": "男"
      },
      "Lrx_score:t2": {
        "t2_sid": "02",
        "t2_cid": "01",
        "t2_grade": 70.0,
        "t3_sid": "02",
        "t3_cid": "02",
        "t3_grade": 60.0,
        "t4_sid": "02",
        "t4_cid": "03",
        "t4_grade": 80.0
      },
      "Lrx_score:t3": {
        "t3_sid": "02",
        "t3_cid": "02",
        "t3_grade": 60.0
      },
      "Lrx_score:t4": {
        "t4_sid": "02",
        "t4_cid": "03",
        "t4_grade": 80.0
      }
    },
    {
      "Lrx_student": {
        "sid": "03",
        "name": "孙风",
        "age": "1990-05-20T00:00:00",
        "sex": "男"
      },
      "Lrx_score:t2": {
        "t2_sid": "03",
        "t2_cid": "01",
        "t2_grade": 80.0,
        "t3_sid": "03",
        "t3_cid": "02",
        "t3_grade": 80.0,
        "t4_sid": "03",
        "t4_cid": "03",
        "t4_grade": 80.0
      },
      "Lrx_score:t3": {
        "t3_sid": "03",
        "t3_cid": "02",
        "t3_grade": 80.0
      },
      "Lrx_score:t4": {
        "t4_sid": "03",
        "t4_cid": "03",
        "t4_grade": 80.0
      }
    },
    {
      "Lrx_student": {
        "sid": "04",
        "name": "李云",
        "age": "1990-08-06T00:00:00",
        "sex": "男"
      },
      "Lrx_score:t2": {
        "t2_sid": "04",
        "t2_cid": "01",
        "t2_grade": 50.0,
        "t3_sid": "04",
        "t3_cid": "02",
        "t3_grade": 30.0,
        "t4_sid": "04",
        "t4_cid": "03",
        "t4_grade": 20.0
      },
      "Lrx_score:t3": {
        "t3_sid": "04",
        "t3_cid": "02",
        "t3_grade": 30.0
      },
      "Lrx_score:t4": {
        "t4_sid": "04",
        "t4_cid": "03",
        "t4_grade": 20.0
      }
    }
  ],
  "ok": true,
  "code": 200,
  "msg": "success",
  "debug:info|help": " \n **环境信息**  \n 系统: Windows 10 10.0 \n 数据库: DEFAULT_DATABASE = MYSQL \n JDK: 12.0.1 amd64 \n APIJSON: 4.9.0 \n   |   \n 常见问题：https://github.com/Tencent/APIJSON/issues/36 \n 通用文档：https://github.com/Tencent/APIJSON/blob/master/Document.md \n 视频教程：https://search.bilibili.com/all?keyword=APIJSON",
  "sql:generate|cache|execute|maxExecute": "13|4|9|200",
  "depth:count|max": "3|5",
  "time:start|duration|end|parse|sql": "1648708994731|48|1648708994779|11|37"
}

[TommyLemon]

同时 SQL JOIN 多个同名副表确实有这个 bug，不过对你这个不影响功能，只是多返回了字段影响性能。
问题出现在 AbstractSQLExecutor.execute(@NotNull SQLConfig config, boolean unknowType)
https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLExecutor.java#L442-L513

JDBC 不返回表别名，所以暂时还不好判断不带 @column 的情况。
不过像你这个需求，每个副表都有 @column，理论上根据这个可以准确计算出来每个字段所在表，不应该出现这个问题。

感谢反馈~

[TommyLemon]

目前有个简单的解决方法：
分别把带别名的副表 key 名改为其它名称，通过 Access 表(apijson-framework) 或 注解+注册(直接用 APIJSON ORM) 配置表名映射为同一个真实数据库表名。

name: Lrx_score // Lrx_score -> Lrx_score

name: Lrx_score, alias: Lrx_score_t3 // Lrx_score_t3 -> Lrx_score

name: Lrx_score, alias: Lrx_score_t4 // Lrx_score_t4 -> Lrx_score

[TommyLemon]

目前有个简单的解决方法： 分别把带别名的副表 key 名改为其它名称，通过 Access 表(apijson-framework) 或 注解+注册(直接用 APIJSON ORM) 配置表名映射为同一个真实数据库表名。

name: Lrx_score // Lrx_score -> Lrx_score

name: Lrx_score, alias: Lrx_score_t3 // Lrx_score_t3 -> Lrx_score

name: Lrx_score, alias: Lrx_score_t4 // Lrx_score_t4 -> Lrx_score

这个也不行，内部用的是真实表名 sqlTable 来判断。

得把 sqlTable 判断改为 sqlTable + alias 或者每条 SQL 语句中 FROM table_name 都加上 AS alias_name，这样从 JDBC 拿到的 tableName 或许是 alias_name，可以用来区分 Lrx_score:t2, Lrx_score:t3, Lrx_score:t4

[TommyLemon]

关于 JDBC 对 SQL 函数拿不到表名的情况，可以解析 function(arg0, .. , table.column, .. , argN ..) 内部的参数 table.column，拿到 table 对比 config.getAlias 来判断。

[TommyLemon]

也可以给对应 SQL 函数 AS table.column，这样在解析结果时通过点 . 来分割拿到 table 名确定把 column 分到哪个 JSONObject