ADQL: GROUP BY <value_expression> ?

[Stelios Voutsinas]

Dear DAL List,

Some of our users at ROE have been running a certain type of query, which they've copied over from published papers and is of the following form:

SELECT
   (ra*6.0) AS alias_A,
   (decl*6.0) AS alias_B,
   COUNT(*) AS num
FROM
   alpha_source
WHERE
   (ra>0)
GROUP BY
    (ra*6.0),
    (decl*6.0)

Basically, the group by clause is an expression on the column, rather than just the column name, or alias.
Currently, the ADQL specification only allows a column name or alias: 

"
SELECT [ ALL | DISTINCT ]
 [ TOP unsigned_integer ]
{ * | { value_expression [ [AS] column_name ] }, … } 

....


[ GROUP BY column_name, … ]
"

While grouping-by on the alias is a simple fix to recommend our users, we've been getting questions from our users on why this query is throwing an error message, I'm guessing because they are copy/pasting the query from a paper or someone's notes.

Having checked with several popular Database Systems (SQLServer, Posgres, MariaDB, MySQL, HSQLDB), this type of query is valid for all. There's a few that I have not checked (Sybase, Oracle..) and I'd be interested to know if it is valid for these as well.

If this is in fact common amongst most or all of the database systems, how would everyone feel about allowing value expressions as GROUP BY clauses?

Cheers,

Stelios Voutsinas
Software Developer
WFAU, Institute for Astronomy
University of Edinburgh