I have been working on allowing Reserved Words for column and table names in Queries of Queries (QoQ) [LDEV-3311] QoQ should allow reserved words as column names - 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] New "unique" property of scheduled tasks causes queries produced by cfschedule to throw exception when joined in QoQ - 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.
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