Extension Function/UDF Aliasing in WHERE clause - is this possible?


I’ve got an Extension Function/UDF which I’m calling in my query, as follows:

SELECT val, x_fun(10, val) AS xval FROM my_data WHERE xval < 12 ORDER BY xval;

this fails with the following error:

Exception: Exception occurred: org.apache.calcite.runtime.CalciteContextException: From line 3, column 7 to line 3, column 16: Column ‘xval’ not found in any table

So the alias, xval, I am creating doesn’t work. However, if I change the query as follows it does work:

SELECT val, x_fun(10, val) AS xval FROM my_data WHERE x_fun(10, val) < 12 ORDER BY xval;

So the xval alias works in the ORDER BY but not the WHERE clause. I’ve googled it and it seems a common challenge with SQL implementations, in that the WHERE clause is processed ahead of the SELECT, so the alias doesn’t exist yet.

Not sure if there’s a way around this. Anyone have any ideas?



There are work arounds for avoiding repeating the amount of typing by using a subquery, but you may be introducing performance issues by adding intermediate result requirements.

As far as actually being able to place an alias in a WHERE statement, this you need to take up with the SQL standard as I believe it disallows this (due to logical query order execution) which Calcite is enforcing here.



Thanks Dwayne, I thought that might be the case.