Nested Object with ZonedDateTime on MariaDB casted/formatted with wrong format (Date format not recognized at FF9)

[SeaLife]

Date format not recognized at FF9 in function to_char.

Hey, so i dont exactly know when the Application broke but i suspect it was a upgrade of the MariaDB version. The EntityView (and all entities associated with the view were created like 10 months ago and were not touched - so thats why i think its either the MariaDB which produces this error or a Spring Boot/JPA/Hibernate update). We'r currently using Spring Boot 3.3.5 and BlazeBit 1.6.14. The EntityView is created like this:

@Entity
@EntityView(Company.class)
public interface CompanyListView {

    @IdMapping
    UUID getId();

    Integer getIid();

    String getName();

    String getLocation();

    String getIban();

    String getNote();

    String getPropertyStatus();

    ZonedDateTime getNextPaymentLicenses();

    ZonedDateTime getVacatedUntil();

    Boolean getClosed();

    Boolean getTemporarilyClosed();

    CompanyType getType();

    Owner getOwner();

    CompanyCreatedByUser getCreatedBy();

    @Mapping(fetch = FetchStrategy.MULTISET)
    List<Strike> getStrikes();

    @Mapping(fetch = FetchStrategy.MULTISET)
    List<LicenseLink> getLicenseLinks();

    @EntityView(CompanyLicenseLink.class)
    interface LicenseLink {
        @IdMapping
        UUID getId();

        Integer getCount();

        License getLicense();
    }

    @EntityView(CompanyLicense.class)
    interface License {
        @IdMapping
        UUID getId();

        String getName();

        String getDescription();

        Integer getFee();
    }

    @EntityView(CriminalRecord.class)
    interface Owner {
        @IdMapping
        UUID getId();

        String getName();

        String getBirthday();

        String getPhoneNumber();
    }

    @EntityView(CompanyStrike.class)
    interface Strike {
        @IdMapping
        UUID getId();

        Integer getPoints();

        Boolean getPayed();

        Boolean getDelivered();

        Integer getFine();

        ZonedDateTime getExpiresAfter();
    }

    @EntityView(User.class)
    interface CompanyCreatedByUser {
        @IdMapping
        UUID getId();

        String getDisplayName();
    }
}

The generated SQL looks like this:

select c1_0.id,
       c1_0.closed,
       c1_0.created_by_id,
       cb1_0.display_name,
       c1_0.iban,
       c1_0.iid,
       (select json_arrayagg(json_object('f0', cast(ll1_0.id as char), 'f1', cast(ll1_0.count as char), 'f2',
                                         cast(ll1_0.license_id as char), 'f3', cast(l1_0.description as char), 'f4',
                                         cast(l1_0.fee as char), 'f5', cast(l1_0.name as char)))
        from company_license_link ll1_0
                 left join company_license l1_0 on l1_0.id = ll1_0.license_id
        where c1_0.id = ll1_0.company_id),
       c1_0.location,
       c1_0.name,
       c1_0.next_payment_licenses,
       c1_0.note,
       c1_0.owner_id,
       o1_0.birthday,
       o1_0.name,
       o1_0.phone_number,
       c1_0.property_status,
       (select json_arrayagg(json_object('f0', cast(s1_0.id as char), 'f1',
                                         cast(case when s1_0.delivered = 1 then 'true' else 'false' end as char), 'f2',
                                         cast(to_char(s1_0.expires_after, 'YYYY-MM-DD"T"HH24:MI:SS.FF9') as char), 'f3',
                                         cast(s1_0.fine as char), 'f4',
                                         cast(case when s1_0.payed = 1 then 'true' else 'false' end as char), 'f5',
                                         cast(s1_0.points as char)))
        from company_strike s1_0
        where c1_0.id = s1_0.company_id),
       c1_0.temporarily_closed,
       c1_0.type,
       c1_0.vacated_until
from company c1_0
         left join user cb1_0 on cb1_0.id = c1_0.created_by_id
         left join criminal_record o1_0 on o1_0.id = c1_0.owner_id

