Error using query of queries ("user lacks privilege"), version 5.4.x

dev
1

Hello,

There is a problem using query of queries from version 5.4.0.80 to 5.4.3.2, while version 5.3.12.1 works as expected.

Error message
user lacks privilege or object not found: [myField2]

query name="myNewQuery" dbtype="query" {
	echo("
		SELECT SUM(field1) AS myField1, YEAR(field2) AS myField2 FROM myQuery
		WHERE (myField2 = 2023)
	");
}

Workaround
Version 5.3.12.1 works fine

Thanks for your time!
Thomas

2

Hi @tab can’t tell for sure, but maybe the QoQ in those versions are more restrictive? Since you are using an aggregate function (but not using the GROUP BY statement) to query, and your are not using the “HAVING” statement, this example should work for those functions. Could you test that please?

<cfscript>
	dump(server.lucee.version);
	myQuery = queryNew("field1,field2",
	"integer,Date",
	[{
			"field1": 30,
			"field2": DateAdd("yyyy", -1, now())
		},
		{
			"field1": 345,
			"field2": now()
		},
		{
			"field1": 123,
			"field2": now()
		},
		{
			"field1": 24,
			"field2": DateAdd("yyyy", -2, now())
		},
		{
			"field1": 56,
			"field2": DateAdd("yyyy", -5, now())
		},
		{
			"field1": 44,
			"field2": DateAdd("yyyy", -6, now())
		},
		{
			"field1": 234,
			"field2": DateAdd("yyyy", -2, now())
		},
		{
			"field1": 3434,
			"field2": now()
		}
	]);

	dump(myQuery);

	query name = "myNewQuery" dbtype = "query" {
		echo("	SELECT	SUM(field1) AS myField1, 
						YEAR(field2) AS myField2 
				FROM 	myQuery
				GROUP BY field2 
				HAVING YEAR( field2 )=2023");
	}

	dump(myNewQuery);
</cfscript>

Here is the TryCF Gist:

1 Like
3

Hi Andreas, thanks for your quick response. Using GROUP BY and HAVING did the trick.

1 Like
4

A quick rule of thumb with QoQ since the 5.4 update, some of the old quirky sql which used to work, might not work anymore, so when in doubt, approach QoQ like you would a normal RDBMS

5

I’m not sure how this ever worked, but using an alias in a where clause is not something most DB’s support. SQL Server, for instance, will throw an error. This is presumably because the select expressions are evaluated only after the where clause has filtered the results, creating a catch-22.
Furthermore, mixing aggregate and non-aggregate functions in your select may not an error, but it makes no sense and most DB’s will also throw an exception on that.

The “user lacks privilege” error appears to be what HSQLDB throws when you try to use an aliased column in the where clause of an aggregated select.

1 Like