Query with commitId fails on Oracle 11g

[herbert-venancio]

I'm trying to implement some mechanism on my system that restore entities from previous snapshots.

        QueryBuilder jqlQuery = QueryBuilder.byInstanceId(request.getId(), type)
                .withCommitId(request.getCommitId());

        List<CdoSnapshot> snaps = javers.findSnapshots(jqlQuery.build());
...

This code fails on Oracle 11g with the following exception:

org.javers.common.exception.JaversException: SQL_EXCEPTION: ORA-01722: número inválido

while executing sql: SELECT a.*, rownum FROM (  SELECT state, type, version, changed_properties, managed_type, commit_pk, author, commit_date, commit_date_instant, commit_id, g.local_id, g.fragment, g.owner_id_fk, o.local_id owner_local_id, o.fragment owner_fragment, o.type_name owner_type_name FROM jv_snapshot INNER JOIN jv_commit ON commit_pk = commit_fk INNER JOIN jv_global_id g ON g.global_id_pk = global_id_fk LEFT OUTER JOIN jv_global_id o ON o.global_id_pk = g.owner_id_fk WHERE 1 = 1 AND commit_id IN ('1.00') AND g.global_id_pk = ? ORDER BY snapshot_pk DESC ) a WHERE rownum <= ?
	at org.javers.repository.sql.session.PreparedStatementExecutor.wrapExceptionAndCall(PreparedStatementExecutor.java:128) ~[javers-persistence-sql-5.5.0.jar:na]
	at org.javers.repository.sql.session.PreparedStatementExecutor.runSql(PreparedStatementExecutor.java:100) ~[javers-persistence-sql-5.5.0.jar:na]
	at org.javers.repository.sql.session.PreparedStatementExecutor.executeQuery(PreparedStatementExecutor.java:58) ~[javers-persistence-sql-5.5.0.jar:na]
	at org.javers.repository.sql.session.Session.executeQuery(Session.java:99) ~[javers-persistence-sql-5.5.0.jar:na]
	at org.javers.repository.sql.session.SelectBuilder.executeQuery(SelectBuilder.java:99) ~[javers-persistence-sql-5.5.0.jar:na]
	at org.javers.repository.sql.finders.SnapshotQuery.run(SnapshotQuery.java:148) ~[javers-persistence-sql-5.5.0.jar:na]
	at org.javers.repository.sql.finders.CdoSnapshotFinder.fetchCdoSnapshots(CdoSnapshotFinder.java:92) ~[javers-persistence-sql-5.5.0.jar:na]
	at org.javers.repository.sql.finders.CdoSnapshotFinder.lambda$getStateHistory$10(CdoSnapshotFinder.java:84) ~[javers-persistence-sql-5.5.0.jar:na]
	at java.util.Optional.map(Optional.java:215) ~[na:1.8.0_202]
	at org.javers.repository.sql.finders.CdoSnapshotFinder.getStateHistory(CdoSnapshotFinder.java:84) ~[javers-persistence-sql-5.5.0.jar:na]
	at org.javers.repository.sql.JaversSqlRepository.getStateHistory(JaversSqlRepository.java:111) ~[javers-persistence-sql-5.5.0.jar:na]
	at org.javers.repository.api.JaversExtendedRepository.getStateHistory(JaversExtendedRepository.java:71) ~[javers-core-5.5.0.jar:na]
	at org.javers.repository.jql.SnapshotQueryRunner.queryForSnapshots(SnapshotQueryRunner.java:37) ~[javers-core-5.5.0.jar:na]
	at org.javers.repository.jql.QueryRunner.queryForSnapshots(QueryRunner.java:44) ~[javers-core-5.5.0.jar:na]
	at org.javers.core.JaversCore.findSnapshots(JaversCore.java:191) ~[javers-core-5.5.0.jar:na]
	at org.javers.spring.jpa.JaversTransactionalDecorator.findSnapshots(JaversTransactionalDecorator.java:151) ~[javers-spring-jpa-5.5.0.jar:na]
	at org.javers.spring.jpa.JaversTransactionalDecorator$$FastClassBySpringCGLIB$$acb40bd0.invoke(<generated>) ~[javers-spring-jpa-5.5.0.jar:na]
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.3.17.RELEASE.jar:4.3.17.RELEASE]
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:736) ~[spring-aop-4.3.17.RELEASE.jar:4.3.17.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.17.RELEASE.jar:4.3.17.RELEASE]
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.17.RELEASE.jar:4.3.17.RELEASE]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) ~[spring-tx-4.3.17.RELEASE.jar:4.3.17.RELEASE]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.17.RELEASE.jar:4.3.17.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.17.RELEASE.jar:4.3.17.RELEASE]
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:671) ~[spring-aop-4.3.17.RELEASE.jar:4.3.17.RELEASE]
	at org.javers.spring.jpa.JaversTransactionalDecorator$$EnhancerBySpringCGLIB$$e5eae1de.findSnapshots(<generated>) ~[javers-spring-jpa-5.5.0.jar:na]
	at objective.taskboard.controller.AuditController.restore(AuditController.java:159) ~[classes/:na]
...

Looks like the problem is commit_id IN ('1.00'), if I run this sql manually removing the single-quotes it works
My environment:

• Spring Boot: 1.5.13.RELEASE
• JaVers starter: 5.5.0
• Oracle Database: 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
• ojdbc6: 11.2.0.4

[bartoszwalacik]

thanks @herbert-venancio for reporting, we will fix it

[bartoszwalacik]

fixed in 5.5.2

[herbert-venancio]

Tested and it's working fine. Yay! Thanks! :D