Use of aliases in WHERE clause (looking for a Firebird minimal version supporting this)

[eric moutie]

Hello,

I've got the following Firebird version: libfbclient.so.3.0.5 (Version = LI-V6.3.5.33220 Firebird 3.0).

The following request works:

"SELECT Res.EXECUTER_ACTION_RESULT_ID, Res.STATUS AS RES_STATUS

FROM EXECUTER_ACTIONS_RESULTS AS Res
WHERE Res.STATUS LIKE  '%C%'
ORDER BY 1 asc"

However, if I use aliases, as below, it doesn't work:

"SELECT Res.EXECUTER_ACTION_RESULT_ID, Res.STATUS AS RES_STATUS

FROM EXECUTER_ACTIONS_RESULTS AS Res
WHERE RES_STATUS LIKE  '%C%'
ORDER BY 1 asc"

Is this invalid for all Firebird versions, or is it possible to upgrade towards a version (in major version 3, if possible) that supports this feature, please?

Regards.

[eric moutie]

For information, I've found this thread (in archives), that says it's possible (without specifying the version :-\ )...:
https://www.mail-archive.com/firebird...@yahoogroups.com/msg07413.html .

... and I've searched in releases notes (Firebird's changelog)  where I've found that aliases are possible in the RETURNING clause (UPDATE ... RETURNING fix named CORE-4774) - but not talking about aliases in the WHERE clause, here...: https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html .

[Mark Rotteveel]

This is not possible in any Firebird version, and will likely never be
possible. Such behaviour is not defined in the SQL standard, and would
also not make sense because the projection of the select happens later
than the WHERE clause, so conceptually the alias does not exist at the
time the WHERE clause is evaluated.

Mark

[Mark Rotteveel]

On 2021-11-01 19:22, 'eric moutie' via firebird-support wrote:
> For information, I've found this thread (in archives), that says it's
> possible (without specifying the version :-\ )...:
> https://www.mail-archive.com/firebird...@yahoogroups.com/msg07413.html
> .

That link does not say it is possible at all, it offers a workaround by
using a Common Table Expresion (CTE) for the projection, and then use
that CTE in another select so you can use the column name (alias)
defined in the CTE. The same can also be achieved with an inline derived
table.


So, solution with CTE:

with res as (
SELECT EXECUTER_ACTION_RESULT_ID, STATUS AS RES_STATUS
FROM EXECUTER_ACTIONS_RESULTS
)
select *
from res
where RES_STATUS like '%C%'
order by 1 asc

or with an inline derived table:

select *
from (
SELECT EXECUTER_ACTION_RESULT_ID, STATUS AS RES_STATUS
FROM EXECUTER_ACTIONS_RESULTS
) res
where RES_STATUS like '%C%'
order by 1 asc

> ... and I've searched in releases notes (Firebird's changelog) where
> I've found that aliases are possible in the RETURNING clause (UPDATE
> ... RETURNING fix named CORE-4774) - but not talking about aliases in
> the WHERE clause, here...:
> https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html

That is not related to column alias, nor usage in a WHERE clause. It was
a bug where explicitly referencing the table was required where it
shouldn't be necessary because the column reference was not ambiguous.
That said, you can uses aliases in the RETURNING clause, but that just
defines the names of the output columns of RETURNING (similar to what
happens in a SELECT), and is also not related to what you're trying to
do.

Mark

[Tomasz Tyrakowski]

On 01.11.2021 at 20:48, Mark Rotteveel wrote:
> [...]

> or with an inline derived table:
>
> select *
> from (
>    SELECT EXECUTER_ACTION_RESULT_ID, STATUS AS RES_STATUS
>    FROM EXECUTER_ACTIONS_RESULTS
> ) res
> where RES_STATUS like '%C%'
> order by 1 asc

Mark, do you happen to know if this "order by 1" in your example would
be able to take advantage of indices created on the inner table
(EXECUTER_ACTIONS_RESULTS in this case) in all circumstances? I think
I've seen somewhere nested selects getting criticized because of low
efficiency (no indices being used in some cases), but I can't remember
when and where I've seen it (so maybe it's just my memory playing tricks
on me).
I've just made a couple of quick tests and it seems the plan uses
indices on the inner table in the ORDER part (even if I aliased the
column in the inner select and then ordered by the alias in the outer
one), but if there are any significant differences or caveats that you
know of, could you please share them?

thanks
Tomasz

[eric moutie]

@Mark

>

> So, solution with CTE:
> WITH res AS (...\...)

>    [snip] 

>

>

> Or with an inline derived table:

> select *
> from (
>        SELECT EXECUTER_ACTION_RESULT_ID, STATUS AS RES_STATUS
>        FROM EXECUTER_ACTIONS_RESULTS
> ) res
> where RES_STATUS like '%C%'
>

The latest solution is the easiest to write - as far as I am concerned - for what I'm doing (having said that, I've also learned about CTE). Thank you very much: I'm not going to test a a useless v4 upgrade just to check an hypothetical aliased WHERE clause syntax.

>

> That said, you can uses aliases in the RETURNING clause,

>

Okay, that's good to know too.

ps: @Tomas: just for your information, I confirm you that my "ORDER BY" is useless in my example (I've got a visual sorting of the columns \ fields in the graphical interface I'm using, which adds it automatically according to the clicked column: I've embedded this automatic "ORDER BY" clause in my copy-paste).

[Mark Rotteveel]

I don't know all the ins and outs of the optimizer, but as far as I'm
aware, in this situation the indices should be available just fine.

Mark