+1 vote
385 views

Hi,

Few potential "bugs" in web based xmatch tool

- DES DR2 is not included in Xmatch menu 

- Xmatch with Decaps_Dr1.object,  it asks to choose RA/DEC but the pull menu does not load any column names

Best, 

asked Jan 27 by My_brain_2_small_4_python | 385 views
q3c_join does not work in queries to DES DR2 as well:
Error: IllegalArgumentException: net.sf.jsqlparser.JSQLParserException JSQLParserException: ParseException: Encountered "( a . ra , a . dec , t . ra , t . dec , 1 ." at line 1, column 238. Was expecting one of: <EOF> "GROUP" ... "LIMIT" ... "ORDER" ... "OFFSET" ... "HAVING" ... ";" ... "." ... "(" "NULL" ... "(" "CASE" ... "....

Query was:
select a.*,
mag_auto_g_dered, mag_auto_r_dered, mag_auto_i_dered,
mag_auto_z_dered, mag_auto_y_dered,
magerr_auto_g, magerr_auto_r, magerr_auto_i,
magerr_auto_z, magerr_auto_y
from test as a, des_dr2.main as t
where q3c_join(a.ra, a.dec,t.ra, t.dec,1./3600.)

(Similar query worked before on DR1!)

1 Answer

+1 vote
Thank you for reaching out.

On des_dr2, we'll add it to the xmatch web tool this week still.

On des_dr1, the main table is .main (not .object), and if I select it, it does load the columns (and also pre-loads 'ra' and 'dec' columns). If you do still see that no columns are loaded, could you please email maybe a screenshot or two to datalab@noao.edu ? Thank you!
answered Jan 27 by robertdemo (2,340 points)
Thank you,
Yes, Des_dr1 works fine. The issue with column names not loading was with decaps_dr1.
Ouch, I misread, sorry! I confirm the issue you report with decaps_dr1.object. We'll fix it ASAP (latest Friday I hope, due to vacations of some team members).
Cheers,
Robert
Thank you, Sir :) Have an awesome end of the week!
q3c_join still does not work though :)
Is `test` a table in your mydb? Then the query should probably be:

select a.*,
mag_auto_g_dered, mag_auto_r_dered, mag_auto_i_dered,
mag_auto_z_dered, mag_auto_y_dered,
magerr_auto_g, magerr_auto_r, magerr_auto_i,
magerr_auto_z, magerr_auto_y
from mydb://test as a, des_dr2.main as t
where q3c_join(a.ra, a.dec,t.ra, t.dec,1./3600.)

Does this help?
Yes, "test" it's in mydb - at least it is shown by datalab command line client. However, I get the following:
datalab query --sql="select * from mydb://test" --out=z.csv
Error: column "objname" does not exist
CONTEXT:  remote SQL command: SELECT id, ra, "dec", z, objname, l, b FROM mydb.test
????
There was an old mydb table definition called 'test' that had an 'objname' column, however it pointed to a physical table which contained only (ra,dec) columns.  I've removed that definition so the next time you access mydb://test you'll get the (ra,dec) values in that table.  If you need additional columns you'll need to recreate the 'test' table under another name.
does that mean that I cannot re-use names for mydb tables? Because I dropped the old "test" table and uploaded a new one before I posted the above error.
Tables with the same name will not be replaced by newer versions automatically, they must be explicitly dropped before you create the new table.  In this case, it appears the foreign table definition was a "leftover" from some failed earlier command:  Your query was "select * from ....." where the '*' expanded to a list of columns included in the foreign table, but not the actual table.  By deleting the foreign table you would now access the real 'test' table again, but that has a different number of columns.
Got it. Now it works from command-line datalab client with SQL query (well, there is another issue, I'll raise a separate question for it), but fails with ADQL (and in web-interface too, as it is ADQL as well):
>datalab query --adql="select * from mydb://test" --out=z1.csv
Error: IllegalArgumentException: Table [ mydb."test" ] is not found in TapSchema

271 questions

273 answers

311 comments

1,836 users

Welcome to Data Lab Help Desk, where you can ask questions and receive answers from other members of the community.