TAP, ADQL and field names inconsistencies between providers.

[Markus Demleitner]

Hi Grégory,

On Mon, Dec 07, 2020 at 05:28:14PM +0100, Grégory Mantelet wrote:
> As editor of the ADQL standard (since few time), I do not think we should
> force one behavior more than another. After all, this flexibility also
> exists in SQL which is the backend language of all the currently existing
> TAP implementations I know. But we could maybe add a recommendation about
> escaping and qualifying identifiers. Let's say something like:
> 
>     "ADQL implementations SHOULD not force identifiers to be escaped and/or
> qualified unless necessary.".
> 
> @other ADQL authors: suggestions/comments?

First off, while the SQL rules for case folding are insane, we
shouldn't overwrite them in ADQL, except perhaps for the one thing:
SQL92 (haven't checked later standards) wants to normalise to
uppercase (meaning colname and "COLNAME" is the same), whereas
postgres normalises to lowercase (i.e., colname and "colname" is the
same).  As always when case folding comes into play, things become
messy.

With that, implementations that accepts "colname" (or, in strict SQL,
"COLNAME") but not colname simply are in violation of SQL and need
to be fixed.  I have a hunch there's mysql behind this -- it's the
responsibility of implementors making such a choice to fix mysql's
quirks in the translation layer.

So, I think we shouldn't have the prose a proposed; SQL is clear that
they MUST NOT.

What we might sneak into ADQL is something like: "a word of advice:
avoid mixed-case names in ADQL databases; they become trouble as soon
as people start gratuitously adding quotes to identifiers, which at
least a certain demography is likely to do".

       -- Markus