Postgres: Error using question mark operator in cfquery

When working with the “jsonb” data type, Postgres uses the “?” as an operator.
Other operators are with the question mark are “? |” and “?&”.

Using these operators in cfquery Lucee raises an exception.

For testing:

CREATE TABLE public.test_json ( field_json JSONB );
 
INSERT INTO public.test_json ("field_json")
  VALUES 
  (E'["Food", "Wine"]'),
  (E'["Food", "Water"]'),
  (E'["Water"]');

SELECT field_json
FROM test_json
WHERE field_json ? 'Water'; <- ERROR in cfquery

I can’t figure out if it’s a driver or Lucee problem.

The error is this:

java.lang.RuntimeException
Message: there are more question marks in the SQL than params defined

Stacktrace:

lucee.runtime.exp.NativeException: there are more question marks in the SQL than params defined
at lucee.runtime.db.SQLImpl.toString(SQLImpl.java:119)
at lucee.runtime.exp.DatabaseException.getCatchBlock(DatabaseException.java:162)
at lucee.runtime.PageContextImpl._setCatch(PageContextImpl.java:3020)
at lucee.runtime.PageContextImpl.setCatch(PageContextImpl.java:3000)
at system.web.controller_cfc$cf$x.udfCall3(/coldbox/system/web/Controller.cfc:992)
at system.web.controller_cfc$cf$x.udfCall(/coldbox/system/web/Controller.cfc)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213)
at lucee.runtime.type.scope.UndefinedImpl.callWithNamedValues(UndefinedImpl.java:804)
at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866)
at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1781)
at system.web.controller_cfc$cf$x.udfCall3(/coldbox/system/web/Controller.cfc:667)
at system.web.controller_cfc$cf$x.udfCall(/coldbox/system/web/Controller.cfc)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:698)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:585)
at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1951)
at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866)
at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1781)
at system.bootstrap_cfc$cf$w.udfCall1(/coldbox/system/Bootstrap.cfc:285)
at system.bootstrap_cfc$cf$w.udfCall(/coldbox/system/Bootstrap.cfc)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
at lucee.runtime.type.scope.UndefinedImpl.call(UndefinedImpl.java:786)
at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787)
at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1762)
at system.bootstrap_cfc$cf$w.udfCall1(/coldbox/system/Bootstrap.cfc:519)
at system.bootstrap_cfc$cf$w.udfCall(/coldbox/system/Bootstrap.cfc)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:697)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:585)
at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1932)
at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787)
at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1762)
at apps._public.application_cfc$cf$1.udfCall(/apps/public/Application.cfc:38)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:697)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:585)
at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1932)
at lucee.runtime.listener.ModernAppListener.call(ModernAppListener.java:444)
at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:135)
at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:44)
at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2475)
at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2465)
at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2436)
at lucee.runtime.engine.Request.exe(Request.java:45)
at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1194)
at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1140)
at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97)
at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:711)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:459)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:385)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:313)
at org.tuckey.web.filters.urlrewrite.NormalRewrittenUrl.doRewrite(NormalRewrittenUrl.java:213)
at org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:171)
at org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:145)
at org.tuckey.web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:92)
at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:394)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:890)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.RuntimeException: there are more question marks in the SQL than params defined
… 92 more

It’s Lucee. There’s a problem with query param resolution when queries contain literal question marks.

https://luceeserver.atlassian.net/browse/LDEV-2754

Thanks Julian!

I add my comment to ticket about PostgreSQL behavior.

For the moment I have solved it using the undocumented function:

jsonb_exists(jsonb, text)

I’ve added this to the sprint for 6.0

1 Like

Just seen. Thanks Zack. :grinning_face_with_smiling_eyes: