The order in which DMBS execute queries is:
FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
I was surprised to find out that this query works well on SQLite:
SELECT *, duration / 60 AS length_in_hours
FROM movies
WHERE length_in_hours > 10;
Why is this query working, while in some other cases it seems to fail?
OK, So I run EXPLAIN to see what's going on here.
.eqp full
PRAGMA vdbe_trace=true;
EXPLAIN
SELECT substr(name, 1, 1) AS initial
FROM names
WHERE initial = 'D';
The query results are:
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 11 0 0 Start at 11
1 OpenRead 0 7 0 2 0 root=7 iDb=0; names
2 Rewind 0 10 0 0
3 Column 0 1 2 0 r[2]=names.name
4 Function 6 2 1 substr(3) 0 r[1]=func(r[2..4])
5 Ne 5 9 1 80 if r[1]!=r[5] goto 9
6 Column 0 1 7 0 r[7]=names.name
7 Function 6 7 6 substr(3) 0 r[6]=func(r[7..9])
8 ResultRow 6 1 0 0 output=r[6]
9 Next 0 3 0 1
10 Halt 0 0 0 0
11 Transaction 0 0 10 0 1 usesStmtJournal=0
12 Integer 1 3 0 0 r[3]=1
13 Integer 1 4 0 0 r[4]=1
14 String8 0 5 0 D 0 r[5]='D'
15 Integer 1 8 0 0 r[8]=1
16 Integer 1 9 0 0 r[9]=1
17 Goto 0 1 0 0
In addr 0, the Init opcode sends us to addr 11 which open new Transaction.
Right after that SQLite allocate the integer 1 FOUR TIMES (addr 12-13, 15-16). That's where I started to suspect SQLite may artificially duplicate the expression before the AS into the WHERE clause.
In opcodes 3-5, which happen before the SELECT (opcodes 6-8), we can see that SQLite duplicated our substr into the WHERE clause.