Usage of Aliases in WHERE clause

Hi all,

I stumbled upon the following expression:

    a_a/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value/magnitude as Temperature_magnitude,
    a_a/data[at0002]/events[at0003]/time/value as Time
from EHR e
contains COMPOSITION a
contains OBSERVATION a_a[openEHR-EHR-OBSERVATION.body_temperature.v1]
    Temperature_magnitude > 37 and

In SQL, it is not possible to have Aliases inside WHERE clauses. I the spec, we don’t say much about aliases at the moment: " As in SQL, AQL supports the use of a name alias for the retrieved data. This is done with the keyword AS , followed by the name which conforms to the syntax rule of AQL variable."

Any strong opinions on this? In EHRbase such a query would fail at the moment

It is(or was) not part of the original AQL spec but it has been introduced by Better, and I think worth supporting. The more we can avoid raw paths, the better.

I don’t tend to use it, precisely because I know it is not supported widely but I would if I could!!

It is not a big deal for us ‘insiders’ but it is just one of these tiny pain points that we should remove if we can.

No strong opinion, Birger :wink:

Just wonder perhaps the aliases could be replaced with paths while translating AQL into proper SQL. Agree with Ian, these names make the query a lot easier to read.

I’d assume that is exactly what happens - the aliases are just resolved into AQL paths by the parser.

I’m trying to find out why this is not supported in SQL. I just want to make sure there is no hassle with Subselects etc. that might be introduced eventually to AQL.

From MySQL doc: " Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined."

From stackexchange: " That’s according to the SQL standard and may not be very intuitive. The (historic) reason behind this is the sequence of events in a SELECT query. WHERE and HAVING are resolved before column aliases are considered, while GROUP BY and ORDER BY happen later, after column aliases have been applied.
Also note that conflicts between input and output names are resolved differently in ORDER BY and GROUP BY - another historic oddity (with a reason behind it, but potentially confusing nonetheless). See:

Best to avoid column aliases that conflict with input column names a priori."

1 Like

I asked exactly the same question of Ian the other day - and I think we should support aliases in the WHERE clause for the very obvious reason that there should never be copies of some complicated programming element, since in maintenance, one can forget to modify both (if correcting an error), and then later on you end up with a query with two different paths, meant to be the same, and no-one can easily figure out which is right.

Also, readability :wink:

Not necessarily a strong opinion but it’d require SELECT clause aliases to be unique to avoid ambiguity.

Other than that, I cannot see any issues at the Aql level. It may give engine implementers a bit of an exercise but no red flags in that department in my head, though not much capability left in it these days…

OK, now I think I got a good example. How would you deal with this alias in the WHERE clause if it was “EMPLOYEE NAME”?


1 Like

While I was reviewing our AQL text about aliases, and the ANTLR4 grammar, I had same question myself: why don’t we supported - in bumped into same (my)SQL explanation. I assumed our AQL engines have same order of processing things (i.e. first FROM, than WHERE, than SELECT) - therefore I did not changed things.

One thing to note, the way grammar is now will allow also aliases to be used inside the WHERE (although is not formally specified).

However, these aliases names should follow AQL variable names syntax: must be unique within an AQL statement, formed of an initial letter followed by any number of alphanumerics and underscores, as long as it does not clash with an reserved words.

In your example I see use of quote, spaces inside the name - both would be illegal. Also the use of ‘+’ is not supported by specs.

Specs are however possible to be changed - as long as we have proposal and unanimity :slight_smile: we can still do it for v1.1.

1 Like

The important point is in the example is that there is not a defined bahaviour once we use the “EMPLOYEE NAME” in the WHERE clause (sorry for being lazy not to edit the example I stole from the internet). However, we might want to enhance the AQL syntax to allow the “+” which then would cause trouble down the road if aliases are allowed at the same time.

1 Like

Would it really cause an issue ? You could just say compound aliases not supported (i’m sure there is proper name!) but would it not be easy enough to support that kind of concatenation in the where clause and actually in the ORDER BY clause too.

This kind of feature in AQL syntax is a nice move in the right direction for human query generation.

IMHO the current way is what the nice human readable syntax is transpiled/compiled into before hitting the query engine.

Said that this kind of feature is a syntactic sugar over AQL. It might be added to the AQL specification or a lightweight tooling specification.

There might be several additions here. Maybe going into a direction of a scriptbased syntax with chained aqls and variable declarations.

Anyway I think I support this change request for AQL :+1: