Query of Queries (QoQ) with union can not have different columns since version 5.3.7.48

dev support
1

After version 5.3.7.48 it is not possible anymore to have different columns in a QoQ which has a UNION in it.

select top 5 name, code from organisation select name from rs where 1 = 1 union select code from rs where 1=1

All this query does now, is return a recordset with 10 records and with just 1 column, namely “name”. But the value from the first 5 records contain the value from “name” and the last 5 records contain the value from “code”.

It used to return a recordset with 10 records with 2 columns, namely “name” and “code” and for the first 5 records only column “name” would be filled and for the last 5 records only column “code” would be filled.

I am not sure if this is a bug how it works now, or that it never was supposed to work like this. But we use this functionality in our code and cannot upgrade to a newer version of Lucee without changing our code. (and we use it a lot)

I know the behaviour how it works now is the same as in SQL, but the flexibilty of having the unions determine which columns they need, instead of making sure all the queries have the same columns, is quite handy.

Regards Jaco

OS: Window 10 (64 bit)
Java Version: 1.8.0_221 (Oracle Corporation) 64bit
Tomcat Version: Apache Tomcat/8.5.50
Lucee Version: 5.4.2.17

2

Alas, it was never mean to work like that, but just happened to work

@Brad_Wood has done a lot of work on improving QoQ performance and behaviour to match how standard databases work.

QoQ used to be very quirky and it now behaves much more consistently, you are going to need to review and refactor your QoQ SQL

There’s been a lot of work done improving QoQ in the last few years, it’s all documented extensively in jira and all the changes have covering test cases

The native QoQ engine only supports single tables, anything else will fallback on using hsqldb, which was also upgraded in 5.4 to the latest version 2.7.2, from 1.8.0 which was also very old

https://luceeserver.atlassian.net/issues/?jql=labels%20%3D%20"QofQ"

Have you tried adding in empty columns select '' as code, name etc?

1 Like
3

Hmm, I was afraid of this answer. If this is the way it’s supposed to work, then there’s no other option but to rewrite code.

Unfortunately, this is code with a technical debt and quite important for the application, so we will have to be careful with refactoring and do some intensive testing. But we will have to, otherwise we will be stuck in version 5.3.7.48.

Thanks for your answer.