Issue 1:
Bug ReportFor English only, other languages will not accept.

Before report a bug, make sure you have:

Searched open and closed GitHub issues.

Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.

If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

5.0.0-RC1-SNAPSHOT

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Expected behavior

i got some error when use 5.0.0-RC1-SNAPSHOT although it's work well in 5.0.0-beta

Actual behavior

SQL: select count() from (select car,count() as cnt from table group by car) a

Reason analyze (If you can)### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Example codes for reproduce this issue (such as a github link).

Issue 2:
Bug Report

Which version of ShardingSphere did you use?

5.1.1

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

execute prepareStatement like INSERT INTO remark (userid, content, time) VALUES (?, ?, ?)

Actual behavior

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'org.apache.shardingsphere.sharding.rewrite.token.pojo.ShardingInsertValuesToken@' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242)
at com.wingkingbobo.splitdb.service.RemarkServiceImpl.testInsert(RemarkServiceImpl.java:107)

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

steps

create database p, p1 by create table statement.

run demo

jdbcUrl=JDBC_URL_MYSQL: OK

jdbcUrl=JDBC_URL_MYSQL: throw MySQLSyntaxErrorException

demo

private static final String JDBC_URL_MYSQL = "jdbc:mysql://localhost:3306/p1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
private static final String JDBC_URL_SHARDING_PROXY = "jdbc:mysql://localhost:3307/sharding_db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";

public static void main(String[] args) {
String jdbcUrl = JDBC_URL_SHARDING_PROXY;
try {
testInsert(createRemarkList(1).get(0), jdbcUrl);
// testBatchInsert(createRemarkList(1).get(0), jdbcUrl);
} catch(Exception e) {
e.printStacktrace();
}

}

private List<RemarkPO> createRemarkList(int count) {
List<RemarkPO> remarks = new ArrayList<>();

for (int i = 0; i < count; i++) {
RemarkPO remark = new RemarkPO();
remark.setUserId(4);
remark.setContent("test"+i);
remark.setTime(new Date());
remarks.add(remark);
}

return remarks;
}

private boolean testInsert(RemarkPO remark, String jdbcUrl) throws Exception{
DriverManager.registerDriver(new Driver());
Connection connection = DriverManager.getConnection(jdbcUrl, "root", "123456");

String sql = "INSERT INTO remark(userid, content, `time`) VALUES (?, ?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setLong(1, remark.getUserId());
statement.setString(2, remark.getContent());
statement.setDate(3, toSqlDate(remark.getTime()));
statement.execute();
return true;
}

private boolean testBatchInsert(List<RemarkPO> remarks, String jdbcUrl) throws Exception {
if (remarks.isEmpty()) {
return false;
}
DriverManager.registerDriver(new Driver());
Connection connection = DriverManager.getConnection(jdbcUrl, "root", "123456");

StringBuilder sb = new StringBuilder("INSERT INTO remark(userid, content, `time`) VALUES ");
for (int i = 0; i < remarks.size(); i++) {
if (i != 0) {
sb.append(",");
}
sb.append(" (?, ?, ?)");
}
String sql = sb.toString();
PreparedStatement statement = connection.prepareStatement(sql);
int stride = 3;
for (int i = 0; i < remarks.size(); i++) {
int offset = stride * i;
RemarkPO remark = remarks.get(i);
statement.setLong(1 + offset, remark.getUserId());
statement.setString(2 + offset, remark.getContent());
statement.setDate(3 + offset, toSqlDate(remark.getTime()));
}

statement.execute();

return true;
}

private java.sql.Date toSqlDate(Date date) {
return new java.sql.Date(date.getTime());
}

package com.test.po;

import java.util.Date;

public class RemarkPO {
private long id;
private long userId;
private String content;
private Date time;

public long getId() {
return id;
}

public void setId(long id) {
this.id = id;
}

public long getUserId() {
return userId;
}

public void setUserId(long userId) {
this.userId = userId;
}

public String getContent() {
return content;
}

public void setContent(String content) {
this.content = content;
}

public Date getTime() {
return time;
}

public void setTime(Date time) {
this.time = time;
}
}

server.yaml

mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: governance_ds
server-lists: localhost:2182
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
overwrite: false

rules:
- !AUTHORITY
users:
- root@%:123456
provider:
type: ALL_PRIVILEGES_PERMITTED
- !TRANSACTION
defaultType: LOCAL
props:
recoveryStoreUrl: jdbc:mysql://127.0.0.1:3306/jbossts
recoveryStoreDataSource: com.mysql.jdbc.jdbc2.optional.MysqlDataSource
recoveryStoreUser: root
recoveryStorePassword: 12345678
- !SQL_PARSER
sqlCommentParseEnabled: true
sqlStatementCache:
initialCapacity: 2000
maximumSize: 65535
concurrencyLevel: 4
parseTreeCache:
initialCapacity: 128
maximumSize: 1024
concurrencyLevel: 4

props:
max-connections-size-per-query: 1
kernel-executor-size: 16
proxy-frontend-flush-threshold: 128
proxy-hint-enabled: false
sql-show: true
check-table-metadata-enabled: false
show-process-list-enabled: false
proxy-backend-query-fetch-size: -1
check-duplicate-table-enabled: false
proxy-frontend-executor-size: 0
proxy-backend-executor-suitable: OLAP
proxy-frontend-max-connections: 0
sql-federation-enabled: false
proxy-backend-driver-type: JDBC

config-sharding.yaml

schemaName: sharding_db

dataSources:
p:
url: jdbc:mysql://localhost:3306/p?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
p1:
url: jdbc:mysql://localhost:3306/p1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1

rules:
- !SHARDING
tables:
remark:
actualDataNodes: p.remark,p$->{1..1}.remark
databaseStrategy:
standard:
shardingColumn: userid
shardingAlgorithmName: database_inline
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: p$->{userid<3?"":(String)((int)((userid-3)/2)+1)}

create table statement

/*
Navicat Premium Data Transfer

Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50708
Source Host : localhost:3306
Source Schema : p

Target Server Type : MySQL
Target Server Version : 50708
File Encoding : 65001

Date: 12/05/2022 16:42:31
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for remark
-- ----------------------------
DROP TABLE IF EXISTS `remark`;
CREATE TABLE `remark` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

SET FOREIGN_KEY_CHECKS = 1;