1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
# name: test/sql/binder/test_select_clause_alias.test
# description: Test referencing an alias that exists earlier on in the SELECT clause
# group: [binder]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE integers(i INTEGER);
statement ok
INSERT INTO integers VALUES (1), (2), (3);
# we can refer to aliases that occur earlier-on in the SELECT clause
query II
SELECT i + 1 AS a, a + 1 AS b FROM integers ORDER BY b
----
2 3
3 4
4 5
# we can also do this in a composite manner
query IIII
SELECT i + 1 AS a, a + a AS b, b + b AS c, c + c AS d FROM integers ORDER BY b
----
2 4 8 16
3 6 12 24
4 8 16 32
# the original columns take priority over aliases
query II
SELECT i + 1 AS i, i + 1 AS b FROM integers ORDER BY b
----
2 2
3 3
4 4
# we cannot refer to aliases that are defined LATER ON in the select clause
statement error
SELECT a + 1 AS b, i + 1 AS a FROM integers
----
# expressions with side effects are not supported yet
statement error
SELECT RANDOM() AS a, a + 1 AS b FROM integers
----
This is not yet supported
statement ok
create table orders as
select
cast(random()*100 as integer) + 1 as customer_id,
date '2020-01-01' + interval (cast(random()*365*10 as integer)) days as order_date,
cast(random()*1000 as integer) as order_amount,
from range(0, 1000)
order by order_date;
# expressions with subqueries are not supported yet
statement error
select
(select distinct date_trunc('month', order_date) from orders) as month,
(select sum(order_amount) from orders where date_trunc('month', order_date) = month) as revenue;
----
This is not yet supported
|
