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
|
# name: test/sql/function/list/aggregates/last.test
# description: Test the list_last aggregate function
# group: [aggregates]
# incorrect usage
statement error
SELECT list_last()
----
# different types
# numerics
foreach type <numeric>
statement ok
CREATE TABLE five AS SELECT LIST(i::${type}) AS i FROM range(1, 6, 1) t1(i)
statement ok
INSERT INTO five VALUES (NULL), ([NULL]), ([]), ([1, 2, NULL])
query I
SELECT list_last(i) FROM five
----
5
NULL
NULL
NULL
NULL
statement ok
DROP TABLE five
endloop
# temporal
statement ok
CREATE TABLE five_dates AS
SELECT
LIST(i::integer) AS i,
LIST('2021-08-20'::DATE + i::INTEGER) AS d,
LIST('2021-08-20'::TIMESTAMP + INTERVAL (i) HOUR) AS dt,
LIST('14:59:37'::TIME + INTERVAL (i) MINUTE) AS t,
LIST(INTERVAL (i) SECOND) AS s
FROM range(1, 6, 1) t1(i)
query IIII
SELECT list_last(d), list_last(dt), list_last(t), list_last(s) FROM five_dates
----
2021-08-25 2021-08-20 05:00:00 15:04:37 00:00:05
statement ok
DROP TABLE five_dates
# with time zone
statement ok
CREATE TABLE five_dates_tz AS
SELECT
LIST(('2021-08-20'::TIMESTAMP + INTERVAL (i) HOUR)::TIMESTAMPTZ) AS dt,
LIST(('14:59:37'::TIME + INTERVAL (i) MINUTE)::TIMETZ) AS t,
FROM range(1, 6, 1) t1(i)
query II
SELECT list_last(dt), list_last(t) FROM five_dates_tz
----
2021-08-20 05:00:00+00 15:04:37+00
statement ok
DROP TABLE five_dates_tz
# complex types
statement ok
CREATE TABLE five_complex AS
SELECT
LIST(i::integer) AS i,
LIST(i::VARCHAR) AS s,
LIST([i]) AS l,
LIST({'a': i}) AS r
FROM range(1, 6, 1) t1(i)
query III
SELECT list_last(s), list_last(l), list_last(r)
FROM five_complex
----
5 [5] {'a': 5}
statement ok
DROP TABLE five_complex
# decimals
foreach type decimal(4,1) decimal(8,1) decimal(12,1) decimal(18,1)
statement ok
CREATE TABLE five AS SELECT LIST(i::${type}) AS i FROM range(1, 6, 1) t1(i)
statement ok
INSERT INTO five VALUES (NULL), ([NULL]), ([]), ([1, 2, NULL])
query I
SELECT list_last(i) FROM five
----
5.0
NULL
NULL
NULL
NULL
statement ok
DROP TABLE five
endloop
|
