Problems with QoQ after upgrading to 5.4.3.2

dev support
1

After upgrading from 5.3.10.120 to 5.4.3.2 I’ve found several query of queries throwing errors.
Are these bugs?

Test case 1

<cfset queryOne = queryNew("column_one,column_two", "decimal,decimal", [[10.22, 100.33], [20.44, 200.55]]) />

<cfquery name="queryTwo" dbtype="query">
	SELECT *, ROUND(column_one + column_two) AS column_three
	FROM queryOne
</cfquery>

Throws the error:
duplicate column name in derived table

Workaround, remove the ROUND() function…
SELECT *, column_one + column_two AS column_three

Alternatively, listing the column names in the SELECT statement…
SELECT column_one, column_two, ROUND(column_one + column_two) AS column_three
…stops the errors but results in all the returned values being rounded.

Test case 2:

<cfset queryOne = queryNew("column_one,column_two", "decimal,decimal", [[10.22, 100.33], [20.44, 200.55]]) />

<cfquery name="queryTwo" dbtype="query">
	SELECT column_one, column_two, column_one + column_two AS column_three
	FROM queryOne
	WHERE column_three > 200
</cfquery>

Trying to use the new column directly in the WHERE clause results in an empty recordset.

Workaround, repeat the calculation in the WHERE clause:
WHERE column_one + column_two > 200

1 Like
2

Hi, I had similar problems this morning.
See https://dev.lucee.org/t/error-using-query-of-queries-user-lacks-privilege-version-5-4-x/12905

Try the following …

<cfscript>
	dump(server.lucee.version);
	myQuery = queryNew("field1,field2",
	"integer,integer",
	[{
			"field1": 30,
			"field2": 3
		},
		{
			"field1": 345,
			"field2": 34
		},
		{
			"field1": 123,
			"field2": 12
		},
		{
			"field1": 24,
			"field2": 2
		},
		{
			"field1": 56,
			"field2": 5
		},
		{
			"field1": 44,
			"field2": 4
		},
		{
			"field1": 234,
			"field2": 23
		},
		{
			"field1": 3434,
			"field2": 34
		}
	]);

	dump(myQuery);

	query name="myNewQuery" dbtype="query" {
		echo("
            SELECT field1, field2, ROUND(field1 + field2) AS field3
	        FROM myQuery
		");
	}

	query name="myNewQuery2" dbtype="query" {
		echo("
            SELECT field1, field2, (field1 + field2) AS field3
	        FROM myQuery
	        WHERE (field1 + field2) > 200
        ");
	}

	dump(myNewQuery);
	dump(myNewQuery2);
	
</cfscript>
3

Thanks tab.

A problem with this, if the fields are decimal not integer, the rounding to create field3 is also applied to field1 and field2.

<cfscript>
	dump(server.lucee.version);
	myQuery = queryNew("field1,field2",
	"decimal,decimal",
	[{
			"field1": 30.5,
			"field2": 3.6
		},
		{
			"field1": 345.7,
			"field2": 34.8
		}
	]);

	dump(myQuery);

	query name="myNewQuery" dbtype="query" {
		echo("
            SELECT field1, field2, ROUND(field1 + field2) AS field3
	        FROM myQuery
		");
	}

	dump(myNewQuery);
</cfscript>
4

Yes, I saw that too late. But it works …

5

The native QoQ doesn’t support round() yet ([LDEV-4331] - Lucee) so this kicks over to HSQLDB, which apparently has a bug. (The HSQLDB lib was recently updated in Lucee to a newer version)

This seems like another bug in HSQLDB.

The ideal fix for both of those issues IMO is to implement LDEV-4331, which prolly isn’t too much work. I’d just need to take a day and do it. (Any offers to sponsor? :slight_smile: )

This uses the native QoQ, but referencing an aliased column in the where is not allowed in the native QoQ, or other major DB’s like SQL Server (Because selects aren’t evaluated until after the where filter has been applied). Instead of throwing an error however, it seems that it just causes the where clause to match nothing. It’s worth noting that HSQLDB DOES allow you to reference aliased columns in the where so long as the select isn’t an aggregate.

IMO, this sort of SQL is wrong, but I’m open to seeing if the native QoQ can find a way to support it in some scenarios since HSQDB does. There is no ticket for this and it would prolly take a few days to hammer on and figure out.

1 Like
6

Is there a list of which functions are/aren’t supported by native QoQ?

Is there a way to tell if a query has used native QoQ or HSQLDB?

That’s fair.

The queries I’ve had problems with on 5.4.3.2 can easily be rewritten (or replaced with query.filter() etc.).

7

btw, what is the estimate amount to sponsor this ?

8

No really. I’ve been meaning to work on some better docs for QoQ, but generally speaking, you can see what is supported here:

The functions built into HSQLDB are documented here

https://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html

Normally If you dump out the result, they look slightly different. The ones which were passed to HSQLDB will show Datasource: _queryofquerydb. However, I didn’t get a consistent result just now when trying to reproduce this on trycf.cfom, so YMMV.

There are also some env vars you can add into Lucee to disable the HSQLDB fallback for testing.

Which one-- adding the round() function, or trying to allow aliases in the where clause? Prolly 7-8 hours of development to add round, get it all tested and summitted. and then of course it’s up to the Lucee team to merge it. Reach out to Ortus if you’re interested. Most of the Lucee QoQ work I’ve done has been sponsored :slight_smile:

1 Like
9

Thanks, this is all very helpful.

10

Can anybody confirm what the latest version is that we can revert to to avoid the HSQLDB issue?

1 Like
11

Did this ever get resolved? Maybe in a later version of lucee 5.x?

1 Like
12

Not sure which of the 17 things above you mean by “this”, lol. The ticket to add round() to the native QoQ is still open. (no one offered to sponsor, and I haven’t had the time). I’m not sure if the HSQLDB version has been updated and if so, if that would have fixed anything. I would guess you’re here because you got the error, which prolly answers your question.

1 Like
13

You are right, Brad. The error brought me here. :slight_smile:

I tried updating to the latest 5.4.6.9 and still got the error. Rolling all the way back to 5.3.9.133 works well. I will try rewriting the query at some point. Appreciate the help!