ADQL using reserved words in tables

[Markus Demleitner]

Dear DAL,

On Thu, Nov 14, 2019 at 11:52:57AM +0100, Gregory MANTELET wrote:
> > SELECT TOP 10 * from "public".hipparcos_newreduction
> > SELECT TOP 10 * from public.hipparcos_newreduction
> > SELECT TOP 10 * from hipparcos_newreduction
> > 
> > Only the first one failed in TOPCAT (error message being “1 unresolved
> > identifiers: hipparcos_newreduction”), whereas in the rest the same data
> > were returned.
> > 
> > Maybe omitting `public` might work... but it is strange.
> 
> 
> Indeed, it should work in this particular case.
> In ADQL/TAP, it is not always required to prefix with the schema name. You
> must absolutely do it only in case of ambiguous table name (e.g. tables
> `public.stuff` and `another.stuff`)....am I wrong?

ADQL as such doesn't say anything about the resolution of table
names.  And I claim that's a good thing.

In postgres, for instance, table names are searched along a search
path a bit like commands in a shell; that path is configured in the
search_path SET variable, which happens to default to "$user, public".
And that's why in postgres, the public schema is searched on most
installations (namely, those that don't touch search_path).

All these technicalities are things we wanted to dodge when writing
the TAP and VODataService specs, and I believe with versions 1.1 and
1.2, respectively, we're finally doing it, too, by making it clear
that table names are opaque and need to be provided ready-to-use.

> As far as I understood, `TAP_SCHEMA.tables.table_name` should contain the
> recommended way to reference a table. I tend to think that the "recommended
> way" should always be the simplest as possible (for non `TAP_SCHEMA`
> tables)...which means no schema prefix if not needed.

Well, you might still get away with some variant specification, but
the simple rule is: If you use what's in TAP_SCHEMA.tables.table_name
or in VODataService table/name, the service must behave.  In any
other case, all bets are off.

Fortunately, TOPCAT makes using that information simple: If you're
looking at the table metadata, you can hit the little "TBL" icon just
above the query edit area, and it'll fill in the provider-specified
form of the table name.

        -- Markus