0001 -- Automatically generated by SQLQueryTestSuite
0002 -- Number of queries: 46
0003
0004
0005 -- !query
0006 select to_json(named_struct('a', 1, 'b', 2))
0007 -- !query schema
0008 struct<to_json(named_struct(a, 1, b, 2)):string>
0009 -- !query output
0010 {"a":1,"b":2}
0011
0012
0013 -- !query
0014 select to_json(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'))
0015 -- !query schema
0016 struct<to_json(named_struct(time, to_timestamp('2015-08-26', 'yyyy-MM-dd'))):string>
0017 -- !query output
0018 {"time":"26/08/2015"}
0019
0020
0021 -- !query
0022 select to_json(array(named_struct('a', 1, 'b', 2)))
0023 -- !query schema
0024 struct<to_json(array(named_struct(a, 1, b, 2))):string>
0025 -- !query output
0026 [{"a":1,"b":2}]
0027
0028
0029 -- !query
0030 select to_json(map(named_struct('a', 1, 'b', 2), named_struct('a', 1, 'b', 2)))
0031 -- !query schema
0032 struct<to_json(map(named_struct(a, 1, b, 2), named_struct(a, 1, b, 2))):string>
0033 -- !query output
0034 {"[1,2]":{"a":1,"b":2}}
0035
0036
0037 -- !query
0038 select to_json(map('a', named_struct('a', 1, 'b', 2)))
0039 -- !query schema
0040 struct<to_json(map(a, named_struct(a, 1, b, 2))):string>
0041 -- !query output
0042 {"a":{"a":1,"b":2}}
0043
0044
0045 -- !query
0046 select to_json(map('a', 1))
0047 -- !query schema
0048 struct<to_json(map(a, 1)):string>
0049 -- !query output
0050 {"a":1}
0051
0052
0053 -- !query
0054 select to_json(array(map('a',1)))
0055 -- !query schema
0056 struct<to_json(array(map(a, 1))):string>
0057 -- !query output
0058 [{"a":1}]
0059
0060
0061 -- !query
0062 select to_json(array(map('a',1), map('b',2)))
0063 -- !query schema
0064 struct<to_json(array(map(a, 1), map(b, 2))):string>
0065 -- !query output
0066 [{"a":1},{"b":2}]
0067
0068
0069 -- !query
0070 select to_json(named_struct('a', 1, 'b', 2), named_struct('mode', 'PERMISSIVE'))
0071 -- !query schema
0072 struct<>
0073 -- !query output
0074 org.apache.spark.sql.AnalysisException
0075 Must use a map() function for options;; line 1 pos 7
0076
0077
0078 -- !query
0079 select to_json(named_struct('a', 1, 'b', 2), map('mode', 1))
0080 -- !query schema
0081 struct<>
0082 -- !query output
0083 org.apache.spark.sql.AnalysisException
0084 A type of keys and values in map() must be string, but got map<string,int>;; line 1 pos 7
0085
0086
0087 -- !query
0088 select to_json()
0089 -- !query schema
0090 struct<>
0091 -- !query output
0092 org.apache.spark.sql.AnalysisException
0093 Invalid number of arguments for function to_json. Expected: one of 1 and 2; Found: 0; line 1 pos 7
0094
0095
0096 -- !query
0097 select from_json('{"a":1}', 'a INT')
0098 -- !query schema
0099 struct<from_json({"a":1}):struct<a:int>>
0100 -- !query output
0101 {"a":1}
0102
0103
0104 -- !query
0105 select from_json('{"time":"26/08/2015"}', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'))
0106 -- !query schema
0107 struct<from_json({"time":"26/08/2015"}):struct<time:timestamp>>
0108 -- !query output
0109 {"time":2015-08-26 00:00:00}
0110
0111
0112 -- !query
0113 select from_json('{"a":1}', 1)
0114 -- !query schema
0115 struct<>
0116 -- !query output
0117 org.apache.spark.sql.AnalysisException
0118 Schema should be specified in DDL format as a string literal or output of the schema_of_json function instead of 1;; line 1 pos 7
0119
0120
0121 -- !query
0122 select from_json('{"a":1}', 'a InvalidType')
0123 -- !query schema
0124 struct<>
0125 -- !query output
0126 org.apache.spark.sql.AnalysisException
0127
0128 DataType invalidtype is not supported.(line 1, pos 2)
0129
0130 == SQL ==
0131 a InvalidType
0132 --^^^
0133 ; line 1 pos 7
0134
0135
0136 -- !query
0137 select from_json('{"a":1}', 'a INT', named_struct('mode', 'PERMISSIVE'))
0138 -- !query schema
0139 struct<>
0140 -- !query output
0141 org.apache.spark.sql.AnalysisException
0142 Must use a map() function for options;; line 1 pos 7
0143
0144
0145 -- !query
0146 select from_json('{"a":1}', 'a INT', map('mode', 1))
0147 -- !query schema
0148 struct<>
0149 -- !query output
0150 org.apache.spark.sql.AnalysisException
0151 A type of keys and values in map() must be string, but got map<string,int>;; line 1 pos 7
0152
0153
0154 -- !query
0155 select from_json()
0156 -- !query schema
0157 struct<>
0158 -- !query output
0159 org.apache.spark.sql.AnalysisException
0160 Invalid number of arguments for function from_json. Expected: one of 2 and 3; Found: 0; line 1 pos 7
0161
0162
0163 -- !query
0164 SELECT json_tuple('{"a" : 1, "b" : 2}', CAST(NULL AS STRING), 'b', CAST(NULL AS STRING), 'a')
0165 -- !query schema
0166 struct<c0:string,c1:string,c2:string,c3:string>
0167 -- !query output
0168 NULL 2 NULL 1
0169
0170
0171 -- !query
0172 CREATE TEMPORARY VIEW jsonTable(jsonField, a) AS SELECT * FROM VALUES ('{"a": 1, "b": 2}', 'a')
0173 -- !query schema
0174 struct<>
0175 -- !query output
0176
0177
0178
0179 -- !query
0180 SELECT json_tuple(jsonField, 'b', CAST(NULL AS STRING), a) FROM jsonTable
0181 -- !query schema
0182 struct<c0:string,c1:string,c2:string>
0183 -- !query output
0184 2 NULL 1
0185
0186
0187 -- !query
0188 DROP VIEW IF EXISTS jsonTable
0189 -- !query schema
0190 struct<>
0191 -- !query output
0192
0193
0194
0195 -- !query
0196 select from_json('{"a":1, "b":2}', 'map<string, int>')
0197 -- !query schema
0198 struct<entries:map<string,int>>
0199 -- !query output
0200 {"a":1,"b":2}
0201
0202
0203 -- !query
0204 select from_json('{"a":1, "b":"2"}', 'struct<a:int,b:string>')
0205 -- !query schema
0206 struct<from_json({"a":1, "b":"2"}):struct<a:int,b:string>>
0207 -- !query output
0208 {"a":1,"b":"2"}
0209
0210
0211 -- !query
0212 select schema_of_json('{"c1":0, "c2":[1]}')
0213 -- !query schema
0214 struct<schema_of_json({"c1":0, "c2":[1]}):string>
0215 -- !query output
0216 struct<c1:bigint,c2:array<bigint>>
0217
0218
0219 -- !query
0220 select from_json('{"c1":[1, 2, 3]}', schema_of_json('{"c1":[0]}'))
0221 -- !query schema
0222 struct<from_json({"c1":[1, 2, 3]}):struct<c1:array<bigint>>>
0223 -- !query output
0224 {"c1":[1,2,3]}
0225
0226
0227 -- !query
0228 select from_json('[1, 2, 3]', 'array<int>')
0229 -- !query schema
0230 struct<from_json([1, 2, 3]):array<int>>
0231 -- !query output
0232 [1,2,3]
0233
0234
0235 -- !query
0236 select from_json('[1, "2", 3]', 'array<int>')
0237 -- !query schema
0238 struct<from_json([1, "2", 3]):array<int>>
0239 -- !query output
0240 NULL
0241
0242
0243 -- !query
0244 select from_json('[1, 2, null]', 'array<int>')
0245 -- !query schema
0246 struct<from_json([1, 2, null]):array<int>>
0247 -- !query output
0248 [1,2,null]
0249
0250
0251 -- !query
0252 select from_json('[{"a": 1}, {"a":2}]', 'array<struct<a:int>>')
0253 -- !query schema
0254 struct<from_json([{"a": 1}, {"a":2}]):array<struct<a:int>>>
0255 -- !query output
0256 [{"a":1},{"a":2}]
0257
0258
0259 -- !query
0260 select from_json('{"a": 1}', 'array<struct<a:int>>')
0261 -- !query schema
0262 struct<from_json({"a": 1}):array<struct<a:int>>>
0263 -- !query output
0264 [{"a":1}]
0265
0266
0267 -- !query
0268 select from_json('[null, {"a":2}]', 'array<struct<a:int>>')
0269 -- !query schema
0270 struct<from_json([null, {"a":2}]):array<struct<a:int>>>
0271 -- !query output
0272 [null,{"a":2}]
0273
0274
0275 -- !query
0276 select from_json('[{"a": 1}, {"b":2}]', 'array<map<string,int>>')
0277 -- !query schema
0278 struct<from_json([{"a": 1}, {"b":2}]):array<map<string,int>>>
0279 -- !query output
0280 [{"a":1},{"b":2}]
0281
0282
0283 -- !query
0284 select from_json('[{"a": 1}, 2]', 'array<map<string,int>>')
0285 -- !query schema
0286 struct<from_json([{"a": 1}, 2]):array<map<string,int>>>
0287 -- !query output
0288 NULL
0289
0290
0291 -- !query
0292 select from_json('{"d": "2012-12-15", "t": "2012-12-15 15:15:15"}', 'd date, t timestamp')
0293 -- !query schema
0294 struct<from_json({"d": "2012-12-15", "t": "2012-12-15 15:15:15"}):struct<d:date,t:timestamp>>
0295 -- !query output
0296 {"d":2012-12-15,"t":2012-12-15 15:15:15}
0297
0298
0299 -- !query
0300 select from_json(
0301 '{"d": "12/15 2012", "t": "12/15 2012 15:15:15"}',
0302 'd date, t timestamp',
0303 map('dateFormat', 'MM/dd yyyy', 'timestampFormat', 'MM/dd yyyy HH:mm:ss'))
0304 -- !query schema
0305 struct<from_json({"d": "12/15 2012", "t": "12/15 2012 15:15:15"}):struct<d:date,t:timestamp>>
0306 -- !query output
0307 {"d":2012-12-15,"t":2012-12-15 15:15:15}
0308
0309
0310 -- !query
0311 select from_json(
0312 '{"d": "02-29"}',
0313 'd date',
0314 map('dateFormat', 'MM-dd'))
0315 -- !query schema
0316 struct<>
0317 -- !query output
0318 org.apache.spark.SparkUpgradeException
0319 You may get a different result due to the upgrading of Spark 3.0: Fail to parse '02-29' in the new parser. You can set spark.sql.legacy.timeParserPolicy to LEGACY to restore the behavior before Spark 3.0, or set to CORRECTED and treat it as an invalid datetime string.
0320
0321
0322 -- !query
0323 select from_json(
0324 '{"t": "02-29"}',
0325 't timestamp',
0326 map('timestampFormat', 'MM-dd'))
0327 -- !query schema
0328 struct<>
0329 -- !query output
0330 org.apache.spark.SparkUpgradeException
0331 You may get a different result due to the upgrading of Spark 3.0: Fail to parse '02-29' in the new parser. You can set spark.sql.legacy.timeParserPolicy to LEGACY to restore the behavior before Spark 3.0, or set to CORRECTED and treat it as an invalid datetime string.
0332
0333
0334 -- !query
0335 select to_json(array('1', '2', '3'))
0336 -- !query schema
0337 struct<to_json(array(1, 2, 3)):string>
0338 -- !query output
0339 ["1","2","3"]
0340
0341
0342 -- !query
0343 select to_json(array(array(1, 2, 3), array(4)))
0344 -- !query schema
0345 struct<to_json(array(array(1, 2, 3), array(4))):string>
0346 -- !query output
0347 [[1,2,3],[4]]
0348
0349
0350 -- !query
0351 select schema_of_json('{"c1":1}', map('primitivesAsString', 'true'))
0352 -- !query schema
0353 struct<schema_of_json({"c1":1}):string>
0354 -- !query output
0355 struct<c1:string>
0356
0357
0358 -- !query
0359 select schema_of_json('{"c1":01, "c2":0.1}', map('allowNumericLeadingZeros', 'true', 'prefersDecimal', 'true'))
0360 -- !query schema
0361 struct<schema_of_json({"c1":01, "c2":0.1}):string>
0362 -- !query output
0363 struct<c1:bigint,c2:decimal(1,1)>
0364
0365
0366 -- !query
0367 select schema_of_json(null)
0368 -- !query schema
0369 struct<>
0370 -- !query output
0371 org.apache.spark.sql.AnalysisException
0372 cannot resolve 'schema_of_json(NULL)' due to data type mismatch: The input json should be a string literal and not null; however, got NULL.; line 1 pos 7
0373
0374
0375 -- !query
0376 CREATE TEMPORARY VIEW jsonTable(jsonField, a) AS SELECT * FROM VALUES ('{"a": 1, "b": 2}', 'a')
0377 -- !query schema
0378 struct<>
0379 -- !query output
0380
0381
0382
0383 -- !query
0384 SELECT schema_of_json(jsonField) FROM jsonTable
0385 -- !query schema
0386 struct<>
0387 -- !query output
0388 org.apache.spark.sql.AnalysisException
0389 cannot resolve 'schema_of_json(jsontable.`jsonField`)' due to data type mismatch: The input json should be a string literal and not null; however, got jsontable.`jsonField`.; line 1 pos 7
0390
0391
0392 -- !query
0393 DROP VIEW IF EXISTS jsonTable
0394 -- !query schema
0395 struct<>
0396 -- !query output
0397
