Column name specified via AS can't be used in WHERE

[blais]

Original report by Martin Michlmayr (Bitbucket: tbm13, GitHub: tbm).

---

I specify a column name via "AS foo" but then "WHERE foo" says that "foo" is an invalid column. It would be nice if it would recognise the custom column names I make via "AS".

beancount> SELECT ANY_META('entity') as entity WHERE ANY_META('entity') ~ 'Michlmayr';
 entity  
---------
Michlmayr
Michlmayr
Michlmayr
Michlmayr
beancount> SELECT ANY_META('entity') as entity WHERE entity ~ 'Michlmayr';
ERROR: Invalid column name 'entity' in WHERE clause context.
beancount> 

[blais]

Original comment by Martin Michlmayr (Bitbucket: tbm13, GitHub: tbm).

---

It works in GROUP BY, btw.

[dnicolodi]

I implemented support for referring to columns names set with AS in the WHERE and HAVING clauses in #29. However, I am not sure this is a desirable feature.

Using the AS column names in WHERE and HAVING is not allowed by the SQL standard and is not supported by PostgreSQL (which is my SQL reference implementation) see "SELECT list" in https://www.postgresql.org/docs/current/sql-select.html SQLite supports this use, but the SQLite author wishes he could remove support for this https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg94070.html

I see the convenience of being able to reference column aliases in the WHERE and HAVING clause but I wonder if I am missing some good reason not to support this that can come biting us down the road.

[blais]

Good question. I have no idea either. We should all the SQLite authors. I'm curious

On Sat, Apr 16, 2022, 05:54 Daniele Nicolodi @.***> wrote:

I implemented support for referring to columns names set with AS in the WHERE and HAVING clauses in #29 https://github.com/beancount/beanquery/pull/29. However, I am not sure this is a desirable feature.

Using the AS column names in WHERE and HAVING is not allowed by the SQL standard and is not supported by PostgreSQL (which is my SQL reference implementation) see "SELECT list" in https://www.postgresql.org/docs/current/sql-select.html SQLite supports this use, but the SQLite author wishes he could remove support for this @.***/msg94070.html

I see the convenience of being able to reference column aliases in the WHERE and HAVING clause but I wonder if I am missing some good reason not to support this that can come biting us down the road.

— Reply to this email directly, view it on GitHub https://github.com/beancount/beanquery/issues/46#issuecomment-1100624770, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACSBE4ZNQVJRHGVQE772WTVFKE4TANCNFSM5SMN74YA . You are receiving this because you authored the thread.Message ID: @.***>

[tbm]

I didn't realize this was not a standard SQL feature. I'm ok with this request being closed since this is not a standard SQL feature.

[blais]

I also wonder why SQL does not support this. It always surprises me when I write SQL (and I do). Id love to know the rationale

On Wed, Nov 30, 2022, 21:20 Martin Michlmayr @.***> wrote:

I didn't realize this was not a standard SQL feature. I'm ok with this request being closed since this is not a standard SQL feature.

— Reply to this email directly, view it on GitHub https://github.com/beancount/beanquery/issues/46#issuecomment-1333065023, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACSBE2PTHAKAZRG43CK7Z3WLADPVANCNFSM5SMN74YA . You are receiving this because you authored the thread.Message ID: @.***>