Friday 22 March 2013

Whitespace in HQL causes exceptions, and how Adobe deals with it

G'day:
Here's the "paper trail" of an issue ColdFusion has had ormExecuteQuery() (and - I found out whilst writing this article - <cfquery> too) since the function first existed, in CF9. I'm raising it here because the CF11 pre-release cycle is about to start and I want to try to encourage this behaviour to cease, and someone's just been bitten on the bum by this again. I myself have been bitten by this too.



Here's the original bug (3041019), logged three years ago. The text of the bug entry is not very clear due to formatting not being preserved when the bug tracker data was migrated from the old Flex-based one to the newer HTML-based one. But the gist of it is covered by the intro text:

Using a tab instead of a space after "FROM" or "SELECT" in the HQL passed to ormExecuteQuery() causes the error "Not supported for select queries" to be thrown
To make this more clear, here's some example code:

// Scratch.cfc
component persistent=true table="tbl_scratch"  {

    property name="id"         fieldtype="id";
    property name="varchar"    fieldtype="column" column="scr_varchar";      
    property name="int"        fieldtype="column" column="scr_int";      

}  

// hql.cfm
records = ormExecuteQuery("FROM Scratch WHERE id > 2");
writeDump(records);

The output of which (which I include only for completeness, it's not relevant here):

array
1
component shared.CF.bugs.3041019.model.Scratch
PROPERTIES
id3
varchartoru
int3
METHODS
2
component shared.CF.bugs.3041019.model.Scratch
PROPERTIES
id4
varcharwha
int4
METHODS

This just demonstrates the query statement works OK. Now I'm a stickler for indentation and whitespace in my code. I'm not as OTT with it as Ben Nadel is, but I like splitting my statements over multiple lines to keep them as far away from the right-hand edge of the screen as possible. So I would chuck some whitespace in that query call like this:

// hql.cfm
records = ormExecuteQuery("
    FROM    Scratch
    WHERE    id > 2
");
writeDump(records);

To me that's a lot more readable, and it's nice to focus on the HQL, rather than the baggage that is necessary to run it.

However if one has non-space (ie: ASCII 32) whitespace - such as TABs - in one's HQL statement, CF spits back an error, thus:

Error while executing the Hibernate query.

org.hibernate.hql.QueryExecutionRequestException: Not supported for select queries [ FROM Scratch WHERE id > 2]

So obviously that's a bug, right? Right? Well, apparently: no. Because 3041019 has been closed as "Not a bug". Seriously. Go look.

Update:
Adobe have gone and changed the entry for this bug, now identifying it as a duplicate, instead of "not a bug". The original state of the bug can be seen here, via Google's cache:



(this is just to demonstrate I'm not talking out of my arse. Well at least on this count ;-)


As per my comment against the ticket:
I dunno how this can be described as "not a bug". It sure seems like a bug to Mike, Aaron, Haylo75, Julian and myself. And even if it is "not a bug", where's the explanation as to why?
I stand by this now as much as I did at the time of writing. There is two things wrong with Adobe's resolution of this ticket:
  1. yes it bloody is a bug;
  2. even if this was some other case in which the issue at hand wasn't a bug, it's just not on to simply close the ticket: they need to put an explanation as to why they think it's not a bug. They might be misunderstanding the situation, the person raising the ticket might have missed some important information out, or [whatever]. Simply closing the ticket like this is dismissive, patronising, and - really - a bit rude (not to mention "wrong" in this particular case). The person has taken the time to let you know about a bug in your software (software they've paid for), other people have taken time to vote for it, and Adobe's response is just "nup". That's not on.
The next time someone encountered this (3043823), the person gave more info: the problem also exists with <cfquery> when doing HQL queries. So this works:

<cfquery name="records" dbtype="hql">
    FROM Scratch
    WHERE id > 2
</cfquery>    
<cfdump var="#records#">

But this doesn't:

<cfquery name="records" dbtype="hql">
    FROM    Scratch
    WHERE    id > 2
</cfquery>    
<cfdump var="#records#">

It's difficult to tell with the formatting of the mark-up there, but the first example has a single space in "FROM Scratch" and "WHERE id", whereas the latter example uses TABs to line-up "Scratch" and "id" nicely. Somewhat pedantic, but not exactly an edge-case, I think.

This ticket was closed with "not enough time". Which is fine (other than the fact they'd already had quite some time to look at it), but in that case... why close the ticket? Just leave it open. It's not a race to close tickets, so if something hasn't been dealt with, just leave it not dealt with. Again, this is a really dismissive approach to "solving" tickets like this.

Also bear in mind that by the time Adobe was closing this ticket as "not enough time" (read: "we couldn't be arsed"), two people had taken the time to raise tickets, and four other people had voted for the issue to be fixed. Four votes might not sound like a lot, but that is quite a few for the ColdFusion bug tracker.

Finally, someone else has now been bitten by this as well now, raising 3525456. By now the issue is affecting people using CF10, not just CF9 as per the previous two.

To be completely honest, the issue itself is more a demonstration of sloppiness than anything else, and it's pretty easy to work around. The bigger issue here is the poor approach to client communication Adobe have here. This needs to be fixed. I think the Adobe dev team people need to sit in on the Railo Google Group for a while, and watch how the Railo guys do it. I don't necessarily mean being as reactive as Railo is in fixing the bugs, but they way Micha (et al) deal with their community.

Let's - Adobe - turn over a new clients communications leaf as part of the ColdFusion 11 pre-release, eh? (well: after the pre-release when things go public, I mean ;-)

Cheers.

--
Adam