DALQueryError on a complex query

Support Rubin Science Platform
#1

Hi team!

I’ve tried running this query agains DP2, but

result = tap.run_sync("""
SELECT COUNT(*) AS objects_with_singletons
FROM (
    SELECT
        nightly_counts.ssObjectId
    FROM (
        SELECT
            s.ssObjectId,
            FLOOR(d.midpointMjdTai - 0.5) AS observing_night,
            COUNT(*) AS observations_that_night
        FROM SSSource AS s
        JOIN DiaSource AS d
          ON d.diaSourceId = s.diaSourceId
        GROUP BY
            s.ssObjectId,
            FLOOR(d.midpointMjdTai - 0.5)
    ) AS nightly_counts
    GROUP BY nightly_counts.ssObjectId
    HAVING
        SUM(nightly_counts.observations_that_night) >= 4
        AND MIN(nightly_counts.observations_that_night) = 1
) AS qualifying_objects
""")

table = result.to_table()
table

but encountered a “DAL Error”:

---------------------------------------------------------------------------
DALQueryError                             Traceback (most recent call last)
Cell In[7], line 1
----> 1 result = tap.run_sync("""
      2 SELECT COUNT(*) AS objects_with_singletons
      3 FROM (
      4     SELECT

File /opt/lsst/software/stack/conda/envs/lsst-scipipe-12.3.0-exact/lib/python3.13/site-packages/pyvo/dal/tap.py:295, in TAPService.run_sync(self, query, language, maxrec, uploads, **keywords)
    266 def run_sync(
    267         self, query, *, language="ADQL", maxrec=None, uploads=None,
    268         **keywords):
    269     """
    270     runs sync query and returns its result
    271 
   (...)    291     TAPResults
    292     """
    293     return self.create_query(
    294         query, language=language, maxrec=maxrec, uploads=uploads,
--> 295         **keywords).execute()

File /opt/lsst/software/stack/conda/envs/lsst-scipipe-12.3.0-exact/lib/python3.13/site-packages/pyvo/dal/tap.py:1207, in TAPQuery.execute(self)
   1193 def execute(self):
   1194     """
   1195     submit the query and return the results as a TAPResults instance
   1196 
   (...)   1205        for errors parsing the VOTable response
   1206     """
-> 1207     result = TAPResults(
   1208         self.execute_votable(),
   1209         url=self.queryurl,
   1210         session=self._session
   1211     )
   1212     result.check_overflow_warning(self._client_set_maxrec)
   1214     return result

File /opt/lsst/software/stack/conda/envs/lsst-scipipe-12.3.0-exact/lib/python3.13/site-packages/pyvo/dal/adhoc.py:115, in AdhocServiceResultsMixin.__init__(self, votable, url, session)
    114 def __init__(self, votable, *, url=None, session=None):
--> 115     super().__init__(votable, url=url, session=session)
    116     self._adhocservices = list(
    117         resource for resource in votable.resources
    118         if resource.type == "meta" and resource.utype == "adhoc:service"
    119     )

File /opt/lsst/software/stack/conda/envs/lsst-scipipe-12.3.0-exact/lib/python3.13/site-packages/pyvo/dal/query.py:340, in DALResults.__init__(self, votable, url, session, client_set_maxrec)
    338 self._status = self._findstatus(votable)
    339 if self._status[0].lower() not in ("ok", "overflow"):
--> 340     raise DALQueryError(self._status[1], self._status[0], url)
    342 if self._status[0].lower() == "overflow":
    343     self._handle_overflow_warning(client_set_maxrec)

DALQueryError: ADQL syntax error: Encountered " "(" "( "" at line 16, column 18.
Was expecting one of:
    "LIMIT" ...
    "ORDER" ...
    "OFFSET" ...
    "HAVING" ...
    "," ...
    ")" ...
    "." ...
    "." ...
    "," ...
    "HAVING" ...
    "ORDER" ...
    "LIMIT" ...
    "OFFSET" ...
    "LIMIT" ...
    "." ...
    "," ...
    "HAVING" ...
    "ORDER" ...
    "LIMIT" ...
    "OFFSET" ...
    "LIMIT" ...
    ")" ...
    ")" ...
    "." ...
    "," ...
    "HAVING" ...
    "ORDER" ...
    "LIMIT" ...
    "OFFSET" ...
    "LIMIT" ...
    "." ...
    "," ...
    "HAVING" ...
    "ORDER" ...
    "LIMIT" ...
    "OFFSET" ...
    "LIMIT" ...
    ")" ...
    ")" ...
    "." ...
    "," ...
    "HAVING" ...
    "ORDER" ...
    "LIMIT" ...
    "OFFSET" ...
    "LIMIT" ...
    "." ...
    "," ...
    "HAVING" ...
    "ORDER" ...
    "LIMIT" ...
    "OFFSET" ...
    "LIMIT" ...
    ")" ...
    ")" ...
    "." ...
    "," ...
    "HAVING" ...
    "ORDER" ...
    "LIMIT" ...
    "OFFSET" ...
    "LIMIT" ...

Wondering if this is a bug or a known parser limitation? If the latter, can someone lend a hand to rewrite this query into a form that DP2 supports?

FWIW, the goal of the query is to count how many objects with 4+ observations have at least one “singleton” (an observation in a single night). These pop up when analyzing observations one can trust for orbit fitting.

#2

Hi Mario – Qserv (running behind TAP here) does not support parenthesized subqueries, unfortunately. :-/

I’ll think on a rewrite for you, and update here with whatever I come up with.

#3

Thanks Fritz – is there a good summary of the ADQL subset that’s supported? Looking for something I can give an LLM to steer it in the right direction when generating queries.

#4

Hi Mario,

The ADQL parser of our TAP service is rejecting that query (albeit in a cryptical way) as it is not ADQL 2.0 compliant in regards to the GROUP BY which only allows column names, not expressions.
So GROUP BY FLOOR(...) is invalid ADQL 2.0 which is what we support currently. (ADQL 2.1 doess allow expressions there, but we do not support 2.1 at this current time).

Agreed that this error message is not very helpful however and this is on our radar for future improvements.