GeezerVison

{
    "[]": {
        "Comment": {  
            "name$": "%a%",
            "content$": "%a%",
            "@combine": "name$,content$", 
            "@from@": {  
                "from": "Comment",
                "join": "&/User/id@",
                "Comment": {},
                "User": {
                    "id@": "/Comment/userId",
		    "sex": 1, // 这个条件会和全局条件相调换
                    "@column": "name,sex"  
                }
            }
        }
    },
    "@explain": true
}

执行时sql参数顺序乱了，sex=1 变成了 sex=%a%

explain返回的sql是正确的

Activity

TommyLemon
added
描述简陋或态度无礼，7天内未改好将关闭或删除！先看完常见问题 issues/36 再按要求提问！
on Sep 20, 2022
transtone
changed the title ~~[-]多表关联模糊查询时，子查询类型报错[/-]~~ [+]多表关联模糊查询时，临时表查询类型报错[/+] on Sep 20, 2022
TommyLemon
removed
描述简陋或态度无礼，7天内未改好将关闭或删除！先看完常见问题 issues/36 再按要求提问！
on Sep 21, 2022
transtone
changed the title ~~[-]多表关联模糊查询时，临时表查询类型报错[/-]~~ [+]多表关联模糊查询时，查询条件顺序错乱[/+] on Sep 21, 2022
transtone
changed the title ~~[-]多表关联模糊查询时，查询条件顺序错乱[/-]~~ [+]多表关联模糊查询时，查询条件顺序错乱bug[/+] on Sep 21, 2022
TommyLemon commented on Sep 21, 2022
TommyLemon
on Sep 21, 2022
Member
More actions
升级到 5.2.0 试试
https://github.com/Tencent/APIJSON/releases/tag/5.2.0

transtone commented on Sep 21, 2022

transtone

on Sep 21, 2022

ContributorAuthor

More actions

已经升级过，当前版本是 apijson-framework-5.2.0

        <dependency>
            <groupId>com.github.APIJSON</groupId>
            <artifactId>apijson-framework</artifactId>
            <version>5.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.github.APIJSON</groupId>
            <artifactId>apijson-column</artifactId>
            <version>1.2.5</version>
        </dependency>
        <dependency>
            <groupId>com.github.APIJSON</groupId>
            <artifactId>apijson-router</artifactId>
            <version>1.0.5</version>
        </dependency>

TommyLemon commented on Sep 21, 2022
TommyLemon
on Sep 21, 2022
Member
More actions
有没有哪里单独指定了 APIJSON 5.1.5？
apijson-framework 和 APIJSON 是两个项目
TommyLemon
added
Bug 漏洞
on Sep 21, 2022
transtone commented on Sep 21, 2022
transtone
on Sep 21, 2022
ContributorAuthor
More actions

有没有哪里单独指定了 APIJSON 5.1.5？ apijson-framework 和 APIJSON 是两个项目

没有单独指定。是两个项目，但是apijson-framework 是需要引用 APIJSON 的，
IDEA debug 的显示不会错的吧。
TommyLemon commented on Sep 21, 2022
TommyLemon
on Sep 21, 2022
Member
More actions
感谢反馈，外查询和子查询都有条件时触发了这个 bug。

这里得把 preparedValueList 反过来装
https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L4027-L4030

参考 concatJoinWhereString，先外查询 preparedValueList、子查询的 preparedValueList 取出来用一个变量暂存，然后清空，再用子查询的 subPreparedValueList.add(preparedValueList) 然后 setPreparedValueList
https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L2924-L3049

可以改了后提个 PR 贡献代码，开源要大家一起参与才会更美好，并可持续发展~

https://github.com/Tencent/APIJSON/blob/master/CONTRIBUTING.md#%E4%B8%BA%E4%BB%80%E4%B9%88%E4%B8%80%E5%AE%9A%E8%A6%81%E8%B4%A1%E7%8C%AE%E4%BB%A3%E7%A0%81
transtone commented on Sep 21, 2022
transtone
on Sep 21, 2022
ContributorAuthor
More actions
已提交，如可用请合并。
如果不是因为explain返回的sql是正确的，还发现不了这个问题。
还一度想添加一个接口，直接获取 config.getSQL() 的值，手动执行呢。
TommyLemon
added
help wanted请求帮助请求帮助
on Sep 25, 2022
transtone commented on Sep 30, 2022
transtone
on Sep 30, 2022
ContributorAuthor
More actions

