Correlated Sub Queries is not supported

[d15801543974]

• dble version：3.22.01.0
• preconditions ：
  no
• configs：

cluster.cnf

bootstrap.cnf

db.xml

<dble:db xmlns:dble="http://dble.cloud/" version="4.0">
    <dbGroup name="dbGroup1" rwSplitMode="2" delayThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <dbInstance name="M1" url="localhost:3306" user="root" password="root" maxCon="1000" minCon="10"
                    primary="true" readWeight="1">
            <property name="testOnCreate">true</property>
        </dbInstance>
    </dbGroup>
</dble:db>

user.xml

<dble:user xmlns:dble="http://dble.cloud/" version="4.0">
    <managerUser name="man1" password="654321" maxCon="100"/>
    <managerUser name="user" usingDecrypt="true" whiteIPs="127.0.0.1,0:0:0:0:0:0:0:1" readOnly="true"
                 password="AqEkFEuIFAX6g2TJQnp4cJ2r7Yc0Z4/KBsZqKhT8qSz18Aj91e8lxO49BKQElC6OFfW4c38pCYa8QGFTub7pnw=="/>

    <shardingUser name="root" password="123456" schemas="testdb" readOnly="false" maxCon="20" >
    </shardingUser>
</dble:user>

sharding.xml

<globalTable name="sys_dept" shardingNode="dn1,dn2,dn3" sqlMaxLimit="200" />


<shardingNode name="dn1" dbGroup="dbGroup1" database="dairy"/>
<shardingNode name="dn2" dbGroup="dbGroup1" database="dairy01"/>
<shardingNode name="dn3" dbGroup="dbGroup1" database="dairy02"/>

• steps：
  step1. SELECT d.dept_id, d.parent_id, d.ancestors, d.dept_name, d.order_num, d.leader, d.phone, d.email, d.product_ids, d.STATUS, ( SELECT dept_name FROM sys_dept WHERE dept_id = d.parent_id ) parent_name FROM sys_dept d

• expect result：

  1. 这个是我在mysql中执行sql得到的结果

• real result：
  1.连接dble执行sql得到的错误提示 Cause: java.sql.SQLException: Correlated Sub Queries is not supported ; uncategorized SQLException; SQL state [HY000]; error code [1105]; Correlated Sub Queries is not supported ; nested exception is java.sql.SQLException: Correlated Sub Queries is not supported

• supplements：
  1.我看dble的介绍支持子查询，但是介绍的很模糊，不知道我这种情况（全局表），能不能支持子查询？

[PanternBao]

please refer : https://dev.mysql.com/doc/refman/8.0/en/correlated-subqueries.html, subquery like this doesn't support. You can replace sql with join or other way.