在sqlite3 where子句中使用列别名

I know that in oracle/mysql/sqlserver select statement you are not able to reference a column alias in the where clause because it is executed before the select, yet it works in sqlite3:

我知道在oracle / mysql / sqlserver select语句中你无法在where子句中引用列别名,因为它在select之前执行,但它在sqlite3中有效:

sqlite> create table t (c1 text, c2 text);
sqlite> insert into t values ("a1", "a2");
sqlite> insert into t values ("b1", "b2");
sqlite> select c1, c2 from t;
a1|a2
b1|b2
sqlite> select c1, c2 as alias_c2 from t where alias_c2='b2';
b1|b2

Why is this possible in sqlite?

为什么在sqlite中这可能?

2 个解决方案

#1

So, from docs

所以,从文档

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

标准SQL不允许您引用WHERE子句中的列别名。施加此限制是因为当执行WHERE代码时,可能尚未确定列值。

but current SQLite allows it

但是目前的SQLite允许它

SQL standard, column aliases can be referenced in ORDER BY, GROUP BY and HAVING clauses. As an extension, SQLite also allows them in WHERE and JOIN ON clauses, but again, such usage is non-standard (though very convenient at times). Neither the standard nor SQLite implementation allow referencing aliases in the SELECT clause.

SQL标准,列别名可以在ORDER BY,GROUP BY和HAVING子句中引用。作为扩展,SQLite还允许它们在WHERE和JOIN ON子句中,但同样,这种用法是非标准的(虽然有时非常方便)。标准和SQLite实现都不允许在SELECT子句中引用别名。

#2

Using sqlite3 with SQLITE_DEBUG flag enabled:

使用带有SQLITE_DEBUG标志的sqlite3:

sqlite> create table x (x1 integer);
sqlite> insert into x values (1);
sqlite> insert into x values (2);
sqlite> insert into x values (3);
sqlite> insert into x values (4);
sqlite> insert into x values (5);
sqlite> pragma vdbe_listing=1;
VDBE Program Listing:
   0 Expire           0    0    0      00 
   1 Halt             0    0    0      00 
sqlite> select x1*x1 as s from x where s>-10 and s>-9 and s>0 and s>-4 and s>2;
VDBE Program Listing:
   0 Trace            0    0    0      00 
   1 Integer        -10    1    0      00 
   2 Integer         -9    2    0      00 
   3 Integer          0    3    0      00 
   4 Integer         -4    4    0      00 
   5 Integer          2    5    0      00 
   6 Goto             0   26    0      00 
   7 OpenRead         0    3    0 1    00 x
   8 Rewind           0   24    0      00 
   9 Column           0    0    7      00 x.x1
  10 Multiply         7    7    6      00 
  11 Le               1   23    6      6A 
  12 Multiply         7    7    6      00 
  13 Le               2   23    6      6A 
  14 Multiply         7    7    6      00 
  15 Le               3   23    6      6A 
  16 Multiply         7    7    6      00 
  17 Le               4   23    6      6A 
  18 Multiply         7    7    6      00 
  19 Le               5   23    6      6A 
  20 Column           0    0    6      00 x.x1
  21 Multiply         6    6    9      00 
  22 ResultRow        9    1    0      00 
  23 Next             0    9    0      01 
  24 Close            0    0    0      00 
  25 Halt             0    0    0      00 
  26 Transaction      0    0    0      00 
  27 VerifyCookie     0    4    0      00 
  28 TableLock        0    3    0 x    00 
  29 Goto             0    7    0      00 
s   
----
4   
9   
16  
25  
sqlite>

As can be seen from the instruction stack above, the loop over the rows (lines 8-23) repeats the Multiply and Le commands for each expression in the where clause, for each row in the table.

从上面的指令堆栈可以看出,对于表中的每一行,行上的循环(第8-23行)为where子句中的每个表达式重复了Multiply和Le命令。

So to answer my own question, sqlite engine is able to use the column aliases by substituting their definitions from the select at execution time of the where.

因此,为了回答我自己的问题,sqlite引擎能够通过在where的执行时从select中替换它们的定义来使用列别名。

#1