这里得把 preparedValueList 反过来装 https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L4027-L4030

参考 concatJoinWhereString，先外查询 preparedValueList、子查询的 preparedValueList 取出来用一个变量暂存，然后清空，再用子查询的 subPreparedValueList.add(preparedValueList) 然后 setPreparedValueList https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L2924-L3049

getConditionString 这个函数是反复调用的，也就是说 getSubqueryString 会执行多次，依据什么来判断当前条件包含外查询和子查询，并且需要反装呢？
TommyLemon commented on Sep 30, 2022
TommyLemon
on Sep 30, 2022
Member
More actions

这里得把 preparedValueList 反过来装 https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L4027-L4030
参考 concatJoinWhereString，先外查询 preparedValueList、子查询的 preparedValueList 取出来用一个变量暂存，然后清空，再用子查询的 subPreparedValueList.add(preparedValueList) 然后 setPreparedValueList https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L2924-L3049

getConditionString 这个函数是反复调用的，也就是说 getSubqueryString 会执行多次，依据什么来判断当前条件包含外查询和子查询，并且需要反装呢？

getFrom 值不为 null 就一定是有 FROM(SELECT ...) 这种子查询，
getSubqueryString 执行多次，其实是前端传参子查询数量的两倍，一倍用来生成完整 SQL，另一倍用来生成 WHERE id=? 这种预编译的 SQL，具体见 getSQL(boolean prepared)
TommyLemon commented on Sep 30, 2022
TommyLemon
on Sep 30, 2022
Member
More actions

很抱歉，不会修。静待您的更新。如果不是因为explain返回的sql是正确的，还发现不了这个问题。还一度想添加一个接口，直接获取 config.getSQL() 的值，手动执行呢。

取两个列表、合并两个列表、设置两个列表，也就 5 行代码
transtone
closed this as completed on Oct 8, 2022
gxmanito commented on Jul 24, 2024
gxmanito
on Jul 24, 2024
More actions

这里得把 preparedValueList 反过来装 https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L4027-L4030
参考 concatJoinWhereString，先外查询 preparedValueList、子查询的 preparedValueList 取出来用一个变量暂存，然后清空，再用子查询的 subPreparedValueList.add(preparedValueList) 然后 setPreparedValueList https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L2924-L3049

getConditionString 这个函数是反复调用的，也就是说 getSubqueryString 会执行多次，依据什么来判断当前条件包含外查询和子查询，并且需要反装呢？

getFrom 值不为 null 就一定是有 FROM(SELECT ...) 这种子查询， getSubqueryString 执行多次，其实是前端传参子查询数量的两倍，一倍用来生成完整 SQL，另一倍用来生成 WHERE id=? 这种预编译的 SQL，具体见 getSQL(boolean prepared)

6.3.0版本，List subPvl = cfg.getPreparedValueList()一直是空集合，导致最终No value specified for parameter 4
TommyLemon commented on Jul 24, 2024
TommyLemon
on Jul 24, 2024
Member
More actions

这里得把 preparedValueList 反过来装 https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L4027-L4030
参考 concatJoinWhereString，先外查询 preparedValueList、子查询的 preparedValueList 取出来用一个变量暂存，然后清空，再用子查询的 subPreparedValueList.add(preparedValueList) 然后 setPreparedValueList https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L2924-L3049

getConditionString 这个函数是反复调用的，也就是说 getSubqueryString 会执行多次，依据什么来判断当前条件包含外查询和子查询，并且需要反装呢？

getFrom 值不为 null 就一定是有 FROM(SELECT ...) 这种子查询， getSubqueryString 执行多次，其实是前端传参子查询数量的两倍，一倍用来生成完整 SQL，另一倍用来生成 WHERE id=? 这种预编译的 SQL，具体见 getSQL(boolean prepared)

