Issue with discovering a TAP service in the RR

[Markus Demleitner]

Dear Registry WG,

Sorry, I hadn't realised the mail is on-list before hectically
passing the mail to my MTA.  Apologies for a mail intended to be
private and a discussion of my transportation predicaments on the
list.

Anyway, since it *was* on-list, let me state the problem for
posteriority now that I've arrived:

On Fri, Oct 22, 2021 at 06:04:54PM +0200, Markus Demleitner wrote:
> On Fri, Oct 22, 2021 at 04:07:07PM +0200, Laurent Michel wrote:
> >     SELECT  TOP 100    rr.capability.*, rr.interface.*
> >     FROM rr.capability
> >     JOIN rr.interface ON rr.interface.ivoid = rr.capability.ivoid
> >     WHERE rr.capability.ivoid like 'ivo://cadc.nrc.ca/youcat'
> >          AND rr.capability.standard_id = 'ivo://ivoa.net/std/tap'

The problem here is that you do not join on cap_index; my natural
join of course did that automatically.

For all I can see, the foreign key from interface into capability is
correctly declared at least on reg.g-vo.org, though, no?

Anyway, *if* you want to explicitly write the join (which, as I said,
is unidiomatic for RegTAP), you would have to write this as

SELECT  TOP 100    rr.capability.*, rr.interface.*
FROM rr.capability
JOIN rr.interface ON (
  rr.interface.ivoid = rr.capability.ivoid
  AND rr.interface.cap_index = rr.capability.cap_index)
WHERE rr.capability.ivoid like 'ivo://cadc.nrc.ca/youcat'
  AND rr.capability.standard_id = 'ivo://ivoa.net/std/tap'

-- or, more concisely, write the join condition as

  USING (ivoid, cap_index)

Oh, and avoid the LIKE operator (here in the ivoid condition) when
you don't actually have a pattern.  One of these days there'll be a %
or _ in your literal and you'll have unexpected results.

I am also on a crusade against SELECT * -- it usually pays all around
to think what data exactly one needs.  Perhaps not so much in this
case, but we have lots of tables in the VO with 100+ columns, and we
ought to educate our users that pulling them all is not a good idea
in general (if only because you'll much earlier hit upload limits
when re-using the table).  

Side note: The cool TOPCAT feature where you can control-click the
columns you want in the metadata browser and then hit the "insert
columns" botton would be on my wishlist for taphandle...

      -- Markus