Extract Not Working with Access Group Constraint

I have the below JPQL in a Value Collection Datasource

select extract(HOUR from wl.startTime), count(wl.id) from worklog$WorkLog wl
group by extract(HOUR from wl.startTime)
order by extract(HOUR from wl.startTime)

This all works fine untill I add a security constraint on worklog entity as- {E}.domainID = :session$userLogin.

After adding the above constraint i’m getting the below error.

JpqlSyntaxException: Errors found for input jpql:[select extract(**HOURfromwl.startTime**), count(wl.id) from worklog$WorkLog wl where wl.domainID = :session$userLogin group by extract(**, HOUR, from, wl.startTime,**) order by extract( HOUR from wl.startTime)]

Seems like the JPQL statement is parsing wrongly after the constraint. Look at the bolded text in the above error, they are incorrect the JPQL. The same issue happens after removing the Group by and Order by also.
If i deactivate the constraint, it works again.

So this issue is only when there is a constraint being applied.

This is the full error message which I get.

com.haulmont.cuba.core.global.RemoteException:
---
com.haulmont.cuba.core.sys.jpql.JpqlSyntaxException: Errors found for input jpql:[select extract(HOURfromwl.startTime), count(wl.id) from worklog$WorkLog wl where wl.domainID = :session$userLogin group by extract(, HOUR, from, wl.startTime,) order by extract( HOUR from wl.startTime)]
CommonErrorNode [<unexpected: [@3,15:24='HOURfromwl',<61>,1:15], resync=extract(>]
CommonErrorNode [<mismatched token: [@4,25:25='.',<68>,1:25], resync=.startTime), count(wl.id) from worklog$WorkLog wl>]
CommonErrorNode [<mismatched token: [@37,131:131=',',<66>,1:131], resync=extract(, HOUR>]
CommonErrorNode [<unexpected: [@42,139:142='from',<103>,1:139], resync=from>]
CommonErrorNode [<unexpected: [@49,158:158=')',<35>,1:158], resync=)>]
	at com.haulmont.cuba.core.sys.ServiceInterceptor.aroundInvoke(ServiceInterceptor.java:129)
	at sun.reflect.GeneratedMethodAccessor147.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:629)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:618)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
	at com.sun.proxy.$Proxy212.loadValues(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.haulmont.cuba.core.sys.remoting.LocalServiceInvokerImpl.invoke(LocalServiceInvokerImpl.java:94)
	at com.haulmont.cuba.web.sys.remoting.LocalServiceProxy$LocalServiceInvocationHandler.invoke(LocalServiceProxy.java:154)
	at com.sun.proxy.$Proxy29.loadValues(Unknown Source)
	at com.haulmont.cuba.client.sys.DataManagerClientImpl.loadValues(DataManagerClientImpl.java:137)
	at com.haulmont.cuba.gui.data.impl.GenericDataSupplier.loadValues(GenericDataSupplier.java:80)
	at com.haulmont.cuba.gui.data.impl.ValueDatasourceDelegate.loadData(ValueDatasourceDelegate.java:86)
	at com.haulmont.cuba.gui.data.impl.ValueCollectionDatasourceImpl.loadData(ValueCollectionDatasourceImpl.java:83)
	at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.refresh(CollectionDatasourceImpl.java:161)
	at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.refresh(CollectionDatasourceImpl.java:119)
	at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.setSuspended(CollectionDatasourceImpl.java:722)
	at com.haulmont.cuba.gui.data.impl.DsContextImpl.resumeSuspended(DsContextImpl.java:84)
	at com.haulmont.cuba.gui.WindowManager.afterShowWindow(WindowManager.java:1159)
	at com.haulmont.cuba.web.WebWindowManager.showWindow(WebWindowManager.java:422)
	at com.haulmont.cuba.gui.WindowManager.openWindow(WindowManager.java:756)
	at com.haulmont.cuba.web.WebWindowManager.openWindow(WebWindowManager.java:158)
	at com.haulmont.cuba.gui.config.MenuCommand$ScreenCommand.run(MenuCommand.java:181)
	at com.haulmont.cuba.gui.config.MenuCommand.execute(MenuCommand.java:76)
	at com.haulmont.cuba.web.sys.SideMenuBuilder.lambda$createMenuCommandExecutor$0(SideMenuBuilder.java:180)
	at com.haulmont.cuba.web.gui.components.mainwindow.WebSideMenu$MenuItemImpl.lambda$setCommand$0(WebSideMenu.java:471)

Please, attach long exception logs as files or format it properly using code blocks.

1 Like

Hi,
Thank for reporting the problem.
We have reproduced it and created the YouTrack ticket.