Using OR in the query of a Page Provider

ioihanguren_ — Fri, 03 Feb 2017 10:00:07 GMT

Hello,

We have a problem related with the query defined in a page provider. We are using Nuxeo 7.10.

We are using the widgetType singleDocumentSuggestion. The following page provider works correctly:


<coreQueryPageProvider name="test">  
<property name="maxResults">PAGE_SIZE</property>  
<pattern escapeParameters="true" quoteParameters="false">  
**SELECT * FROM Document WHERE (ecm:fulltext.dc:title LIKE '?*') AND ecm:mixinType !=  
'HiddenInNavigation' AND ecm:isCheckedInVersion = 0 AND  
ecm:currentLifeCycleState != 'deleted' AND ecm:isProxy = 0**  
</pattern>  
<pageSize>10</pageSize>  
</coreQueryPageProvider>

But, if we add an OR in the condition, the query fails.

  
<coreQueryPageProvider name="test">  
<property name="maxResults">PAGE_SIZE</property>  
<pattern escapeParameters="true" quoteParameters="false">  
SELECT * FROM Document WHERE **(ecm:fulltext.dc:title LIKE '?*' OR ecm:fulltext.dc:description LIKE '?*')** AND ecm:mixinType !=  
'HiddenInNavigation' AND ecm:isCheckedInVersion = 0 AND  
ecm:currentLifeCycleState != 'deleted' AND ecm:isProxy = 0  
</pattern>  
<pageSize>10</pageSize>  
</coreQueryPageProvider>

This is the error and the stack trace:

