Attempt to use a column expression as a boolean_factor term in a WHERE produces an unhelpful error

[gpdf]

It's well-known that standard ADQL does not currently contain Boolean-valued expressions. It's also pretty well-known that there have been efforts to extend ADQL with them, most recently to try to allow the use of, e.g., CONTAINS( ... ) as a boolean_factor on its own, rather than as CONTAINS( ... ) = 1. However, for now this is not in the ADQL 2.1 PR and is a problem for another day.

In the mean time, when a user attempts to use a variable as a Boolean, the CADC TAP service (both Argus and in its Rubin RSP variant) produces a largely unhelpful and rather strange error. The following query:

select count(*) from caom2.Observation where obsID

produces the error message:

IllegalArgumentException:ADQL syntax error: Encountered " <S_IDENTIFIER> "obsID "" at line 1, column 47.
Was expecting one of:
    "(" ...
    "(" ...
    "(" ...
    "(" ...
    "(" ...
    "(" ...
    "(" ...
    "(" ...
    "(" ...
    "(" ...
    "(" ...

While the offending column expression is at least mentioned, the "was expecting" text is entirely unhelpful.

In the Rubin context this is a very natural thing for users to attempt, because we have many, many columns that are, in effect, Boolean flags even though they are not actually booleans in the database. We've had a user support request on this already just during the Data Previews era.

I'm hoping this is perhaps an easy thing to fix?

[pdowler]

It is certainly true that the current error message is not useful. That comes from the underlying jsqlparser code so I don't think I can fix this without modifying jsqlparser or getting around to generating a better ADQL parser from BNF or PEG. Basically, jsqlparser gets invoked first and then all our code does stuff to the resulting parse tree.

There are other kinds of syntax that result in a message like the above... I think we could identify the above by looking for "syntax error: encountered" and trying to output something more helpful. Even a list of hints about typical causes would be better than what we have. That should not be too hard.