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
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
# name: test/sql/table_function/range_function_lateral.test
# description: Test range functions with lateral functions
# group: [table_function]
statement ok
PRAGMA enable_verification
query I
SELECT * FROM range(1, NULL, 1);
----
query II
SELECT * FROM (SELECT NULL a), range(a);
----
query II
SELECT * FROM (SELECT NULL a), range(timestamp '2010-01-01', a, null);
----
query II
SELECT * FROM range(3) t(i), range(i) t2(j) ORDER BY i, j;
----
1 0
2 0
2 1
query III
SELECT * FROM range(4) t(i), range(i) t2(j), range(j) t3(k) ORDER BY i, j, k;
----
2 1 0
3 1 0
3 2 0
3 2 1
query III
SELECT * FROM generate_series(0,2) t(i), generate_series(0,i) t2(j), generate_series(0,j) t3(k) ORDER BY i, j, k;
----
0 0 0
1 0 0
1 1 0
1 1 1
2 0 0
2 1 0
2 1 1
2 2 0
2 2 1
2 2 2
query IIII
SELECT i, j, l, str FROM (SELECT ARRAY['null'], NULL, 'null' UNION ALL SELECT ARRAY['five'], 5, 'five' UNION ALL SELECT ARRAY['two'], 2, 'two') t(l, i, str), generate_series(0,i-1) t2(j) order by i, j
----
2 0 [two] two
2 1 [two] two
5 0 [five] five
5 1 [five] five
5 2 [five] five
5 3 [five] five
5 4 [five] five
query II
SELECT * FROM (SELECT 42 WHERE 42>84) t(i), range(i) t2(j)
----
statement error
SELECT * FROM (SELECT '5'::VARCHAR) t(str), range(str) t2(j)
----
No function matches the given name and argument types
statement ok
PREPARE v1 AS SELECT * FROM range(?);
query I
EXECUTE v1(5)
----
0
1
2
3
4
query IIII
SELECT * FROM (SELECT 3, 1, -1 UNION ALL SELECT 1, 3, 2) t(s, e, increment), range(s, e, increment) t2(j) ORDER BY s, j
----
1 3 2 1
3 1 -1 2
3 1 -1 3
query IIII
SELECT * FROM (SELECT DATE '2000-01-01', DATE '2000-10-1', INTERVAL '3' MONTHS) t(s, e, increment), range(s, e, increment) t2(j) ORDER BY s, j
----
2000-01-01 2000-10-01 3 months 2000-01-01 00:00:00
2000-01-01 2000-10-01 3 months 2000-04-01 00:00:00
2000-01-01 2000-10-01 3 months 2000-07-01 00:00:00
query IIII
SELECT * FROM (SELECT DATE '2000-01-01', DATE '2000-10-1', INTERVAL '3' MONTHS) t(s, e, increment), generate_series(s, e, increment) t2(j) ORDER BY s, j
----
2000-01-01 2000-10-01 3 months 2000-01-01 00:00:00
2000-01-01 2000-10-01 3 months 2000-04-01 00:00:00
2000-01-01 2000-10-01 3 months 2000-07-01 00:00:00
2000-01-01 2000-10-01 3 months 2000-10-01 00:00:00
query IIII
SELECT * FROM (SELECT DATE '2000-01-01', DATE '2000-10-1', NULL) t(s, e, increment), generate_series(s, e, increment) t2(j) ORDER BY s, j
----
# many rows
query I
select count(*) from (values (1), (10), (100), (1000), (10000)) t(a), range(a);
----
11111
require icu
statement ok
SET TimeZone='UTC'
query IIII
SELECT * FROM (SELECT TIMESTAMPTZ '2000-01-01', TIMESTAMPTZ '2000-10-1', INTERVAL '3' MONTHS) t(s, e, increment), range(s, e, increment) t2(j) ORDER BY s, j
----
2000-01-01 00:00:00+00 2000-10-01 00:00:00+00 3 months 2000-01-01 00:00:00+00
2000-01-01 00:00:00+00 2000-10-01 00:00:00+00 3 months 2000-04-01 00:00:00+00
2000-01-01 00:00:00+00 2000-10-01 00:00:00+00 3 months 2000-07-01 00:00:00+00
query IIII
SELECT * FROM (SELECT TIMESTAMPTZ '2000-01-01', TIMESTAMPTZ '2000-10-1', NULL) t(s, e, increment), range(s, e, increment) t2(j) ORDER BY s, j
----
query IIII
SELECT * FROM (SELECT TIMESTAMPTZ '2000-01-01', TIMESTAMPTZ '2000-10-1', NULL UNION ALL SELECT TIMESTAMPTZ '2000-10-01', TIMESTAMPTZ '2000-01-1', INTERVAL '-3 months') t(s, e, increment), range(s, e, increment) t2(j) ORDER BY s, j
----
2000-10-01 00:00:00+00 2000-01-01 00:00:00+00 -3 months 2000-04-01 00:00:00+00
2000-10-01 00:00:00+00 2000-01-01 00:00:00+00 -3 months 2000-07-01 00:00:00+00
2000-10-01 00:00:00+00 2000-01-01 00:00:00+00 -3 months 2000-10-01 00:00:00+00
|