WARN [http-bio-0.0.0.0-8080-exec-42] [org.nuxeo.ecm.platform.query.nxql.CoreQueryDocumentPageProvider]** **Failed to execute query: SELECT * FROM Document WHERE (ecm:fulltext.dc:title LIKE 'torn*' OR ecm:fulltext.dc:description LIKE ', Syntax error: Invalid token <null> at offset -1**  
org.nuxeo.ecm.core.query.QueryParseException: Failed to execute query: SELECT * FROM Document WHERE (ecm:fulltext.dc:title LIKE 'torn*' OR ecm:fulltext.dc:description LIKE ', **Syntax error: Invalid token <null> at offset -1  
at org.nuxeo.ecm.core.query.sql.parser.parser.parseFailed(parser.java:519)  
at org.nuxeo.ecm.core.query.sql.parser.parser.unrecovered_syntax_error(parser.java:510)  
at java_cup.runtime.lr_parser.parse(lr_parser.java:601)  
at org.nuxeo.ecm.core.query.sql.SQLQueryParser.parse(SQLQueryParser.java:39)  
at org.nuxeo.ecm.core.query.sql.SQLQueryParser.parse(SQLQueryParser.java:48)  
at org.nuxeo.ecm.core.storage.sql.jdbc.NXQLQueryMaker.buildQuery(NXQLQueryMaker.java:281)  
at org.nuxeo.ecm.core.storage.sql.jdbc.JDBCMapper.query(JDBCMapper.java:742)  
at sun.reflect.GeneratedMethodAccessor148.invoke(Unknown Source)  
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)  
at java.lang.reflect.Method.invoke(Unknown Source)  
at org.nuxeo.ecm.core.storage.sql.jdbc.JDBCMapperConnector.doInvoke(JDBCMapperConnector.java:24)  
at org.nuxeo.ecm.core.storage.sql.jdbc.JDBCMapperConnector.invoke(JDBCMapperConnector.java:67)  
at com.sun.proxy.$Proxy55.query(Unknown Source)  
at org.nuxeo.ecm.core.storage.sql.SoftRefCachingMapper.query(SoftRefCachingMapper.java:86)  
at org.nuxeo.ecm.core.storage.sql.SessionImpl.query(SessionImpl.java:1223)  
at org.nuxeo.ecm.core.storage.sql.ra.ConnectionImpl.query(ConnectionImpl.java:324)  
at org.nuxeo.ecm.core.storage.sql.coremodel.SQLSession.query(SQLSession.java:389)  
at org.nuxeo.ecm.core.api.AbstractSession.query(AbstractSession.java:1174)  
at org.nuxeo.ecm.core.api.AbstractSession.query(AbstractSession.java:1147)  
at org.nuxeo.ecm.platform.query.nxql.CoreQueryDocumentPageProvider.getCurrentPage(CoreQueryDocumentPageProvider.java:143)  
at org.nuxeo.ecm.automation.core.util.PaginablePageProvider.<init>(PaginablePageProvider.java:39)  
at org.nuxeo.ecm.automation.jaxrs.io.documents.PaginableDocumentModelListImpl.<init>(PaginableDocumentModelListImpl.java:42)  
at org.nuxeo.ecm.automation.core.operations.services.DocumentPageProviderOperation.run(DocumentPageProviderOperation.java:252)  
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)  
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)  
at java.lang.reflect.Method.invoke(Unknown Source)  
at org.nuxeo.ecm.automation.core.impl.InvokableMethod.doInvoke(InvokableMethod.java:157)  
at org.nuxeo.ecm.automation.core.impl.InvokableMethod.invoke(InvokableMethod.java:170)  
at org.nuxeo.ecm.automation.core.impl.CompiledChainImpl.doInvoke(CompiledChainImpl.java:128)  
at org.nuxeo.ecm.automation.core.impl.CompiledChainImpl.invoke(CompiledChainImpl.java:114)  
at org.nuxeo.ecm.automation.core.impl.OperationServiceImpl.run(OperationServiceImpl.java:208)  
at org.nuxeo.ecm.automation.core.impl.OperationServiceImpl.run(OperationServiceImpl.java:104)  
at org.nuxeo.ecm.automation.seam.operations.RunOperationInSeam.runChain(RunOperationInSeam.java:86)  
at org.nuxeo.ecm.automation.seam.operations.RunOperationInSeam.run(RunOperationInSeam.java:70)  
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)  
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)  
at java.lang.reflect.Method.invoke(Unknown Source)  
at org.nuxeo.ecm.automation.core.impl.InvokableMethod.doInvoke(InvokableMethod.java:157)  
at org.nuxeo.ecm.automation.core.impl.InvokableMethod.invoke(InvokableMethod.java:170)  
at org.nuxeo.ecm.automation.core.impl.CompiledChainImpl.doInvoke(CompiledChainImpl.java:128)  
at org.nuxeo.ecm.automation.core.impl.CompiledChainImpl.invoke(CompiledChainImpl.java:114)  
at org.nuxeo.ecm.automation.core.impl.OperationServiceImpl.run(OperationServiceImpl.java:208)  
at org.nuxeo.ecm.automation.core.impl.OperationServiceImpl.run(OperationServiceImpl.java:115)  
at org.nuxeo.ecm.automation.server.jaxrs.OperationResource.execute(OperationResource.java:51)  
at org.nuxeo.ecm.automation.server.jaxrs.ExecutableResource.doPost(ExecutableResource.java:61)  
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)  
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)  
at java.lang.reflect.Method.invoke(Unknown Source)  
at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)  
at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ObjectOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:258)  
at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)  
at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)  
at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)  
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)  
at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)  
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)  
at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)  
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)  
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)  
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)  
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)  
at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)  
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:558)  
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:733)  
at org.nuxeo.ecm.webengine.app.jersey.WebEngineServlet.containerService(WebEngineServlet.java:171)  
at org.nuxeo.ecm.webengine.app.jersey.WebEngineServlet.service(WebEngineServlet.java:148)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at org.nuxeo.ecm.webengine.jaxrs.session.SessionCleanupFilter.run(SessionCleanupFilter.java:43)  
at org.nuxeo.ecm.webengine.jaxrs.HttpFilter.doFilter(HttpFilter.java:42)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at org.nuxeo.ecm.webengine.app.WebEngineFilter.doFilter(WebEngineFilter.java:92)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at org.nuxeo.ecm.webengine.jaxrs.context.RequestContextFilter.run(RequestContextFilter.java:41)  
at org.nuxeo.ecm.webengine.jaxrs.HttpFilter.doFilter(HttpFilter.java:42)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at org.nuxeo.ecm.platform.web.common.requestcontroller.filter.NuxeoThreadTrackerFilter.doFilter(NuxeoThreadTrackerFilter.java:28)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at org.nuxeo.ecm.core.management.jtajca.internal.Log4jWebFilter.doFilter(Log4jWebFilter.java:67)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at org.nuxeo.ecm.platform.ui.web.rest.FancyURLFilter.doFilter(FancyURLFilter.java:118)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at org.nuxeo.ecm.platform.web.common.requestcontroller.filter.NuxeoRequestControllerFilter.doFilter(NuxeoRequestControllerFilter.java:144)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at org.nuxeo.ecm.webdav.service.WIRequestFilter.doFilter(WIRequestFilter.java:59)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at org.nuxeo.ecm.platform.ui.web.auth.NuxeoAuthenticationFilter.doFilterInternal(NuxeoAuthenticationFilter.java:591)  
at org.nuxeo.ecm.platform.ui.web.auth.service.NuxeoAuthFilterChain.doFilter(NuxeoAuthFilterChain.java:36)  
at org.nuxeo.ecm.platform.ui.web.auth.oauth.NuxeoOAuthFilter.doFilter(NuxeoOAuthFilter.java:120)  
at org.nuxeo.ecm.platform.ui.web.auth.service.NuxeoAuthFilterChain.doFilter(NuxeoAuthFilterChain.java:34)  
at org.nuxeo.ecm.platform.ui.web.auth.oauth2.NuxeoOAuth2Filter.doFilter(NuxeoOAuth2Filter.java:68)  
at org.nuxeo.ecm.platform.ui.web.auth.service.NuxeoAuthFilterChain.doFilter(NuxeoAuthFilterChain.java:34)  
at org.nuxeo.ecm.platform.ui.web.auth.NuxeoAuthenticationFilter.doFilter(NuxeoAuthenticationFilter.java:401)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:208)  
at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:274)  
at org.nuxeo.ecm.platform.web.common.requestcontroller.filter.NuxeoCorsFilter.doFilter(NuxeoCorsFilter.java:64)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at org.nuxeo.ecm.platform.web.common.exceptionhandling.NuxeoExceptionFilter.doFilter(NuxeoExceptionFilter.java:75)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at org.nuxeo.ecm.platform.web.common.encoding.NuxeoEncodingFilter.doFilter(NuxeoEncodingFilter.java:73)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)  
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)  
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)  
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)  
at org.nuxeo.runtime.tomcat.dev.DevValve.invoke(DevValve.java:67)  
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)  
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)  
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)  
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)  
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)  
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)  
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)  
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)  
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)  
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)  
at java.lang.Thread.run(Unknown Source)

Is it possible to add an OR in this kind of queries?

Thanks in advance

topic Using OR in the query of a Page Provider in Nuxeo Forum

Using OR in the query of a Page Provider