# Incorrect algorithm for configuring director table keys for dependent tables during catalog ingests

XMLWordPrintable

#### Details

• Type: Bug
• Status: Done
• Resolution: Done
• Fix Version/s: None
• Component/s:
• Labels:
None
• Sprint:
DB_S21_12
• Team:
Data Access and Database
• Urgent?:
No

# The bug

This ticket is related to DM-28433, though it doesn't require the one.

The current implementation of the ingest system is based on a false assumption that the FK for the director table's PK in the dependent tables would have the same names as the one in the director one. The implementation still has the right interface (JSON schema) for specifying correct per-table keys. It's only the implementation that ignores the correct specifications and uses the same key name instead of the ones provided by the ingest workflows. The incorrect keys are passed into CSS and confuse Qserv during the processing of queries joining on the objectId-based associations. Here is an example of a error reported by Qserv:

 SELECT o1.objectId as id1, tm.match_objectId as id2   FROM dc2_object_run22i_dr6_wfd_v2_17.object o1, dc2_object_run22i_dr6_wfd_v2_17.truth_match_04 tm  WHERE scisql_s2PtInCircle(o1.ra, o1.dec, 60.0, -30.0, 0.05)=1  AND scisql_angSep(o1.ra, o1.dec, tm.ra, tm.dec) < 0.01  AND tm.match_objectId != -1  AND o1.objectId = tm.match_objectId; 

 ERROR 4110 (Proxy) at line 1: Query processing error: QI=?: Failed to instantiate query:  AnalysisError:Query involves partitioned table joins that Qserv does not know how to evaluate using  only partition-local data 

Note the different names for the object identifier key names as objectId (the PK) in the director table object and named as match_objectId (the FK) in the dependent table truth_match_04.

The temporary workaround for the above shown problem was to modify the corresponidng CSS entry directly:

 UPDATE qservCssData.kvData  SET kvVal='{"dirColName":"match_objectId","dirDb":"dc2_object_run22i_dr6_wfd_v2_17","dirTable":"object","latColName":"dec","lonColName":"ra","subChunks":"0"}'  WHERE kvKey='/DBS/dc2_object_run22i_dr6_wfd_v2_17/TABLES/truth_match_04/partitioning/.packed.json'; 

## The fix

The code analysis has shown that no database schema change would be required to fix the problem. It's only the definition of the database descriptor class replica::DatabaseInfo and the relevant algorithms that need to be corrected.

#### Activity

Hide
Igor Gaponenko added a comment -
Show
Igor Gaponenko added a comment - PR: https://github.com/lsst/qserv/pull/620
Hide
Andy Salnikov added a comment -

Looks good, few minor comments on PR.

Show
Andy Salnikov added a comment - Looks good, few minor comments on PR.

#### People

Assignee:
Igor Gaponenko
Reporter:
Igor Gaponenko
Reviewers:
Andy Salnikov
Watchers:
Andy Salnikov, Fabrice Jammes, Fritz Mueller, Hsin-Fang Chiang, Igor Gaponenko