When using query of queries, the order from the first query is “lost” and we are having to add another ORDER BY to the query of the query. Is this a bug or a feature?
OS : Windows Server 2012 R2 Datacenter
Java Version : 1.8.0_181 (Oracle Corporation) 64bit
Tomcat Version : Tomcat/8.5.33
Lucee Version : Lucee 6.0.0.585
I wouldn’t have initially thought that it would “just” maintain the order.
For as long as I can remember I have always “assumed” no order.
And I think of a query of queries as being a whole “new” query.
The fact that inner query was ordered, (or was even a query) I deliberately ignore for the outer query and go back to; “Always assume, no order” - unless you explicitly force one into play via the use of ORDER BY.
However;
If Lucee previously behaved differently for you, I would log a bug as a regression.
Or if Adobe behaves differently (the way you’re expecting Lucee to) - you could log a “compatability” bug.
Otherwise - I wouldn’t think there was an actual issue to address here.
Thank you. Logged but I didn’t see a way to mark it as a regression. Lucee preserved the order of the original query in the past.
Sorry I didn’t see this thread sooner. Here’s the same comment I left in JIRA.
This is not a bug nor a regression, it is working as designed. It was incorrect for your previous code to assume the order would be retained when not using an ORDER BY statement. Even traditional RDBMs don’t offer this guarantee. The reason this changed is because in Lucee 6, parallel streams are used to increase the performance of QoQ operations by processing each row in multiple threads at a time. This means the original order will not necessarily be preserved unless you use an ORDER BY, which is the correct way to ensure the order. This “worked” on older versions of Lucee as a “happy accident”.
Okay. Thanks for the reply!
Is there any way to retain the QoQ lost “order by” as per Lucee 5 by way of a new “sponsored optional feature” in Lucee 6 ? Who is right party to talk to, about the possibility and the sponsor cost ?
You should be able to get in touch with the team through the contact form for those purposes:
https://www.lucee.org/get-support.html
Or may be @Gert is able to give you some direction.
Thank you for your reply.
Hey there, do you need us to fix anything? Because the ORDER BY clause solves your issue, no?
Hi @Gert, yes, we are asking if possible to retain the QoQ “order by” as per Lucee 5 by way of a new sponsored optional function in Lucee 6. Is it possible? could you let us know an estimate of sponsor cost and how to go about it?
Again, regardless of how Lucee 5 behaved, it was incorrect for any code not specifying an ORDER BY to expect any particular order.
That said, you can already get the old behavior back by adding the following system property to Lucee (as covered in the ticket)
Improving Lucee’s QoQ Support Again- now 200% faster-Dlucee.qoq.parallelism=999999
This will effectively turn off the parallel processing of Query of queries. Your QoQs will be slower, but will retain the original order of the query.