Hello,
I’m using @OrderBy attempting to sort a list of teams by multiple expressions like:
@javax.persistence.OrderBy("SUBSTRING( teamTypeCode, 1, 1) ASC, CASE WHEN SUBSTRING( teamTypeCode, 1, 1 ) = 'O' THEN -1 ELSE 1 END DESC, SUBSTRING( teamTypeCode, 4, 1 ) DESC, ordinalNbr")
@OneToMany(mappedBy = "club")
private List<Team> teams;
This fails with:
11:58:34,890 ERROR [stderr] (ServerService Thread Pool -- 72) line 1:37: unexpected token: CASE
11:58:34,897 INFO [org.hibernate.orm.beans] (ServerService Thread Pool -- 72) HHH10005004: Stopping BeanContainer : org.hibernate.resource.beans.container.internal.CdiBeanContainerExtendedAccessImpl@6f2807cc
11:58:34,898 INFO [org.hibernate.service.internal.AbstractServiceRegistryImpl] (ServerService Thread Pool -- 72) HHH000369: Error stopping service [class org.hibernate.resource.beans.internal.ManagedBeanRegistryImpl] : java.lang.NullPointerException
11:58:34,898 ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 72) MSC000001: Failed to start service jboss.persistenceunit."bbstats-0.8.war#BBStatsPU": org.jboss.msc.service.StartException in service jboss.persistenceunit."bbstats-0.8.war#BBStatsPU": javax.persistence.PersistenceException: [PersistenceUnit: BBStatsPU] Unable to build Hibernate SessionFactory
at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:195)
at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:125)
at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:650)
at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1.run(PersistenceUnitServiceImpl.java:209)
at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1985)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1487)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1378)
at java.lang.Thread.run(Thread.java:748)
at org.jboss.threads.JBossThread.run(JBossThread.java:485)
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: BBStatsPU] Unable to build Hibernate SessionFactory
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:1016)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:942)
at org.jboss.as.jpa.hibernate5.TwoPhaseBootstrapImpl.build(TwoPhaseBootstrapImpl.java:44)
at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:167)
... 9 more
Caused by: org.hibernate.MappingException: Could not get constructor for org.hibernate.persister.collection.OneToManyPersister
at org.hibernate.persister.internal.PersisterFactoryImpl.createCollectionPersister(PersisterFactoryImpl.java:178)
at org.hibernate.persister.internal.PersisterFactoryImpl.createCollectionPersister(PersisterFactoryImpl.java:140)
at org.hibernate.metamodel.internal.MetamodelImpl.initialize(MetamodelImpl.java:198)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:295)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:467)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:939)
... 11 more
Caused by: org.hibernate.HibernateException: Unable to parse order-by fragment
at org.hibernate.sql.ordering.antlr.OrderByFragmentTranslator.translate(OrderByFragmentTranslator.java:55)
at org.hibernate.sql.Template.translateOrderBy(Template.java:724)
at org.hibernate.persister.collection.AbstractCollectionPersister.<init>(AbstractCollectionPersister.java:556)
at org.hibernate.persister.collection.OneToManyPersister.<init>(OneToManyPersister.java:69)
at sun.reflect.GeneratedConstructorAccessor59.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.hibernate.persister.internal.PersisterFactoryImpl.createCollectionPersister(PersisterFactoryImpl.java:152)
... 16 more
Caused by: java.lang.NullPointerException
at org.hibernate.sql.ordering.antlr.OrderByFragmentParser.postProcessSortSpecification(OrderByFragmentParser.java:251)
at org.hibernate.sql.ordering.antlr.GeneratedOrderByFragmentParser.sortSpecification(GeneratedOrderByFragmentParser.java:314)
at org.hibernate.sql.ordering.antlr.GeneratedOrderByFragmentParser.orderByFragment(GeneratedOrderByFragmentParser.java:198)
at org.hibernate.sql.ordering.antlr.OrderByFragmentTranslator.translate(OrderByFragmentTranslator.java:49)
... 23 more
11:58:34,898 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation ("deploy") failed - address: ([("deployment" => "bbstats-0.8.war")]) - failure description: {"WFLYCTL0080: Failed services" => {"jboss.persistenceunit.\"bbstats-0.8.war#BBStatsPU\"" => "javax.persistence.PersistenceException: [PersistenceUnit: BBStatsPU] Unable to build Hibernate SessionFactory
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: BBStatsPU] Unable to build Hibernate SessionFactory
Caused by: org.hibernate.MappingException: Could not get constructor for org.hibernate.persister.collection.OneToManyPersister
Caused by: org.hibernate.HibernateException: Unable to parse order-by fragment
Caused by: java.lang.NullPointerException"}}
I have also tried the Hibernate-specific annotation (here SQL clause):
@org.hibernate.annotations.OrderBy( clause= "SUBSTRING( team_type_code, 1, 1 ) ASC, CASE WHEN SUBSTRING( team_type_code, 1, 1 ) = 'O' THEN -CAST(SUBSTRING( team_type_code, 2, 2 ) AS DECIMAL) ELSE CAST(SUBSTRING( team_type_code, 2, 2 ) AS DECIMAL) END DESC, SUBSTRING( team_type_code, 4, 1 ) DESC, ordinal_nbr" )
@OneToMany(mappedBy = "club")
private List<Team> teams;
This fails with the same:
11:41:35,831 ERROR [stderr] (ServerService Thread Pool -- 72) line 1:40: unexpected token: CASE
11:41:35,835 INFO [org.hibernate.orm.beans] (ServerService Thread Pool -- 72) HHH10005004: Stopping BeanContainer : org.hibernate.resource.beans.container.internal.CdiBeanContainerExtendedAccessImpl@2a69582b
11:41:35,835 INFO [org.hibernate.service.internal.AbstractServiceRegistryImpl] (ServerService Thread Pool -- 72) HHH000369: Error stopping service [class org.hibernate.resource.beans.internal.ManagedBeanRegistryImpl] : java.lang.NullPointerException
11:41:35,835 ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 72) MSC000001: Failed to start service jboss.persistenceunit."bbstats-0.8.war#BBStatsPU": org.jboss.msc.service.StartException in service jboss.persistenceunit."bbstats-0.8.war#BBStatsPU": javax.persistence.PersistenceException: [PersistenceUnit: BBStatsPU] Unable to build Hibernate SessionFactory
at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:195)
at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:125)
at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:650)
at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1.run(PersistenceUnitServiceImpl.java:209)
at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1985)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1487)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1378)
at java.lang.Thread.run(Thread.java:748)
at org.jboss.threads.JBossThread.run(JBossThread.java:485)
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: BBStatsPU] Unable to build Hibernate SessionFactory
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:1016)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:942)
at org.jboss.as.jpa.hibernate5.TwoPhaseBootstrapImpl.build(TwoPhaseBootstrapImpl.java:44)
at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:167)
... 9 more
Caused by: org.hibernate.MappingException: Could not get constructor for org.hibernate.persister.collection.OneToManyPersister
at org.hibernate.persister.internal.PersisterFactoryImpl.createCollectionPersister(PersisterFactoryImpl.java:178)
at org.hibernate.persister.internal.PersisterFactoryImpl.createCollectionPersister(PersisterFactoryImpl.java:140)
at org.hibernate.metamodel.internal.MetamodelImpl.initialize(MetamodelImpl.java:198)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:295)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:467)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:939)
... 11 more
Caused by: org.hibernate.HibernateException: Unable to parse order-by fragment
at org.hibernate.sql.ordering.antlr.OrderByFragmentTranslator.translate(OrderByFragmentTranslator.java:55)
at org.hibernate.sql.Template.translateOrderBy(Template.java:724)
at org.hibernate.persister.collection.AbstractCollectionPersister.<init>(AbstractCollectionPersister.java:556)
at org.hibernate.persister.collection.OneToManyPersister.<init>(OneToManyPersister.java:69)
at sun.reflect.GeneratedConstructorAccessor53.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.hibernate.persister.internal.PersisterFactoryImpl.createCollectionPersister(PersisterFactoryImpl.java:152)
... 16 more
Caused by: java.lang.NullPointerException
at org.hibernate.sql.ordering.antlr.OrderByFragmentParser.postProcessSortSpecification(OrderByFragmentParser.java:251)
at org.hibernate.sql.ordering.antlr.GeneratedOrderByFragmentParser.sortSpecification(GeneratedOrderByFragmentParser.java:314)
at org.hibernate.sql.ordering.antlr.GeneratedOrderByFragmentParser.orderByFragment(GeneratedOrderByFragmentParser.java:198)
at org.hibernate.sql.ordering.antlr.OrderByFragmentTranslator.translate(OrderByFragmentTranslator.java:49)
... 23 more
11:41:35,835 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation ("deploy") failed - address: ([("deployment" => "bbstats-0.8.war")]) - failure description: {"WFLYCTL0080: Failed services" => {"jboss.persistenceunit.\"bbstats-0.8.war#BBStatsPU\"" => "javax.persistence.PersistenceException: [PersistenceUnit: BBStatsPU] Unable to build Hibernate SessionFactory
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: BBStatsPU] Unable to build Hibernate SessionFactory
Caused by: org.hibernate.MappingException: Could not get constructor for org.hibernate.persister.collection.OneToManyPersister
Caused by: org.hibernate.HibernateException: Unable to parse order-by fragment
Caused by: java.lang.NullPointerException"}}
The construct look good to me, also given that when removing the CASE WHEN part the order by seems to work. It just doesn’t give me the full meal deal, so I’d have to settle with some Comparator manually. 
Any idea what’s going on? Is there a problem with the CASE WHEN? Could it be a bug?
BTW: I’m using Hibernate 5.3.6.Final
The full problem description along with mappings etc. can be found on SO here:
I’d very much appreciate if someone could have a look at this.
