Query of Queries Quirkiness

CF5 introduced the ability to run a sub set of SQL commands against CF query objects in memory. But there are some quirky things to look out for.

I sent this to mailing lists a while back and did a presentation at the NSW CFUG... thought I mmight put my thoughts online in case you might like a recap on the quirkiness of query of queries.

There is now a knowledge base article on some of this at: http://www.macromedia.com/v1/handlers/index.cfm?ID=21873

For those following my QofQ saga... these seem to be the final results of hacking around... though I've had little feedback -> so some findings may be the rantings of a crack smoking maniac... I hope not :)

Ok. I've tried to distill all issues into this one email and give example code to illustrate the issue... so this is a longish but concise account :)

Summary of issues with CFSQL discovered thus far (elaborated in more detail below):

a *single* join only, despite marketing info to the contrary
capitalisation issues with query table name (must be same case or p-code error)
must SELECT for columnname if ORDER BY same name
must use query prefix for SELECT of column name in join BUT if you use prefix for a single table query prefix becomes part of the columnname variable (this is actually more complicated but see below)
comparisons using dates requires query modification
modifying cached queries presents locking issues
cannot modify existing query column, must add column of modified data
Incorrect join and cartesian products
no outer join
string comparisons are case sensitive

Not to mention that the whole querying caper actually appears to be rather slow. Haven't done much quantitative research on this but I'm getting queries taking around 70-90ms to get slices of a query that has only 400 records in it. SQL server would typically take about 10ms for this sort of thing.

Examples are all done using the ColdFusion installation CompanyInfo db.
// 1. a *single* join only, despite marketing info to the contrary
This was a real disappointment and contrary to what is clearly advertised in the promotional material (ie the ability to perform a join across an LDAP, ODBC and POP queries). Initially I thought I could load all tables of a database into memory and just run queries.

A serious deficieny is that a twin join returns no error - just zero
records. Again the documentation is misleading at best. Well admittedly there is a one line bullet-point in the docs.

// 2. capitalisation issues with query table name (must be same case or
p-code error)

this works..
SELECT qEmployee.emp_id
FROM qEmployee
ORDER BY qEmployee.emp_id

pcode error..
SELECT qEmployee.emp_id
FROM qemployee
ORDER BY qEmployee.emp_id

pcode error..
SELECT qemployee.emp_id
FROM qEmployee
ORDER BY qEmployee.emp_id

but this also works..
SELECT qEmployee.emp_id
FROM qEmployee
ORDER BY qemployee.emp_id

// 3. must SELECT for columnname if ORDER BY same name

this works..
SELECT emp_id, startdate
FROM qEmployee
ORDER BY startdate

this fails..
SELECT emp_id
FROM qEmployee
ORDER BY startdate

this fails.. (as it should)
SELECT emp_id, startdate as otherdate
FROM qEmployee
ORDER BY otherdate

// 4. must use query prefix for SELECT of column name in join BUT if you use prefix for a single table query prefix becomes part of the columnname variable (this is actually more complicated but see below)

Firstly, this works..
SELECT qEmployee.emp_id, qEmployee.startdate
FROM qEmployee
ORDER BY qEmployee.startdate

BUT you end up with column variable names like "qEmployee.startdate". That is the table prefix is made *part of* the actual variable name. I beleive this is a bug.

In a single table query the only way around this is to *not* use a table
prefix or alternatively set an AS alias eg. "qEmployee.startdate AS
startdate"

this works...
SELECT emp_id, qEmployee.startdate AS startdate
FROM qEmployee

--> columns become (emp_id, startdate)
(clearly the CFSQL is correctly interpreting the table prefix for data
manipulation but incorrectly assigning a variable name)

this fails..
SELECT emp_id
FROM qEmployee, qDepartmt
WHERE qEmployee.dept_id = qDepartmt.dept_id

BUT interestingly the error is:
"The column 'emp_id' was not found in query qDepartmt"

CFSQL looks for column names in the first table by alpha sort, then
*requires* a prefix for the additional table! The fact that the table
prefix is required at all for a unique column name seems like a bug - if
not, it should be documented.

