Query of queries and "Not a condition" error message

dev support
1

Hi,

After migrating from Adobe Coldfusion 8 to Lucee last version, we start to get errors messages in some query of queries used in our reports. The error is Not a condition. It seems to depends of the data used in the query of queries, because the same code can work for client A, but not for client B who have differents data.

lucee.runtime.exp.DatabaseException: Not a condition at org.hsqldb.jdbc.Util.sqlException(Unknown Source) at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source) at org.hsqldb.jdbc.jdbcStatement.execute(Unknown Source) at lucee.runtime.type.util.QueryUtil.execute(QueryUtil.java:320) at lucee.runtime.type.QueryImpl.execute(QueryImpl.java:287) at lucee.runtime.type.QueryImpl.(QueryImpl.java:235) at lucee.runtime.db.HSQLDBHandler.__execute(HSQLDBHandler.java:358) at lucee.runtime.db.HSQLDBHandler._execute(HSQLDBHandler.java:319) at lucee.runtime.db.HSQLDBHandler.execute(HSQLDBHandler.java:307) at lucee.runtime.tag.Query.executeQoQ(Query.java:1110) at lucee.runtime.tag.Query._doEndTag(Query.java:680) at lucee.runtime.tag.Query.doEndTag(Query.java:565) at fr.intranet.rapport_rentabilite360.index_cfm$cf$7di.call(/fr/intranet/rapport-rentabilite/index.cfm:380) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1034) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:926) at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:907) at application_cfc$cf.udfCall(/application.cfc:288) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:106) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:344) at lucee.runtime.type.UDFImpl.call(UDFImpl.java:217) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:684) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:572) at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1911) at lucee.runtime.listener.ModernAppListener.call(ModernAppListener.java:437) at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:216) at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:44) at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2460) at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2450) at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2421) at lucee.runtime.engine.Request.exe(Request.java:45) at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1179) at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1125) at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:102) at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51) at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:479) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:806) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748)

I will soon have access to a development environment to test it further and send you the SQL query, but I wonder if any of you have an idea where it can come from? I suspect null data in some column or datatype conversion, but not sure yet. It happens with differents query of queries.

Here an example :
SELECT Sum(myColumnA) AS myColumnA, Sum(myColumnB) AS myColumnB, ((Sum(myColumnA) / 26) * 100) AS myPercentage FROM myQuery WHERE showtimeID = 55 AND anotherColumnID > 0 AND Amount > 0
I suspect it can come from Sum(myColumnA) AS myColumnA because I use the same name, like I saw in this subject : https://lucee.daemonite.io/t/query-of-queries-differences-in-cf-vs-lucee/1202

I found this too :

Thank you

OS: Windows Server 2016 (10.0) 64bit
Java Version: 1.8.0_181 (Oracle Corporation) 64bit
Tomcat Version: Apache Tomcat/8.5.33
Lucee Version: Lucee 5.3.8.206

2

Lucee has a couple different engines that are used to process query of queries depending on the complexity of the query which you can read about here Improving Lucee's Query of Query Support

The stack above tells me that the HSQLDB engine is processing your query, which means it’s likely a bug in HSQLDB itself. The LDEV-272 ticket with the same error was resolved when the QoQ started using Lucee’s native approach instead of the in-memory DB.

An excellent question is why your query above isn’t using the native QoQ implementation either. Can you provide a complete self-contained reproducible example? I’m unable to reproduce your error on trycf.com with the SQL you provided:

1 Like
3

Is that possible that Lucee use HSQLDB engine because there is a lot of data, or the data have null or empty value?

As soon my development environment is up and running, I’m gonna test less data to see if it’s related.

4

No, the decision to use HSQLDB is not based on the size or nature of the data in the query. It’s purely based on the native implementation’s ability to parse and process the SQL in your QoQ. Now it is possible that there is an error due to a bug in the native implementation which may cause Lucee to fail over to the HSQLDB, but there are no preemptive checks for this. Look at the lucee.qoq.hsqldb.disable and lucee.qoq.hsqldb.debug covered in the blog post I linked to above if you want to debug that. But for now, I’d confirm that the QoQ that’s erroring is actually the same as what you posted here.

I’ll add the current native QoQ implementation in Lucee was written (or at least heavily refactored) by myself on behalf of a client converting from Adobe CF to Lucee.

1 Like
5

Many thanks Brad. You are a great help. My development server is ready. I’ll check it all out.

6

Hi Brad,

I managed to correct the errors in my query of queries. This was from the GROUP BY clause and the columns I was referring in the SELECT. In one case, I was doing a COUNT on a column in the SELECT and this column was not in the GROUP BY clause, and in the other case, I was doing a GROUP BY on a column that I created on the fly like that : SELECT '1' AS myNewColumn FROM anotherQuery GROUP BY myNewColumn. Everything is in order now!

I wanted to create a very simple example to reproduce the error, but I can’t do it for some reason I don’t know.