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
|
# name: test/sql/binder/alias_qualification_having.test
# description: Test alias.name resolution in HAVING clause
# group: [binder]
# basic HAVING filter using a projection alias
query II
SELECT a % 2 AS x, SUM(a) AS s
FROM (VALUES (1),(2),(3),(4)) t(a)
GROUP BY alias.x
HAVING alias.s >= 6
ORDER BY x;
----
0 6
# chaining aliases and using the later alias in HAVING
query III
SELECT a + 1 AS x, alias.x % 2 AS y, SUM(a) AS s
FROM (VALUES (1),(2),(3),(4)) t(a)
GROUP BY a
HAVING alias.s > 2
ORDER BY y;
----
4 0 3
5 1 4
# quoted alias in HAVING
query II
SELECT (a/2)::INT AS "Half", COUNT(*) AS c
FROM (VALUES (1),(2),(3),(4)) t(a)
GROUP BY alias."Half"
HAVING alias.c >= 1
ORDER BY "Half";
----
0 1
1 1
2 2
# Table actually named `alias` should be prioritized over alias references
statement ok
CREATE TABLE alias (g INT);
statement ok
INSERT INTO alias VALUES (1), (1), (2);
query II
SELECT g AS x, COUNT(*) c FROM alias
GROUP BY alias.g
HAVING alias.c = 2
ORDER BY x;
----
1 2
query II
SELECT c1, sum(c2) as c3 FROM VALUES ('a', 1), ('b', 2), ('b', 3), ('c', 4), ('d', null) AS t(c1, c2) group by c1 having c3.add(1) > 2 order by c1;
----
b 5
c 4
query II
FROM VALUES ('CS', 'Bachelor'), ('CS', 'Bachelor'), ('CS', 'PhD'), ('Math', 'Masters') AS t(c1, c2)
SELECT c1, STRING_AGG(c2, ',' order by c2) as c3
GROUP BY c1
HAVING len(c3) > 7
----
CS Bachelor,Bachelor,PhD
query II
FROM VALUES ('CS', 'Bachelor'), ('CS', 'Bachelor'), ('CS', 'PhD'), ('Math', 'Masters') AS t(c1, c2)
SELECT c1, STRING_AGG(c2, ',' order by c2) as c3
GROUP BY c1
HAVING c3.len() > 7
----
CS Bachelor,Bachelor,PhD
|
