(gravitino) branch main updated: [#8307] fix(backend): fix Postgresql SQL syntax error (#8355)

[jshao]

This is an automated email from the ASF dual-hosted git repository.

jshao 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 d2d76c797e [#8307] fix(backend): fix Postgresql SQL syntax error 
(#8355)
d2d76c797e is described below

commit d2d76c797e6b92294e7861f214ac25ff460d26c7
Author: mchades <liminghu...@datastrato.com>
AuthorDate: Fri Aug 29 20:02:30 2025 +0800

    [#8307] fix(backend): fix Postgresql SQL syntax error (#8355)
    
    ### What changes were proposed in this pull request?
    
    fix Postgresql SQL syntax error
    
    ### Why are the changes needed?
    
    Fix: #8307
    
    ### Does this PR introduce _any_ user-facing change?
    
    no
    
    ### How was this patch tested?
    
    local tested
---
 .../mapper/provider/postgresql/FilesetVersionPostgreSQLProvider.java  | 4 +++-
 .../mapper/provider/postgresql/PolicyVersionPostgreSQLProvider.java   | 4 +++-
 2 files changed, 6 insertions(+), 2 deletions(-)

diff --git 
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/FilesetVersionPostgreSQLProvider.java
 
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/FilesetVersionPostgreSQLProvider.java
index 89cf6b0eca..98b5dede88 100644
--- 
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/FilesetVersionPostgreSQLProvider.java
+++ 
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/FilesetVersionPostgreSQLProvider.java
@@ -79,7 +79,9 @@ public class FilesetVersionPostgreSQLProvider extends 
FilesetVersionBaseSQLProvi
         + VERSION_TABLE_NAME
         + " SET deleted_at = floor(extract(epoch from((current_timestamp -"
         + " timestamp '1970-01-01 00:00:00')*1000)))"
-        + " WHERE fileset_id = #{filesetId} AND version <= 
#{versionRetentionLine} AND deleted_at = 0 LIMIT #{limit}";
+        + " WHERE id IN (SELECT id FROM "
+        + VERSION_TABLE_NAME
+        + " WHERE fileset_id = #{filesetId} AND version <= 
#{versionRetentionLine} AND deleted_at = 0 LIMIT #{limit})";
   }
 
   @Override
diff --git 
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/PolicyVersionPostgreSQLProvider.java
 
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/PolicyVersionPostgreSQLProvider.java
index 9e396937b8..471099a5ec 100644
--- 
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/PolicyVersionPostgreSQLProvider.java
+++ 
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/PolicyVersionPostgreSQLProvider.java
@@ -58,8 +58,10 @@ public class PolicyVersionPostgreSQLProvider extends 
PolicyVersionBaseSQLProvide
         + POLICY_VERSION_TABLE_NAME
         + " SET deleted_at = floor(extract(epoch from((current_timestamp -"
         + " timestamp '1970-01-01 00:00:00')*1000)))"
+        + " WHERE id IN (SELECT id FROM "
+        + POLICY_VERSION_TABLE_NAME
         + " WHERE policy_id = #{policyId} AND version < 
#{versionRetentionLine}"
-        + " AND deleted_at = 0 LIMIT #{limit}";
+        + " AND deleted_at = 0 LIMIT #{limit})";
   }
 
   @Override