this works..
SELECT qEmployee.emp_id
FROM qEmployee, qDepartmt
WHERE qEmployee.dept_id = qDepartmt.dept_id

this also works..
SELECT dept_name
FROM qEmployee, qDepartmt
WHERE qEmployee.dept_id = qDepartmt.dept_id

The only way to be sure of getting what you want in a join is something
like:
SELECT
qEmployee.emp_id AS emp_id,
qDepartmt.dept_name AS dept_name
FROM qEmployee, qDepartmt
WHERE qEmployee.dept_id = qDepartmt.dept_id

--> column names (emp_id, dept_name)

// 5. comparisons using dates requires query modification
Obviously due to the typeless nature of the ColdFusion variable you need to be more careful about integer and or date comparisons. But then again CFSQL appears to cast dates but not in any clearly defined way.

this fails..
SELECT *
FROM qEmployee
WHERE startdate > #createodbcdate("1995-01-01")#

with an error of:
"Can't find symbol: {d"

this fails..
SELECT *
FROM qEmployee
WHERE startdate > 1995-01-01

with an error of:
"Error: 1995.000000 is not a valid date"

this works..
SELECT *
FROM qEmployee
WHERE startdate > '1995-01-01'

this works..
SELECT *
FROM qEmployee
WHERE startdate > '01-jan-1995'

>From all this i can only assume:
a. CF casts the date on the fly
b. date must be designated a string using single quotes
c. CF date cast cannot identify an ODBC date (bug?)

(BTW adding an extra column of startdates converted to Spectra-like dates works a treat for comparisons:
<cfif NOT IsDefined("qEmployee.datemod")>
<cfset aSpacDate = ArrayNew(1)>
<cfloop query="qEmployee">
    
    <cfset spacdate = CreateODBCDate(dateformat(qEmployee.StartDate)) + 0>
    <cfset tmp = ArrayAppend(aSpacDate, spacdate)>
</cfloop>
<cfset tmp = QueryAddColumn(qEmployee, "datemod", aSpacDate)>
</cfif>

but I worry about potential locking issues...
)

// 6. modifying cached queries presents locking issues
(perhaps a lack of understanding on my part of cached queries)
Given that you may need to modify cached queries, does this present locking problems? The only way i can see to copy a cached query safely is using duplicate in a named lock etc.. this is not a great scenario. Is it required? Who would know?

(20011206 GRB don't bother with this.)

// 7. cannot modify existing query column, must add column of modified data
(perhaps a lack of understanding on my part of cached queries)
I can't appear to modify a cached query column successfully with
querysetcell(). I end up with anomalous results... typically the column is empty after the manipulation. Adding a column appears to present no problems.

I started doing this to get around issues of date comparison. I was adding an additional column with startdate converted into a spectra-like date (days from 1900). I don't think this is necessary now that i have my head around CFSQL date behaviour.

// 8. Incorrect join and cartesian products
This works..
SELECT
qEmployee.emp_id AS emp_id,
qDepartmt.dept_name AS dept_name
FROM qEmployee, qDepartmt

BUT result set is very strange.
dept_name maps to qEmployee.firstname
emp_id maps to qEmployee.emp_id
and the emp_id and firstname are from the correct record.

Normally I would have anticipated a cartesian product. ie. the complete list of emp_id's repeated as many times as there are departments.

// 9. no outer join
Speaks for itself. Not really a bug but causes grief as you absolutely have to have appropriate data in all relevant columns.

// 10. string comparisons are case sensitive
When performing string comparisons query of queries is case sensitive. For example:
WHERE firstname LIKE 'G%'
Will return different results to:
WHERE firstname LIKE 'g%'

Well folks... after that I'm cooked. I need to come up for air.. there may have been a few other items of note but that will do for starters.

Best regards,

-- Geoff

PS. Please let me know if my musings are wrong in any way.

modiusATdaemonDOTcomDOTau
Director - Daemon Internet Consultants
Allaire Master Instructor
http://www.daemon.com.au/
p. +61 2 9380 4162 % f. +61 2 9380 4204
17 Roslyn Gardens, Elizabeth Bay NSW 2011 Australia