search

spring-projects / spring-data-jpa

Simplifies the development of creating a JPA-based data access layer.
https://spring.io/projects/spring-data-jpa/
Apache License 2.0
2.98k stars 1.41k forks source link

@SoftDelete existsById query problem with Joined Inheritance hierarchy structure #3304

Closed ggomarighetti closed 8 months ago

ggomarighetti commented 8 months ago

Hi, I am trying to implement soft delete functionality in an entity hierarchy. My entity hierarchy is as follows:

I have added the @SoftDelete(columnName = "soft_delete") annotation to the base entity Person. However, when I try to run a query that involves the NaturalPerson and LegalPerson entities, I receive the following error:

org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select count(*) from legal_persons lp1_0 where lp1_0.joined_fk=? and lp1_1.soft_delete=false] [ERROR: missing FROM-clause entry for table "lp1_1" Position: 71] [n/a]; SQL [n/a]

It seems that Hibernate is looking for the soft_delete column in the legal_persons table, but it cannot find it because the @SoftDelete annotation is on the base entity Person.

I have tried moving the @SoftDelete annotation to the NaturalPerson and LegalPerson entities, but this breaks the soft delete functionality when I use the base entity repository to find entities by ID.

Is there a way for Hibernate to look for the soft_delete column in the correct table when the @SoftDelete annotation is on the base entity in an entity hierarchy?

I opened an issue in hibernate and after testing it and checking that it worked, I am inclined to think that it may be something from spring. link

I attach the steps to reproduce.

// Person.java

@Entity
@Table(name = "persons")
@SoftDelete(columnName = "soft_delete")
@Inheritance(strategy = InheritanceType.JOINED)
public class Person {

  @Id
  @UuidGenerator
  @Column(nullable = false, updatable = false)
  private UUID id;

  @Column(name = "tax_number", nullable = false)
  private Long taxNumber;

  ...
}
// LegalPerson.java

@Entity
@Table(name = "legal_persons")
@PrimaryKeyJoinColumn(name = "joined_fk")
public class LegalPerson extends Person {

  @Column(name = "legal_name", nullable = false)
  private String legalName;

  ...
}
// PersonRepository.java

public interface PersonRepository extends JpaRepository<Person, UUID>, JpaSpecificationExecutor<Person> {
  interface Specs {
    ...
    }
  }
}
// LegalPersonRepository.java

public interface LegalPersonRepository extends JpaRepository<LegalPerson, UUID> {}
// PersonService.java

@Service
@Transactional
@RequiredArgsConstructor
public class LegalPersonService {
  private final LegalPersonRepository legalPersonRepository;

  ...

  public void delete(final UUID id) {
    if (!legalPersonRepository.existsById(id)) {
      throw new PersonNotFound();
    }

    legalPersonRepository.deleteById(id);
  }
}
christophstrobl commented 8 months ago

Thank you for getting in touch. Care to also share the full stacktrace? It seems the exists lookup does not work as expected.

Since you already shared some code would you please please take the time to provide a complete minimal sample (something that we can unzip or git clone, build, and deploy) that reproduces the problem.

ggomarighetti commented 8 months ago

@christophstrobl thanks to you for replying, I attach the link to a public repository with the minimum sample.

I don't know if you still need the full stacktrace after uploading the code, in any case, tag me and I'll find out how to print it so I can upload it here.

ggomarighetti commented 8 months ago

I had opened an issue in hibernate-orm for this same problem, and apparently they solved it, I don't know if it applies or not to my code because I haven't tested it yet, I attach link to the issue and the pull requests.

ggomarighetti commented 8 months ago

I reran the sample repository tests with the new update (Spring Boot Starter Data JPA " 3.2.2) that implements the fixed version (Hibernate ORM Hibernate Core " 6.4.2.Final) and it still doesn't work.

Connected to the target VM, address: '127.0.0.1:65453', transport: 'socket'

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::                (v3.2.2)

