sql/core/src/test/resources/sql-tests/analyzer-results/sql-compatibility-functions.sql.out

-- Automatically generated by SQLQueryTestSuite
-- !query
SELECT ifnull(null, 'x'), ifnull('y', 'x'), ifnull(null, null)
-- !query analysis
Project [ifnull(null, x) AS ifnull(NULL, x)#x, ifnull(y, x) AS ifnull(y, x)#x, ifnull(null, null) AS ifnull(NULL, NULL)#x]
+- OneRowRelation


-- !query
SELECT nullif('x', 'x'), nullif('x', 'y')
-- !query analysis
Project [nullif(x, x) AS nullif(x, x)#x, nullif(x, y) AS nullif(x, y)#x]
+- OneRowRelation


-- !query
SELECT nvl(null, 'x'), nvl('y', 'x'), nvl(null, null)
-- !query analysis
Project [nvl(null, x) AS nvl(NULL, x)#x, nvl(y, x) AS nvl(y, x)#x, nvl(null, null) AS nvl(NULL, NULL)#x]
+- OneRowRelation


-- !query
SELECT nvl2(null, 'x', 'y'), nvl2('n', 'x', 'y'), nvl2(null, null, null)
-- !query analysis
Project [nvl2(null, x, y) AS nvl2(NULL, x, y)#x, nvl2(n, x, y) AS nvl2(n, x, y)#x, nvl2(null, null, null) AS nvl2(NULL, NULL, NULL)#x]
+- OneRowRelation


-- !query
SELECT ifnull(1, 2.1d), ifnull(null, 2.1d)
-- !query analysis
Project [ifnull(1, 2.1) AS ifnull(1, 2.1)#x, ifnull(null, 2.1) AS ifnull(NULL, 2.1)#x]
+- OneRowRelation


-- !query
SELECT nullif(1, 2.1d), nullif(1, 1.0d)
-- !query analysis
Project [nullif(1, 2.1) AS nullif(1, 2.1)#x, nullif(1, 1.0) AS nullif(1, 1.0)#x]
+- OneRowRelation


-- !query
SELECT nvl(1, 2.1d), nvl(null, 2.1d)
-- !query analysis
Project [nvl(1, 2.1) AS nvl(1, 2.1)#x, nvl(null, 2.1) AS nvl(NULL, 2.1)#x]
+- OneRowRelation


-- !query
SELECT nvl2(null, 1, 2.1d), nvl2('n', 1, 2.1d)
-- !query analysis
Project [nvl2(null, 1, 2.1) AS nvl2(NULL, 1, 2.1)#x, nvl2(n, 1, 2.1) AS nvl2(n, 1, 2.1)#x]
+- OneRowRelation


-- !query
SELECT boolean(1), tinyint(1), smallint(1), int(1), bigint(1)
-- !query analysis
Project [cast(1 as boolean) AS 1#x, cast(1 as tinyint) AS 1#x, cast(1 as smallint) AS 1#x, cast(1 as int) AS 1#x, cast(1 as bigint) AS 1#xL]
+- OneRowRelation


-- !query
SELECT float(1), double(1), decimal(1)
-- !query analysis
Project [cast(1 as float) AS 1#x, cast(1 as double) AS 1#x, cast(1 as decimal(10,0)) AS 1#x]
+- OneRowRelation


-- !query
SELECT date("2014-04-04"), timestamp(date("2014-04-04"))
-- !query analysis
Project [cast(2014-04-04 as date) AS 2014-04-04#x, cast(cast(2014-04-04 as date) as timestamp) AS 2014-04-04#x]
+- OneRowRelation


-- !query
SELECT string(1, 2)
-- !query analysis
org.apache.spark.sql.AnalysisException
{
  "errorClass" : "WRONG_NUM_ARGS.WITHOUT_SUGGESTION",
  "sqlState" : "42605",
  "messageParameters" : {
    "actualNum" : "2",
    "docroot" : "https://spark.apache.org/docs/latest",
    "expectedNum" : "1",
    "functionName" : "`string`"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 8,
    "stopIndex" : 19,
    "fragment" : "string(1, 2)"
  } ]
}


-- !query
CREATE TEMPORARY VIEW tempView1 AS VALUES (1, NAMED_STRUCT('col1', 'gamma', 'col2', 'delta')) AS T(id, st)
-- !query analysis
CreateViewCommand `tempView1`, VALUES (1, NAMED_STRUCT('col1', 'gamma', 'col2', 'delta')) AS T(id, st), false, false, LocalTempView, true
   +- SubqueryAlias T
      +- LocalRelation [id#x, st#x]


-- !query
SELECT nvl(st.col1, "value"), count(*) FROM from tempView1 GROUP BY nvl(st.col1, "value")
-- !query analysis
Aggregate [nvl(st#x.col1, value)], [nvl(st#x.col1, value) AS nvl(st.col1, value)#x, count(1) AS FROM#xL]
+- SubqueryAlias tempview1
   +- View (`tempView1`, [id#x, st#x])
      +- Project [cast(id#x as int) AS id#x, cast(st#x as struct<col1:string,col2:string>) AS st#x]
         +- SubqueryAlias T
            +- LocalRelation [id#x, st#x]


-- !query
SELECT nullif(SUM(id), 0) from range(5)
-- !query analysis
Aggregate [nullif(sum(id#xL), 0) AS nullif(sum(id), 0)#xL]
+- Range (0, 5, step=1)