cfml

Question on Query-of-Query, what is supported when it comes to doing pagination logic? i.e. The LIMIT, OFFSET, FETCH NEXT keywords

I tried LIMIT, OFFSET + FETCH NEXT and both do not work

Note: don't worry about using QofQ is bad on resource and slow etc. I am asking this question mainly to learn if there is a way to do SQL pagination in QofQ

According to Lucee
&gt; *HSQLDB SQL Implementation*
&gt; This is the fallback for when Lucee's SQL implementation can't handle the QoQ syntax. See the <http://hsqldb.org/doc/2.0/guide/sqlgeneral-chapt.html|HSQLDB documentation> for details.
And the HSQLDB documentation from the above link mentions:
&gt; HyperSQL also supports enhancements with keywords and expressions that are not part of the SQL standard. Expressions such as `SELECT TOP 5 FROM ..`, `SELECT LIMIT 0 10 FROM ...` or `DROP TABLE mytable IF EXISTS` are among such constructs.
So seems like they say `SELECT LIMIT 0 10 FROM ...`  should work in QofQ, but in my testing it doesn't

I can confirm the inbuilt Java QoQ (which I've worked on in the past) supports limit, but not offset

I wouldn't rely too much on the HSQLDB features as it's a fallback that you can't really force to be used.

Also, the version of the HSQLDB docs you're looking at may be newer than the actual HSQLDB version bundled in Lucee

Lucee was on version 1.8 for a while, but the POM.xml file in the 5.4.6 branch looks like HSQLDB 2.7.2 is actually in use, so that's good

I actually take that back about the java  QoQ parser handling limit.  I saw a reference to it in the code, but I actually can't find any unit tests covering it and even a simple limit example on trycf doesn't work.  I guess that isn't implemented.

As far as why the HSQLDB fallback isn't working, I'm not sure, but debugging is not easy because the original exception is getting caught and rethrown with ONLY the error message so the original stack trace is lost

In the fallback code, Lucee attempts to still perform a parse on the SQl, to clean it up, or get the tables names out or both prior to passing it on to HSQLDB

That effectively means that any SQL features which are not supported in the parser Lucee is using are basically never going to work even if HSQLDB supports them

The exception that is caught when I try and run the QoQ SQL `select * from q limit 1` is `lucee.runtime.sql.old.ParseException` which looks like it's coming from one of Lucee's SQL parsers, not from HSQLDB itself

On a side note, BoxLang's QoQ support (which I'm working on right now does support `limit` but I haven't added any offset support.  I suppose that would be pretty easy

As far as your needs, since it's all in memory, I would write a simple wrapper function to extract the rows you need from the query

It's not like you're optimizing anything by not selecting the rows you don't want from the DB.  They're already in memory so you may as well use a loop to extract them

Thank you Brad ! It is good to know all these info and knowing BoxLang's support on QofQ

Yes, as I said, I only try to confirm if QofQ support LIMIT or OFFSET. Pagination SQL should most likely happen on database level. For some edge cases we do use QuerySlice() to achieve pagination logic