Table upload of objectId on RSP problem

[jgizis (John Gizis)]

This might a version or return of a problem mentioned on community some years ago and marked as solved.

I am getting a problem where the (huge!) integers used for objectIds are not read properly if I upload them as a simple .csv file in the Portal.

For example, a file that starts as:

name_1,objectId,coord_ra,coord_dec

1RXS J160929.1-210524B,762266397647596966,242.37620126341753,-21.08320526467467

2MASS J00062250+1300451,798914357151925539,1.5939615213680893,13.012076073925172

2MASS J00065552+0236376,787328184654777539,1.7305295846161663,2.6089368836270914

then used to match to the ForcedSource table in the Portal view by objectId.

Returns bogus matches, apparently because the objectId is not read correctly. I return the user columns by including ut.* in the ADL query. Example from above:

2MASS J00062250+1300451,798914357151925539
is listed as 7.989143571519255E17 in the user table columns (i.e., it’s been converted to float and lost precision) and therefore matches to the incorrect objectId 798914357151925504

Of course, all this fails silently because there are enough objectIds in the ForcedSource database that the new incorrect ones often match a real objectId.

Admittedly, I went to no effort to specify the format to be used, but it does seem like this is a bug and flaw in the user interface.

[Note that I can accomplish this task with joins, the notebook mode, etc.]

[jrob93 (James Robinson)]

Hi John, thanks for flagging this issue. I have encountered a similar issue myself in the notebook aspect where the dtype of one of these long id integers can be changed unintentionally which results in the same problem. I’ve had a quick go at replicating, so I have saved the sample you provided as a csv file and uploaded to the portal and looks like the objectId column is recognised as long type:

I then do a simple query on ForcedSource to match on objectId (please correct me if this is not similar to what you have tried):

SELECT d.objectId,ut.* 
FROM dp2.ForcedSource AS d, TAP_UPLOAD.upload_table AS ut 
WHERE (ut.objectId = d.objectId)

And then for some reason the objectId_2 returned with the results is now a double:

Certainly looks like a bug to me!

[jrob93 (James Robinson)]

Interestingly I tried a different query doing a spatial match on the Object catalogue and the results seemed to preserve the correct type:

SELECT d.objectId, ut.* 
FROM dp2.Object AS d, TAP_UPLOAD.upload_table AS ut 
WHERE CONTAINS(POINT('ICRS', d.coord_ra, d.coord_dec), CIRCLE('ICRS', ut.coord_ra, ut.coord_dec, 0.002777777777777778))=1 

I will flag this for someone with a bit more knowledge about table uploads to investigate further.

[galaxyumi331 (Yumi Choi)]

Hi @jgizis, I am reporting back that the root cause lies in Firefly, which must infer data types when processing plain CSV files. While the RSP team discusses a longer-term solution, you can immediately bypass this issue by uploading a VOTable, an IPAC table, or another familiar format that explicitly defines the data types for each column, eliminating the need for Firefly to guess them. I will mark this as the solution for now, but feel free to unmark it if this workaround doesn’t resolve your issue. Thanks!