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
135
136
137
138
139
140
141
142
143
|
# name: test/sql/ordinality/ordinality_constant.test
# description: Tests with ordinality for constant TVFs and non-correlated InOut-functions
# group: [ordinality]
statement ok
PRAGMA enable_verification;
query II
SELECT * FROM range(1) WITH ORDINALITY AS _(ordinality);
----
0 1
query II
SELECT o,range FROM range(1) WITH ORDINALITY AS _(range,o);
----
1 0
query II
SELECT col = '{DATA_DIR}/csv/customer.csv' OR col = '{DATA_DIR}\csv\customer.csv', o FROM glob('{DATA_DIR}/csv/customer.csv') with ordinality AS _(col,o);
----
true 1
query II
SELECT my_range, my_ordinality FROM range(3) WITH ORDINALITY AS _(my_range, my_ordinality) ORDER BY my_range,my_ordinality;
----
0 1
1 2
2 3
query II
SELECT my_series, my_ordinality FROM generate_series(0,3,1) WITH ORDINALITY AS _(my_series,my_ordinality) ORDER BY my_series, my_ordinality;
----
0 1
1 2
2 3
3 4
query II
SELECT * FROM repeat('x', 5) WITH ORDINALITY ORDER BY ordinality;
----
x 1
x 2
x 3
x 4
x 5
query II
SELECT * FROM repeat('x', 9) WITH ORDINALITY WHERE ordinality > 4 ORDER BY ordinality;
----
x 5
x 6
x 7
x 8
x 9
statement ok
CREATE OR REPLACE VIEW view_ordinality AS
SELECT r AS range, o AS my_ordinality
FROM range((select 10)) WITH ORDINALITY AS _(r, o)
WHERE my_ordinality > 4
ORDER BY range, my_ordinality;
query II
SELECT range, my_ordinality FROM view_ordinality ORDER BY range, my_ordinality;
----
4 5
5 6
6 7
7 8
8 9
9 10
query I
WITH RECURSIVE ordinalityCTE AS (
SELECT 1 AS ordinality
UNION
SELECT oCTE.ordinality + r.ordinality AS ordinality
FROM
ordinalityCTE AS oCTE,
LATERAL range(1) WITH ORDINALITY AS r(range,ordinality)
WHERE oCTE.ordinality < 5
) SELECT ordinality FROM ordinalityCTE ORDER BY ordinality;
----
1
2
3
4
5
query I
WITH RECURSIVE ordinalityCTE AS (
SELECT 1 AS ordinality
UNION ALL
SELECT oCTE.ordinality + r.ordinality AS ordinality
FROM
ordinalityCTE AS oCTE,
LATERAL range(1) WITH ORDINALITY AS r(range,ordinality)
WHERE oCTE.ordinality < 5
) SELECT ordinality FROM ordinalityCTE ORDER BY ordinality;
----
1
2
3
4
5
query II
SELECT * FROM unnest([41,42,43]) WITH ORDINALITY;
----
41 1
42 2
43 3
query II nosort read_csv_result
SELECT column00, ordinality FROM read_csv('{DATA_DIR}/csv/customer.csv') WITH ORDINALITY ORDER BY ordinality,column00;
query II nosort read_csv_result
SELECT column00, row_number() OVER () AS ordinality FROM read_csv('{DATA_DIR}/csv/customer.csv') ORDER BY ordinality,column00;
require parquet
query II nosort parquet_result
SELECT d,ordinality FROM read_parquet('{DATA_DIR}/parquet-testing/date.parquet') WITH ORDINALITY ORDER BY ordinality,d;
query III nosort parquet_result
SELECT d, row_number() OVER () AS ordinality FROM read_parquet('{DATA_DIR}/parquet-testing/date.parquet') ORDER BY ordinality,d;
|
