vollt icon indicating copy to clipboard operation
vollt copied to clipboard

Published 20 hours ago • verified publisher icon gmantele

Broader support of SQL92.

Open yazun opened this issue 7 years ago • 3 comments

Would it be possible to add basic conditional support like CASE WHEN and some basic SQL92 functions like COALESCE, NULLIF, GREATEST etc?

This would greatly simplify certain queries in the Gaia Archive.

yazun avatar Jun 27 '18 12:06 yazun

In theory, it would be easily possible, especially for COALESCE, NULLIF and GREATEST which are just functions.

But ADQL reserved several words like these. So, except if the ADQL grammar now allows such functions and CASE WHEN, I won't do it in the ADQL Library. The goal of this library is to follow the syntax and grammar of the ADQL standard. Allowing such features in the library would make the language parsed and executed by my library non compatible with ADQL.

Since a new ADQL version is in preparation, I will forward this idea to the competent persons. I also think that these functions could not hurt. Besides, they seems to be supported by most of the databases (or otherwise it would be easy to write the SQL query doing the same thing). I will inform you through this GitHub Issue if I have any answer about the integration of these functions in the ADQL standard.

In the meantime, what you can do, is to define User Defined Functions with a different name in ADQL (and which would be translated into the correct name in SQL). That is fairly easy to do with the library. I can help you with that if you want to.

gmantele avatar Jun 27 '18 15:06 gmantele

Thanks! Question of possibility of UDFs in the Gaia Archive is also a valid one. I will try to inquire if this could be done this way to the Archive people.

yazun avatar Jul 17 '18 11:07 yazun

Also, I naively assumed they reserved SQL tokens so these are mapped directly to SQL, but quick look at the spec shows some ambiguity:

We can extend the list of SQL92 reserved keywords to accommodate those useful for astronomical purposes and/or present in a subset of vendor specific languages only (e.g. TOP). This leads to the following list: 5 • SQL reserved keywords: ABSOLUTE, ACTION, ADD, ALL, ALLOCATE,

. Otherwise it would not make much sense to reserve them (except it would make the parser easier to maintain).

yazun avatar Jul 17 '18 11:07 yazun