Hi Jon, can you please submit a support ticket. Thanks!
Bug: CZQL queries: entity field identifiers with names that match keywords cannot be used in all contexts
The CZQL query parser does not correctly determine whether a token is an identifier or a keyword when the entity being SELECTed contains a field with a matching name.
Unfortunately, CZQL does not appear to support a syntax that allows identifiers to be delimited, which would let developers correct the ambiguity on their own. (This is what every SQL flavour does.)
All of the following queries should either run successfully, or all should result in a syntax error; however, that's not what happens:
SELECT Name, Order FROM ProjectType
Actual result: fails
SELECT Order, Name FROM ProjectType
Actual result: succeeds (!)
SELECT Order, Name FROM ProjectType ORDER BY Order ASC
Actual result: fails
Please either change the parser to correctly identify the intended context, or add a syntax element, such as square brackets, that allows developers to delimit identifiers themselves.
Please sign in to leave a comment.
I had already opened a support ticket (#33091) and was told that I should come here instead for developer issues.
Hi Jon, I am not a developer myself so I am struggling to determine where this request belongs. From the ticket, this sounds like more of a feature request on improving the functionality of the parser as opposed to a bug in the system. Is that correct?
I did some digging and found with CZQL you can prefix field names with a @ to differentiate them from keywords. Thus:
SELECT @Order, Name FROM ProjectType ORDER BY @Order ASC
does work. You can add the @ prefix for all or some of the fields, (e.g. SELECT @Order, @Name FROM ProjectType ORDER BY @Order ASC also works).
Hope this helps,
Yup, confirmed the @ syntax works. Thank you.
Could this please be documented in the API reference?
I added a note in the CZQL documentation for that. Changes should be deployed with the next hot fix (scheduled for Feb-5).
Documentation was updated, you may check https://api2.clarizen.com/V2.0/services/data/Query
Thanks for helping us make Clarizen better.
Thank you, what was added is helpful.
Further improvements: There are two misspellings in that paragraph ("altough" should be "although" and "macthes" should be "matches"). You could also consider adding the syntax element to the "General CZQL Syntax" code block above it, which would make it more obvious at a glance, i.e., "SELECT [@]field | groupingFunction (...)".
You're absolutely right. I took your recommendations and they should be deployed next week.
Thanks again for helping us make Clarizen better.
Thank you very much!