giggs131400 commented on pull request #9346:
URL: https://github.com/apache/shardingsphere/pull/9346#issuecomment-775166117
I reproduced the issue on my local again based on the latest commit
(9424b261f6a125a06890c706bf56f7dd410423f3) on master branch.
The step how to reproduce:
1. create table as:
```sql
create table t_student (
id bigint auto_increment primary key,
name varchar(20),
name_encrypt varchar(30)
);
create table t_score (
id bigint auto_increment primary key,
student_id bigint,
score int
);
```
2. unit test code:
```java
package com.java.se.framework.shardingsphere.encrypt;
import com.zaxxer.hikari.HikariDataSource;
import jdk.nashorn.internal.ir.annotations.Ignore;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import
org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource;
import org.apache.shardingsphere.encrypt.api.config.EncryptRuleConfiguration;
import
org.apache.shardingsphere.encrypt.api.config.rule.EncryptColumnRuleConfiguration;
import
org.apache.shardingsphere.encrypt.api.config.rule.EncryptTableRuleConfiguration;
import
org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmConfiguration;
import org.junit.jupiter.api.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
public class TwoTableEncryptTest {
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/encrypt";
private static String username = "root";
private static String password = "123456";
private ShardingSphereDataSource dataSource;
@BeforeEach
public void init() throws SQLException {
// 配置数据源
HikariDataSource ds = new HikariDataSource();
ds.setDriverClassName(driver);
ds.setJdbcUrl(url);
ds.setUsername(username);
ds.setPassword(password);
// 配置脱敏规则
Properties props = new Properties();
props.setProperty("aes-key-value", "123456");
props.setProperty("query-with-cipher-column", "true");
// 定义加密算法
Map<String, ShardingSphereAlgorithmConfiguration>
encryptAlgorithmConfigs = new LinkedHashMap<>(2, 1);
encryptAlgorithmConfigs.put("name_encryptor", new
ShardingSphereAlgorithmConfiguration("aes", props));
// 定义加密列
EncryptColumnRuleConfiguration encryptColumnConfig = new
EncryptColumnRuleConfiguration(
"name", "name_encrypt", "", "name", "name_encryptor"
);
// 定义加密表
EncryptTableRuleConfiguration encryptTableConfig = new
EncryptTableRuleConfiguration(
"t_student",
Arrays.asList(encryptColumnConfig)
);
// 定义加密策略
EncryptRuleConfiguration encryptRuleConfig = new
EncryptRuleConfiguration(Collections.singleton(encryptTableConfig),
encryptAlgorithmConfigs);
// 获取数据源对象
this.dataSource =
(ShardingSphereDataSource)ShardingSphereDataSourceFactory.createDataSource(ds,
Collections.singleton(encryptRuleConfig), props);
}
@AfterEach
public void close() throws Exception {
if(dataSource != null) {
dataSource.close();
}
}
@Test
@Disabled
public void prepare() throws SQLException {
String sqlt = "insert into t_student(name) values(?)";
String sqls = "insert into t_score(student_id, score) values(?, ?)";
try(
Connection connection = dataSource.getConnection();
PreparedStatement pst = connection.prepareStatement(sqlt);
PreparedStatement pss = connection.prepareStatement(sqls);
) {
pst.setString(1, "aaa");
pss.setLong(1, 1L);
pss.setInt(2, 80);
int cntt = pst.executeUpdate();
int cnts = pss.executeUpdate();
Assertions.assertEquals(1, cntt);
Assertions.assertEquals(1, cnts);
}
}
@Test
public void select1() throws SQLException {
String sql = "select name, sc.score from t_student s left join
t_score sc on s.id = sc.student_id";
try(
Connection connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet resultSet = ps.executeQuery();
) {
int count = 0;
while (resultSet.next()) {
String name = resultSet.getString("name");
Assertions.assertEquals("aaa", name);
Integer score = resultSet.getInt("score");
Assertions.assertEquals(80, score);
count++;
}
Assertions.assertEquals(1, count);
}
}
@Test
public void select2() throws SQLException {
String sql = "select name, sc.score from t_score sc left join
t_student s on s.id = sc.student_id";
try(
Connection connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet resultSet = ps.executeQuery();
) {
int count = 0;
while (resultSet.next()) {
String name = resultSet.getString("name");
Assertions.assertEquals("aaa", name);
Integer score = resultSet.getInt("score");
Assertions.assertEquals(80, score);
count++;
}
Assertions.assertEquals(1, count);
}
}
}
```
3. in the junit test, run prepare() method firstly
4. update the table:
```sql
update t_student set name = null;
```
5. in the junit test, run select1() and select2()
expect result:
- select1() and select2() should be success
actually result:
- select1() success
- select2() failed
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
[email protected]