6.3.0版本，List subPvl = cfg.getPreparedValueList()一直是空集合，导致最终No value specified for parameter 4

@gxmanito 升级 6.4+ 或 7.0.3 试试。还不行的话麻烦提供详细信息，方便排查问题
gxmanito commented on Jul 25, 2024
gxmanito
on Jul 25, 2024
More actions
#445 (comment)
@TommyLemon 项目使用的是springboot2.x和jdk8，升级6.4+ 或 7.0.3 会向下兼容吗？

另还有个多表join（left、inner、app均使用）其中查询有模糊查，会报错，把"deviceName$": "%设备名称%"条件去掉就没问题，打断点查看第一遍会先生成（SELECT point_code FROM base_norm_point WHERE ( (device_type_code != ?) ) ），导致占位符多出一个是201
TommyLemon commented on Jul 28, 2024
TommyLemon
on Jul 28, 2024
Member
More actions
@gxmanito
升级到 6.4+ 后只支持 JDK 17+ 的版本，7.0+ 兼容 SpringBoot2 但 JDK 也必须是 17+

JOIN 带条件有占位符错误问题，我们再排查下，感谢反馈。

APP JOIN 和 LEFT JOIN 可以互换试试，结果应该一样，只是性能区别
TommyLemon commented on Jul 28, 2024
TommyLemon
on Jul 28, 2024
Member
More actions
官网 7.0.3 试了未复现

http://apijson.cn/api/?send=true&type=JSON&url=http%3A%2F%2Fapijson.cn%3A8080%2Fget&json={%22[]%22:{%22join%22:%22%26%2FUser%2C%40%2FComment%2C%3C%2FPraise%22,%22Moment%22:{%22content~%22:%22a%22,%22@column%22:%22id%2CuserId%2Ccontent%22},%22User%22:{%22sex{}%22:[1,0],%22@column%22:%22id%2Cname%22,%22id@%22:%22%2FMoment%2FuserId%22},%22Comment%22:{%22momentId@%22:%22%2FMoment%2Fid%22,%22date%3E%22:%222019-02-17%2000%3A00%3A00%22,%22@column%22:%22id%2CmomentId%2Ccontent%22},%22Praise%22:{%22momentId@%22:%22%2FMoment%2Fid%22,%22userId!%22:0}},%22@explain%22:true}

http://apijson.cn/api/?send=true&type=JSON&url=http%3A%2F%2Fapijson.cn%3A8080%2Fget&json={%22[]%22:{%22join%22:%22%26%2FUser%2C%40%2FComment%2C%3E%2FPraise%22,%22Moment%22:{%22content~%22:%22a%22,%22@column%22:%22id%2CuserId%2Ccontent%22},%22User%22:{%22sex{}%22:[1,0],%22@column%22:%22id%2Cname%22,%22id@%22:%22%2FMoment%2FuserId%22},%22Comment%22:{%22momentId@%22:%22%2FMoment%2Fid%22,%22date%3E%22:%222019-02-17%2000%3A00%3A00%22,%22@column%22:%22id%2CmomentId%2Ccontent%22},%22Praise%22:{%22momentId@%22:%22%2FMoment%2Fid%22,%22userId!%22:0}},%22@explain%22:true}
gxmanito commented on Jul 28, 2024
gxmanito
on Jul 28, 2024
More actions
#445 (comment)
可以试试两张表inner join且都有模糊查询，其中一张子表既有inner还有left join
gxmanito commented on Jul 28, 2024
gxmanito
on Jul 28, 2024
More actions
#445 (comment)
另针对这个情况是这样复现的，当开启ENABLE_WITH_AS = true; 且子查询有where条件时，会报此错误No value specified for parameter 2

不加where不会报错
TommyLemon commented on Jul 28, 2024
TommyLemon
on Jul 28, 2024
Member
More actions
@gxmanito WITH AS 确实有这个问题，可以先关掉，只是对性能有点影响

TommyLemon commented on Jul 28, 2024

TommyLemon

on Jul 28, 2024

Member

More actions

@gxmanito 改用刚发布的 7.0.3-jdk1.8 或 7.0.3-jdk1.8.0.0 试试
https://github.com/Tencent/APIJSON/releases/tag/7.0.3-jdk1.8

