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
144
145
146
147
148
149
150
|
# name: test/sql/error/test_try_expression.test_slow
# group: [error]
statement ok
pragma enable_verification;
# --- OPERATOR_CAST ---
query I
select TRY('abc'::INTEGER)
----
NULL
# --- OPERATOR_NOT ---
statement error
select ~CAST('abc' as INTEGER)
----
Could not convert string
query I
select TRY(~CAST('abc' as INTEGER))
----
NULL
# --- OPERATOR_IS_NULL ---
query I
select TRY(CAST('abc' as INTEGER) IS NULL);
----
NULL
# --- OPERATOR_IS_NOT_NULL ---
query I
select TRY(CAST('abc' as INTEGER) IS NOT NULL);
----
NULL
# --- COMPARE_EQUAL ---
query I
select TRY(CAST('abc' as INTEGER) == 'abc')
----
NULL
# --- COMPARE_EQUAL ---
query I
select TRY(CAST('abc' as INTEGER) == 'abc')
----
NULL
# --- COMPARE_NOTEQUAL ---
query I
select TRY(CAST('abc' as INTEGER) != 'abc')
----
NULL
# --- Functions ---
query I
select TRY(ln(0));
----
NULL
query I
with cte as (
select * from (VALUES
('123'),
('test'),
('235')
) t(a)
)
select try(a::INTEGER) from cte
----
123
NULL
235
# Using TRY('abc'::BIGINT) results in null
# Making the result of these queries equivalent
query I nosort expected_result
with cte as (
select i % 5 i from range(100_000) t(i)
),
cte2 as (
select if(i == 0, NULL, i) res from cte
)
select count(res) from cte2;
----
query I nosort expected_result
with cte as (
select i % 5 i from range(100_000) t(i)
),
cte2 as (
select TRY(if(i == 0, 'abc', i::VARCHAR)::BIGINT) res from cte
)
select count(res) from cte2;
----
statement error
select try(if(random() > 2.0, '123', 'abc')::TINYINT)
----
TRY can not be used in combination with a volatile function
statement error
select try(CAST((select 'ABC') as INTEGER))
----
TRY can not be used in combination with a scalar subquery
# Aggregates or plain column references are also not allowed.
statement error
with cte(x) as (
select 123 a
)
SELECT try(avg(x)) FROM cte;
----
aggregates are not allowed inside the TRY expression
# column reference can be used just fine
query I
with cte(x) as (
select 123 a
)
SELECT TRY(x) FROM cte
----
123
# Aggregate appearing in a deeper nested expression
statement error
with cte(x) as (
select 123 a
)
select TRY(2 + avg(x)) from cte
----
aggregates are not allowed inside the TRY expression
# The values below are literals, which do not have a PhysicalType, caused a crash before fixing duckdb-internal/issues/5047
statement ok
select TRY('hello');
statement ok
select TRY(123);
|
