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 5.3.7.48 but returns error in subsequent versions.
main();
public any function main() {
var q1 = makeQuery(1);
var q2 = makeQuery(12);
writeDump(var=q1);
writeDump(var=q2);
writeDump(var=joinQuery(q1,q2), abort=true);
}
public any function makeQuery(id) {
var q = queryNew('id, q_col');
queryAddRow(q);
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(
sql:"
select * from arguments.q1
union
select * from arguments.q2
",
options:{ dbtype="query" }
);
return result;
}
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 ), is simply to use UNION ALL instead which completely bypasses the partitioning of the query
select * from arguments.q1
UNION ALL
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)
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?
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.
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!”
), is simply to use 