blaze-persistence icon indicating copy to clipboard operation
blaze-persistence copied to clipboard
verified publisher icon Blazebit

"limit" in "SetExpression" does not work for querydsl-integration

Open LucaRitz opened this issue 2 years ago • 3 comments

Description

A "limit" in a "SetExpression" leads to an invalid sql query.

Expected behavior

Query gets first n results.

Actual behavior

Stacktrace java.lang.IllegalArgumentException: org.hibernate.query.SemanticException: A query exception occurred [(SELECT DISTINCT entity1.brand AS brand FROM ENTITY entity1 WHERE lower(entity1.brand) LIKE :param_0 ESCAPE '!' LIMIT 100 UNION SELECT DISTINCT entity2.brand AS brand FROM ENTITY entity2 WHERE lower(entity2.brand) LIKE :param_1 ESCAPE '!') ORDER BY brand ASC] at org.jboss.resteasy.core.ExceptionHandler.handleApplicationException(ExceptionHandler.java:107) at org.jboss.resteasy.core.ExceptionHandler.handleException(ExceptionHandler.java:344) at org.jboss.resteasy.core.SynchronousDispatcher.writeException(SynchronousDispatcher.java:205) at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:452) at org.jboss.resteasy.core.SynchronousDispatcher.lambda$invoke$4(SynchronousDispatcher.java:240) at org.jboss.resteasy.core.SynchronousDispatcher.lambda$preprocess$0(SynchronousDispatcher.java:154) at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:321) at org.jboss.resteasy.core.SynchronousDispatcher.preprocess(SynchronousDispatcher.java:157) at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:229) at io.quarkus.resteasy.runtime.standalone.RequestDispatcher.service(RequestDispatcher.java:82) at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler.dispatch(VertxRequestHandler.java:147) at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler$1.run(VertxRequestHandler.java:93) at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:576) at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513) at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538) at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29) at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29) at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) at java.base/java.lang.Thread.run(Unknown Source) Caused by: java.lang.IllegalArgumentException: org.hibernate.query.SemanticException: A query exception occurred [(SELECT DISTINCT entity1.brand AS brand FROM ENTITY entity1 WHERE lower(entity1.brand) LIKE :param_0 ESCAPE '!' LIMIT 100 UNION SELECT DISTINCT entity2.brand AS brand FROM ENTITY entity2 WHERE lower(entity2.brand) LIKE :param_1 ESCAPE '!') ORDER BY brand ASC] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:162) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:168) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:795) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:120) at io.quarkus.hibernate.orm.runtime.session.TransactionScopedSession.createQuery(TransactionScopedSession.java:401) at org.hibernate.engine.spi.SessionLazyDelegator.createQuery(SessionLazyDelegator.java:541) at org.hibernate.engine.spi.SessionLazyDelegator.createQuery(SessionLazyDelegator.java:66) at org.hibernate.Session_3a974b6a18ac399f675913d732c105426414d370_Synthetic_ClientProxy.createQuery(Unknown Source) at com.blazebit.persistence.impl.AbstractCommonQueryBuilder.getTypedQuery(AbstractCommonQueryBuilder.java:2877) at com.blazebit.persistence.impl.BaseFinalSetOperationBuilderImpl.getTypedQuery(BaseFinalSetOperationBuilderImpl.java:355) at com.blazebit.persistence.impl.BaseFinalSetOperationBuilderImpl.getQuery(BaseFinalSetOperationBuilderImpl.java:503) at com.blazebit.persistence.querydsl.AbstractBlazeJPAQuery.createQuery(AbstractBlazeJPAQuery.java:177) at com.blazebit.persistence.querydsl.AbstractBlazeJPAQuery.createQuery(AbstractBlazeJPAQuery.java:167) at com.querydsl.jpa.impl.AbstractJPAQuery.fetch(AbstractJPAQuery.java:242) at com.blazebit.persistence.querydsl.SetExpressionImpl.fetch(SetExpressionImpl.java:61) at ch.ims.asa.gate.gate.control.brand.BrandService.fetchBrands(BrandService.java:48) at ch.ims.asa.gate.gate.control.brand.BrandService.search(BrandService.java:35) at ch.ims.asa.gate.gate.control.brand.BrandService_Subclass.search$$superforward(Unknown Source) at ch.ims.asa.gate.gate.control.brand.BrandService_Subclass$$function$$1.apply(Unknown Source) at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:73) at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:62) at io.quarkus.security.runtime.interceptor.SecurityHandler.handle(SecurityHandler.java:47) at io.quarkus.security.runtime.interceptor.RolesAllowedInterceptor.intercept(RolesAllowedInterceptor.java:29) at io.quarkus.security.runtime.interceptor.RolesAllowedInterceptor_Bean.intercept(Unknown Source) at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42) at io.quarkus.arc.impl.AroundInvokeInvocationContext.perform(AroundInvokeInvocationContext.java:30) at io.quarkus.arc.impl.InvocationContexts.performAroundInvoke(InvocationContexts.java:27) at ch.ims.asa.gate.gate.control.brand.BrandService_Subclass.search(Unknown Source) at ch.ims.asa.gate.gate.boundary.BrandResource.search(BrandResource.java:26) at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:154) at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:118) at org.jboss.resteasy.core.ResourceMethodInvoker.internalInvokeOnTarget(ResourceMethodInvoker.java:560) at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTargetAfterFilter(ResourceMethodInvoker.java:452) at org.jboss.resteasy.core.ResourceMethodInvoker.lambda$invokeOnTarget$2(ResourceMethodInvoker.java:413) at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:321) at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:415) at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:378) at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:356) at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:70) at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:429) ... 15 more Caused by: org.hibernate.query.SemanticException: A query exception occurred [(SELECT DISTINCT entity1.brand AS brand FROM ENTITY entity1 WHERE lower(entity1.brand) LIKE :param_0 ESCAPE '!' LIMIT 100 UNION SELECT DISTINCT entity2.brand AS brand FROM ENTITY entity2 WHERE lower(entity2.brand) LIKE :param_1 ESCAPE '!') ORDER BY brand ASC] at org.hibernate.query.hql.internal.StandardHqlTranslator.parseHql(StandardHqlTranslator.java:130) at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:77) at org.hibernate.internal.AbstractSharedSessionContract.lambda$interpretHql$2(AbstractSharedSessionContract.java:744) at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:141) at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:128) at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:741) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:786) ... 54 more Caused by: org.hibernate.query.sqm.ParsingException: line 1:131 no viable alternative at input '(SELECTDISTINCTentity1.brandASbrandFROMENTITYentity1WHERElower(entity1.brand)LIKE:param_0ESCAPE'!'LIMIT' at org.hibernate.query.hql.internal.StandardHqlTranslator$1.syntaxError(StandardHqlTranslator.java:46) at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41) at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:543) at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310) at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136) at org.hibernate.grammars.hql.HqlParser.queryExpression(HqlParser.java:1780) at org.hibernate.grammars.hql.HqlParser.selectStatement(HqlParser.java:400) at org.hibernate.grammars.hql.HqlParser.statement(HqlParser.java:331) at org.hibernate.query.hql.internal.StandardHqlTranslator.parseHql(StandardHqlTranslator.java:116) ... 60 more

