Query of Query support in Lucee does not perform well. The native Java implementation of QoQ only supports the most basic of SQL. All complex queries throw an exception and fall back to a second attempt which runs the select against an embedded HyperSQL database. This has a few issues:

• The HSQLDB implementation is single threaded due to a synchronized block which makes QoQ untenable under any sort of load

• There is a base overhead of the JDBC connection and parameter handling that makes any page with a large number of of QofQs to slow down significantly

• HSQLDBs parser is not very good and throws exceptions for many valid SQL statements such as count(1)

Improve the native Java implementation of QofQ to support

• group by clause

• having clause

• aggregate functions

• Allow aggregates to reference nested operations including scalar functions

  • ceiling( max( floor( yearsEmployed ) )+count(1) )

• faster distinct support

• Fix buginess in unions were results aren't distinct by default (union distinct)

• Improve count() to support

  • count( all col )

  • count( 1 )

  • count( * )

  • count( 'literal' )

  • count( distinct col )

  • count( distinct scalarFunc( col ) )

• Fix bugs in SQL parser that incorrectly requires only a single space between multi-word clauses

  • is null

  • is not null

  • not in

  • not like

  • order by

  • group by

• Remove single-threaded limitations

• Performance tune speed of queries

I am submitting a pull request that includes all of this. I have done rigorous testing and added a new suite of tests for all the functionality. I have also done side-by-side performance tests with Adobe CF and the new implementation is faster than Lucee's old implementation in every single test and is faster than Adobe CF's implementation in most of the tests. All code in the pull request is fully commented.

I have also added support for the following system props/env vars

• lucee.qoq.hsqldb.disable=true – Throw exception if native QoQ logic fails

• lucee.qoq.hsqldb.debug=true – Log message to WEB context's datasource log any time a QofQ "falls back" to HyperSQL. This could include just bad SQL syntax.