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?