Snags for the unwary

A simple query with PostgreSQL :

SELECT "source_id" , "ra","dec", ("ra" - "ra2000"),("dec" - "dec2000") 
FROM "gdr2"."gaia_source" 
LIMIT 1000

returns with

Error column "?column?" specified more than once

Reason: The database wants unique column names for a return table. The unnamed differences columns are named as “?column?”, and this occurs twice. So the database refuses to process the query.

Reformulate the query by providing names for the differences

SELECT "source_id" , "ra","dec", ("ra" - "ra2000") as ra_diff,("dec" - "dec2000") as dec_diff 
FROM "gdr2"."gaia_source" 
LIMIT 1000

and it runs ok.


Gaia DR2 has columns with “NaN” (not a number) values. ‘NaN’ is considered bigger than any other number.
A query like

SELECT * FROM "gdr2"."vari_long_period_variable" 
WHERE "abs_mag_bol" < NaN 
LIMIT 1000

returns :

Column NaN not found

Enclose the NaN with quotes and it works:

SELECT * FROM "gdr2"."vari_long_period_variable" 
WHERE "abs_mag_bol" < 'NaN' 
LIMIT 1000

ADQL problem with ORDER BY:
(postgresql does fine)

select t1.source_id,t1.ra, t1.dec, t2.mean_mag_bp 
FROM gaiadr2.gaia_source as t1, gaiadr2.vari_time_series_statistics as t2 
WHERE t1.source_id = t2.source_id 
ORDER BY t1.source_id 

returns :

There has been an error while translating your query.

Provide an alias and the ADQL statement is valid:

select t1.source_id as gs_id ,t1.ra, t1.dec, t2.mean_mag_bp 
FROM gaiadr2.gaia_source as t1, gaiadr2.vari_time_series_statistics as t2 
WHERE t1.source_id = t2.source_id 
ORDER BY gs_id