ADQL: GROUP BY <value_expression> ?

[Simon Murphy]

I second this request. Our TAP users (including myself just yesterday) were having similar difficulties with the ORDER BY clause. I thought it might be a bug in our parser until I checked the ADQL spec and discovered only column names or aliases are permitted there too.

Allowing unaliased expressions to follow GROUP BY and ORDER BY would be a useful improvement to ADQL, IMHO.

Cheers,

Simon Murphy

VC Postdoctoral Fellow
University of New South Wales, Canberra
(currently moonlighting on SkyMapper at ANU)



On 4 May 2016, at 4:59 am, Stelios Voutsinas <stv at roe.ac.uk> wrote:

> 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