The error seems to be cast(to_char(s1_0.expires_after, 'YYYY-MM-DD"T"HH24:MI:SS.FF9') as char), 'f3', which causes the following SQL error:

[HY000][3047] (conn=91411) Invalid argument error: date format not recognized at FF9 in function to_char.

Expected behavior

I expect the date to be returned without any error.

Actual behavior

This causes the generated SQL to be malformed and therefore being rejected from MariaDB.

//EDIT: Forgot to mention: I removed the .FF9 in the SQL statement which caused the SQL statement to be valid and return valid data. My guess is that MariaDB ignored the .FF9 format somehow in a older release.

Error:

[HY000][3047] (conn=91411) Invalid argument error: date format not recognized at FF9 in function to_char.

Exception:

govnet-backend-1  | 2024-11-14T16:52:07.946+01:00  WARN 1 --- [ XNIO-1 task-46] o.m.jdbc.message.server.ErrorPacket      : Error: 3047-HY000: Invalid argument error: date format not recognized at FF9 in function to_char.
govnet-backend-1  | 2024-11-14T16:52:07.947+01:00  WARN 1 --- [ XNIO-1 task-46] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 3047, SQLState: HY000
govnet-backend-1  | 2024-11-14T16:52:07.947+01:00 ERROR 1 --- [ XNIO-1 task-46] o.h.engine.jdbc.spi.SqlExceptionHelper   : (conn=91413) Invalid argument error: date format not recognized at FF9 in function to_char.
govnet-backend-1  | 2024-11-14T16:52:07.949+01:00 ERROR 1 --- [ XNIO-1 task-46] io.undertow.request                      : UT005023: Exception handling request to /api/v2/companies
govnet-backend-1  |
govnet-backend-1  | jakarta.servlet.ServletException: Request processing failed: org.springframework.orm.jpa.JpaSystemException: JDBC exception executing SQL [select c1_0.id,c1_0.closed,c1_0.created_by_id,cb1_0.display_name,c1_0.iban,c1_0.iid,(select json_arrayagg(json_object('f0',cast(ll1_0.id as char),'f1',cast(ll1_0.count as char),'f2',cast(ll1_0.license_id as char),'f3',cast(l1_0.description as char),'f4',cast(l1_0.fee as char),'f5',cast(l1_0.name as char))) from company_license_link ll1_0 left join company_license l1_0 on l1_0.id=ll1_0.license_id where c1_0.id=ll1_0.company_id),c1_0.location,c1_0.name,c1_0.next_payment_licenses,c1_0.note,c1_0.owner_id,o1_0.birthday,o1_0.name,o1_0.phone_number,c1_0.property_status,(select json_arrayagg(json_object('f0',cast(s1_0.id as char),'f1',cast(case when s1_0.delivered=1 then 'true' else 'false' end as char),'f2',cast(to_char(s1_0.expires_after, 'YYYY-MM-DD"T"HH24:MI:SS.FF9') as char),'f3',cast(s1_0.fine as char),'f4',cast(case when s1_0.payed=1 then 'true' else 'false' end as char),'f5',cast(s1_0.points as char))) from company_strike s1_0 where c1_0.id=s1_0.company_id),c1_0.temporarily_closed,c1_0.type,c1_0.vacated_until from company c1_0 left join user cb1_0 on cb1_0.id=c1_0.created_by_id left join criminal_record o1_0 on o1_0.id=c1_0.owner_id] [(conn=91413) Invalid argument error: date format not recognized at FF9 in function to_char.] [n/a]
govnet-backend-1  | 	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1022) ~[spring-webmvc-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:903) ~[spring-webmvc-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:527) ~[jakarta.servlet-api-6.0.0.jar!/:6.0.0]
govnet-backend-1  | 	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885) ~[spring-webmvc-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:614) ~[jakarta.servlet-api-6.0.0.jar!/:6.0.0]
govnet-backend-1  | 	at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at org.springframework.session.web.http.SessionRepositoryFilter.doFilterInternal(SessionRepositoryFilter.java:142) ~[spring-session-core-3.3.3.jar!/:3.3.3]
govnet-backend-1  | 	at org.springframework.session.web.http.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:82) ~[spring-session-core-3.3.3.jar!/:3.3.3]
govnet-backend-1  | 	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:362) ~[spring-web-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:278) ~[spring-web-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at org.springframework.web.filter.ServerHttpObservationFilter.doFilterInternal(ServerHttpObservationFilter.java:113) ~[spring-web-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:67) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:117) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) ~[undertow-core-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46) ~[undertow-core-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60) ~[undertow-core-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43) ~[undertow-core-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) ~[undertow-core-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.SendErrorPageHandler.handleRequest(SendErrorPageHandler.java:52) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) ~[undertow-core-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:276) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:135) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:132) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:256) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:101) ~[undertow-servlet-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.server.Connectors.executeRootHandler(Connectors.java:395) ~[undertow-core-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:859) ~[undertow-core-2.3.17.Final.jar!/:2.3.17.Final]
govnet-backend-1  | 	at org.jboss.threads.ContextHandler$1.runWith(ContextHandler.java:18) ~[jboss-threads-3.5.0.Final.jar!/:3.5.0.Final]
govnet-backend-1  | 	at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513) ~[jboss-threads-3.5.0.Final.jar!/:3.5.0.Final]
govnet-backend-1  | 	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538) ~[jboss-threads-3.5.0.Final.jar!/:3.5.0.Final]
govnet-backend-1  | 	at org.xnio.XnioWorker$WorkerThreadFactory$1$1.run(XnioWorker.java:1282) ~[xnio-api-3.8.16.Final.jar!/:3.8.16.Final]
govnet-backend-1  | 	at java.base/java.lang.Thread.run(Thread.java:1583) ~[na:na]
govnet-backend-1  | Caused by: org.springframework.orm.jpa.JpaSystemException: JDBC exception executing SQL [select c1_0.id,c1_0.closed,c1_0.created_by_id,cb1_0.display_name,c1_0.iban,c1_0.iid,(select json_arrayagg(json_object('f0',cast(ll1_0.id as char),'f1',cast(ll1_0.count as char),'f2',cast(ll1_0.license_id as char),'f3',cast(l1_0.description as char),'f4',cast(l1_0.fee as char),'f5',cast(l1_0.name as char))) from company_license_link ll1_0 left join company_license l1_0 on l1_0.id=ll1_0.license_id where c1_0.id=ll1_0.company_id),c1_0.location,c1_0.name,c1_0.next_payment_licenses,c1_0.note,c1_0.owner_id,o1_0.birthday,o1_0.name,o1_0.phone_number,c1_0.property_status,(select json_arrayagg(json_object('f0',cast(s1_0.id as char),'f1',cast(case when s1_0.delivered=1 then 'true' else 'false' end as char),'f2',cast(to_char(s1_0.expires_after, 'YYYY-MM-DD"T"HH24:MI:SS.FF9') as char),'f3',cast(s1_0.fine as char),'f4',cast(case when s1_0.payed=1 then 'true' else 'false' end as char),'f5',cast(s1_0.points as char))) from company_strike s1_0 where c1_0.id=s1_0.company_id),c1_0.temporarily_closed,c1_0.type,c1_0.vacated_until from company c1_0 left join user cb1_0 on cb1_0.id=c1_0.created_by_id left join criminal_record o1_0 on o1_0.id=c1_0.owner_id] [(conn=91413) Invalid argument error: date format not recognized at FF9 in function to_char.] [n/a]
govnet-backend-1  | 	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:341) ~[spring-orm-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:241) ~[spring-orm-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550) ~[spring-orm-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:335) ~[spring-tx-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:160) ~[spring-tx-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at com.blazebit.persistence.spring.data.base.repository.EntityViewAwareCrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(EntityViewAwareCrudMethodMetadataPostProcessor.java:143) ~[blaze-persistence-integration-spring-data-base-3.3-1.6.14.jar!/:1.6.14]
govnet-backend-1  | 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57) ~[spring-data-commons-3.3.5.jar!/:3.3.5]
govnet-backend-1  | 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223) ~[spring-aop-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at jdk.proxy2/jdk.proxy2.$Proxy237.findAllCompanyListViewBy(Unknown Source) ~[na:na]
govnet-backend-1  | 	at de.alteravitarp.govnet.service.v2.CompanyResource.getAllCompanies(CompanyResource.java:104) ~[!/:1.0.1-SNAPSHOT]
govnet-backend-1  | 	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) ~[na:na]
govnet-backend-1  | 	at java.base/java.lang.reflect.Method.invoke(Method.java:580) ~[na:na]
govnet-backend-1  | 	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:255) ~[spring-web-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:188) ~[spring-web-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118) ~[spring-webmvc-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:926) ~[spring-webmvc-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:831) ~[spring-webmvc-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089) ~[spring-webmvc-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979) ~[spring-webmvc-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014) ~[spring-webmvc-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	... 58 common frames omitted
govnet-backend-1  | Caused by: org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL [select c1_0.id,c1_0.closed,c1_0.created_by_id,cb1_0.display_name,c1_0.iban,c1_0.iid,(select json_arrayagg(json_object('f0',cast(ll1_0.id as char),'f1',cast(ll1_0.count as char),'f2',cast(ll1_0.license_id as char),'f3',cast(l1_0.description as char),'f4',cast(l1_0.fee as char),'f5',cast(l1_0.name as char))) from company_license_link ll1_0 left join company_license l1_0 on l1_0.id=ll1_0.license_id where c1_0.id=ll1_0.company_id),c1_0.location,c1_0.name,c1_0.next_payment_licenses,c1_0.note,c1_0.owner_id,o1_0.birthday,o1_0.name,o1_0.phone_number,c1_0.property_status,(select json_arrayagg(json_object('f0',cast(s1_0.id as char),'f1',cast(case when s1_0.delivered=1 then 'true' else 'false' end as char),'f2',cast(to_char(s1_0.expires_after, 'YYYY-MM-DD"T"HH24:MI:SS.FF9') as char),'f3',cast(s1_0.fine as char),'f4',cast(case when s1_0.payed=1 then 'true' else 'false' end as char),'f5',cast(s1_0.points as char))) from company_strike s1_0 where c1_0.id=s1_0.company_id),c1_0.temporarily_closed,c1_0.type,c1_0.vacated_until from company c1_0 left join user cb1_0 on cb1_0.id=c1_0.created_by_id left join criminal_record o1_0 on o1_0.id=c1_0.owner_id] [(conn=91413) Invalid argument error: date format not recognized at FF9 in function to_char.] [n/a]
govnet-backend-1  | 	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:63) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:264) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:167) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advanceNext(JdbcValuesResultSetImpl.java:265) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:145) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:19) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:67) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:204) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:211) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:83) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:76) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:65) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$2(ConcreteSqmSelectQueryPlan.java:139) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:382) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:302) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:526) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:423) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at org.hibernate.query.Query.getResultList(Query.java:120) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	at com.blazebit.persistence.impl.query.TypedQueryWrapper.getResultList(TypedQueryWrapper.java:38) ~[blaze-persistence-core-impl-jakarta-1.6.14.jar!/:1.6.14]
govnet-backend-1  | 	at com.blazebit.persistence.impl.query.ObjectBuilderTypedQuery.getResultList(ObjectBuilderTypedQuery.java:52) ~[blaze-persistence-core-impl-jakarta-1.6.14.jar!/:1.6.14]
govnet-backend-1  | 	at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:129) ~[spring-data-jpa-3.3.5.jar!/:3.3.5]
govnet-backend-1  | 	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:92) ~[spring-data-jpa-3.3.5.jar!/:3.3.5]
govnet-backend-1  | 	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:152) ~[spring-data-jpa-3.3.5.jar!/:3.3.5]
govnet-backend-1  | 	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:140) ~[spring-data-jpa-3.3.5.jar!/:3.3.5]
govnet-backend-1  | 	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170) ~[spring-data-commons-3.3.5.jar!/:3.3.5]
govnet-backend-1  | 	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158) ~[spring-data-commons-3.3.5.jar!/:3.3.5]
govnet-backend-1  | 	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:169) ~[spring-data-commons-3.3.5.jar!/:3.3.5]
govnet-backend-1  | 	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:148) ~[spring-data-commons-3.3.5.jar!/:3.3.5]
govnet-backend-1  | 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:70) ~[spring-data-commons-3.3.5.jar!/:3.3.5]
govnet-backend-1  | 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at com.blazebit.persistence.spring.data.repository.EntityViewReplacingMethodInterceptor.invoke(EntityViewReplacingMethodInterceptor.java:41) ~[blaze-persistence-integration-spring-data-base-3.3-1.6.14.jar!/:1.6.14]
govnet-backend-1  | 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:379) ~[spring-tx-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138) ~[spring-tx-6.1.14.jar!/:6.1.14]
govnet-backend-1  | 	... 79 common frames omitted
govnet-backend-1  | Caused by: java.sql.SQLException: (conn=91413) Invalid argument error: date format not recognized at FF9 in function to_char.
govnet-backend-1  | 	at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:306) ~[mariadb-java-client-3.5.0.jar!/:na]
govnet-backend-1  | 	at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:378) ~[mariadb-java-client-3.5.0.jar!/:na]
govnet-backend-1  | 	at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:189) ~[mariadb-java-client-3.5.0.jar!/:na]
govnet-backend-1  | 	at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:1246) ~[mariadb-java-client-3.5.0.jar!/:na]
govnet-backend-1  | 	at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:1185) ~[mariadb-java-client-3.5.0.jar!/:na]
govnet-backend-1  | 	at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:1104) ~[mariadb-java-client-3.5.0.jar!/:na]
govnet-backend-1  | 	at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:1028) ~[mariadb-java-client-3.5.0.jar!/:na]
govnet-backend-1  | 	at org.mariadb.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:101) ~[mariadb-java-client-3.5.0.jar!/:na]
govnet-backend-1  | 	at org.mariadb.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:257) ~[mariadb-java-client-3.5.0.jar!/:na]
govnet-backend-1  | 	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.1.0.jar!/:na]
govnet-backend-1  | 	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar!/:na]
govnet-backend-1  | 	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:246) ~[hibernate-core-6.5.3.Final.jar!/:6.5.3.Final]
govnet-backend-1  | 	... 115 common frames omitted

Steps to reproduce

I think it has something to do with the nested object. As i have "ZonedDateTime"'s in my base object which get not casted/to_char'ed but the nested will be parsed/casted using cast(to_char(datetime, format) as char).

Environment

Version: 1.6.14
JPA Provider: 6.5.3.Final
DBMS: MariaDB in Docker-Container 11.5.2-MariaDB-ubu2404
Application Server: Spring Boot 3.3.5 with embedded Undertow (With Spring Data JPA)

[beikov]

Please post the HQL that is used for the query.

[beikov]

This is probably caused by the fix for #1905 which will make use of the Blaze-Persistence internal timestamp_iso function to format such timestamp columns. We don't have a MariaDB implementation for this yet, so it uses a default implementation that is apparently not valid.

[SeaLife]

Please post the HQL that is used for the query.

Hey, there is no special Query defined. Im using a simple JPA/Blaze Repository:

public interface CompanyRepository extends JpaRepository<Company, UUID> {
    List<CompanyListView> findAllCompanyListViewBy(); // this method is being used and causing the issue to appear.

    List<Company> findAllCompanyListViewByOwner(CriminalRecord owner);
}