This is an automated email from the ASF dual-hosted git repository.
jmclean pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/gravitino.git
The following commit(s) were added to refs/heads/main by this push:
new 6bb689e63c [#10173] improvement(core): Guard the empty list case in
sql generation for PostgreSQL (#10261)
6bb689e63c is described below
commit 6bb689e63c43c4bc80af0d03580b92bf09e5a56c
Author: HONGGEUN JI <[email protected]>
AuthorDate: Wed Apr 8 02:38:26 2026 -0400
[#10173] improvement(core): Guard the empty list case in sql generation for
PostgreSQL (#10261)
### What changes were proposed in this pull request?
Guard empty roleIds from generating invalid PostgreSQL IN () SQL in
UserRoleRel and GroupRoleRel providers.
### Why are the changes needed?
It's a possible bug and can be eliminated by this improvement.
Fix: #10173
### Does this PR introduce _any_ user-facing change?
No
### How was this patch tested?
UTs
---
.../provider/base/GroupRoleRelBaseSQLProvider.java | 10 +-
.../provider/base/UserRoleRelBaseSQLProvider.java | 14 ++-
.../postgresql/GroupRoleRelPostgreSQLProvider.java | 10 +-
.../postgresql/UserRoleRelPostgreSQLProvider.java | 10 +-
.../base/TestGroupRoleRelBaseSQLProvider.java | 102 +++++++++++++++++++++
.../base/TestUserRoleRelBaseSQLProvider.java | 102 +++++++++++++++++++++
.../TestGroupRoleRelPostgreSQLProvider.java | 102 +++++++++++++++++++++
.../TestUserRoleRelPostgreSQLProvider.java | 102 +++++++++++++++++++++
8 files changed, 446 insertions(+), 6 deletions(-)
diff --git
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/base/GroupRoleRelBaseSQLProvider.java
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/base/GroupRoleRelBaseSQLProvider.java
index ab0347cba7..733ae73079 100644
---
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/base/GroupRoleRelBaseSQLProvider.java
+++
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/base/GroupRoleRelBaseSQLProvider.java
@@ -88,11 +88,19 @@ public class GroupRoleRelBaseSQLProvider {
+ GROUP_ROLE_RELATION_TABLE_NAME
+ " SET deleted_at = (UNIX_TIMESTAMP() * 1000.0)"
+ " + EXTRACT(MICROSECOND FROM CURRENT_TIMESTAMP(3)) / 1000"
- + " WHERE group_id = #{groupId} AND role_id in ("
+ + " WHERE group_id = #{groupId} "
+ + "<choose>"
+ + "<when test='roleIds != null and roleIds.size() > 0'>"
+ + "AND role_id IN ("
+ "<foreach collection='roleIds' item='roleId' separator=','>"
+ "#{roleId}"
+ "</foreach>"
+ ") "
+ + "</when>"
+ + "<otherwise>"
+ + "AND 1 = 0 "
+ + "</otherwise>"
+ + "</choose>"
+ "AND deleted_at = 0"
+ "</script>";
}
diff --git
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/base/UserRoleRelBaseSQLProvider.java
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/base/UserRoleRelBaseSQLProvider.java
index 3d197df540..08076e46d2 100644
---
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/base/UserRoleRelBaseSQLProvider.java
+++
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/base/UserRoleRelBaseSQLProvider.java
@@ -87,12 +87,20 @@ public class UserRoleRelBaseSQLProvider {
+ USER_ROLE_RELATION_TABLE_NAME
+ " SET deleted_at = (UNIX_TIMESTAMP() * 1000.0)"
+ " + EXTRACT(MICROSECOND FROM CURRENT_TIMESTAMP(3)) / 1000"
- + " WHERE user_id = #{userId} AND role_id IN ("
+ + " WHERE user_id = #{userId} "
+ + "<choose>"
+ + "<when test='roleIds != null and roleIds.size() > 0'>"
+ + "AND role_id IN ("
+ "<foreach collection='roleIds' item='roleId' separator=','>"
+ "#{roleId}"
+ "</foreach>"
- + " )"
- + " AND deleted_at = 0"
+ + ") "
+ + "</when>"
+ + "<otherwise>"
+ + "AND 1 = 0 "
+ + "</otherwise>"
+ + "</choose>"
+ + "AND deleted_at = 0"
+ "</script>";
}
diff --git
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/GroupRoleRelPostgreSQLProvider.java
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/GroupRoleRelPostgreSQLProvider.java
index 040f17dfc3..2ab7d9f5c0 100644
---
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/GroupRoleRelPostgreSQLProvider.java
+++
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/GroupRoleRelPostgreSQLProvider.java
@@ -40,11 +40,19 @@ public class GroupRoleRelPostgreSQLProvider extends
GroupRoleRelBaseSQLProvider
+ "UPDATE "
+ GROUP_ROLE_RELATION_TABLE_NAME
+ " SET deleted_at = CAST(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) * 1000
AS BIGINT)"
- + " WHERE group_id = #{groupId} AND role_id IN ("
+ + " WHERE group_id = #{groupId} "
+ + "<choose>"
+ + "<when test='roleIds != null and roleIds.size() > 0'>"
+ + "AND role_id IN ("
+ "<foreach collection='roleIds' item='roleId' separator=','>"
+ "#{roleId}"
+ "</foreach>"
+ ") "
+ + "</when>"
+ + "<otherwise>"
+ + "AND 1 = 0 "
+ + "</otherwise>"
+ + "</choose>"
+ "AND deleted_at = 0"
+ "</script>";
}
diff --git
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/UserRoleRelPostgreSQLProvider.java
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/UserRoleRelPostgreSQLProvider.java
index 7d798a9bad..e210069809 100644
---
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/UserRoleRelPostgreSQLProvider.java
+++
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/UserRoleRelPostgreSQLProvider.java
@@ -42,11 +42,19 @@ public class UserRoleRelPostgreSQLProvider extends
UserRoleRelBaseSQLProvider {
+ "UPDATE "
+ USER_ROLE_RELATION_TABLE_NAME
+ " SET deleted_at = CAST(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) * 1000
AS BIGINT)"
- + " WHERE user_id = #{userId} AND role_id IN ("
+ + " WHERE user_id = #{userId} "
+ + "<choose>"
+ + "<when test='roleIds != null and roleIds.size() > 0'>"
+ + "AND role_id IN ("
+ "<foreach collection='roleIds' item='roleId' separator=','>"
+ "#{roleId}"
+ "</foreach>"
+ ") "
+ + "</when>"
+ + "<otherwise>"
+ + "AND 1 = 0 "
+ + "</otherwise>"
+ + "</choose>"
+ "AND deleted_at = 0"
+ "</script>";
}
diff --git
a/core/src/test/java/org/apache/gravitino/storage/relational/mapper/provider/base/TestGroupRoleRelBaseSQLProvider.java
b/core/src/test/java/org/apache/gravitino/storage/relational/mapper/provider/base/TestGroupRoleRelBaseSQLProvider.java
new file mode 100644
index 0000000000..cd6fe3d3d7
--- /dev/null
+++
b/core/src/test/java/org/apache/gravitino/storage/relational/mapper/provider/base/TestGroupRoleRelBaseSQLProvider.java
@@ -0,0 +1,102 @@
+/*
+ * 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.
+ */
+package org.apache.gravitino.storage.relational.mapper.provider.base;
+
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.Map;
+import org.apache.ibatis.mapping.BoundSql;
+import org.apache.ibatis.mapping.SqlSource;
+import org.apache.ibatis.scripting.xmltags.XMLLanguageDriver;
+import org.apache.ibatis.session.Configuration;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+public class TestGroupRoleRelBaseSQLProvider {
+
+ @Test
+ void testSoftDeleteGroupRoleRelByGroupAndRolesWithEmptyRoles() {
+ GroupRoleRelBaseSQLProvider provider = new GroupRoleRelBaseSQLProvider();
+ String script = provider.softDeleteGroupRoleRelByGroupAndRoles(1L,
Collections.emptyList());
+
+ SqlSource sqlSource =
+ new XMLLanguageDriver().createSqlSource(new Configuration(), script,
Map.class);
+ Map<String, Object> params = new HashMap<>();
+ params.put("groupId", 1L);
+ params.put("roleIds", Collections.emptyList());
+
+ BoundSql boundSql = sqlSource.getBoundSql(params);
+ String normalizedSql = boundSql.getSql().replaceAll("\\s+", " ").trim();
+
+ Assertions.assertFalse(
+ normalizedSql.matches(".*\\bIN\\s*\\(\\s*\\).*"),
+ "Empty roleIds should not generate invalid SQL IN (...) with no
values");
+
+ Assertions.assertTrue(
+ normalizedSql.matches(".*\\b1\\s*=\\s*0\\b.*"),
+ "Empty roleIds should result in an unsatisfiable WHERE clause (e.g.,
AND 1 = 0)");
+ }
+
+ @Test
+ void testSoftDeleteGroupRoleRelByGroupAndRolesWithNonEmptyRoles() {
+ GroupRoleRelBaseSQLProvider provider = new GroupRoleRelBaseSQLProvider();
+ String script = provider.softDeleteGroupRoleRelByGroupAndRoles(1L,
Arrays.asList(100L, 200L));
+
+ SqlSource sqlSource =
+ new XMLLanguageDriver().createSqlSource(new Configuration(), script,
Map.class);
+ Map<String, Object> params = new HashMap<>();
+ params.put("groupId", 1L);
+ params.put("roleIds", Arrays.asList(100L, 200L));
+
+ BoundSql boundSql = sqlSource.getBoundSql(params);
+ String normalizedSql = boundSql.getSql().replaceAll("\\s+", " ").trim();
+
+ Assertions.assertTrue(
+ normalizedSql.matches(".*\\brole_id\\s+IN\\s*\\(.*\\).*"),
+ "Non-empty roleIds should generate SQL with role_id IN (...) clause");
+
+ Assertions.assertFalse(
+ normalizedSql.matches(".*\\b1\\s*=\\s*0\\b.*"),
+ "Non-empty roleIds should not contain unsatisfiable WHERE clause (1 =
0)");
+ }
+
+ @Test
+ void testSoftDeleteGroupRoleRelByGroupAndRolesWithNullRoles() {
+ GroupRoleRelBaseSQLProvider provider = new GroupRoleRelBaseSQLProvider();
+ String script = provider.softDeleteGroupRoleRelByGroupAndRoles(1L, null);
+
+ SqlSource sqlSource =
+ new XMLLanguageDriver().createSqlSource(new Configuration(), script,
Map.class);
+ Map<String, Object> params = new HashMap<>();
+ params.put("groupId", 1L);
+ params.put("roleIds", null);
+
+ BoundSql boundSql = sqlSource.getBoundSql(params);
+ String normalizedSql = boundSql.getSql().replaceAll("\\s+", " ").trim();
+
+ Assertions.assertFalse(
+ normalizedSql.matches(".*\\bIN\\s*\\(\\s*\\).*"),
+ "Null roleIds should not generate invalid SQL IN (...) with no
values");
+
+ Assertions.assertTrue(
+ normalizedSql.matches(".*\\b1\\s*=\\s*0\\b.*"),
+ "Null roleIds should result in an unsatisfiable WHERE clause (e.g.,
AND 1 = 0)");
+ }
+}
diff --git
a/core/src/test/java/org/apache/gravitino/storage/relational/mapper/provider/base/TestUserRoleRelBaseSQLProvider.java
b/core/src/test/java/org/apache/gravitino/storage/relational/mapper/provider/base/TestUserRoleRelBaseSQLProvider.java
new file mode 100644
index 0000000000..07fdcaa6c0
--- /dev/null
+++
b/core/src/test/java/org/apache/gravitino/storage/relational/mapper/provider/base/TestUserRoleRelBaseSQLProvider.java
@@ -0,0 +1,102 @@
+/*
+ * 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.
+ */
+package org.apache.gravitino.storage.relational.mapper.provider.base;
+
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.Map;
+import org.apache.ibatis.mapping.BoundSql;
+import org.apache.ibatis.mapping.SqlSource;
+import org.apache.ibatis.scripting.xmltags.XMLLanguageDriver;
+import org.apache.ibatis.session.Configuration;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+public class TestUserRoleRelBaseSQLProvider {
+
+ @Test
+ void testSoftDeleteUserRoleRelByUserAndRolesWithEmptyRoles() {
+ UserRoleRelBaseSQLProvider provider = new UserRoleRelBaseSQLProvider();
+ String script = provider.softDeleteUserRoleRelByUserAndRoles(1L,
Collections.emptyList());
+
+ SqlSource sqlSource =
+ new XMLLanguageDriver().createSqlSource(new Configuration(), script,
Map.class);
+ Map<String, Object> params = new HashMap<>();
+ params.put("userId", 1L);
+ params.put("roleIds", Collections.emptyList());
+
+ BoundSql boundSql = sqlSource.getBoundSql(params);
+ String normalizedSql = boundSql.getSql().replaceAll("\\s+", " ").trim();
+
+ Assertions.assertFalse(
+ normalizedSql.matches(".*\\bIN\\s*\\(\\s*\\).*"),
+ "Empty roleIds should not generate invalid SQL IN (...) with no
values");
+
+ Assertions.assertTrue(
+ normalizedSql.matches(".*\\b1\\s*=\\s*0\\b.*"),
+ "Empty roleIds should result in an unsatisfiable WHERE clause (e.g.,
AND 1 = 0)");
+ }
+
+ @Test
+ void testSoftDeleteUserRoleRelByUserAndRolesWithNonEmptyRoles() {
+ UserRoleRelBaseSQLProvider provider = new UserRoleRelBaseSQLProvider();
+ String script = provider.softDeleteUserRoleRelByUserAndRoles(1L,
Arrays.asList(100L, 200L));
+
+ SqlSource sqlSource =
+ new XMLLanguageDriver().createSqlSource(new Configuration(), script,
Map.class);
+ Map<String, Object> params = new HashMap<>();
+ params.put("userId", 1L);
+ params.put("roleIds", Arrays.asList(100L, 200L));
+
+ BoundSql boundSql = sqlSource.getBoundSql(params);
+ String normalizedSql = boundSql.getSql().replaceAll("\\s+", " ").trim();
+
+ Assertions.assertTrue(
+ normalizedSql.matches(".*\\brole_id\\s+IN\\s*\\(.*\\).*"),
+ "Non-empty roleIds should generate SQL with role_id IN (...) clause");
+
+ Assertions.assertFalse(
+ normalizedSql.matches(".*\\b1\\s*=\\s*0\\b.*"),
+ "Non-empty roleIds should not contain unsatisfiable WHERE clause (1 =
0)");
+ }
+
+ @Test
+ void testSoftDeleteUserRoleRelByUserAndRolesWithNullRoles() {
+ UserRoleRelBaseSQLProvider provider = new UserRoleRelBaseSQLProvider();
+ String script = provider.softDeleteUserRoleRelByUserAndRoles(1L, null);
+
+ SqlSource sqlSource =
+ new XMLLanguageDriver().createSqlSource(new Configuration(), script,
Map.class);
+ Map<String, Object> params = new HashMap<>();
+ params.put("userId", 1L);
+ params.put("roleIds", null);
+
+ BoundSql boundSql = sqlSource.getBoundSql(params);
+ String normalizedSql = boundSql.getSql().replaceAll("\\s+", " ").trim();
+
+ Assertions.assertFalse(
+ normalizedSql.matches(".*\\bIN\\s*\\(\\s*\\).*"),
+ "Null roleIds should not generate invalid SQL IN (...) with no
values");
+
+ Assertions.assertTrue(
+ normalizedSql.matches(".*\\b1\\s*=\\s*0\\b.*"),
+ "Null roleIds should result in an unsatisfiable WHERE clause (e.g.,
AND 1 = 0)");
+ }
+}
diff --git
a/core/src/test/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/TestGroupRoleRelPostgreSQLProvider.java
b/core/src/test/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/TestGroupRoleRelPostgreSQLProvider.java
new file mode 100644
index 0000000000..8fcd67d1a5
--- /dev/null
+++
b/core/src/test/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/TestGroupRoleRelPostgreSQLProvider.java
@@ -0,0 +1,102 @@
+/*
+ * 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.
+ */
+package org.apache.gravitino.storage.relational.mapper.provider.postgresql;
+
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.Map;
+import org.apache.ibatis.mapping.BoundSql;
+import org.apache.ibatis.mapping.SqlSource;
+import org.apache.ibatis.scripting.xmltags.XMLLanguageDriver;
+import org.apache.ibatis.session.Configuration;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+public class TestGroupRoleRelPostgreSQLProvider {
+
+ @Test
+ void testSoftDeleteGroupRoleRelByGroupAndRolesWithEmptyRoles() {
+ GroupRoleRelPostgreSQLProvider provider = new
GroupRoleRelPostgreSQLProvider();
+ String script = provider.softDeleteGroupRoleRelByGroupAndRoles(1L,
Collections.emptyList());
+
+ SqlSource sqlSource =
+ new XMLLanguageDriver().createSqlSource(new Configuration(), script,
Map.class);
+ Map<String, Object> params = new HashMap<>();
+ params.put("groupId", 1L);
+ params.put("roleIds", Collections.emptyList());
+
+ BoundSql boundSql = sqlSource.getBoundSql(params);
+ String normalizedSql = boundSql.getSql().replaceAll("\\s+", " ").trim();
+
+ Assertions.assertFalse(
+ normalizedSql.matches(".*\\bIN\\s*\\(\\s*\\).*"),
+ "Empty roleIds should not generate invalid SQL IN (...) with no
values");
+
+ Assertions.assertTrue(
+ normalizedSql.matches(".*\\b1\\s*=\\s*0\\b.*"),
+ "Empty roleIds should result in an unsatisfiable WHERE clause (e.g.,
AND 1 = 0)");
+ }
+
+ @Test
+ void testSoftDeleteGroupRoleRelByGroupAndRolesWithNonEmptyRoles() {
+ GroupRoleRelPostgreSQLProvider provider = new
GroupRoleRelPostgreSQLProvider();
+ String script = provider.softDeleteGroupRoleRelByGroupAndRoles(1L,
Arrays.asList(100L, 200L));
+
+ SqlSource sqlSource =
+ new XMLLanguageDriver().createSqlSource(new Configuration(), script,
Map.class);
+ Map<String, Object> params = new HashMap<>();
+ params.put("groupId", 1L);
+ params.put("roleIds", Arrays.asList(100L, 200L));
+
+ BoundSql boundSql = sqlSource.getBoundSql(params);
+ String normalizedSql = boundSql.getSql().replaceAll("\\s+", " ").trim();
+
+ Assertions.assertTrue(
+ normalizedSql.matches(".*\\brole_id\\s+IN\\s*\\(.*\\).*"),
+ "Non-empty roleIds should generate SQL with role_id IN (...) clause");
+
+ Assertions.assertFalse(
+ normalizedSql.matches(".*\\b1\\s*=\\s*0\\b.*"),
+ "Non-empty roleIds should not contain unsatisfiable WHERE clause (1 =
0)");
+ }
+
+ @Test
+ void testSoftDeleteGroupRoleRelByGroupAndRolesWithNullRoles() {
+ GroupRoleRelPostgreSQLProvider provider = new
GroupRoleRelPostgreSQLProvider();
+ String script = provider.softDeleteGroupRoleRelByGroupAndRoles(1L, null);
+
+ SqlSource sqlSource =
+ new XMLLanguageDriver().createSqlSource(new Configuration(), script,
Map.class);
+ Map<String, Object> params = new HashMap<>();
+ params.put("groupId", 1L);
+ params.put("roleIds", null);
+
+ BoundSql boundSql = sqlSource.getBoundSql(params);
+ String normalizedSql = boundSql.getSql().replaceAll("\\s+", " ").trim();
+
+ Assertions.assertFalse(
+ normalizedSql.matches(".*\\bIN\\s*\\(\\s*\\).*"),
+ "Null roleIds should not generate invalid SQL IN (...) with no
values");
+
+ Assertions.assertTrue(
+ normalizedSql.matches(".*\\b1\\s*=\\s*0\\b.*"),
+ "Null roleIds should result in an unsatisfiable WHERE clause (e.g.,
AND 1 = 0)");
+ }
+}
diff --git
a/core/src/test/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/TestUserRoleRelPostgreSQLProvider.java
b/core/src/test/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/TestUserRoleRelPostgreSQLProvider.java
new file mode 100644
index 0000000000..e1805556ba
--- /dev/null
+++
b/core/src/test/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/TestUserRoleRelPostgreSQLProvider.java
@@ -0,0 +1,102 @@
+/*
+ * 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.
+ */
+package org.apache.gravitino.storage.relational.mapper.provider.postgresql;
+
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.Map;
+import org.apache.ibatis.mapping.BoundSql;
+import org.apache.ibatis.mapping.SqlSource;
+import org.apache.ibatis.scripting.xmltags.XMLLanguageDriver;
+import org.apache.ibatis.session.Configuration;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+public class TestUserRoleRelPostgreSQLProvider {
+
+ @Test
+ void testSoftDeleteUserRoleRelByUserAndRolesWithEmptyRoles() {
+ UserRoleRelPostgreSQLProvider provider = new
UserRoleRelPostgreSQLProvider();
+ String script = provider.softDeleteUserRoleRelByUserAndRoles(1L,
Collections.emptyList());
+
+ SqlSource sqlSource =
+ new XMLLanguageDriver().createSqlSource(new Configuration(), script,
Map.class);
+ Map<String, Object> params = new HashMap<>();
+ params.put("userId", 1L);
+ params.put("roleIds", Collections.emptyList());
+
+ BoundSql boundSql = sqlSource.getBoundSql(params);
+ String normalizedSql = boundSql.getSql().replaceAll("\\s+", " ").trim();
+
+ Assertions.assertFalse(
+ normalizedSql.matches(".*\\bIN\\s*\\(\\s*\\).*"),
+ "Empty roleIds should not generate invalid SQL IN (...) with no
values");
+
+ Assertions.assertTrue(
+ normalizedSql.matches(".*\\b1\\s*=\\s*0\\b.*"),
+ "Empty roleIds should result in an unsatisfiable WHERE clause (e.g.,
AND 1 = 0)");
+ }
+
+ @Test
+ void testSoftDeleteUserRoleRelByUserAndRolesWithNonEmptyRoles() {
+ UserRoleRelPostgreSQLProvider provider = new
UserRoleRelPostgreSQLProvider();
+ String script = provider.softDeleteUserRoleRelByUserAndRoles(1L,
Arrays.asList(100L, 200L));
+
+ SqlSource sqlSource =
+ new XMLLanguageDriver().createSqlSource(new Configuration(), script,
Map.class);
+ Map<String, Object> params = new HashMap<>();
+ params.put("userId", 1L);
+ params.put("roleIds", Arrays.asList(100L, 200L));
+
+ BoundSql boundSql = sqlSource.getBoundSql(params);
+ String normalizedSql = boundSql.getSql().replaceAll("\\s+", " ").trim();
+
+ Assertions.assertTrue(
+ normalizedSql.matches(".*\\brole_id\\s+IN\\s*\\(.*\\).*"),
+ "Non-empty roleIds should generate SQL with role_id IN (...) clause");
+
+ Assertions.assertFalse(
+ normalizedSql.matches(".*\\b1\\s*=\\s*0\\b.*"),
+ "Non-empty roleIds should not contain unsatisfiable WHERE clause (1 =
0)");
+ }
+
+ @Test
+ void testSoftDeleteUserRoleRelByUserAndRolesWithNullRoles() {
+ UserRoleRelPostgreSQLProvider provider = new
UserRoleRelPostgreSQLProvider();
+ String script = provider.softDeleteUserRoleRelByUserAndRoles(1L, null);
+
+ SqlSource sqlSource =
+ new XMLLanguageDriver().createSqlSource(new Configuration(), script,
Map.class);
+ Map<String, Object> params = new HashMap<>();
+ params.put("userId", 1L);
+ params.put("roleIds", null);
+
+ BoundSql boundSql = sqlSource.getBoundSql(params);
+ String normalizedSql = boundSql.getSql().replaceAll("\\s+", " ").trim();
+
+ Assertions.assertFalse(
+ normalizedSql.matches(".*\\bIN\\s*\\(\\s*\\).*"),
+ "Null roleIds should not generate invalid SQL IN (...) with no
values");
+
+ Assertions.assertTrue(
+ normalizedSql.matches(".*\\b1\\s*=\\s*0\\b.*"),
+ "Null roleIds should result in an unsatisfiable WHERE clause (e.g.,
AND 1 = 0)");
+ }
+}
