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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
# name: test/sql/binder/test_having_alias.test
# description: Test that aliases can be used in the HAVING clause
# group: [binder]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE integers AS SELECT * FROM range(5) tbl(i);
# use an alias to an aggregate in the having clause
query II
SELECT i, COUNT(*) AS k FROM integers GROUP BY i HAVING k=1 ORDER BY i;
----
0 1
1 1
2 1
3 1
4 1
# alias cannot be qualified
statement error
SELECT i, COUNT(*) AS k FROM integers GROUP BY i HAVING integers.k=1 ORDER BY i;
----
Binder Error: column k must appear in the GROUP BY clause
# column name wins over the alias
query II
SELECT 1 AS i, COUNT(*) FROM integers GROUP BY i HAVING i=2;
----
1 1
# as if qualified
query II
SELECT i AS j, COUNT(*) AS i FROM integers GROUP BY j HAVING integers.i=1 ORDER BY i;
----
1 1
# or we use the group by alias
query II
SELECT i AS j, COUNT(*) AS i FROM integers GROUP BY j HAVING j=1 ORDER BY i;
----
1 1
query I
SELECT COUNT(i) AS j FROM integers HAVING j=5;
----
5
query I
SELECT COUNT(i) AS i FROM integers HAVING i=5;
----
5
query I
SELECT COUNT(i) AS i FROM integers GROUP BY i HAVING i=5;
----
query I
SELECT COUNT(i) AS i FROM integers HAVING i=5 ORDER BY i;
----
5
# use the same alias multiple times
query I
SELECT COUNT(i) AS j FROM integers HAVING j=j;
----
5
# multiple alias in HAVING to expression with side-effects
query I
SELECT COUNT(*) FROM (SELECT i, SUM(RANDOM()) AS k FROM integers GROUP BY i HAVING k=k) tbl(i, k);
----
5
# if this is qualified we get an error
statement error
SELECT COUNT(i) AS i FROM integers HAVING integers.i=5 ORDER BY i;
----
Binder Error: column i must appear in the GROUP BY clause
# recursive alias without aggregate
statement error
SELECT i + i AS i FROM integers HAVING i=5 ORDER BY i;
----
Binder Error: column i must appear in the GROUP BY clause
|
