[SPARK-32726] Filter by column alias in where clause

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Yuming Wang
• Created: 28/Aug/20 09:19
• Updated: 03/Feb/23 02:49

Description

group by and order by clause support it. but where does not support it:

spark-sql> select id + 1 as new_id from range(5) group by new_id order by new_id;
1
2
3
4
5
spark-sql> select id + 1 as new_id from range(5) where new_id > 2;
Error in query: cannot resolve '`new_id`' given input columns: [id]; line 1 pos 44;
'Project [('id + 1) AS new_id#5]
+- 'Filter ('new_id > 2)
   +- Range (0, 5, step=1, splits=None
spark-sql> select id + 1 as new_id, new_id + 1 as new_new_id from range(5);
Error in query: cannot resolve '`new_id`' given input columns: [id]; line 1 pos 25;
'Project [(id#12L + cast(1 as bigint)) AS new_id#10L, ('new_id + 1) AS new_new_id#11]
+- Range (0, 5, step=1, splits=None)

Teradata support this feature:
https://docs.teradata.com/reader/e79ET77~NzPDz~Ykinj44w/MKSYuTyx2UJWXzdHJf3~sQ

Issue Links

• is related to: CALCITE-5248 Support Extended Column Aliasing