Breaking QoQ change between and +

Query of Queries with a column that has query as a value returns following error when using union.
“Can’t cast Complex Object Type Query to String Use Built-In-Function “serialize(Query):String” to create a String from Query”

I realize that it’s probably an edge case but has anyone had to solve this?

Following code works in but returns error in subsequent versions.

public any function main() {
	var q1 = makeQuery(1);
	var q2 = makeQuery(12);
	writeDump(var=joinQuery(q1,q2), abort=true);

public any function makeQuery(id) {
	var q =  queryNew('id, q_col');

	var q_col_data = queryNew('id,col');
	querySetCell(q, 'id', id);
	querySetCell(q, 'q_col', q_col_data);
	return q;

public any function joinQuery(q1, q2) {
        var result = queryExecute( 
                select * from arguments.q1
                select * from arguments.q2
            options:{ dbtype="query" } 
        return result;

Don’t forget to tell us about your stack!

OS: Ubuntu
Java Version: ???
Tomcat Version: ???
Lucee Version:

This regression is due to this ticket

which is more fully explained here:

Generally speaking, QoQ doesn’t care if you have complex values in your query objects, BUT part of this rewrite was fixing the behavior where UNION is meant to also distinct the result. As part of partitioning the query, a string representation needs to be made for every value in the row, so each row can be compared for uniqueness. The Lucee Caster.toString() method does not work for queries, so it throws an error (that you don’t see). Once the native QoQ implementation errors out, Lucee moves on to the HSQLDB implementation which does NOT allow complex values to be in a cell, which also errors out for the same basic reason with a similar exception (that you do see).

So the workaround for you is quite simple, so long as you are OK with duplicate records in your final result (and since older versions of Lucee didn’t distinct the results of unions, I assume you are :slight_smile: ), is simply to use UNION ALL instead which completely bypasses the partitioning of the query

select * from arguments.q1
select * from arguments.q2

Now, as far as the error goes, we need to make a decision and choose one of the following paths forward (cc/ @Zackster )

  • Continue to throw an exception, but possibly a better-worded one that explains you can’t DISTINCT or GROUP a select when some of the cells contain complex values which cannot be turned into a string for easy comparison.
  • OR we implement some janky attempt at representing complex values as a string so we can still attempt to compare them for uniqueness
  • OR we just ignore complex values and don’t even try to represent them internally for the sake of determining uniqueness and just document that complex values won’t be taken into account when distincting or grouping a query. This would avoid the error, but may not work like someone expects.

I don’t have a super strong opinion, but I’m inclined to say the 2nd bullet is the “best” solution, even though it’s tiny bit more work (and still may not cover 100% of possible cases)


4 posts were split to a new topic: Java hashcodes for CFML variables

My vote is for bullet 3 as default with possibly a param to turn on bullet 2


I see this as being quite the rabbit hole. At least since @bdw429s magnificent refactoring, QoQ ignores the complex values when querying and excluding any complex values in queries

for example, are we comparing just the instance data or the whole object, same same but different?

I’d say definitely implement 1 now

Technically, QoQ always ignored (or at least didn’t mind) complex values, so long as it didn’t need to do anything with them as a simple value. For example, length( complexColumn ) never would have worked. The change was that a distinct query (like a union) makes the assumption all values will be simple.

The query partitioning in the new QoQ creates an MD5 hash of the string representation of the data from the entire row (or in some cases, the grouped columns) so it can easily find matching rows with the same data. So what’s happening is (java code)

Caster.toString( cellValue )

which is fine for most types, but not so fine for a query.

Out of curiosity, is that because you think it’s best, or just a good stop gap in the mean time? I ask because the other bullets really aren’t that much more work to be honest. So if you really prefer option 2 or 3, it’s not that much different to just do one of those instead.

I’m saying 1 coz it’s super easy and we have an existing backlog for QoQ tickets too :slight_smile:

1 Like

Right, I get that. I’m jus saying the amount of time it will take me to

  • grab the latest code
  • write some new tests
  • implement a try/catch with a custom throw
  • test and create the merge

is basically the same amount of time it will take me to

  • grab the latest code
  • write some new tests
  • implement a try/catch and ignore
  • test and create the merge

And in the scope of the 2 hours of work above, it’s probably only another 10-15 minutes extra to

  • grab the latest code
  • write some new tests
  • implement a try/catch and pass the edge cases through Lucee’s serialize() BIF for a quick and dirty comparison of compex objects
  • test and create the merge

Oh crap- and another issue with bullet #1 I didn’t think about until just now is even if I put in a custom throw that explains everything all nice and pretty, it will be promptly discarded by Lucee and then the HSQLDB will proceed to fall on its face with a useless message. That goes back to needing to have an exception type that means “no seriously, this QoQ won’t work don’t even try!:confused:

1 Like

exactly! that’s one of the small issues in the backlog


this one that would be nice to see in 5.3.10 if you had time @bdw429s

1 Like