Insert with sub-query failed [5.0.0]

[ghost]

Hi,

**[ updated , version 5.1.1. cannot repeat case 2 (generated key retrieval) ] , so removed case 2 , but I cannot rename the post **
.
I caught 2 exception cases with shardingsphere-jdbc (readwrite-splitting) with spring boot starter supports, I want to confirm if or not they are bugs, I am new to both spring boot and shardingsphere, and afraid of missing critical parameters.

Following spring boot jdbc template code fragment,
Case 1:
code works with mariadb 10.5.15 linux arm64 / derby 10.14 linux arm64 without shardingsphere, but when I enable shardingsphere with mariadb, it returns an SQL error: parameter at position 5 is not set. version 5.1.1 can still repeat this case but Spring jdbc template shows a more generic error message'.

my 'application.properties' attached.

shardingsphere-jdbc: 5.1.1,
spring boot: 2.7.0,
mariadb: 10.5.15,

Code fragment:

` public long insert(Comment record) {
String author = record.getAuthor();
String content = record.getContent();
long parentId = record.getParentId();
long postId = record.getPostId();

reparedStatementCreator delegate = new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
String sql = "";
if (parentId > 0)
sql = "insert into comments (post_id, parent_id, author, content, lvl) values (?, ?, ?, ?, (select coalesce(lvl + 1, 0) from comments x where id=?))";
else
sql = "insert into comments (post_id, parent_id, author, content, lvl) values (?, ?, ?, ?, 0)";
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setLong(1, postId);
ps.setLong(2, parentId);
ps.setString(3, author);
ps.setString(4, content);
if (parentId > 0)
ps.setLong(5, parentId);
return ps;
}
};

GeneratedKeyHolder holder = new GeneratedKeyHolder();

jdbcTemplate.update(delegate, holder); // version 1.

return holder.getKey().longValue();
}
`

Case 1 stack trace with 5.0.0:
org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; (conn=75) Parameter at position 5 is not set; nested exception is java.sql.SQLTransientConnectionException: (conn=75) Parameter at position 5 is not set at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:70) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:685) at com.example.demo.dao.CommentDAO.insert(CommentDAO.java:56)

Case 1 stack trace with 5.1.1:
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException; SQL state [HY000]; error code [1210]; (conn=128) Incorrect arguments to mysqld_stmt_execute; nested exception is java.sql.SQLException: (conn=128) Incorrect arguments to mysqld_stmt_execute at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1542) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:991) at com.example.demo.dao.CommentDAO.insert(CommentDAO.java:56)

application.properties:
spring.shardingsphere.enabled=true spring.shardingsphere.schema.name=training spring.shardingsphere.datasource.names=m1,s1 spring.shardingsphere.datasource.m1.name=m1 spring.shardingsphere.datasource.m1.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.m1.driverClassName=org.mariadb.jdbc.Driver spring.shardingsphere.datasource.m1.jdbcUrl=jdbc:mariadb://192.168.1.41:3306/training?useUnicode=true&characterEncoding=UTF-8 spring.shardingsphere.datasource.m1.username=pi spring.shardingsphere.datasource.m1.password=java spring.shardingsphere.datasource.s1.name=s1 spring.shardingsphere.datasource.s1.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.s1.driverClassName=org.mariadb.jdbc.Driver spring.shardingsphere.datasource.s1.jdbcUrl=jdbc:mariadb://192.168.1.42:3306/training?useUnicode=true&characterEncoding=UTF-8 spring.shardingsphere.datasource.s1.username=pi spring.shardingsphere.datasource.s1.password=java spring.shardingsphere.rules.readwrite-splitting.data-sources.ms_ds.type=Static spring.shardingsphere.rules.readwrite-splitting.data-sources.ms_ds.props.write-data-source-name=m1 spring.shardingsphere.rules.readwrite-splitting.data-sources.ms_ds.propsread-data-source-names=s1