如果有用到 apijson-framework，必须 6.3.0+，并且排除它依赖的 APIJSON ORM， apijson-column, apijson-router 都只能用 1.8.0+，并且排除它们依赖的 APIJSON ORM

        <dependency>
            <groupId>com.github.Tencent</groupId>
            <artifactId>APIJSON</artifactId>
            <version>7.0.3-jdk1.8</version>
        </dependency>
        <dependency>
            <groupId>com.github.APIJSON</groupId>
            <artifactId>apijson-framework</artifactId>
            <version>6.3.0</version>
            <exclusions>
                <exclusion>
                    <groupId>com.github.Tencent</groupId>
                    <artifactId>APIJSON</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.github.APIJSON</groupId>
            <artifactId>apijson-column</artifactId>
            <version>1.8.0</version>
            <exclusions>
                <exclusion>
                    <groupId>com.github.Tencent</groupId>
                    <artifactId>APIJSON</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.github.APIJSON</groupId>
            <artifactId>apijson-router</artifactId>
            <version>1.8.0</version>
            <exclusions>
                <exclusion>
                    <groupId>com.github.Tencent</groupId>
                    <artifactId>APIJSON</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

TommyLemon commented on Jul 28, 2024
TommyLemon
on Jul 28, 2024
Member
More actions
完整的 Demo：
APIJSONBoot-MultiDataSource-APIJSON-7.0.3-jdk1.8-springboot2.5.zip
TommyLemon commented on Jul 28, 2024
TommyLemon
on Jul 28, 2024
Member
More actions
@gxmanito 复现了：

http://apijson.cn/api/?send=true&type=JSON&url=http%3A%2F%2Fapijson.cn%3A8080%2Fget&json={%22[]%22:{%22join%22:%22%26%2FUser%2C%26%2FPraise%2C%3C%2FComment%2C%3C%2FComment%3Ato%22,%22Moment%22:{%22content$%22:%22%25a%25%22,%22@column%22:%22id%2CuserId%2Ccontent%22},%22User%22:{%22sex%22:0,%22@column%22:%22id%2Cname%22,%22id@%22:%22%2FMoment%2FuserId%22},%22Praise%22:{%22momentId@%22:%22%2FMoment%2Fid%22,%22userId!%22:0},%22Comment%22:{%22momentId@%22:%22%2FMoment%2Fid%22,%22date$%22:%22%2520%25%22,%22@column%22:%22id%2CmomentId%2Ccontent%22},%22Comment:to%22:{%22momentId@%22:%22%2FMoment%2Fid%22,%22toId!%22:0,%22@column%22:%22id%2CtoId%2CmomentId%2Ccontent%22}},%22@explain%22:true}

删除这两个 INNER JOIN 对应任何一张表里的条件可以正常返回结果：