2024-01-20T18:10:58.748-03:00  INFO 58856 --- [           main] i.g.g.s.p.legal.LegalPersonResourceTest  : Starting LegalPersonResourceTest using Java 21.0.1 with PID 58856 (started by ggomarighetti in C:\Users\ggomarighetti\source\repos\spring-softdelete-issue)
2024-01-20T18:10:58.752-03:00  INFO 58856 --- [           main] i.g.g.s.p.legal.LegalPersonResourceTest  : The following 1 profile is active: "test"
2024-01-20T18:10:59.549-03:00  INFO 58856 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2024-01-20T18:10:59.623-03:00  INFO 58856 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 64 ms. Found 2 JPA repository interfaces.
2024-01-20T18:11:00.794-03:00  INFO 58856 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port 0 (http)
2024-01-20T18:11:00.816-03:00  INFO 58856 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2024-01-20T18:11:00.817-03:00  INFO 58856 --- [           main] o.apache.catalina.core.StandardEngine    : Starting Servlet engine: [Apache Tomcat/10.1.18]
2024-01-20T18:11:00.927-03:00  INFO 58856 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2024-01-20T18:11:00.929-03:00  INFO 58856 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 2157 ms
2024-01-20T18:11:01.083-03:00  INFO 58856 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2024-01-20T18:11:01.166-03:00  INFO 58856 --- [           main] org.testcontainers.images.PullPolicy     : Image pull policy will be performed by: DefaultPullPolicy()
2024-01-20T18:11:01.170-03:00  INFO 58856 --- [           main] o.t.utility.ImageNameSubstitutor         : Image name substitution will be performed by: DefaultImageNameSubstitutor (composite of 'ConfigurationFileImageNameSubstitutor' and 'PrefixingImageNameSubstitutor')
2024-01-20T18:11:01.472-03:00  INFO 58856 --- [           main] o.t.d.DockerClientProviderStrategy       : Loaded org.testcontainers.dockerclient.NpipeSocketClientProviderStrategy from ~/.testcontainers.properties, will try it first
2024-01-20T18:11:01.924-03:00  INFO 58856 --- [           main] o.t.d.DockerClientProviderStrategy       : Found Docker environment with local Npipe socket (npipe:////./pipe/docker_engine)
2024-01-20T18:11:01.927-03:00  INFO 58856 --- [           main] org.testcontainers.DockerClientFactory   : Docker host IP address is localhost
2024-01-20T18:11:01.957-03:00  INFO 58856 --- [           main] org.testcontainers.DockerClientFactory   : Connected to docker: 
  Server Version: 24.0.6
  API Version: 1.43
  Operating System: Docker Desktop
  Total Memory: 25599 MB
2024-01-20T18:11:02.004-03:00  INFO 58856 --- [           main] tc.testcontainers/ryuk:0.5.1             : Creating container for image: testcontainers/ryuk:0.5.1
2024-01-20T18:11:02.316-03:00  INFO 58856 --- [           main] o.t.utility.RegistryAuthLocator          : Credential helper/store (docker-credential-desktop) does not have credentials for https://index.docker.io/v1/
2024-01-20T18:11:02.477-03:00  INFO 58856 --- [           main] tc.testcontainers/ryuk:0.5.1             : Container testcontainers/ryuk:0.5.1 is starting: 6b80c90d2302413f628bba2dac64e83d778fd410dda0aec07630118993829eed
2024-01-20T18:11:03.215-03:00  INFO 58856 --- [           main] tc.testcontainers/ryuk:0.5.1             : Container testcontainers/ryuk:0.5.1 started in PT1.211288S
2024-01-20T18:11:03.225-03:00  INFO 58856 --- [           main] o.t.utility.RyukResourceReaper           : Ryuk started - will monitor and terminate Testcontainers containers on JVM exit
2024-01-20T18:11:03.225-03:00  INFO 58856 --- [           main] org.testcontainers.DockerClientFactory   : Checking the system...
2024-01-20T18:11:03.226-03:00  INFO 58856 --- [           main] org.testcontainers.DockerClientFactory   : ✔︎ Docker server version should be at least 1.6.0
2024-01-20T18:11:03.227-03:00  INFO 58856 --- [           main] tc.postgres:15.2                         : Creating container for image: postgres:15.2
2024-01-20T18:11:03.228-03:00  WARN 58856 --- [           main] tc.postgres:15.2                         : Reuse was requested but the environment does not support the reuse of containers
To enable reuse of containers, you must set 'testcontainers.reuse.enable=true' in a file located at C:\Users\ggomarighetti\.testcontainers.properties
2024-01-20T18:11:03.259-03:00  INFO 58856 --- [           main] tc.postgres:15.2                         : Container postgres:15.2 is starting: 280c33479c1c2cdfa5bf0da9356fad82ef49645e46e58c15a0423dd23b9adad6
2024-01-20T18:11:05.063-03:00  INFO 58856 --- [           main] tc.postgres:15.2                         : Container postgres:15.2 started in PT1.8357367S
2024-01-20T18:11:05.064-03:00  INFO 58856 --- [           main] tc.postgres:15.2                         : Container is started (JDBC URL: jdbc:postgresql://localhost:65463/minerva-test?loggerLevel=OFF)
2024-01-20T18:11:05.299-03:00  INFO 58856 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.testcontainers.jdbc.ConnectionWrapper@42cfd794
2024-01-20T18:11:05.301-03:00  INFO 58856 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2024-01-20T18:11:05.394-03:00  INFO 58856 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 9.22.3 by Redgate
2024-01-20T18:11:05.394-03:00  INFO 58856 --- [           main] o.f.c.internal.license.VersionPrinter    : See release notes here: https://rd.gt/416ObMi
2024-01-20T18:11:05.394-03:00  INFO 58856 --- [           main] o.f.c.internal.license.VersionPrinter    : 
2024-01-20T18:11:05.422-03:00  INFO 58856 --- [           main] org.flywaydb.core.FlywayExecutor         : Database: jdbc:postgresql://localhost:65463/minerva-test (PostgreSQL 15.2)
2024-01-20T18:11:05.479-03:00  INFO 58856 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table "public"."flyway_schema_history" ...
2024-01-20T18:11:05.558-03:00  INFO 58856 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema "public": << Empty Schema >>
2024-01-20T18:11:05.576-03:00  INFO 58856 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "public" to version "1 - INIT MIGRATION"
2024-01-20T18:11:05.626-03:00  INFO 58856 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema "public", now at version v1 (execution time 00:00.029s)
2024-01-20T18:11:06.323-03:00  INFO 58856 --- [           main] o.s.o.j.p.SpringPersistenceUnitInfo      : No LoadTimeWeaver setup: ignoring JPA class transformer
2024-01-20T18:11:07.677-03:00  INFO 58856 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2024-01-20T18:11:08.261-03:00 DEBUG 58856 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : 6 mappings in 'requestMappingHandlerMapping'
2024-01-20T18:11:08.419-03:00 DEBUG 58856 --- [           main] o.s.w.s.handler.SimpleUrlHandlerMapping  : Patterns [/webjars/**, /**] in 'resourceHandlerMapping'
2024-01-20T18:11:08.469-03:00 DEBUG 58856 --- [           main] s.w.s.m.m.a.RequestMappingHandlerAdapter : ControllerAdvice beans: 0 @ModelAttribute, 0 @InitBinder, 1 RequestBodyAdvice, 1 ResponseBodyAdvice
2024-01-20T18:11:08.518-03:00 DEBUG 58856 --- [           main] .m.m.a.ExceptionHandlerExceptionResolver : ControllerAdvice beans: 1 @ExceptionHandler, 1 ResponseBodyAdvice
2024-01-20T18:11:08.702-03:00  INFO 58856 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring TestDispatcherServlet ''
2024-01-20T18:11:08.702-03:00  INFO 58856 --- [           main] o.s.t.web.servlet.TestDispatcherServlet  : Initializing Servlet ''
2024-01-20T18:11:08.703-03:00  INFO 58856 --- [           main] o.s.t.web.servlet.TestDispatcherServlet  : Completed initialization in 1 ms
2024-01-20T18:11:08.781-03:00  INFO 58856 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port 65477 (http) with context path ''
2024-01-20T18:11:08.794-03:00  INFO 58856 --- [           main] i.g.g.s.p.legal.LegalPersonResourceTest  : Started LegalPersonResourceTest in 10.483 seconds (process running for 12.102)
WARNING: A Java agent has been loaded dynamically (C:\Users\ggomarighetti\.m2\repository\net\bytebuddy\byte-buddy-agent\1.14.11\byte-buddy-agent-1.14.11.jar)
WARNING: If a serviceability tool is in use, please run with -XX:+EnableDynamicAgentLoading to hide this warning
WARNING: If a serviceability tool is not in use, please run with -Djdk.instrument.traceUsage for more information
WARNING: Dynamic loading of agents will be disallowed by default in a future release
OpenJDK 64-Bit Server VM warning: Sharing is only supported for boot loader classes because bootstrap classpath has been appended
2024-01-20T18:11:09.632-03:00 DEBUG 58856 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped to io.github.ggomarighetti.sample.person.legal.LegalPersonResource#delete(UUID)
2024-01-20T18:11:10.237-03:00 ERROR 58856 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: missing FROM-clause entry for table "lp1_1"
  Position: 71
2024-01-20T18:11:10.248-03:00 DEBUG 58856 --- [           main] .m.m.a.ExceptionHandlerExceptionResolver : Using @ExceptionHandler io.github.ggomarighetti.sample.config.ExceptionConfig#handleThrowable(Throwable)
org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select count(*) from legal_persons lp1_0 where lp1_0.joined_fk=? and lp1_1.soft_delete=false] [ERROR: missing FROM-clause entry for table "lp1_1"
  Position: 71] [n/a]; SQL [n/a]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:277)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:241)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:335)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:164)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:220)
    at jdk.proxy2/jdk.proxy2.$Proxy153.existsById(Unknown Source)
    at io.github.ggomarighetti.sample.person.legal.LegalPersonService.delete(LegalPersonService.java:55)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
    at java.base/java.lang.reflect.Method.invoke(Method.java:580)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:351)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:385)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:717)
    at io.github.ggomarighetti.sample.person.legal.LegalPersonService$$SpringCGLIB$$0.delete(<generated>)
    at io.github.ggomarighetti.sample.person.legal.LegalPersonResource.delete(LegalPersonResource.java:35)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
    at java.base/java.lang.reflect.Method.invoke(Method.java:580)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:261)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:189)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:917)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:829)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)
    at org.springframework.web.servlet.FrameworkServlet.doDelete(FrameworkServlet.java:936)
    at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:596)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885)
    at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:72)
    at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
    at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:165)
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:132)
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
    at org.springframework.test.web.servlet.setup.MockMvcFilterDecorator.doFilter(MockMvcFilterDecorator.java:151)
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:132)
    at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
    at org.springframework.test.web.servlet.setup.MockMvcFilterDecorator.doFilter(MockMvcFilterDecorator.java:151)
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:132)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
    at org.springframework.test.web.servlet.setup.MockMvcFilterDecorator.doFilter(MockMvcFilterDecorator.java:151)
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:132)
    at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:201)
    at io.github.ggomarighetti.sample.person.legal.LegalPersonResourceTest.deleteLegalPerson_success(LegalPersonResourceTest.java:134)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
    at java.base/java.lang.reflect.Method.invoke(Method.java:580)
    at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:728)
    at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
    at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
    at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
    at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
    at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
    at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
    at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:92)
    at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:86)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:218)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:214)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:139)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:69)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:198)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:169)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:93)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:58)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:141)
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:57)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:103)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:85)
    at org.junit.platform.launcher.core.DelegatingLauncher.execute(DelegatingLauncher.java:47)
    at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:63)
    at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:57)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
    at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)
Caused by: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select count(*) from legal_persons lp1_0 where lp1_0.joined_fk=? and lp1_1.soft_delete=false] [ERROR: missing FROM-clause entry for table "lp1_1"
  Position: 71] [n/a]
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:91)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94)
    at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:265)
    at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:167)
    at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advanceNext(JdbcValuesResultSetImpl.java:218)
    at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:98)
    at org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:19)
    at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:66)
    at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:203)
    at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33)
    at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:209)
    at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:83)
    at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:76)
    at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:65)
    at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$2(ConcreteSqmSelectQueryPlan.java:137)
    at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:359)
    at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:300)
    at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:509)
    at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:427)
    at org.hibernate.query.spi.AbstractSelectionQuery.getSingleResult(AbstractSelectionQuery.java:564)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.existsById(SimpleJpaRepository.java:359)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
    at java.base/java.lang.reflect.Method.invoke(Method.java:580)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:351)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:277)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
    at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:516)
    at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:285)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:628)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:168)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:70)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:385)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    ... 122 more
Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "lp1_1"
  Position: 71
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:246)
    ... 160 more
2024-01-20T18:11:10.294-03:00 DEBUG 58856 --- [           main] o.s.w.s.m.m.a.HttpEntityMethodProcessor  : Using 'application/json', given [application/json] and supported [application/json, application/*+json]
2024-01-20T18:11:10.296-03:00 DEBUG 58856 --- [           main] o.s.w.s.m.m.a.HttpEntityMethodProcessor  : Writing [io.github.ggomarighetti.sample.model.ErrorResponse@14a7e16e]
2024-01-20T18:11:10.312-03:00 DEBUG 58856 --- [           main] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select count(*) from legal_persons lp1_0 where lp1_0.joined_fk=? and lp1_1.soft_delete=false] [ERROR: missing FROM-clause entry for table "lp1_1"<EOL>  Position: 71] [n/a]; SQL [n/a]]

I updated the dependencies in the sample repository. Link

ggomarighetti commented 8 months ago

After further review, the problem comes from a line of code that I use when verifying the entity before deleting it, more specifically: 

  // PersonService.java

  public void delete(final UUID id) {

    if (!legalPersonRepository.existsById(id)) {
      throw new ResponseStatusException(HttpStatus.CONFLICT, "Person.NotFound");
    }

    legalPersonRepository.deleteById(id);
  }

When calling the existsById() method, the query that is executed does not include a JOIN with the table of the parent entity, so the last clause that verifies the soft_delete in the hierarchy gives error.

select count(*) from legal_persons lp1_0 where lp1_0.joined_fk=? and lp1_1.soft_delete=false

It should be corrected so that it executes something similar to:

select count(*) from legal_persons lp join persons p on lp.joined_fk = p.id where lp.joined_fk=? and p.soft_delete=false
christophstrobl commented 8 months ago

@ggomarighetti thank you for the additional input and the reproducer. This seems to be a hibernate issue which can be reproduced with the domain model from the sample using plain JPA (outlined below). Therefore I'm closing this ticket. 

LegalPerson source = new LegalPerson();
source.setLegalName("name");
source.setTaxNumber(12345L);
source.setEnactment(LocalDate.now());
em.persist(source);

// ...

TypedQuery<Long> query = em.createQuery("Select count(*) from LegalPerson p where p.id = :id", Long.class);
query.setParameter("id", source.getId());
query.getSingleResult();

Maybe you want to provide some feedback to the hibernate team on HHH-17615.

ggomarighetti commented 8 months ago

@christophstrobl Thanks for your answer too, one last thing, I tried to reproduce the test you attached with the hibernate test template (6.4.2.Final) and everything worked correctly.

But the spring starters are currently on 6.4.1.Final, apparently we just have to wait for spring-data-jpa to update its dependencies, thanks a lot!