<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">
<br class="">
<div class="">
<div dir="auto" style="background-color: rgb(255, 254, 254); caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0); letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">
<div dir="auto" style="background-color: rgb(255, 254, 254); caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0); letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">
<div><font face="Arial" class=""><span style="font-size: 9px;" class=""><b style="color: rgb(118, 119, 123);" class="">Serge Monkewitz</b> <span style="color: rgb(118, 119, 123);" class="">| Applications Developer | Caltech/IPAC</span></span></font></div>
<div><span style="color: rgb(118, 119, 123); font-size: 9px;" class=""><font face="Arial" class="">NASA/IPAC Infrared Science Archive</font></span></div>
</div>
</div>
</div>
<div><br class="">
<blockquote type="cite" class="">
<div class="">On Dec 8, 2020, at 1:43 AM, Markus Demleitner <<a href="mailto:msdemlei@ari.uni-heidelberg.de" class="">msdemlei@ari.uni-heidelberg.de</a>> wrote:</div>
<br class="Apple-interchange-newline">
<div class="">
<div class="">Hi Grégory,<br class="">
<br class="">
On Mon, Dec 07, 2020 at 05:28:14PM +0100, Grégory Mantelet wrote:<br class="">
<blockquote type="cite" class="">As editor of the ADQL standard (since few time), I do not think we should<br class="">
force one behavior more than another. After all, this flexibility also<br class="">
exists in SQL which is the backend language of all the currently existing<br class="">
TAP implementations I know. But we could maybe add a recommendation about<br class="">
escaping and qualifying identifiers. Let's say something like:<br class="">
<br class="">
"ADQL implementations SHOULD not force identifiers to be escaped and/or<br class="">
qualified unless necessary.".<br class="">
<br class="">
@other ADQL authors: suggestions/comments?<br class="">
</blockquote>
<br class="">
First off, while the SQL rules for case folding are insane, we<br class="">
shouldn't overwrite them in ADQL, except perhaps for the one thing:<br class="">
SQL92 (haven't checked later standards) wants to normalise to<br class="">
uppercase (meaning colname and "COLNAME" is the same), whereas<br class="">
postgres normalises to lowercase (i.e., colname and "colname" is the<br class="">
same). As always when case folding comes into play, things become<br class="">
messy.<br class="">
<br class="">
With that, implementations that accepts "colname" (or, in strict SQL,<br class="">
"COLNAME") but not colname simply are in violation of SQL and need<br class="">
to be fixed. I have a hunch there's mysql behind this -- it's the<br class="">
responsibility of implementors making such a choice to fix mysql's<br class="">
quirks in the translation layer.<br class="">
<br class="">
So, I think we shouldn't have the prose a proposed; SQL is clear that<br class="">
they MUST NOT.<br class="">
<br class="">
What we might sneak into ADQL is something like: "a word of advice:<br class="">
avoid mixed-case names in ADQL databases; they become trouble as soon<br class="">
as people start gratuitously adding quotes to identifiers, which at<br class="">
least a certain demography is likely to do".<br class="">
<br class="">
-- Markus<br class="">
</div>
</div>
</blockquote>
</div>
<br class="">
<div class="">Whether or not colname/"COLNAME"/"colname" can be used to refer to the same column also depends on how the table was created. For example, if someone were to:</div>
<div class=""><br class="">
</div>
<div class="">CREATE TABLE foo (</div>
<div class=""> "colName" integer</div>
<div class="">);</div>
<div class=""><br class="">
</div>
<div class="">then none of the previously mentioned options will work (or at least, not in Postgres 9.5), and the user must use "colName" in queries. If someone instead writes:</div>
<div class=""><br class="">
</div>
<div class="">CREATE TABLE foo ("COLNAME" integer)</div>
<div class=""><br class="">
</div>
<div class="">then only "COLNAME" will work. So, a user can be forced to quote identifiers just based on how a particular table was created, rather than by any oddity of the TAP service implementation.</div>
<div class=""><br class="">
</div>
<div class="">I agree that some advice pointing out pitfalls like this would be helpful.</div>
<div class=""><br class="">
</div>
<div class="">Given all this, I'm not sure how a TAP implementation could compensate for the non-standard fold-to-lowercase behavior in Postgres, unless there is a way to determine that a table column wasn't quoted at table creation time. I imagine there is
some way to figure that out, but I don't know what it is.</div>
<div class=""><br class="">
</div>
<div class="">Serge</div>
<div class=""><br class="">
</div>
</body>
</html>