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
|
-- Automatically generated by SQLQueryTestSuite
-- Number of queries: 13
-- !query 0
SELECT ifnull(null, 'x'), ifnull('y', 'x'), ifnull(null, null)
-- !query 0 schema
struct<ifnull(NULL, 'x'):string,ifnull('y', 'x'):string,ifnull(NULL, NULL):null>
-- !query 0 output
x y NULL
-- !query 1
SELECT nullif('x', 'x'), nullif('x', 'y')
-- !query 1 schema
struct<nullif('x', 'x'):string,nullif('x', 'y'):string>
-- !query 1 output
NULL x
-- !query 2
SELECT nvl(null, 'x'), nvl('y', 'x'), nvl(null, null)
-- !query 2 schema
struct<nvl(NULL, 'x'):string,nvl('y', 'x'):string,nvl(NULL, NULL):null>
-- !query 2 output
x y NULL
-- !query 3
SELECT nvl2(null, 'x', 'y'), nvl2('n', 'x', 'y'), nvl2(null, null, null)
-- !query 3 schema
struct<nvl2(NULL, 'x', 'y'):string,nvl2('n', 'x', 'y'):string,nvl2(NULL, NULL, NULL):null>
-- !query 3 output
y x NULL
-- !query 4
SELECT ifnull(1, 2.1d), ifnull(null, 2.1d)
-- !query 4 schema
struct<ifnull(1, 2.1D):double,ifnull(NULL, 2.1D):double>
-- !query 4 output
1.0 2.1
-- !query 5
SELECT nullif(1, 2.1d), nullif(1, 1.0d)
-- !query 5 schema
struct<nullif(1, 2.1D):int,nullif(1, 1.0D):int>
-- !query 5 output
1 NULL
-- !query 6
SELECT nvl(1, 2.1d), nvl(null, 2.1d)
-- !query 6 schema
struct<nvl(1, 2.1D):double,nvl(NULL, 2.1D):double>
-- !query 6 output
1.0 2.1
-- !query 7
SELECT nvl2(null, 1, 2.1d), nvl2('n', 1, 2.1d)
-- !query 7 schema
struct<nvl2(NULL, 1, 2.1D):double,nvl2('n', 1, 2.1D):double>
-- !query 7 output
2.1 1.0
-- !query 8
explain extended
select ifnull(id, 'x'), nullif(id, 'x'), nvl(id, 'x'), nvl2(id, 'x', 'y')
from range(2)
-- !query 8 schema
struct<plan:string>
-- !query 8 output
== Parsed Logical Plan ==
'Project [unresolvedalias('ifnull('id, x), None), unresolvedalias('nullif('id, x), None), unresolvedalias('nvl('id, x), None), unresolvedalias('nvl2('id, x, y), None)]
+- 'UnresolvedTableValuedFunction range, [2]
== Analyzed Logical Plan ==
ifnull(`id`, 'x'): string, nullif(`id`, 'x'): bigint, nvl(`id`, 'x'): string, nvl2(`id`, 'x', 'y'): string
Project [ifnull(id#xL, x) AS ifnull(`id`, 'x')#x, nullif(id#xL, x) AS nullif(`id`, 'x')#xL, nvl(id#xL, x) AS nvl(`id`, 'x')#x, nvl2(id#xL, x, y) AS nvl2(`id`, 'x', 'y')#x]
+- Range (0, 2, step=1, splits=None)
== Optimized Logical Plan ==
Project [coalesce(cast(id#xL as string), x) AS ifnull(`id`, 'x')#x, id#xL AS nullif(`id`, 'x')#xL, coalesce(cast(id#xL as string), x) AS nvl(`id`, 'x')#x, x AS nvl2(`id`, 'x', 'y')#x]
+- Range (0, 2, step=1, splits=None)
== Physical Plan ==
*Project [coalesce(cast(id#xL as string), x) AS ifnull(`id`, 'x')#x, id#xL AS nullif(`id`, 'x')#xL, coalesce(cast(id#xL as string), x) AS nvl(`id`, 'x')#x, x AS nvl2(`id`, 'x', 'y')#x]
+- *Range (0, 2, step=1, splits=2)
-- !query 9
SELECT boolean(1), tinyint(1), smallint(1), int(1), bigint(1)
-- !query 9 schema
struct<CAST(1 AS BOOLEAN):boolean,CAST(1 AS TINYINT):tinyint,CAST(1 AS SMALLINT):smallint,CAST(1 AS INT):int,CAST(1 AS BIGINT):bigint>
-- !query 9 output
true 1 1 1 1
-- !query 10
SELECT float(1), double(1), decimal(1)
-- !query 10 schema
struct<CAST(1 AS FLOAT):float,CAST(1 AS DOUBLE):double,CAST(1 AS DECIMAL(10,0)):decimal(10,0)>
-- !query 10 output
1.0 1.0 1
-- !query 11
SELECT date("2014-04-04"), timestamp(date("2014-04-04"))
-- !query 11 schema
struct<CAST(2014-04-04 AS DATE):date,CAST(CAST(2014-04-04 AS DATE) AS TIMESTAMP):timestamp>
-- !query 11 output
2014-04-04 2014-04-04 00:00:00
-- !query 12
SELECT string(1, 2)
-- !query 12 schema
struct<>
-- !query 12 output
org.apache.spark.sql.AnalysisException
Function string accepts only one argument; line 1 pos 7
|
