问题描述
我尝试在 teradata 上运行以下查询，结果如预期:
I tried to run below query at teradata and It resulted as expected :
select column1 as c1Alias from my_table where column2 in ( c1Alias , 10 , 20 , 30) ;
但我试图在 HIVE 上运行相同的查询，它抛出如下异常:
But I tried to run same query on HIVE , It throws exception as given below :
FAILED: SemanticException [Error 10004]: Line 1:44 Invalid table alias or column reference 'c1Alias': (possible column names are: .......)
我对它在 HIVE 上失败的原因并不感到惊讶，但对它在 Teradata 上的表现感到惊讶.
I am not surprised why it is failing at HIVE , but surprised how it is working on Teradata.
根据我的理解，子句按 WHERE >> SELECT 的顺序执行.显然，在 SELECT 子句中生成的别名不能用于 WHERE 子句.如果我在这里错了，请纠正我.
As per my understanding, Clauses are executed in order as WHERE >> SELECT. Apparently alias generated at SELECT clause would not be available for use in WHERE clause. Correct me if I am wrong here.
我真的很想知道它在 teradata 中是如何工作的?
I really wanted to know how it is working in teradata ?
推荐答案
你说得对，逻辑上任何 SELECT 都按以下顺序处理:
You're correct, logically any SELECT is processed in following order:
- 来自
- 哪里
- 分组依据
- 拥有
- OLAP 函数
- 合格
- 创建SELECT列列表
- 样本
- 订购者
除了专有的 QUALIFY/SAMPLE 之外，每个 DBMS 都会做同样的事情.
Besides the proprietary QUALIFY/SAMPLE every DBMS will do it exactly the same.
当您向 WHERE 条件添加过滤器时，列列表尚未创建，因此使用别名应该会失败(并且在几乎所有其他 DBMS 中都会失败，只有 Access 允许使用它类似于 Teradata).
When you add a filter to the WHERE-condition the column list is not yet created, thus using an alias should fail (and will fail in almost every other DBMS, afaik only Access allows using it similar to Teradata).
它没有失败，因为 Teradata 比标准 SQL 更旧，这似乎是 Teradata 首先实现的查询语言的遗物.
It's not failing because Teradata is older than Standard SQL and this seems to be an relict of the query language Teradata implemented first.
但它是一个很好的扩展(只是从不对现有列名使用别名以避免混淆优化器和/或最终用户)并且您很快就会习惯它，它避免了大量剪切和粘贴或派生表.
But it's a nice extension (just never alias to an existing column name to avoid confusing the optimizer and/or end user) and you get used to it very fast, it avoids lots cut&paste or Derived Tables.
这篇关于Teradata 的查询是如何工作的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！