Bug Report

For 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.4.0

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

ShardingSphere-JDBC with SharingSphere-Mask

Expected behavior

[
  {
    "actor_id": 77,
    "film_id": 8,
    "last_update": "2006-02-15 05:03:42",
    "first_name": "CARY",
    "last_name": "M********Y",
    "title": "AIRPORT POLLOCK",
    "description": "A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India",
    "release_year": 2006,
    "language_id": 1,
    "original_language_id": null,
    "rental_duration": 6,
    "rental_rate": 4.99,
    "length": 54,
    "replacement_cost": 15.99,
    "rating": "R",
    "special_features": "Trailers"
  }
]

Actual behavior

[
  {
    "actor_id": 1,
    "first_name": 1,
    "last_name": "2006-02-15 05:05:03",
    "last_update": 79
  }
]

Reason analyze (If you can)

May MaskingContext pollute MetadataContext

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

1. build.gradle.kts

   dependencies {
       // ShardingSphere
       implementation("org.apache.shardingsphere:shardingsphere:5.4.0")
       implementation("org.apache.shardingsphere:shardingsphere-jdbc:5.4.0")
       implementation("org.apache.shardingsphere:shardingsphere-jdbc-core:5.4.0")
   
       // ShardingSphere Mask
       implementation("org.apache.shardingsphere:shardingsphere-mask:5.4.0")
       implementation("org.apache.shardingsphere:shardingsphere-mask-api:5.4.0")
       implementation("org.apache.shardingsphere:shardingsphere-mask-core:5.4.0")
       implementation("org.apache.shardingsphere:shardingsphere-mask-distsql:5.4.0")
       implementation("org.apache.shardingsphere:shardingsphere-mask-distsql-parser:5.4.0")
       implementation("org.apache.shardingsphere:shardingsphere-mask-distsql-statement:5.4.0")
       implementation("org.apache.shardingsphere:shardingsphere-mask-distsql-handler:5.4.0")
       implementation("org.apache.shardingsphere:shardingsphere-mask-distsql-parser:5.4.0")
   
       // jdbc
       runtimeOnly("com.mysql:mysql-connector-j")
   }

2. Prepare MySQL

   docker run --name mysql -e MYSQL_ROOT_PASSWORD=root -d -p 3306:3306 mysql

3. Run these SQLs

   -- Sakila MySQL Sample Database: https://dev.mysql.com/doc/sakila/en/
   -- Use sakila or run this query.
   
   CREATE DATABASE sakila;
   USE sakila;
   
   -- DDL
   CREATE TABLE `actor` (
     `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
     `first_name` varchar(45) NOT NULL,
     `last_name` varchar(45) NOT NULL,
     `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (`actor_id`)
   );
   CREATE TABLE `film` (
     `film_id` smallint unsigned NOT NULL AUTO_INCREMENT,
     `title` varchar(128) NOT NULL,
     `description` text,
     `release_year` year DEFAULT NULL,
     `language_id` tinyint unsigned NOT NULL,
     `original_language_id` tinyint unsigned DEFAULT NULL,
     `rental_duration` tinyint unsigned NOT NULL DEFAULT '3',
     `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
     `length` smallint unsigned DEFAULT NULL,
     `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
     `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
     `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
     `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (`film_id`)
   );
   CREATE TABLE `film_actor` (
     `actor_id` smallint unsigned NOT NULL,
     `film_id` smallint unsigned NOT NULL,
     `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (`actor_id`,`film_id`)
   );
   
   -- DML
   INSERT INTO sakila.actor (actor_id, first_name, last_name, last_update, social_number) VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33', '010-1234-1234');
   INSERT INTO sakila.film (film_id, title, description, release_year, language_id, original_language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features, last_update) VALUES (1, 'ACADEMY DINOSAUR', 'A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies', 2006, 1, null, 6, 0.99, 86, 20.99, 'PG', 'Deleted Scenes,Behind the Scenes', '2006-02-15 05:03:42');
   INSERT INTO sakila.film_actor (actor_id, film_id, last_update) VALUES (1, 1, '2006-02-15 05:05:03');

4. Run this Query with ShardingSphere-Masking

   SELECT *
   FROM film_actor
            JOIN actor AS a ON a.actor_id
            JOIN film AS f ON f.film_id
   LIMIT 1;

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

fun main() {

	// Hikari
	val hikariDataSource = HikariDataSource().apply {
		driverClassName = "com.mysql.jdbc.Driver"
		jdbcUrl = "jdbc:mysql://localhost:3306/sakila"
		username = "root"
		password = "root"
	}
	
	// ShardingSphere
	val maskRuleConfiguration = MaskRuleConfiguration(
		Collections.singleton(MaskTableRuleConfiguration ("actor", listOf(
			MaskColumnRuleConfiguration("first_name", "first_name_mask"),
		))),
		mapOf(
			"first_name_mask" to AlgorithmConfiguration("KEEP_FIRST_N_LAST_M", Properties().apply {
				setProperty("first-n", "1")
				setProperty("last-m", "1")
				setProperty("replace-char", "*")
			}),
		)
	)
	
	val dataSource = ShardingSphereDataSourceFactory.createDataSource(
		hikariDataSource,
		listOf(maskRuleConfiguration),
		Properties()
	)

	// Query 
	val sql = "SELECT * FROM film_actor\n" +
		  "         JOIN actor AS a ON a.actor_id\n" +
		  "         JOIN film AS f ON f.film_id\n" +
		  "LIMIT 1"

	// Query directly (Expected)
	query(sql, hikariDataSource.connection)

	// Query with ShardingSphere (Actual)
	query(sql, dataSource.connection)
}

fun query(sql: String, connection: Connection) {
	val statement = connection.createStatement()
	val resultSet = statement.executeQuery(sql)

	while (resultSet.next()) {
		for (i in 1..resultSet.metaData.columnCount) {
			println("${resultSet.metaData.getColumnName(i)}: ${resultSet.getString(i)} ")
		}
		println()
	}
}