[BUG] Large-ish queries with truth-table triple-joins fail with error

[jeffcarlin]

Describe the bug
When working out how to do a triple join between MatchesTruth, TruthSummary, and Object tables (using TAP in the RSP Notebook aspect), I found that a cone search often fails with the error posted below. After some testing, I found that the query succeeds with a smaller cone-search radius, so it seems to be related to the size of the result. This was confirmed by Ralf Kaehlerr (@kaehlerr), who found the same behavior but with a different query.

The query will seem like it is executing, and after 10-15 minutes, it fails with this message:

---

ValueError Traceback (most recent call last)
File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/pyvo/dal/query.py:242, in DALQuery.execute_votable(self, post)
241 try:
--> 242 return votableparse(self.execute_stream(post=post).read)
243 except Exception as e:

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/astropy/utils/decorators.py:546, in deprecated_renamed_argument..decorator..wrapper(*args, **kwargs)
544 warnings.warn(msg, warning_type, stacklevel=2)
--> 546 return function(*args, **kwargs)

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/astropy/io/votable/table.py:160, in parse(source, columns, invalid, verify, chunk_size, table_number, table_id, filename, unit_format, datatype_mapping, _debug_python_based_parser)
156 with iterparser.get_xml_iterator(
157 source,
158 _debug_python_based_parser=_debug_python_based_parser) as iterator:
159 return tree.VOTableFile(
--> 160 config=config, pos=(1, 1)).parse(iterator, config)

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/astropy/io/votable/tree.py:3572, in VOTableFile.parse(self, iterator, config)
3570 config['_current_table_number'] = 0
-> 3572 for start, tag, data, pos in iterator:
3573 if start:

ValueError: 1:0: syntax error

During handling of the above exception, another exception occurred:

DALServiceError Traceback (most recent call last)
File :47, in

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/pyvo/dal/tap.py:248, in TAPService.run_sync(self, query, language, maxrec, uploads, **keywords)
219 def run_sync(
220 self, query, language="ADQL", maxrec=None, uploads=None,
221 **keywords):
222 """
223 runs sync query and returns its result
224
(...)
244 TAPResults
245 """
246 return self.create_query(
247 query, language=language, maxrec=maxrec, uploads=uploads,
--> 248 **keywords).execute()

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/pyvo/dal/tap.py:942, in TAPQuery.execute(self)
928 def execute(self):
929 """
930 submit the query and return the results as a TAPResults instance
931
(...)
940 for errors parsing the VOTable response
941 """
--> 942 return TAPResults(self.execute_votable(), url=self.queryurl, session=self._session)

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/pyvo/dal/query.py:244, in DALQuery.execute_votable(self, post)
242 return votableparse(self.execute_stream(post=post).read)
243 except Exception as e:
--> 244 self.raise_if_error()
245 raise DALFormatError(e, self.queryurl)

File /opt/lsst/software/stack/conda/miniconda3-py38_4.9.2/envs/lsst-scipipe-4.1.0/lib/python3.10/site-packages/pyvo/dal/query.py:253, in DALQuery.raise_if_error(self)
251 if self._ex:
252 e = self._ex
--> 253 raise DALServiceError.from_except(e, self.queryurl)

DALServiceError: 500 Server Error: for url: https://data.lsst.cloud/api/tap/sync/zipen4f6pqalp1ba/run

To Reproduce
Steps to reproduce the behavior:

The notebooks that Ralf and I were running are on the IDF at /scratch/jeffcarlin/qserv_issue/

Expected behavior
Given that the same queries work for smaller result sizes, the failure was unexpected. (Mine works for a cone radius of up to 1.0 deg., and Ralf's works when he uses LIMIT 10000 instead of LIMIT 1000000.)

Additional context
I was using a Large RSP container with the recommended pipelines version (Weekly 2022_40).

[jeffcarlin]

The Qserv team is investigating the issue. Tagging @iagaponenko here as well.