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
|
# name: test/sql/function/variant/variant_extract_try_cast.test
# group: [variant]
load __TEST_DIR__/variant_extract_try_cast.db readwrite v1.5.0
statement ok
SET variant_minimum_shredding_size = 0;
statement ok
create table tbl(col VARIANT);
statement ok
insert into tbl SELECT * FROM UNNEST([
{'almost_a_number': c, 'a_number': CAST(TRY_CAST(c AS INT) AS VARCHAR)} for c in ['12', '24', '25a6', '24c', '16']
])
statement ok
checkpoint
restart
query I
from tbl order by all
----
{'a_number': NULL, 'almost_a_number': 24c}
{'a_number': NULL, 'almost_a_number': 25a6}
{'a_number': 12, 'almost_a_number': 12}
{'a_number': 16, 'almost_a_number': 16}
{'a_number': 24, 'almost_a_number': 24}
query I
select col.almost_a_number from tbl order by all
----
12
16
24
24c
25a6
# Can't use CAST
statement error
select col.almost_a_number::BIGINT from tbl order by all
----
Conversion Error
# Use TRY_CAST instead
query I
select TRY_CAST(col.almost_a_number AS BIGINT) from tbl order by all
----
12
16
24
NULL
NULL
query I
select col.a_number from tbl order by all
----
12
16
24
NULL
NULL
query I
select col.a_number::BIGINT from tbl order by all
----
12
16
24
NULL
NULL
statement ok
set explain_output='optimized_only';
# Very that the EXPLAIN plan doesn't contain a pushed down extract
query II
EXPLAIN select TRY_CAST(col.almost_a_number AS BIGINT) from tbl order by all
----
logical_opt <REGEX>:.*Expressions:.*TRY_CAST.*
|
