ADQL queries error when trying to use boolean columns as a WHERE conditional

Support Rubin Science Platform
#1

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

#2

I’m more familiar with SQL than ADQL, but ADQL is a highly restricted subset of SQL, and as far as I can tell (per the language spec) it really does require that predicates be binary expressions or compositions thereof. It also looks like ADQL does not have boolean literals (which I assume is why you used > 0 rather than = true in your example above)?

#3

I’ve marked @kfindeisen 's answer as the solution (thank you!) and will make a final note from @ktl that in these situations, = 1 or != 0 or <> 0 might be better than > 0.

#4

A further followup:

We acknowledge that the error message is unhelpful. We’ve submitted an issue against the upstream for the TAP service: Attempt to use a column expression as a boolean_factor term in a WHERE produces an unhelpful error · Issue #156 · opencadc/tap · GitHub and will pursue an improved error message.

A clarification and correction to what was said about ADQL above: while it’s true that ADQL is a subset of SQL, that’s (mostly) about SQL only supporting SELECT as its primary operation. ADQL is, specifically, based on SQL92, and SQL92 does not have a Boolean type.

Booleans were added to SQL in SQL99: SQL:1999 - Wikipedia
and even today don’t have first-class support in all common RDBMSes.

There is interest in the IVOA in adding BOOLEAN to a future version of ADQL, and we’ll be supporting that effort. Our database back ends would, it appears, be able to support it.

We declare our flag columns as boolean in order to better document them and to ensure that query results, in particular, include that declaration (VOTable does support a boolean type). But we do recognize that it’s then awkward that they can’t be used that way in a query.

In the mean time, we hope that a better error message will help mitigate the confusion this causes.