http://apijson.cn/api/?send=false&type=JSON&url=http%3A%2F%2Fapijson.cn%3A8080%2Fget&json={%22[]%22:{%22join%22:%22%26%2FUser%2C%26%2FPraise%2C%3C%2FComment%2C%3C%2FComment%3Ato%22,%22Moment%22:{%22content$%22:%22%25a%25%22,%22@column%22:%22id%2CuserId%2Ccontent%22},%22User%22:{%22@column%22:%22id%2Cname%22,%22id@%22:%22%2FMoment%2FuserId%22},%22Praise%22:{%22momentId@%22:%22%2FMoment%2Fid%22,%22userId!%22:0},%22Comment%22:{%22momentId@%22:%22%2FMoment%2Fid%22,%22date$%22:%22%2520%25%22,%22@column%22:%22id%2CmomentId%2Ccontent%22},%22Comment:to%22:{%22momentId@%22:%22%2FMoment%2Fid%22,%22toId!%22:0,%22@column%22:%22id%2CtoId%2CmomentId%2Ccontent%22}},%22@explain%22:true}
https://apijson.cn/api/?send=false&type=JSON&url=http%3A%2F%2Fapijson.cn%3A8080%2Fget&json=%7B%0A%20%20%20%20%22%5B%5D%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%22join%22%3A%20%22%26%2FUser%2C%26%2FPraise%2C%3C%2FComment%2C%3C%2FComment%3Ato%22%2C%0A%20%20%20%20%20%20%20%20%22Moment%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22content%24%22%3A%20%22%25a%25%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%40column%22%3A%20%22id%2CuserId%2Ccontent%22%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%22User%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22sex%22%3A%200%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%40column%22%3A%20%22id%2Cname%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22id%40%22%3A%20%22%2FMoment%2FuserId%22%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%22Praise%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22momentId%40%22%3A%20%22%2FMoment%2Fid%22%2C%20%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%22Comment%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22momentId%40%22%3A%20%22%2FMoment%2Fid%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22date%24%22%3A%20%22%2520%25%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%40column%22%3A%20%22id%2CmomentId%2Ccontent%22%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%22Comment%3Ato%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22momentId%40%22%3A%20%22%2FMoment%2Fid%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22toId!%22%3A%200%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%40column%22%3A%20%22id%2CtoId%2CmomentId%2Ccontent%22%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%2C%0A%20%20%20%20%22%40explain%22%3A%20true%0A%7D
这个得继续排查是 AbstractSQLConfig 哪步出错的。
每个条件最后都会调用 getValue，里面 preparedValueList.add(value) 发生在同步拼接 JOIN SQL 片段时候

可能 getJoinString 对 LEFT/RIGHT 等 OUTER JOIN 处理条件时清空现有副表的 preparedValueList，然后加到主表，这块有问题
TommyLemon commented on Jul 28, 2024
TommyLemon
on Jul 28, 2024
Member
More actions
临时解决方法是把带条件的 LEFT JOIN 都改成 APP JOIN 或者不用 JOIN

http://apijson.cn/api/?send=true&type=JSON&url=http%3A%2F%2Fapijson.cn%3A8080%2Fget&json={%22[]%22:{%22join%22:%22%26%2FUser%2C%26%2FPraise%2C%40%2FComment%2C%40%2FComment%3Ato%22,%22Moment%22:{%22@column%22:%22id%2CuserId%2Ccontent%22,%22content$%22:%22%25a%25%22},%22User%22:{%22id@%22:%22%2FMoment%2FuserId%22,%22@column%22:%22id%2Cname%22,%22sex%22:0},%22Praise%22:{%22momentId@%22:%22%2FMoment%2Fid%22,%22userId%3E%22:82001},%22Comment%22:{%22momentId@%22:%22%2FMoment%2Fid%22,%22@column%22:%22id%2CmomentId%2Ccontent%22,%22date$%22:%22%2520%25%22},%22Comment:to%22:{%22momentId@%22:%22%2FMoment%2Fid%22,%22@column%22:%22id%2CtoId%2CmomentId%2Ccontent%22,%22toId!%22:0}},%22@explain%22:true}

Metadata

Assignees

No one assigned

Labels

Bug 漏洞help wanted请求帮助请求帮助

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions

You are visitor 01402503 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.

GeezerVison

多表关联模糊查询时，查询条件顺序错乱bug #445

Description

Activity

TommyLemon commented on Sep 21, 2022

transtone commented on Sep 21, 2022

TommyLemon commented on Sep 21, 2022

transtone commented on Sep 21, 2022

TommyLemon commented on Sep 21, 2022

transtone commented on Sep 21, 2022

transtone commented on Sep 30, 2022

TommyLemon commented on Sep 30, 2022

TommyLemon commented on Sep 30, 2022

gxmanito commented on Jul 24, 2024

TommyLemon commented on Jul 24, 2024

gxmanito commented on Jul 25, 2024

TommyLemon commented on Jul 28, 2024

TommyLemon commented on Jul 28, 2024

gxmanito commented on Jul 28, 2024

gxmanito commented on Jul 28, 2024

TommyLemon commented on Jul 28, 2024

TommyLemon commented on Jul 28, 2024

TommyLemon commented on Jul 28, 2024

TommyLemon commented on Jul 28, 2024

TommyLemon commented on Jul 28, 2024

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions