Complex QoQ examples needed for testing

Zackster · February 28, 2021, 11:46am

I have been working on allowing Reserved Words for column and table names in Queries of Queries (QoQ) [LDEV-3311] - Lucee for Lucee 6.0

Currently Lucee’s QoQ engine blows up and we’d like to make this more developer friendly and flexible, coz QoQ rocks!

The high performance native QoQ support doesn’t support joins, so Lucee falls back on using HSQLDB which loads the queries into temp tables and then performs the QoQ.

The SQL reserved words like (unique etc) cause the QoQ to fail in this loading phase, which leads to confusing error messages like with [LDEV-2755] - Lucee

With my changes, all columns and tables are loaded with double quotes and in upper case, so unique becomes “UNIQUE”, so any problems then happens in the execution of the QoQ query, rather than this setup phase.

I have some basic test cases, but it would be great to have some additional more complex ones to be sure this approach works. We don’t actually need any data in the source tables for the tests, just a queryNew() that creates the table structure, so we can test that the HSQLB engine can parse the SQL correctly.

github.com

zspitzer/Lucee/blob/qoq-reserved-words-LDEV-3311/test/tickets/LDEV3311.cfc

component extends="org.lucee.cfml.test.LuceeTestCase"	{

	function run( testResults , testBox ) {

		describe( 'QofQ' , function(){
			var t1 = queryNew( "id,unique,and,order,by,table,type,select,distinct" ); 
			var t1 = queryNew( "id,zac,unique" ); 
			var t2 = queryNew( "id" ); 
			var unique = t1; 
			var q = "";
			queryAddRow( t1 );
			queryAddRow( t2 );
			// add dummy data, force all cols to be varchar
			loop list="#t1.columnList()#" item="local.col" {
				querySetCell( t1, col, "lucee rocks", 1 );
			}
			querySetCell( t2, "id", "lucee rocks", 1 );

			it( 'QoQ select * from table with reserved word as column name with HSQLDB (one col)' , function() {
				// force fallback to hsqldb via join

This file has been truncated. show original

So if you have some complex joined QoQ examples, would you like to contribute some test cases?

just checkout this branch https://github.com/zspitzer/Lucee/blob/qoq-reserved-words-LDEV-3311, add your test and then run ant in the loader directory, as per the directions below