Steps to reproduce

Query (queryFactory is of type "BlazeJPAQueryFactory"):

var qEntity1 = new QEntity("entity1");
var qEntity2 = new QEntity("entity2");
var brandPath = Expressions.stringPath("brand");
var query = "val";
queryFactory
                .union(
                        queryFactory
                            .select(qEntity1.brand.as(brandPath))
                            .from(qEntity1)
                            .where(qEntity1.brand.containsIgnoreCase(query)),
                        queryFactory
                            .select(qEntity2.brand.as(brandPath))
                            .from(qEntity2)
                            .where(qEntity2.brand.containsIgnoreCase(query))
                )
                .orderBy(brandPath.asc())
                .limit(n)
                .fetch()

Environment

Quarkus: 3.1.3.Final JPA-Provider: Hibernate: 6.2.5 Blaze: 1.6.9 DBMS: PostgreSQL 10.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit

LucaRitz avatar Aug 02 '23 11:08 LucaRitz

IIRC there is a test that tests exactly this, so I am wondering if this may actually be a Hibernate 6.2 issue as Blaze Persistence renders set operations natively in Hibernate 6.🤔

Did you try with a plain Blaze-Persistence CriteriaBuilder query or a HQL query directly?

jwgmeligmeyling avatar Aug 02 '23 19:08 jwgmeligmeyling

The generated query is:

(
	SELECT DISTINCT entity1.brand AS brand FROM ENTITY entity1 WHERE lower(entity1.brand) LIKE :param_0 ESCAPE '!' LIMIT 100 
	UNION 
	SELECT DISTINCT entity2.brand AS brand FROM ENTITY entity2 WHERE lower(entity2.brand) LIKE :param_1 ESCAPE '!'
) 
ORDER BY brand ASC

Seems the QueryDSL integration might apply the limit on the wrong node maybe. Can you check that please @jwgmeligmeyling?

beikov avatar Aug 03 '23 12:08 beikov 


SetExpression<Document> query = queryFactory
        .union(
                queryFactory.intersect(
                        select(document).from(document).where(document.id.eq(41L)).limit(1),
                        queryFactory.except(
                                select(document).from(document).where(document.id.eq(42L)).limit(2),
                                select(document).from(document).where(document.id.eq(43L)).limit(3)
                        ).limit(4)
                ).limit(5),
                select(document).from(document).where(document.id.eq(46L)).limit(6)
        )
        .limit(7);

produces the following JPQL Next:

((SELECT document FROM Document document WHERE document.id = :param_0 LIMIT 1
INTERSECT
((SELECT document FROM Document document WHERE document.id = :param_1 LIMIT 2
EXCEPT
SELECT document FROM Document document WHERE document.id = :param_2 LIMIT 3) LIMIT 4)) LIMIT 5)
UNION
SELECT document FROM Document document WHERE document.id = :param_3 LIMIT 6

Although the outermost limit doesn't seem to be applied, I don't see the outermost limit being applied to any of the subqueries.

I think the error is not from the exact query from the original issue.

Even though the outer most limit is not applied, the serializer always produced valid JPQL which leads me to believe there is another issue somewhere still.

jwgmeligmeyling avatar Aug 03 '23 15:08 jwgmeligmeyling