DALQueryError: Query Error: Qserv request failed: QI=?: Failed to instantiate query: AnalysisError:Query involves partitioned table joins that Qserv does not know how to evaluate using only partition-local data

Support
#1

(Apologies if I’m doing this wrong, first time)

Hi,

I have a query which is (I think) only a small change from one of the tutorials and it isn’t working. I was just trying to add two columns from the dp1.Source table to combine with the dp1.ForcedSourceOnDiaObject table.

Here is the problem query:

query = (
    "SELECT fsodo.coord_ra, fsodo.coord_dec, fsodo.diaObjectId, fsodo.visit, fsodo.detector, fsodo.band, vis.skyRotation, "
    "fsodo.tract, fsodo.patch, fsodo.psfDiffFlux, fsodo.psfDiffFluxErr, fsodo.psfFlux, fsodo.psfFluxErr, vis.expMidptMJD, "
    "src.x, src.y "
    "FROM dp1.ForcedSourceOnDiaObject as fsodo "
    "JOIN dp1.Visit as vis ON vis.visit = fsodo.visit "
    "JOIN dp1.Source as src ON src.visit = fsodo.visit "
    f"WHERE fsodo.diaObjectId = {diaobjectid}"
)

The resulting error is:

DALQueryError                             Traceback (most recent call last)
Cell In[7], line 15
     13 job.wait(phases=["COMPLETED", "ERROR"])
     14 print("Job phase is", job.phase)
---> 15 job.raise_if_error()
     16 assert job.phase == "COMPLETED"
     18 df_exposure = job.fetch_result().to_table()

File /opt/lsst/software/stack/conda/envs/lsst-scipipe-10.1.0/lib/python3.12/site-packages/pyvo/dal/tap.py:1018, in AsyncTAPJob.raise_if_error(self)
   1016     msg = self._job.errorsummary.message.content
   1017 msg = msg or "<No useful error from server>"
-> 1018 raise DALQueryError("Query Error: " + msg, self.url)

DALQueryError: Query Error: Qserv request failed: QI=?: Failed to instantiate query: AnalysisError:Query involves partitioned table joins that Qserv does not know how to evaluate using only partition-local data

Which makes me think that the dp1.Source table is hosted in a different place than the other tables so it can’t be joined? I’m really not sure, I’m very new to this.

For comparison, this query works just fine:

query = (
    "SELECT fsodo.coord_ra, fsodo.coord_dec, fsodo.diaObjectId, fsodo.visit, fsodo.detector, fsodo.band, vis.skyRotation, "
    "fsodo.tract, fsodo.patch, fsodo.psfDiffFlux, fsodo.psfDiffFluxErr, fsodo.psfFlux, fsodo.psfFluxErr, vis.expMidptMJD "
    "FROM dp1.ForcedSourceOnDiaObject as fsodo "
    "JOIN dp1.Visit as vis ON vis.visit = fsodo.visit "
    f"WHERE fsodo.diaObjectId = {diaobjectid}"
)

This isn’t an issue with joining 3 tables, since I can remove the dp1.Visit columns/JOIN and I still get the same error.

I’m sure I’m doing something wrong, but what I know about SQL and these tables based on the schema says that this should have worked.

Best,
Connor

#2

Hi @ConnorStone. Thanks for posting your issue.

While the tables you are trying to join are not hosted in different places, it is not possible to join the ForcedSourceOnDiaObject table with the Source table. You can join it with the DiaSource table, using DiaObjectId, rather than visit. For example:

query = (
    "SELECT fsodo.coord_ra, fsodo.coord_dec, fsodo.diaObjectId, fsodo.visit, fsodo.detector, fsodo.band, vis.skyRotation, "
    "fsodo.tract, fsodo.patch, fsodo.psfDiffFlux, fsodo.psfDiffFluxErr, fsodo.psfFlux, fsodo.psfFluxErr, vis.expMidptMJD, "
    "diasrc.x, diasrc.y "
    "FROM dp1.ForcedSourceOnDiaObject as fsodo "
    "JOIN dp1.Visit as vis ON vis.visit = fsodo.visit "
    "JOIN dp1.DiaSource as diasrc ON diasrc.diaObjectId = fsodo.diaObjectId "\
    f"WHERE fsodo.diaObjectId = {diaobjectid}"
)

The reason I am using diaObjectId rather than visit here is because for each of the visits in the ForcedSourceOnDiaObject table, there are multiple diaSources.

Could you share a bit about what your goal is by joining the source table? I can then see if this query is what you were after.

#3

Hi @gfonsecaalvarez thanks for the clarification. So I am just starting on some supernova stuff. I had identified the SN in dp1.DiaObject I had gotten fluxes for a multi-band light curve from dp1.ForcedSourceOnDiaObject but I needed the pixel position in each visit_image to make a Point2D object to place a circle on the image to show where it is. I eventually got around this by taking the ra, dec and using the visit_image wcs to get the pixel location. I’m still learning the schema, so its good to know that Source and ForcedSourceOnDiaObject don’t really have anything to do with each other and so can’t be joined.

So should I be thinking of Object, Source, and ForcedSourceas one “information silo” while DiaObject, DiaSource, and ForcedSourceOnDiaObjectas a separate unconnected “information silo”? The two collections can’t talk to each other, but within one group it is possible to join tables and do analysis.

#4

Ah okay! If you did not already come across it, the recommended way to convert between sky coordinates and pixels Is in our visit_images notebook tutorial 202_2.

You can easily join between DiaObject , DiaSource , and ForcedSourceOnDiaObject since they all have a DiaObjectId. And you can easily join between Object and ForcedSource since they both have ObjectId. The Source table is independent from those two.

Since my suggestion was not what you needed, but your issue was resolved, I will mark your reply as the solution to the topic.

1 Like
#5

Thanks @gfonsecaalvarez I had gone through that tutorial, but had forgotten about that section. I did visit_image.wcs instead of `visit_image.getWcs(), but it looks like these return the same object.

Thanks for the extra clarifications!