When running an ADQL query on the RSP I encountered an odd and unhelpful error, and wondered if this was a bug or simply something that needs a more informative error message.
The command is relatively simple:
SELECT COUNT(*) FROM dp02_dc2_catalogs.Source WHERE centroid_flag_noSecondDerivative
in which I was simply trying to get the number of sources where a particular boolean quality flag had been set; since the column is a boolean I assumed it would therefore satisfy the True/False requirements for the WHERE <condition>.
However, I was given the error message
ADQL syntax error: Encountered " <S_IDENTIFIER> "centroid_flag_noSecondDerivative "" at line 1, column 53. Was expecting one of: "(" ... "(" ... "(" ... "(" ... "(" ... "(" ... "(" ... "(" ... "(" ... "(" ... "(" ...
which does not particularly inform me of my mistake.
With some trial and error I eventually found that
select count(*) from dp02_dc2_catalogs.Source where centroid_flag_noSecondDerivative > 0
works; or at least it does not error and submits the job – I’m not sure what ADQL does with True > 0 as a conditional.
With this, however, I do not know what the specific issue is or where the fix lies: is there a particular reason that ADQL does not allow for direct true/false boolean columns to be used in a conditional; is this an issue with the implementation/version of ADQL on the RSP, or an RSP-specific bug in parsing the query; or should where <boolean> work, but with different formatting?
For comparison the where <boolean> conditional does work on the HSC SQL server, but I’m afraid I don’t know the differences between SQL and ADQL enough to know if that is a useful thing to say or not!
Thanks, Tom