qiuweilu opened a new issue, #33355:
URL: https://github.com/apache/shardingsphere/issues/33355
### Which version of ShardingSphere did you use?
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc</artifactId>
<version>5.5.1</version>
</dependency>
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
While sharding rules have been defined, the execution engine is unable to
perform sharding operations for complex queries. We would like to manually
specify table names to work around this.
### Actual behavior
For complex nested queries, it's desirable to specify data table joins to
avoid generating x*y*z queries.
### Reason analyze (If you can)
part_cmnt and part_vote specify the sharding rules, ds_0.part_cmnt_${0..9} &
part_fkid and part_vote_${0..9} & part_fkid, but the matching rule cannot be
executed.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
"nested exception is org.apache.ibatis.executor.ExecutorException: Error
preparing statement. Cause:
org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException:
Table or view 'part_cmnt_1' does not exist."
### Example codes for reproduce this issue (such as a github link).
```
<select id="searchCmntList"
parameterType="com.foxwish.dao.entity.ext.PartCmntExt"
resultMap="BaseResultMap">
SELECT out_cmnt.cmnt_id cmnt_id, out_cmnt.cmnt_pid cmnt_pid,
out_cmnt.cmnt_gid cmnt_gid, out_cmnt.cmnt_mark cmnt_mark,
out_cmnt.cmnt_score cmnt_score, out_cmnt.cmnt_label cmnt_label,
out_cmnt.cmnt_cont cmnt_cont, out_cmnt.cmnt_json cmnt_json,
out_cmnt.cmnt_data cmnt_data, out_cmnt.cmnt_num_son cmnt_num_son,
out_cmnt.cmnt_num_grp cmnt_num_grp, out_cmnt.for_uid for_uid,
out_cmnt.user_id user_id, out_cmnt.cmnt_ctime cmnt_ctime,
out_cmnt.part_fkid part_fkid, out_cmnt.vote_bind vote_bind,
out_cmnt.vote_label vote_label, IF(vote_bind IS NULL, 0,
out_cmnt.vote_num) vote_num
<if test="userId != null and userId > 0">, vote_user_tab.user_id
vote_uid</if>
FROM
(
SELECT cmnt_tab.*, count(cmnt_tab.cmnt_id) vote_num,
vote_bind_tab.bind_fkid vote_bind, vote_bind_tab.vote_label vote_label
FROM
(
SELECT base_cmnt.* FROM part_cmnt base_cmnt
WHERE base_cmnt.part_fkid = #{partFkid,jdbcType=BIGINT}
AND base_cmnt.cmnt_label = #{cmntLabel,jdbcType=VARCHAR}
AND base_cmnt.cmnt_mark = #{cmntMark,jdbcType=VARCHAR}
AND base_cmnt.cmnt_score <= #{cmntScore,jdbcType=INTEGER}
AND base_cmnt.cmnt_ctime >=
#{cmntCtime,jdbcType=TIMESTAMP}
AND base_cmnt.cmnt_del = 0
<if test="cmntGid == null or cmntGid == 0">
AND base_cmnt.cmnt_pid = 0
</if>
<if test="cmntGid != null and cmntGid > 0">
AND base_cmnt.cmnt_pid > 0
AND base_cmnt.cmnt_gid = #{cmntGid,jdbcType=BIGINT}
</if>
ORDER BY base_cmnt.cmnt_id DESC
LIMIT 1000
) cmnt_tab
LEFT JOIN part_vote vote_bind_tab ON vote_bind_tab.bind_fkid =
cmnt_tab.cmnt_id
AND vote_bind_tab.part_fkid = #{partFkid,jdbcType=BIGINT}
AND vote_bind_tab.vote_label = #{voteLabel,jdbcType=VARCHAR}
AND vote_bind_tab.vote_mark = #{voteMark,jdbcType=VARCHAR}
AND vote_bind_tab.vote_del = 0
GROUP BY cmnt_tab.cmnt_id
) out_cmnt
<if test="userId != null and userId > 0">
LEFT JOIN part_vote vote_user_tab ON vote_user_tab.bind_fkid =
out_cmnt.cmnt_id
AND vote_user_tab.user_id = #{userId,jdbcType=BIGINT}
AND vote_user_tab.part_fkid = #{partFkid,jdbcType=BIGINT}
AND vote_user_tab.vote_label = #{voteLabel,jdbcType=VARCHAR}
AND vote_user_tab.vote_mark = #{voteMark,jdbcType=VARCHAR}
AND vote_user_tab.vote_del = 0
GROUP BY out_cmnt.cmnt_id ORDER BY out_cmnt.cmnt_id DESC
</if>
</select>
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
