You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2021/02/07 02:34:38 UTC
[shardingsphere] branch master updated: Test encrypt rule,
replicaQuery rule and sharding rule all exist when do query in
calcite. (#9249)
This is an automated email from the ASF dual-hosted git repository.
panjuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new e1c4c7c Test encrypt rule, replicaQuery rule and sharding rule all exist when do query in calcite. (#9249)
e1c4c7c is described below
commit e1c4c7c9133ac88b0760f617d333742f33b087dc
Author: MingHao Li <lm...@163.com>
AuthorDate: Sun Feb 7 10:34:06 2021 +0800
Test encrypt rule, replicaQuery rule and sharding rule all exist when do query in calcite. (#9249)
* templete
* Test encrypt rule, replicaQuery rule and sharding rule all exist.
* test mixed rule.
---
.../driver/jdbc/base/AbstractSQLCalciteTest.java | 5 +-
...ractShardingSphereDataSourceForCalciteTest.java | 4 +-
.../statement/CalcitePrepareStatementTest.java | 57 ++++++++++++----
.../jdbc/core/statement/CalciteStatementTest.java | 76 +++++++++++++++++-----
.../src/test/resources/config/config-calcite.yaml | 52 ++++++++++++++-
.../src/test/resources/sql/calcite_data_0.sql | 8 ++-
.../src/test/resources/sql/calcite_data_1.sql | 17 +++++
.../src/test/resources/sql/calcite_data_2.sql | 47 +++++++++++++
.../src/test/resources/sql/jdbc_init_calcite_0.sql | 1 +
.../src/test/resources/sql/jdbc_init_calcite_1.sql | 3 +
10 files changed, 235 insertions(+), 35 deletions(-)
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/base/AbstractSQLCalciteTest.java b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/base/AbstractSQLCalciteTest.java
index 8132e6c..4d1276a 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/base/AbstractSQLCalciteTest.java
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/base/AbstractSQLCalciteTest.java
@@ -35,11 +35,14 @@ public abstract class AbstractSQLCalciteTest {
private static final String INIT_CALCITE_DATABASE_0 = "sql/jdbc_init_calcite_0.sql";
private static final String INIT_CALCITE_DATABASE_1 = "sql/jdbc_init_calcite_1.sql";
-
+
+ private static final String INIT_CALCITE_DATABASE_2 = "sql/jdbc_init_calcite_1.sql";
+
@BeforeClass
public static synchronized void initializeDataSource() throws SQLException {
createDataSources("calcite_jdbc_0", INIT_CALCITE_DATABASE_0);
createDataSources("calcite_jdbc_1", INIT_CALCITE_DATABASE_1);
+ createDataSources("calcite_jdbc_2", INIT_CALCITE_DATABASE_2);
}
private static void createDataSources(final String dataSourceName, final String initSql) throws SQLException {
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/base/AbstractShardingSphereDataSourceForCalciteTest.java b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/base/AbstractShardingSphereDataSourceForCalciteTest.java
index d382fd8..a8e3b90 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/base/AbstractShardingSphereDataSourceForCalciteTest.java
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/base/AbstractShardingSphereDataSourceForCalciteTest.java
@@ -42,7 +42,7 @@ public abstract class AbstractShardingSphereDataSourceForCalciteTest extends Abs
private static ShardingSphereDataSource dataSource;
- private static final List<String> ACTUAL_DATA_SOURCE_NAMES = Arrays.asList("calcite_jdbc_0", "calcite_jdbc_1");
+ private static final List<String> ACTUAL_DATA_SOURCE_NAMES = Arrays.asList("calcite_jdbc_0", "calcite_jdbc_1", "calcite_jdbc_2");
private static final String CONFIG_CALCITE = "config/config-calcite.yaml";
@@ -65,8 +65,10 @@ public abstract class AbstractShardingSphereDataSourceForCalciteTest extends Abs
Map<String, DataSource> dataSourceMap = conn.getDataSourceMap();
Connection database0 = dataSourceMap.get("calcite_jdbc_0").getConnection();
Connection database1 = dataSourceMap.get("calcite_jdbc_1").getConnection();
+ Connection database2 = dataSourceMap.get("calcite_jdbc_2").getConnection();
RunScript.execute(database0, new InputStreamReader(Objects.requireNonNull(AbstractSQLTest.class.getClassLoader().getResourceAsStream("sql/calcite_data_0.sql"))));
RunScript.execute(database1, new InputStreamReader(Objects.requireNonNull(AbstractSQLTest.class.getClassLoader().getResourceAsStream("sql/calcite_data_1.sql"))));
+ RunScript.execute(database2, new InputStreamReader(Objects.requireNonNull(AbstractSQLTest.class.getClassLoader().getResourceAsStream("sql/calcite_data_2.sql"))));
conn.close();
} catch (final SQLException ex) {
throw new RuntimeException(ex);
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/CalcitePrepareStatementTest.java b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/CalcitePrepareStatementTest.java
index bf7d372..a17a6fb 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/CalcitePrepareStatementTest.java
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/CalcitePrepareStatementTest.java
@@ -31,7 +31,8 @@ import static org.junit.Assert.assertTrue;
public final class CalcitePrepareStatementTest extends AbstractShardingSphereDataSourceForCalciteTest {
- private static final String SELECT_SQL_BY_ID_ACROSS_SINGLE_TABLES = "select o.*, i.* from t_order_calcite o, t_order_item_calcite i where o.order_id = ? and i.item_id = ?";
+ private static final String SELECT_SQL_BY_ID_ACROSS_SINGLE_TABLES =
+ "select o.*, i.* from t_order_calcite o, t_order_item_calcite i where o.order_id = ? and i.item_id = ?";
private static final String SELECT_SQL_BY_ID_ACROSS_SINGLE_AND_SHARDING_TABLES =
"select t_order_calcite.*, t_order_item_calcite_sharding.* "
@@ -50,6 +51,14 @@ public final class CalcitePrepareStatementTest extends AbstractShardingSphereDat
+ "AND t_order_item_calcite_sharding.remarks = 't_order_item_calcite_sharding' "
+ "AND t_order_item_calcite_sharding.user_id = ?";
+ private static final String SELECT_SQL_BY_ID_ACROSS_SINGLE_TABLES_WITH_ENCRYPT =
+ "select t_user_encrypt_calcite.user_id, t_user_encrypt_calcite.pwd, t_user_info.information from t_user_encrypt_calcite, t_user_info "
+ + "where t_user_encrypt_calcite.user_id = t_user_info.user_id and t_user_encrypt_calcite.user_id > ? ";
+
+ private static final String SELECT_SQL_BY_ID_ACROSS_SINGLE_AND_SHARDING_TABLES_WITH_ENCRYPT =
+ "select t_user_encrypt_calcite_sharding.user_id, t_user_encrypt_calcite_sharding.pwd, t_user_info.information from t_user_encrypt_calcite_sharding, t_user_info "
+ + "where t_user_encrypt_calcite_sharding.user_id = t_user_info.user_id and t_user_encrypt_calcite_sharding.user_id > ? ";
+
@Test
public void assertQueryWithCalciteInSingleTables() throws SQLException {
ShardingSpherePreparedStatement preparedStatement = (ShardingSpherePreparedStatement) getShardingSphereDataSource().getConnection().prepareStatement(SELECT_SQL_BY_ID_ACROSS_SINGLE_TABLES);
@@ -81,8 +90,6 @@ public final class CalcitePrepareStatementTest extends AbstractShardingSphereDat
assertThat(resultSet.getString(3), is("init"));
assertThat(resultSet.getInt(4), is(1001));
assertThat(resultSet.getInt(5), is(10001));
- assertThat(resultSet.getInt(6), is(11));
- assertThat(resultSet.getString(7), is("init"));
assertFalse(resultSet.next());
}
@@ -99,8 +106,6 @@ public final class CalcitePrepareStatementTest extends AbstractShardingSphereDat
assertThat(resultSet.getString(3), is("init"));
assertThat(resultSet.getInt(4), is(1001));
assertThat(resultSet.getInt(5), is(10001));
- assertThat(resultSet.getInt(6), is(11));
- assertThat(resultSet.getString(7), is("init"));
assertFalse(resultSet.next());
}
@@ -117,9 +122,6 @@ public final class CalcitePrepareStatementTest extends AbstractShardingSphereDat
assertThat(resultSet.getString(3), is("init"));
assertThat(resultSet.getInt(4), is(1001));
assertThat(resultSet.getInt(5), is(10001));
- assertThat(resultSet.getInt(6), is(11));
- assertThat(resultSet.getString(7), is("init"));
- assertThat(resultSet.getString(8), is("t_order_item_calcite_sharding"));
assertFalse(resultSet.next());
preparedStatement.setInt(1, 10);
ResultSet resultSet1 = preparedStatement.executeQuery();
@@ -130,9 +132,42 @@ public final class CalcitePrepareStatementTest extends AbstractShardingSphereDat
assertThat(resultSet1.getString(3), is("init"));
assertThat(resultSet1.getInt(4), is(1000));
assertThat(resultSet1.getInt(5), is(10000));
- assertThat(resultSet1.getInt(6), is(10));
- assertThat(resultSet1.getString(7), is("init"));
- assertThat(resultSet1.getString(8), is("t_order_item_calcite_sharding"));
assertFalse(resultSet1.next());
}
+
+ @Test
+ public void assertQueryWithCalciteInSingleTablesWithEncryptRule() throws SQLException {
+ ShardingSpherePreparedStatement preparedStatement = (ShardingSpherePreparedStatement) getShardingSphereDataSource()
+ .getConnection().prepareStatement(SELECT_SQL_BY_ID_ACROSS_SINGLE_TABLES_WITH_ENCRYPT);
+ preparedStatement.setInt(1, 1);
+ ResultSet resultSet = preparedStatement.executeQuery();
+ assertNotNull(resultSet);
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(2));
+ assertThat(resultSet.getString(2), is("decryptValue"));
+ assertThat(resultSet.getString(3), is("description2"));
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(3));
+ assertThat(resultSet.getString(2), is("decryptValue"));
+ assertThat(resultSet.getString(3), is("description3"));
+ assertFalse(resultSet.next());
+ }
+
+ @Test
+ public void assertQueryWithCalciteInSingleAndShardingTablesWithEncryptRule() throws SQLException {
+ ShardingSpherePreparedStatement preparedStatement = (ShardingSpherePreparedStatement) getShardingSphereDataSource()
+ .getConnection().prepareStatement(SELECT_SQL_BY_ID_ACROSS_SINGLE_AND_SHARDING_TABLES_WITH_ENCRYPT);
+ preparedStatement.setInt(1, 1);
+ ResultSet resultSet = preparedStatement.executeQuery();
+ assertNotNull(resultSet);
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(2));
+ assertThat(resultSet.getString(2), is("decryptValue"));
+ assertThat(resultSet.getString(3), is("description2"));
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(3));
+ assertThat(resultSet.getString(2), is("decryptValue"));
+ assertThat(resultSet.getString(3), is("description3"));
+ assertFalse(resultSet.next());
+ }
}
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/CalciteStatementTest.java b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/CalciteStatementTest.java
index 50dcb35..b920481 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/CalciteStatementTest.java
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/CalciteStatementTest.java
@@ -31,7 +31,8 @@ import static org.junit.Assert.assertTrue;
public final class CalciteStatementTest extends AbstractShardingSphereDataSourceForCalciteTest {
- private static final String SELECT_SQL_BY_ID_ACROSS_SINGLE_TABLES = "select o.*, i.* from t_order_calcite o, t_order_item_calcite i "
+ private static final String SELECT_SQL_BY_ID_ACROSS_SINGLE_TABLES =
+ "select o.*, i.* from t_order_calcite o, t_order_item_calcite i "
+ "where o.order_id = 1000 and i.item_id = 100000";
private static final String SELECT_SQL_BY_ID_ACROSS_SINGLE_AND_SHARDING_TABLES =
@@ -53,6 +54,14 @@ public final class CalciteStatementTest extends AbstractShardingSphereDataSource
+ "where t_order_calcite.order_id = t_order_item_calcite_sharding.item_id "
+ "ORDER BY t_order_item_calcite_sharding.user_id";
+ private static final String SELECT_SQL_BY_ID_ACROSS_SINGLE_TABLES_WITH_ENCRYPT =
+ "select t_user_encrypt_calcite.user_id, t_user_encrypt_calcite.pwd, t_user_info.information from t_user_encrypt_calcite, t_user_info "
+ + "where t_user_encrypt_calcite.user_id = t_user_info.user_id ";
+
+ private static final String SELECT_SQL_BY_ID_ACROSS_SINGLE_AND_SHARDING_TABLES_WITH_ENCRYPT =
+ "select t_user_encrypt_calcite_sharding.user_id, t_user_encrypt_calcite_sharding.pwd, t_user_info.information from t_user_encrypt_calcite_sharding, t_user_info "
+ + "where t_user_encrypt_calcite_sharding.user_id = t_user_info.user_id ";
+
@Test
public void assertQueryWithCalciteInSingleTables() throws SQLException {
ShardingSphereStatement preparedStatement = (ShardingSphereStatement) getShardingSphereDataSource().getConnection().createStatement();
@@ -80,17 +89,12 @@ public final class CalciteStatementTest extends AbstractShardingSphereDataSource
assertThat(resultSet.getString(3), is("init"));
assertThat(resultSet.getInt(4), is(1000));
assertThat(resultSet.getInt(5), is(10000));
- assertThat(resultSet.getInt(6), is(10));
- assertThat(resultSet.getString(7), is("init"));
- assertThat(resultSet.getString(7), is("init"));
assertTrue(resultSet.next());
assertThat(resultSet.getInt(1), is(1001));
assertThat(resultSet.getInt(2), is(11));
assertThat(resultSet.getString(3), is("init"));
assertThat(resultSet.getInt(4), is(1001));
assertThat(resultSet.getInt(5), is(10001));
- assertThat(resultSet.getInt(6), is(11));
- assertThat(resultSet.getString(7), is("init"));
assertFalse(resultSet.next());
}
@@ -105,18 +109,12 @@ public final class CalciteStatementTest extends AbstractShardingSphereDataSource
assertThat(resultSet.getString(3), is("init"));
assertThat(resultSet.getInt(4), is(1000));
assertThat(resultSet.getInt(5), is(10000));
- assertThat(resultSet.getInt(6), is(10));
- assertThat(resultSet.getString(7), is("init"));
- assertThat(resultSet.getString(8), is("t_order_item_calcite_sharding"));
assertTrue(resultSet.next());
assertThat(resultSet.getInt(1), is(1001));
assertThat(resultSet.getInt(2), is(11));
assertThat(resultSet.getString(3), is("init"));
assertThat(resultSet.getInt(4), is(1001));
assertThat(resultSet.getInt(5), is(10001));
- assertThat(resultSet.getInt(6), is(11));
- assertThat(resultSet.getString(7), is("init"));
- assertThat(resultSet.getString(8), is("t_order_item_calcite_sharding"));
assertFalse(resultSet.next());
}
@@ -131,18 +129,12 @@ public final class CalciteStatementTest extends AbstractShardingSphereDataSource
assertThat(resultSet.getString(3), is("init"));
assertThat(resultSet.getInt(4), is(1000));
assertThat(resultSet.getInt(5), is(10000));
- assertThat(resultSet.getInt(6), is(10));
- assertThat(resultSet.getString(7), is("init"));
- assertThat(resultSet.getString(8), is("t_order_item_calcite_sharding"));
assertTrue(resultSet.next());
assertThat(resultSet.getInt(1), is(1001));
assertThat(resultSet.getInt(2), is(11));
assertThat(resultSet.getString(3), is("init"));
assertThat(resultSet.getInt(4), is(1001));
assertThat(resultSet.getInt(5), is(10001));
- assertThat(resultSet.getInt(6), is(11));
- assertThat(resultSet.getString(7), is("init"));
- assertThat(resultSet.getString(8), is("t_order_item_calcite_sharding"));
assertFalse(resultSet.next());
}
@@ -161,4 +153,52 @@ public final class CalciteStatementTest extends AbstractShardingSphereDataSource
assertThat(resultSet.getString(3), is("init"));
assertFalse(resultSet.next());
}
+
+ @Test
+ public void assertQueryWithCalciteInSingleTablesWithEncryptRule() throws SQLException {
+ ShardingSphereStatement preparedStatement = (ShardingSphereStatement) getShardingSphereDataSource().getConnection().createStatement();
+ ResultSet resultSet = preparedStatement.executeQuery(SELECT_SQL_BY_ID_ACROSS_SINGLE_TABLES_WITH_ENCRYPT);
+ assertNotNull(resultSet);
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(0));
+ assertThat(resultSet.getString(2), is("decryptValue"));
+ assertThat(resultSet.getString(3), is("description0"));
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(1));
+ assertThat(resultSet.getString(2), is("decryptValue"));
+ assertThat(resultSet.getString(3), is("description1"));
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(2));
+ assertThat(resultSet.getString(2), is("decryptValue"));
+ assertThat(resultSet.getString(3), is("description2"));
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(3));
+ assertThat(resultSet.getString(2), is("decryptValue"));
+ assertThat(resultSet.getString(3), is("description3"));
+ assertFalse(resultSet.next());
+ }
+
+ @Test
+ public void assertQueryWithCalciteInSingleAndShardingTablesWithEncryptRule() throws SQLException {
+ ShardingSphereStatement preparedStatement = (ShardingSphereStatement) getShardingSphereDataSource().getConnection().createStatement();
+ ResultSet resultSet = preparedStatement.executeQuery(SELECT_SQL_BY_ID_ACROSS_SINGLE_AND_SHARDING_TABLES_WITH_ENCRYPT);
+ assertNotNull(resultSet);
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(0));
+ assertThat(resultSet.getString(2), is("decryptValue"));
+ assertThat(resultSet.getString(3), is("description0"));
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(1));
+ assertThat(resultSet.getString(2), is("decryptValue"));
+ assertThat(resultSet.getString(3), is("description1"));
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(2));
+ assertThat(resultSet.getString(2), is("decryptValue"));
+ assertThat(resultSet.getString(3), is("description2"));
+ assertTrue(resultSet.next());
+ assertThat(resultSet.getInt(1), is(3));
+ assertThat(resultSet.getString(2), is("decryptValue"));
+ assertThat(resultSet.getString(3), is("description3"));
+ assertFalse(resultSet.next());
+ }
}
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/config/config-calcite.yaml b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/config/config-calcite.yaml
index fd60dcc..745591c 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/config/config-calcite.yaml
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/config/config-calcite.yaml
@@ -19,14 +19,60 @@ rules:
- !SHARDING
tables:
t_order_item_calcite_sharding:
- actualDataNodes: calcite_jdbc_1.t_order_item_calcite_sharding_${0..1}
+ actualDataNodes: calcite_ds.t_order_item_calcite_sharding_${0..1}
tableStrategy:
standard:
shardingColumn: item_id
- shardingAlgorithmName: table_inline
+ shardingAlgorithmName: table_inline_item_id
+ t_user_encrypt_calcite_sharding:
+ actualDataNodes: calcite_ds.t_user_encrypt_calcite_sharding_${0..1}
+ tableStrategy:
+ standard:
+ shardingColumn: user_id
+ shardingAlgorithmName: table_inline_user_id
shardingAlgorithms:
- table_inline:
+ table_inline_item_id:
type: INLINE
props:
algorithm-expression: t_order_item_calcite_sharding_${item_id % 2}
+ table_inline_user_id:
+ type: INLINE
+ props:
+ algorithm-expression: t_user_encrypt_calcite_sharding_${user_id % 2}
+ - !ENCRYPT
+ encryptors:
+ encryptor_aes:
+ type: aes
+ props:
+ aes-key-value: 123456abc
+ test:
+ type: test
+ tables:
+ t_user_encrypt_calcite:
+ columns:
+ pwd:
+ plainColumn: plain_pwd
+ cipherColumn: cipher_pwd
+ encryptorName: test
+ t_user_encrypt_calcite_sharding:
+ columns:
+ pwd:
+ plainColumn: plain_pwd
+ cipherColumn: cipher_pwd
+ encryptorName: test
+ - !REPLICA_QUERY
+ dataSources:
+ calcite_ds:
+ name: calcite_ds
+ primaryDataSourceName:
+ - calcite_jdbc_1
+ replicaDataSourceNames:
+ - calcite_jdbc_2
+ loadBalancerName: roundRobin
+ loadBalancers:
+ roundRobin:
+ type: ROUND_ROBIN
+props:
+ sql-show: true
+ query-with-cipher-column: true
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/calcite_data_0.sql b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/calcite_data_0.sql
index 30d1143..f64f735 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/calcite_data_0.sql
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/calcite_data_0.sql
@@ -16,6 +16,12 @@
*/
DELETE FROM t_order_calcite;
+DELETE FROM t_user_info;
INSERT INTO t_order_calcite VALUES(1000, 10, 'init');
-INSERT INTO t_order_calcite VALUES(1001, 11, 'init')
+INSERT INTO t_order_calcite VALUES(1001, 11, 'init');
+
+INSERT INTO t_user_info VALUES(0, 'description0');
+INSERT INTO t_user_info VALUES(1, 'description1');
+INSERT INTO t_user_info VALUES(2, 'description2');
+INSERT INTO t_user_info VALUES(3, 'description3');
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/calcite_data_1.sql b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/calcite_data_1.sql
index acfbcd3..39c6865 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/calcite_data_1.sql
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/calcite_data_1.sql
@@ -18,6 +18,9 @@
DELETE FROM t_order_item_calcite;
DELETE FROM t_order_item_calcite_sharding_0;
DELETE FROM t_order_item_calcite_sharding_1;
+DELETE FROM t_user_encrypt_calcite;
+DELETE FROM t_user_encrypt_calcite_sharding_0;
+DELETE FROM t_user_encrypt_calcite_sharding_1;
INSERT INTO t_order_item_calcite VALUES(100000, 1000, 10, 'init');
INSERT INTO t_order_item_calcite VALUES(100001, 1000, 10, 'init');
@@ -28,3 +31,17 @@ INSERT INTO t_order_item_calcite_sharding_0 VALUES(1000, 10000, 10, 'init', 't_o
INSERT INTO t_order_item_calcite_sharding_1 VALUES(1001, 10001, 11, 'init', 't_order_item_calcite_sharding');
INSERT INTO t_order_item_calcite_sharding_0 VALUES(1010, 10001, 10, 'init', 't_order_item_calcite_sharding');
INSERT INTO t_order_item_calcite_sharding_1 VALUES(1011, 10001, 10, 'init', 't_order_item_calcite_sharding');
+
+INSERT INTO t_user_encrypt_calcite VALUES(0, 'plain password1', 'U2FsdGVkX19XstrYErommVKzbiaDrBzs5hkJnfw2iqY=', 'Rachel');
+INSERT INTO t_user_encrypt_calcite VALUES(1, 'plain password2', 'U2FsdGVkX1+qxFaxftJmfwfUT8Q8e8QnNesNZjP9jLo=', 'Monica');
+INSERT INTO t_user_encrypt_calcite VALUES(2, 'plain password3', 'U2FsdGVkX19d4VBBrjaSC7QX9tsdNFcpMNaZaxmJyYs=', 'Phoebe');
+INSERT INTO t_user_encrypt_calcite VALUES(3, 'plain password4', 'U2FsdGVkX18viyM9JBybwvkks0qCmP56vojYnniqZOo=', 'Ross');
+INSERT INTO t_user_encrypt_calcite VALUES(4, 'plain password5', 'U2FsdGVkX1+Lbc0iRrg/vk5BDQ+hHqbFEN7TFXztjro=', 'Chandler');
+INSERT INTO t_user_encrypt_calcite VALUES(5, 'plain password6', 'U2FsdGVkX1/liIWOUCQ2W6sUgkGDSKZ/3QEiMTZtIsg=', 'Joey');
+
+INSERT INTO t_user_encrypt_calcite_sharding_0 VALUES(0, 'plain passwordA', 'U2FsdGVkX1/TAZ4ul/UUIbvMJvbZ4SLibJzd7pwmDOM=', 'Rachel');
+INSERT INTO t_user_encrypt_calcite_sharding_1 VALUES(1, 'plain passwordB', 'U2FsdGVkX1/EWOAnnTvPQIsdleGxw01nnnEa2VUk8vo=', 'Monica');
+INSERT INTO t_user_encrypt_calcite_sharding_0 VALUES(2, 'plain passwordC', 'U2FsdGVkX19nXmysCFFcVtUFriy5ev7s0RsNT1XNJKg=', 'Phoebe');
+INSERT INTO t_user_encrypt_calcite_sharding_1 VALUES(3, 'plain passwordD', 'U2FsdGVkX19KwrlD1BIcVtxGOSXGguAUCb2SydUhqRc=', 'Ross');
+INSERT INTO t_user_encrypt_calcite_sharding_0 VALUES(4, 'plain passwordE', 'U2FsdGVkX1+70hUvvGH16ddUxpK1s5sJXQLQRNbcNeE=', 'Chandler');
+INSERT INTO t_user_encrypt_calcite_sharding_1 VALUES(5, 'plain passwordF', 'U2FsdGVkX191hCfwkImzIRP1DIAK2M9FLgQSFHa1zlE=', 'Joey');
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/calcite_data_2.sql b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/calcite_data_2.sql
new file mode 100644
index 0000000..39c6865
--- /dev/null
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/calcite_data_2.sql
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+DELETE FROM t_order_item_calcite;
+DELETE FROM t_order_item_calcite_sharding_0;
+DELETE FROM t_order_item_calcite_sharding_1;
+DELETE FROM t_user_encrypt_calcite;
+DELETE FROM t_user_encrypt_calcite_sharding_0;
+DELETE FROM t_user_encrypt_calcite_sharding_1;
+
+INSERT INTO t_order_item_calcite VALUES(100000, 1000, 10, 'init');
+INSERT INTO t_order_item_calcite VALUES(100001, 1000, 10, 'init');
+INSERT INTO t_order_item_calcite VALUES(100100, 1001, 10, 'init');
+INSERT INTO t_order_item_calcite VALUES(100101, 1001, 10, 'init');
+
+INSERT INTO t_order_item_calcite_sharding_0 VALUES(1000, 10000, 10, 'init', 't_order_item_calcite_sharding');
+INSERT INTO t_order_item_calcite_sharding_1 VALUES(1001, 10001, 11, 'init', 't_order_item_calcite_sharding');
+INSERT INTO t_order_item_calcite_sharding_0 VALUES(1010, 10001, 10, 'init', 't_order_item_calcite_sharding');
+INSERT INTO t_order_item_calcite_sharding_1 VALUES(1011, 10001, 10, 'init', 't_order_item_calcite_sharding');
+
+INSERT INTO t_user_encrypt_calcite VALUES(0, 'plain password1', 'U2FsdGVkX19XstrYErommVKzbiaDrBzs5hkJnfw2iqY=', 'Rachel');
+INSERT INTO t_user_encrypt_calcite VALUES(1, 'plain password2', 'U2FsdGVkX1+qxFaxftJmfwfUT8Q8e8QnNesNZjP9jLo=', 'Monica');
+INSERT INTO t_user_encrypt_calcite VALUES(2, 'plain password3', 'U2FsdGVkX19d4VBBrjaSC7QX9tsdNFcpMNaZaxmJyYs=', 'Phoebe');
+INSERT INTO t_user_encrypt_calcite VALUES(3, 'plain password4', 'U2FsdGVkX18viyM9JBybwvkks0qCmP56vojYnniqZOo=', 'Ross');
+INSERT INTO t_user_encrypt_calcite VALUES(4, 'plain password5', 'U2FsdGVkX1+Lbc0iRrg/vk5BDQ+hHqbFEN7TFXztjro=', 'Chandler');
+INSERT INTO t_user_encrypt_calcite VALUES(5, 'plain password6', 'U2FsdGVkX1/liIWOUCQ2W6sUgkGDSKZ/3QEiMTZtIsg=', 'Joey');
+
+INSERT INTO t_user_encrypt_calcite_sharding_0 VALUES(0, 'plain passwordA', 'U2FsdGVkX1/TAZ4ul/UUIbvMJvbZ4SLibJzd7pwmDOM=', 'Rachel');
+INSERT INTO t_user_encrypt_calcite_sharding_1 VALUES(1, 'plain passwordB', 'U2FsdGVkX1/EWOAnnTvPQIsdleGxw01nnnEa2VUk8vo=', 'Monica');
+INSERT INTO t_user_encrypt_calcite_sharding_0 VALUES(2, 'plain passwordC', 'U2FsdGVkX19nXmysCFFcVtUFriy5ev7s0RsNT1XNJKg=', 'Phoebe');
+INSERT INTO t_user_encrypt_calcite_sharding_1 VALUES(3, 'plain passwordD', 'U2FsdGVkX19KwrlD1BIcVtxGOSXGguAUCb2SydUhqRc=', 'Ross');
+INSERT INTO t_user_encrypt_calcite_sharding_0 VALUES(4, 'plain passwordE', 'U2FsdGVkX1+70hUvvGH16ddUxpK1s5sJXQLQRNbcNeE=', 'Chandler');
+INSERT INTO t_user_encrypt_calcite_sharding_1 VALUES(5, 'plain passwordF', 'U2FsdGVkX191hCfwkImzIRP1DIAK2M9FLgQSFHa1zlE=', 'Joey');
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_init_calcite_0.sql b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_init_calcite_0.sql
index 188ca15..6dbc09e 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_init_calcite_0.sql
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_init_calcite_0.sql
@@ -16,3 +16,4 @@
*/
CREATE TABLE IF NOT EXISTS t_order_calcite (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
+CREATE TABLE IF NOT EXISTS t_user_info (user_id INT NOT NULL, information VARCHAR(45) NULL, PRIMARY KEY (user_id));
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_init_calcite_1.sql b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_init_calcite_1.sql
index 46e2bd0..1e9d27f 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_init_calcite_1.sql
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_init_calcite_1.sql
@@ -18,3 +18,6 @@
CREATE TABLE IF NOT EXISTS t_order_item_calcite (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_0 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, remarks VARCHAR(45) NULL, PRIMARY KEY (item_id));
CREATE TABLE IF NOT EXISTS t_order_item_calcite_sharding_1 (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, remarks VARCHAR(45) NULL, PRIMARY KEY (item_id));
+CREATE TABLE IF NOT EXISTS t_user_encrypt_calcite (user_id INT NOT NULL, plain_pwd VARCHAR(45) NULL, cipher_pwd VARCHAR(45) NULL, user_name VARCHAR(45) NULL, PRIMARY KEY (user_id));
+CREATE TABLE IF NOT EXISTS t_user_encrypt_calcite_sharding_0 (user_id INT NOT NULL, plain_pwd VARCHAR(45) NULL, cipher_pwd VARCHAR(45) NULL, user_name VARCHAR(45) NULL, PRIMARY KEY (user_id));
+CREATE TABLE IF NOT EXISTS t_user_encrypt_calcite_sharding_1 (user_id INT NOT NULL, plain_pwd VARCHAR(45) NULL, cipher_pwd VARCHAR(45) NULL, user_name VARCHAR(45) NULL, PRIMARY KEY (user_id));