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
88
89
90
91
92
|
# name: test/sql/catalog/function/test_cte_macro.test
# description: Test Macro with CTE
# group: [function]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE integers (a INT)
statement ok
INSERT INTO integers VALUES (1)
statement ok
CREATE MACRO parameterized_cte(a) AS (WITH cte AS (SELECT a AS answer) SELECT answer FROM cte)
query T
SELECT parameterized_cte(42)
----
42
statement ok
CREATE MACRO in_with_cte(i) AS i IN (WITH cte AS (SELECT a AS answer FROM integers) SELECT answer FROM cte)
query T
SELECT in_with_cte(1)
----
1
query T
SELECT in_with_cte(2)
----
0
statement ok
CREATE MACRO plus42(a) AS (WITH cte AS (SELECT 42 AS answer) SELECT answer + a FROM cte)
query T
SELECT plus42(42)
----
84
query T
SELECT plus42(a) FROM integers
----
43
# macro parameters should be contained within the function call (so 42 + 3 + 1)
query T
SELECT plus42(3) + a FROM integers
----
46
# we should not be able to query the CTE from outside the function call
statement error
SELECT plus42(42) + answer FROM cte;
----
statement ok
CREATE MACRO plus1(a) AS (WITH tbl AS (SELECT 1 AS one) SELECT one + a FROM tbl)
query T
SELECT plus1(3)
----
4
query T
SELECT plus42(a) + plus1(a) FROM integers;
----
45
statement ok
CREATE MACRO deep_cte(param) AS (
WITH cte1 AS (
WITH cte2 AS (
WITH cte3 AS (
WITH cte4 AS (
SELECT param AS d
)
SELECT d AS c FROM cte4
)
SELECT c AS b FROM cte3
)
SELECT b AS a FROM cte2
)
SELECT a FROM cte1
)
query T
SELECT deep_cte(42)
----
42
|
