sql/core/src/test/resources/sql-tests/analyzer-results/group-by.sql.out

-- Automatically generated by SQLQueryTestSuite
-- !query
CREATE OR REPLACE TEMPORARY VIEW testData AS SELECT * FROM VALUES
(1, 1), (1, 2), (2, 1), (2, 2), (3, 1), (3, 2), (null, 1), (3, null), (null, null)
AS testData(a, b)
-- !query analysis
CreateViewCommand `testData`, SELECT * FROM VALUES
(1, 1), (1, 2), (2, 1), (2, 2), (3, 1), (3, 2), (null, 1), (3, null), (null, null)
AS testData(a, b), false, true, LocalTempView, true
   +- Project [a#x, b#x]
      +- SubqueryAlias testData
         +- LocalRelation [a#x, b#x]


-- !query
SELECT a, COUNT(b) FROM testData
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "MISSING_GROUP_BY",
  "sqlState" : "42803",
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 1,
    "stopIndex" : 32,
    "fragment" : "SELECT a, COUNT(b) FROM testData"
  } ]
}


-- !query
SELECT COUNT(a), COUNT(b) FROM testData
-- !query analysis
Aggregate [count(a#x) AS count(a)#xL, count(b#x) AS count(b)#xL]
+- SubqueryAlias testdata
   +- View (`testData`, [a#x, b#x])
      +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
         +- Project [a#x, b#x]
            +- SubqueryAlias testData
               +- LocalRelation [a#x, b#x]


-- !query
SELECT a, COUNT(b) FROM testData GROUP BY a
-- !query analysis
Aggregate [a#x], [a#x, count(b#x) AS count(b)#xL]
+- SubqueryAlias testdata
   +- View (`testData`, [a#x, b#x])
      +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
         +- Project [a#x, b#x]
            +- SubqueryAlias testData
               +- LocalRelation [a#x, b#x]


-- !query
SELECT a, COUNT(b) FROM testData GROUP BY b
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "MISSING_AGGREGATION",
  "sqlState" : "42803",
  "messageParameters" : {
    "expression" : "\"a\"",
    "expressionAnyValue" : "\"any_value(a)\""
  }
}


-- !query
SELECT COUNT(a), COUNT(b) FROM testData GROUP BY a
-- !query analysis
Aggregate [a#x], [count(a#x) AS count(a)#xL, count(b#x) AS count(b)#xL]
+- SubqueryAlias testdata
   +- View (`testData`, [a#x, b#x])
      +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
         +- Project [a#x, b#x]
            +- SubqueryAlias testData
               +- LocalRelation [a#x, b#x]


-- !query
SELECT 'foo', COUNT(a) FROM testData GROUP BY 1
-- !query analysis
Aggregate [foo], [foo AS foo#x, count(a#x) AS count(a)#xL]
+- SubqueryAlias testdata
   +- View (`testData`, [a#x, b#x])
      +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
         +- Project [a#x, b#x]
            +- SubqueryAlias testData
               +- LocalRelation [a#x, b#x]


-- !query
SELECT 'foo' FROM testData WHERE a = 0 GROUP BY 1
-- !query analysis
Aggregate [foo], [foo AS foo#x]
+- Filter (a#x = 0)
   +- SubqueryAlias testdata
      +- View (`testData`, [a#x, b#x])
         +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
            +- Project [a#x, b#x]
               +- SubqueryAlias testData
                  +- LocalRelation [a#x, b#x]


-- !query
SELECT 'foo', APPROX_COUNT_DISTINCT(a) FROM testData WHERE a = 0 GROUP BY 1
-- !query analysis
Aggregate [foo], [foo AS foo#x, approx_count_distinct(a#x, 0.05, 0, 0) AS approx_count_distinct(a)#xL]
+- Filter (a#x = 0)
   +- SubqueryAlias testdata
      +- View (`testData`, [a#x, b#x])
         +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
            +- Project [a#x, b#x]
               +- SubqueryAlias testData
                  +- LocalRelation [a#x, b#x]


-- !query
SELECT 'foo', MAX(STRUCT(a)) FROM testData WHERE a = 0 GROUP BY 1
-- !query analysis
Aggregate [foo], [foo AS foo#x, max(struct(a, a#x)) AS max(struct(a))#x]
+- Filter (a#x = 0)
   +- SubqueryAlias testdata
      +- View (`testData`, [a#x, b#x])
         +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
            +- Project [a#x, b#x]
               +- SubqueryAlias testData
                  +- LocalRelation [a#x, b#x]


-- !query
SELECT a + b, COUNT(b) FROM testData GROUP BY a + b
-- !query analysis
Aggregate [(a#x + b#x)], [(a#x + b#x) AS (a + b)#x, count(b#x) AS count(b)#xL]
+- SubqueryAlias testdata
   +- View (`testData`, [a#x, b#x])
      +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
         +- Project [a#x, b#x]
            +- SubqueryAlias testData
               +- LocalRelation [a#x, b#x]


-- !query
SELECT a + 2, COUNT(b) FROM testData GROUP BY a + 1
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "MISSING_AGGREGATION",
  "sqlState" : "42803",
  "messageParameters" : {
    "expression" : "\"a\"",
    "expressionAnyValue" : "\"any_value(a)\""
  }
}


-- !query
SELECT a + 1 + 1, COUNT(b) FROM testData GROUP BY a + 1
-- !query analysis
Aggregate [(a#x + 1)], [((a#x + 1) + 1) AS ((a + 1) + 1)#x, count(b#x) AS count(b)#xL]
+- SubqueryAlias testdata
   +- View (`testData`, [a#x, b#x])
      +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
         +- Project [a#x, b#x]
            +- SubqueryAlias testData
               +- LocalRelation [a#x, b#x]


-- !query
SELECT count(1) FROM testData GROUP BY struct(a + 0.1 AS aa)
-- !query analysis
Aggregate [struct(aa, (cast(a#x as decimal(10,0)) + 0.1))], [count(1) AS count(1)#xL]
+- SubqueryAlias testdata
   +- View (`testData`, [a#x, b#x])
      +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
         +- Project [a#x, b#x]
            +- SubqueryAlias testData
               +- LocalRelation [a#x, b#x]


-- !query
SELECT SKEWNESS(a), KURTOSIS(a), MIN(a), MAX(a), AVG(a), VARIANCE(a), STDDEV(a), SUM(a), COUNT(a)
FROM testData
-- !query analysis
Aggregate [skewness(cast(a#x as double)) AS skewness(a)#x, kurtosis(cast(a#x as double)) AS kurtosis(a)#x, min(a#x) AS min(a)#x, max(a#x) AS max(a)#x, avg(a#x) AS avg(a)#x, variance(cast(a#x as double)) AS variance(a)#x, stddev(cast(a#x as double)) AS stddev(a)#x, sum(a#x) AS sum(a)#xL, count(a#x) AS count(a)#xL]
+- SubqueryAlias testdata
   +- View (`testData`, [a#x, b#x])
      +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
         +- Project [a#x, b#x]
            +- SubqueryAlias testData
               +- LocalRelation [a#x, b#x]


-- !query
SELECT COUNT(DISTINCT b), COUNT(DISTINCT b, c) FROM (SELECT 1 AS a, 2 AS b, 3 AS c) GROUP BY a
-- !query analysis
Aggregate [a#x], [count(distinct b#x) AS count(DISTINCT b)#xL, count(distinct b#x, c#x) AS count(DISTINCT b, c)#xL]
+- SubqueryAlias __auto_generated_subquery_name
   +- Project [1 AS a#x, 2 AS b#x, 3 AS c#x]
      +- OneRowRelation


-- !query
SELECT a AS k, COUNT(b) FROM testData GROUP BY k
-- !query analysis
Aggregate [a#x], [a#x AS k#x, count(b#x) AS count(b)#xL]
+- SubqueryAlias testdata
   +- View (`testData`, [a#x, b#x])
      +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
         +- Project [a#x, b#x]
            +- SubqueryAlias testData
               +- LocalRelation [a#x, b#x]


-- !query
SELECT a AS k, COUNT(b) FROM testData GROUP BY k HAVING k > 1
-- !query analysis
Filter (k#x > 1)
+- Aggregate [a#x], [a#x AS k#x, count(b#x) AS count(b)#xL]
   +- SubqueryAlias testdata
      +- View (`testData`, [a#x, b#x])
         +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
            +- Project [a#x, b#x]
               +- SubqueryAlias testData
                  +- LocalRelation [a#x, b#x]


-- !query
SELECT a AS k, COUNT(non_existing) FROM testData GROUP BY k
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "UNRESOLVED_COLUMN.WITH_SUGGESTION",
  "sqlState" : "42703",
  "messageParameters" : {
    "objectName" : "`non_existing`",
    "proposal" : "`a`, `b`"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 22,
    "stopIndex" : 33,
    "fragment" : "non_existing"
  } ]
}


-- !query
SELECT COUNT(b) AS k FROM testData GROUP BY k
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "GROUP_BY_AGGREGATE",
  "sqlState" : "42903",
  "messageParameters" : {
    "sqlExpr" : "count(testdata.b)"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 8,
    "stopIndex" : 15,
    "fragment" : "COUNT(b)"
  } ]
}


-- !query
CREATE OR REPLACE TEMPORARY VIEW testDataHasSameNameWithAlias AS SELECT * FROM VALUES
(1, 1, 3), (1, 2, 1) AS testDataHasSameNameWithAlias(k, a, v)
-- !query analysis
CreateViewCommand `testDataHasSameNameWithAlias`, SELECT * FROM VALUES
(1, 1, 3), (1, 2, 1) AS testDataHasSameNameWithAlias(k, a, v), false, true, LocalTempView, true
   +- Project [k#x, a#x, v#x]
      +- SubqueryAlias testDataHasSameNameWithAlias
         +- LocalRelation [k#x, a#x, v#x]


-- !query
SELECT k AS a, COUNT(v) FROM testDataHasSameNameWithAlias GROUP BY a
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "MISSING_AGGREGATION",
  "sqlState" : "42803",
  "messageParameters" : {
    "expression" : "\"k\"",
    "expressionAnyValue" : "\"any_value(k)\""
  }
}


-- !query
set spark.sql.groupByAliases=false
-- !query analysis
SetCommand (spark.sql.groupByAliases,Some(false))


-- !query
SELECT a AS k, COUNT(b) FROM testData GROUP BY k
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "UNRESOLVED_COLUMN.WITH_SUGGESTION",
  "sqlState" : "42703",
  "messageParameters" : {
    "objectName" : "`k`",
    "proposal" : "`a`, `b`"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 48,
    "stopIndex" : 48,
    "fragment" : "k"
  } ]
}


-- !query
SELECT a, COUNT(1) FROM testData WHERE false GROUP BY a
-- !query analysis
Aggregate [a#x], [a#x, count(1) AS count(1)#xL]
+- Filter false
   +- SubqueryAlias testdata
      +- View (`testData`, [a#x, b#x])
         +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
            +- Project [a#x, b#x]
               +- SubqueryAlias testData
                  +- LocalRelation [a#x, b#x]


-- !query
SELECT COUNT(1) FROM testData WHERE false
-- !query analysis
Aggregate [count(1) AS count(1)#xL]
+- Filter false
   +- SubqueryAlias testdata
      +- View (`testData`, [a#x, b#x])
         +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
            +- Project [a#x, b#x]
               +- SubqueryAlias testData
                  +- LocalRelation [a#x, b#x]


-- !query
SELECT 1 FROM (SELECT COUNT(1) FROM testData WHERE false) t
-- !query analysis
Project [1 AS 1#x]
+- SubqueryAlias t
   +- Aggregate [count(1) AS count(1)#xL]
      +- Filter false
         +- SubqueryAlias testdata
            +- View (`testData`, [a#x, b#x])
               +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
                  +- Project [a#x, b#x]
                     +- SubqueryAlias testData
                        +- LocalRelation [a#x, b#x]


-- !query
SELECT 1 from (
  SELECT 1 AS z,
  MIN(a.x)
  FROM (select 1 as x) a
  WHERE false
) b
where b.z != b.z
-- !query analysis
Project [1 AS 1#x]
+- Filter NOT (z#x = z#x)
   +- SubqueryAlias b
      +- Aggregate [1 AS z#x, min(x#x) AS min(x)#x]
         +- Filter false
            +- SubqueryAlias a
               +- Project [1 AS x#x]
                  +- OneRowRelation


-- !query
SELECT corr(DISTINCT x, y), corr(DISTINCT y, x), count(*)
  FROM (VALUES (1, 1), (2, 2), (2, 2)) t(x, y)
-- !query analysis
Aggregate [corr(distinct cast(x#x as double), cast(y#x as double)) AS corr(DISTINCT x, y)#x, corr(distinct cast(y#x as double), cast(x#x as double)) AS corr(DISTINCT y, x)#x, count(1) AS count(1)#xL]
+- SubqueryAlias t
   +- Project [col1#x AS x#x, col2#x AS y#x]
      +- LocalRelation [col1#x, col2#x]


-- !query
SELECT 1 FROM range(10) HAVING true
-- !query analysis
Filter cast(true as boolean)
+- Aggregate [1 AS 1#x]
   +- Range (0, 10, step=1)


-- !query
SELECT 1 FROM range(10) HAVING MAX(id) > 0
-- !query analysis
Project [1#x]
+- Filter (max(id#xL)#xL > cast(0 as bigint))
   +- Aggregate [1 AS 1#x, max(id#xL) AS max(id#xL)#xL]
      +- Range (0, 10, step=1)


-- !query
SELECT id FROM range(10) HAVING id > 0
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "MISSING_GROUP_BY",
  "sqlState" : "42803",
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 1,
    "stopIndex" : 38,
    "fragment" : "SELECT id FROM range(10) HAVING id > 0"
  } ]
}


-- !query
SET spark.sql.legacy.parser.havingWithoutGroupByAsWhere=true
-- !query analysis
SetCommand (spark.sql.legacy.parser.havingWithoutGroupByAsWhere,Some(true))


-- !query
SELECT 1 FROM range(10) HAVING true
-- !query analysis
Filter cast(true as boolean)
+- Project [1 AS 1#x]
   +- Range (0, 10, step=1)


-- !query
SELECT 1 FROM range(10) HAVING MAX(id) > 0
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "INVALID_WHERE_CONDITION",
  "sqlState" : "42903",
  "messageParameters" : {
    "condition" : "\"(max(id) > 0)\"",
    "expressionList" : "max(id)"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 1,
    "stopIndex" : 42,
    "fragment" : "SELECT 1 FROM range(10) HAVING MAX(id) > 0"
  } ]
}


-- !query
SELECT id FROM range(10) HAVING id > 0
-- !query analysis
Filter (id#xL > cast(0 as bigint))
+- Project [id#xL]
   +- Range (0, 10, step=1)


-- !query
SET spark.sql.legacy.parser.havingWithoutGroupByAsWhere=false
-- !query analysis
SetCommand (spark.sql.legacy.parser.havingWithoutGroupByAsWhere,Some(false))


-- !query
CREATE OR REPLACE TEMPORARY VIEW test_agg AS SELECT * FROM VALUES
  (1, true), (1, false),
  (2, true),
  (3, false), (3, null),
  (4, null), (4, null),
  (5, null), (5, true), (5, false) AS test_agg(k, v)
-- !query analysis
CreateViewCommand `test_agg`, SELECT * FROM VALUES
  (1, true), (1, false),
  (2, true),
  (3, false), (3, null),
  (4, null), (4, null),
  (5, null), (5, true), (5, false) AS test_agg(k, v), false, true, LocalTempView, true
   +- Project [k#x, v#x]
      +- SubqueryAlias test_agg
         +- LocalRelation [k#x, v#x]


-- !query
SELECT every(v), some(v), any(v), bool_and(v), bool_or(v) FROM test_agg WHERE 1 = 0
-- !query analysis
Aggregate [every(v#x) AS every(v)#x, some(v#x) AS some(v)#x, any(v#x) AS any(v)#x, bool_and(v#x) AS bool_and(v)#x, bool_or(v#x) AS bool_or(v)#x]
+- Filter (1 = 0)
   +- SubqueryAlias test_agg
      +- View (`test_agg`, [k#x, v#x])
         +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
            +- Project [k#x, v#x]
               +- SubqueryAlias test_agg
                  +- LocalRelation [k#x, v#x]


-- !query
SELECT every(v), some(v), any(v), bool_and(v), bool_or(v) FROM test_agg WHERE k = 4
-- !query analysis
Aggregate [every(v#x) AS every(v)#x, some(v#x) AS some(v)#x, any(v#x) AS any(v)#x, bool_and(v#x) AS bool_and(v)#x, bool_or(v#x) AS bool_or(v)#x]
+- Filter (k#x = 4)
   +- SubqueryAlias test_agg
      +- View (`test_agg`, [k#x, v#x])
         +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
            +- Project [k#x, v#x]
               +- SubqueryAlias test_agg
                  +- LocalRelation [k#x, v#x]


-- !query
SELECT every(v), some(v), any(v), bool_and(v), bool_or(v) FROM test_agg WHERE k = 5
-- !query analysis
Aggregate [every(v#x) AS every(v)#x, some(v#x) AS some(v)#x, any(v#x) AS any(v)#x, bool_and(v#x) AS bool_and(v)#x, bool_or(v#x) AS bool_or(v)#x]
+- Filter (k#x = 5)
   +- SubqueryAlias test_agg
      +- View (`test_agg`, [k#x, v#x])
         +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
            +- Project [k#x, v#x]
               +- SubqueryAlias test_agg
                  +- LocalRelation [k#x, v#x]


-- !query
SELECT k, every(v), some(v), any(v), bool_and(v), bool_or(v) FROM test_agg GROUP BY k
-- !query analysis
Aggregate [k#x], [k#x, every(v#x) AS every(v)#x, some(v#x) AS some(v)#x, any(v#x) AS any(v)#x, bool_and(v#x) AS bool_and(v)#x, bool_or(v#x) AS bool_or(v)#x]
+- SubqueryAlias test_agg
   +- View (`test_agg`, [k#x, v#x])
      +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
         +- Project [k#x, v#x]
            +- SubqueryAlias test_agg
               +- LocalRelation [k#x, v#x]


-- !query
SELECT k, every(v) FROM test_agg GROUP BY k HAVING every(v) = false
-- !query analysis
Filter (every(v)#x = false)
+- Aggregate [k#x], [k#x, every(v#x) AS every(v)#x]
   +- SubqueryAlias test_agg
      +- View (`test_agg`, [k#x, v#x])
         +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
            +- Project [k#x, v#x]
               +- SubqueryAlias test_agg
                  +- LocalRelation [k#x, v#x]


-- !query
SELECT k, every(v) FROM test_agg GROUP BY k HAVING every(v) IS NULL
-- !query analysis
Filter isnull(every(v)#x)
+- Aggregate [k#x], [k#x, every(v#x) AS every(v)#x]
   +- SubqueryAlias test_agg
      +- View (`test_agg`, [k#x, v#x])
         +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
            +- Project [k#x, v#x]
               +- SubqueryAlias test_agg
                  +- LocalRelation [k#x, v#x]


-- !query
SELECT k,
       Every(v) AS every
FROM   test_agg
WHERE  k = 2
       AND v IN (SELECT Any(v)
                 FROM   test_agg
                 WHERE  k = 1)
GROUP  BY k
-- !query analysis
Aggregate [k#x], [k#x, every(v#x) AS every#x]
+- Filter ((k#x = 2) AND v#x IN (list#x []))
   :  +- Aggregate [any(v#x) AS any(v)#x]
   :     +- Filter (k#x = 1)
   :        +- SubqueryAlias test_agg
   :           +- View (`test_agg`, [k#x, v#x])
   :              +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
   :                 +- Project [k#x, v#x]
   :                    +- SubqueryAlias test_agg
   :                       +- LocalRelation [k#x, v#x]
   +- SubqueryAlias test_agg
      +- View (`test_agg`, [k#x, v#x])
         +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
            +- Project [k#x, v#x]
               +- SubqueryAlias test_agg
                  +- LocalRelation [k#x, v#x]


-- !query
SELECT k,
       Every(v) AS every
FROM   test_agg
WHERE  k = 2
       AND v IN (SELECT Every(v)
                 FROM   test_agg
                 WHERE  k = 1)
GROUP  BY k
-- !query analysis
Aggregate [k#x], [k#x, every(v#x) AS every#x]
+- Filter ((k#x = 2) AND v#x IN (list#x []))
   :  +- Aggregate [every(v#x) AS every(v)#x]
   :     +- Filter (k#x = 1)
   :        +- SubqueryAlias test_agg
   :           +- View (`test_agg`, [k#x, v#x])
   :              +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
   :                 +- Project [k#x, v#x]
   :                    +- SubqueryAlias test_agg
   :                       +- LocalRelation [k#x, v#x]
   +- SubqueryAlias test_agg
      +- View (`test_agg`, [k#x, v#x])
         +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
            +- Project [k#x, v#x]
               +- SubqueryAlias test_agg
                  +- LocalRelation [k#x, v#x]


-- !query
SELECT every(1)
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
  "sqlState" : "42K09",
  "messageParameters" : {
    "inputSql" : "\"1\"",
    "inputType" : "\"INT\"",
    "paramIndex" : "first",
    "requiredType" : "\"BOOLEAN\"",
    "sqlExpr" : "\"every(1)\""
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 8,
    "stopIndex" : 15,
    "fragment" : "every(1)"
  } ]
}


-- !query
SELECT some(1S)
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
  "sqlState" : "42K09",
  "messageParameters" : {
    "inputSql" : "\"1\"",
    "inputType" : "\"SMALLINT\"",
    "paramIndex" : "first",
    "requiredType" : "\"BOOLEAN\"",
    "sqlExpr" : "\"some(1)\""
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 8,
    "stopIndex" : 15,
    "fragment" : "some(1S)"
  } ]
}


-- !query
SELECT any(1L)
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
  "sqlState" : "42K09",
  "messageParameters" : {
    "inputSql" : "\"1\"",
    "inputType" : "\"BIGINT\"",
    "paramIndex" : "first",
    "requiredType" : "\"BOOLEAN\"",
    "sqlExpr" : "\"any(1)\""
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 8,
    "stopIndex" : 14,
    "fragment" : "any(1L)"
  } ]
}


-- !query
SELECT every("true")
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
  "sqlState" : "42K09",
  "messageParameters" : {
    "inputSql" : "\"true\"",
    "inputType" : "\"STRING\"",
    "paramIndex" : "first",
    "requiredType" : "\"BOOLEAN\"",
    "sqlExpr" : "\"every(true)\""
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 8,
    "stopIndex" : 20,
    "fragment" : "every(\"true\")"
  } ]
}


-- !query
SELECT bool_and(1.0)
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
  "sqlState" : "42K09",
  "messageParameters" : {
    "inputSql" : "\"1.0\"",
    "inputType" : "\"DECIMAL(2,1)\"",
    "paramIndex" : "first",
    "requiredType" : "\"BOOLEAN\"",
    "sqlExpr" : "\"bool_and(1.0)\""
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 8,
    "stopIndex" : 20,
    "fragment" : "bool_and(1.0)"
  } ]
}


-- !query
SELECT bool_or(1.0D)
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
  "sqlState" : "42K09",
  "messageParameters" : {
    "inputSql" : "\"1.0\"",
    "inputType" : "\"DOUBLE\"",
    "paramIndex" : "first",
    "requiredType" : "\"BOOLEAN\"",
    "sqlExpr" : "\"bool_or(1.0)\""
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 8,
    "stopIndex" : 20,
    "fragment" : "bool_or(1.0D)"
  } ]
}


-- !query
SELECT k, v, every(v) OVER (PARTITION BY k ORDER BY v) FROM test_agg
-- !query analysis
Project [k#x, v#x, every(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
+- Project [k#x, v#x, every(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x, every(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
   +- Window [every(v#x) windowspecdefinition(k#x, v#x ASC NULLS FIRST, specifiedwindowframe(RangeFrame, unboundedpreceding$(), currentrow$())) AS every(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x], [k#x], [v#x ASC NULLS FIRST]
      +- Project [k#x, v#x]
         +- SubqueryAlias test_agg
            +- View (`test_agg`, [k#x, v#x])
               +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
                  +- Project [k#x, v#x]
                     +- SubqueryAlias test_agg
                        +- LocalRelation [k#x, v#x]


-- !query
SELECT k, v, some(v) OVER (PARTITION BY k ORDER BY v) FROM test_agg
-- !query analysis
Project [k#x, v#x, some(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
+- Project [k#x, v#x, some(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x, some(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
   +- Window [some(v#x) windowspecdefinition(k#x, v#x ASC NULLS FIRST, specifiedwindowframe(RangeFrame, unboundedpreceding$(), currentrow$())) AS some(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x], [k#x], [v#x ASC NULLS FIRST]
      +- Project [k#x, v#x]
         +- SubqueryAlias test_agg
            +- View (`test_agg`, [k#x, v#x])
               +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
                  +- Project [k#x, v#x]
                     +- SubqueryAlias test_agg
                        +- LocalRelation [k#x, v#x]


-- !query
SELECT k, v, any(v) OVER (PARTITION BY k ORDER BY v) FROM test_agg
-- !query analysis
Project [k#x, v#x, any(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
+- Project [k#x, v#x, any(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x, any(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
   +- Window [any(v#x) windowspecdefinition(k#x, v#x ASC NULLS FIRST, specifiedwindowframe(RangeFrame, unboundedpreceding$(), currentrow$())) AS any(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x], [k#x], [v#x ASC NULLS FIRST]
      +- Project [k#x, v#x]
         +- SubqueryAlias test_agg
            +- View (`test_agg`, [k#x, v#x])
               +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
                  +- Project [k#x, v#x]
                     +- SubqueryAlias test_agg
                        +- LocalRelation [k#x, v#x]


-- !query
SELECT k, v, bool_and(v) OVER (PARTITION BY k ORDER BY v) FROM test_agg
-- !query analysis
Project [k#x, v#x, bool_and(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
+- Project [k#x, v#x, bool_and(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x, bool_and(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
   +- Window [bool_and(v#x) windowspecdefinition(k#x, v#x ASC NULLS FIRST, specifiedwindowframe(RangeFrame, unboundedpreceding$(), currentrow$())) AS bool_and(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x], [k#x], [v#x ASC NULLS FIRST]
      +- Project [k#x, v#x]
         +- SubqueryAlias test_agg
            +- View (`test_agg`, [k#x, v#x])
               +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
                  +- Project [k#x, v#x]
                     +- SubqueryAlias test_agg
                        +- LocalRelation [k#x, v#x]


-- !query
SELECT k, v, bool_or(v) OVER (PARTITION BY k ORDER BY v) FROM test_agg
-- !query analysis
Project [k#x, v#x, bool_or(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
+- Project [k#x, v#x, bool_or(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x, bool_or(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
   +- Window [bool_or(v#x) windowspecdefinition(k#x, v#x ASC NULLS FIRST, specifiedwindowframe(RangeFrame, unboundedpreceding$(), currentrow$())) AS bool_or(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x], [k#x], [v#x ASC NULLS FIRST]
      +- Project [k#x, v#x]
         +- SubqueryAlias test_agg
            +- View (`test_agg`, [k#x, v#x])
               +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
                  +- Project [k#x, v#x]
                     +- SubqueryAlias test_agg
                        +- LocalRelation [k#x, v#x]


-- !query
SELECT count(*) FROM test_agg HAVING count(*) > 1L
-- !query analysis
Filter (count(1)#xL > 1)
+- Aggregate [count(1) AS count(1)#xL]
   +- SubqueryAlias test_agg
      +- View (`test_agg`, [k#x, v#x])
         +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
            +- Project [k#x, v#x]
               +- SubqueryAlias test_agg
                  +- LocalRelation [k#x, v#x]


-- !query
SELECT k, max(v) FROM test_agg GROUP BY k HAVING max(v) = true
-- !query analysis
Filter (max(v)#x = true)
+- Aggregate [k#x], [k#x, max(v#x) AS max(v)#x]
   +- SubqueryAlias test_agg
      +- View (`test_agg`, [k#x, v#x])
         +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
            +- Project [k#x, v#x]
               +- SubqueryAlias test_agg
                  +- LocalRelation [k#x, v#x]


-- !query
SELECT * FROM (SELECT COUNT(*) AS cnt FROM test_agg) WHERE cnt > 1L
-- !query analysis
Project [cnt#xL]
+- Filter (cnt#xL > 1)
   +- SubqueryAlias __auto_generated_subquery_name
      +- Aggregate [count(1) AS cnt#xL]
         +- SubqueryAlias test_agg
            +- View (`test_agg`, [k#x, v#x])
               +- Project [cast(k#x as int) AS k#x, cast(v#x as boolean) AS v#x]
                  +- Project [k#x, v#x]
                     +- SubqueryAlias test_agg
                        +- LocalRelation [k#x, v#x]


-- !query
SELECT count(*) FROM test_agg WHERE count(*) > 1L
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "INVALID_WHERE_CONDITION",
  "sqlState" : "42903",
  "messageParameters" : {
    "condition" : "\"(count(1) > 1)\"",
    "expressionList" : "count(1)"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 1,
    "stopIndex" : 49,
    "fragment" : "SELECT count(*) FROM test_agg WHERE count(*) > 1L"
  } ]
}


-- !query
SELECT count(*) FROM test_agg WHERE count(*) + 1L > 1L
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "INVALID_WHERE_CONDITION",
  "sqlState" : "42903",
  "messageParameters" : {
    "condition" : "\"((count(1) + 1) > 1)\"",
    "expressionList" : "count(1)"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 1,
    "stopIndex" : 54,
    "fragment" : "SELECT count(*) FROM test_agg WHERE count(*) + 1L > 1L"
  } ]
}


-- !query
SELECT count(*) FROM test_agg WHERE k = 1 or k = 2 or count(*) + 1L > 1L or max(k) > 1
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "INVALID_WHERE_CONDITION",
  "sqlState" : "42903",
  "messageParameters" : {
    "condition" : "\"(((k = 1) OR (k = 2)) OR (((count(1) + 1) > 1) OR (max(k) > 1)))\"",
    "expressionList" : "count(1), max(test_agg.k)"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 1,
    "stopIndex" : 86,
    "fragment" : "SELECT count(*) FROM test_agg WHERE k = 1 or k = 2 or count(*) + 1L > 1L or max(k) > 1"
  } ]
}


-- !query
SELECT AVG(DISTINCT decimal_col), SUM(DISTINCT decimal_col) FROM VALUES (CAST(1 AS DECIMAL(9, 0))) t(decimal_col)
-- !query analysis
Aggregate [avg(distinct decimal_col#x) AS avg(DISTINCT decimal_col)#x, sum(distinct decimal_col#x) AS sum(DISTINCT decimal_col)#x]
+- SubqueryAlias t
   +- LocalRelation [decimal_col#x]


-- !query
SELECT not(a IS NULL), count(*) AS c
FROM testData
GROUP BY a IS NULL
-- !query analysis
Aggregate [isnull(a#x)], [NOT isnull(a#x) AS (NOT (a IS NULL))#x, count(1) AS c#xL]
+- SubqueryAlias testdata
   +- View (`testData`, [a#x, b#x])
      +- Project [cast(a#x as int) AS a#x, cast(b#x as int) AS b#x]
         +- Project [a#x, b#x]
            +- SubqueryAlias testData
               +- LocalRelation [a#x, b#x]


-- !query
SELECT if(not(a IS NULL), rand(0), 1), count(*) AS c
FROM testData
GROUP BY a IS NULL
-- !query analysis
[Analyzer test output redacted due to nondeterminism]


-- !query
SELECT
  histogram_numeric(col, 2) as histogram_2,
  histogram_numeric(col, 3) as histogram_3,
  histogram_numeric(col, 5) as histogram_5,
  histogram_numeric(col, 10) as histogram_10
FROM VALUES
 (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), (3), (37), (38), (39), (40),
 (41), (42), (43), (44), (45), (46), (47), (48), (49), (50) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#x, 2, 0, 0) AS histogram_2#x, histogram_numeric(col#x, 3, 0, 0) AS histogram_3#x, histogram_numeric(col#x, 5, 0, 0) AS histogram_5#x, histogram_numeric(col#x, 10, 0, 0) AS histogram_10#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT histogram_numeric(col, 3) FROM VALUES (1), (2), (3) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#x, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT histogram_numeric(col, 3) FROM VALUES (1L), (2L), (3L) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#xL, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#xL]


-- !query
SELECT histogram_numeric(col, 3) FROM VALUES (1F), (2F), (3F) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#x, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT histogram_numeric(col, 3) FROM VALUES (1D), (2D), (3D) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#x, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT histogram_numeric(col, 3) FROM VALUES (1S), (2S), (3S) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#x, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT histogram_numeric(col, 3) FROM VALUES
  (CAST(1 AS BYTE)), (CAST(2 AS BYTE)), (CAST(3 AS BYTE)) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#x, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT histogram_numeric(col, 3) FROM VALUES
  (CAST(1 AS TINYINT)), (CAST(2 AS TINYINT)), (CAST(3 AS TINYINT)) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#x, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT histogram_numeric(col, 3) FROM VALUES
  (CAST(1 AS SMALLINT)), (CAST(2 AS SMALLINT)), (CAST(3 AS SMALLINT)) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#x, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT histogram_numeric(col, 3) FROM VALUES
  (CAST(1 AS BIGINT)), (CAST(2 AS BIGINT)), (CAST(3 AS BIGINT)) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#xL, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#xL]


-- !query
SELECT histogram_numeric(col, 3) FROM VALUES (TIMESTAMP '2017-03-01 00:00:00'),
  (TIMESTAMP '2017-04-01 00:00:00'), (TIMESTAMP '2017-05-01 00:00:00') AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#x, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT histogram_numeric(col, 3) FROM VALUES (INTERVAL '100-00' YEAR TO MONTH),
  (INTERVAL '110-00' YEAR TO MONTH), (INTERVAL '120-00' YEAR TO MONTH) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#x, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT histogram_numeric(col, 3) FROM VALUES (INTERVAL '12 20:4:0' DAY TO SECOND),
  (INTERVAL '12 21:4:0' DAY TO SECOND), (INTERVAL '12 22:4:0' DAY TO SECOND) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#x, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT histogram_numeric(col, 3)
FROM VALUES (NULL), (NULL), (NULL) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(cast(col#x as double), 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT histogram_numeric(col, 3)
FROM VALUES (CAST(NULL AS DOUBLE)), (CAST(NULL AS DOUBLE)), (CAST(NULL AS DOUBLE)) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#x, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT histogram_numeric(col, 3)
FROM VALUES (CAST(NULL AS INT)), (CAST(NULL AS INT)), (CAST(NULL AS INT)) AS tab(col)
-- !query analysis
Aggregate [histogram_numeric(col#x, 3, 0, 0) AS histogram_numeric(col, 3)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT
  collect_list(col),
  array_agg(col)
FROM VALUES
  (1), (2), (1) AS tab(col)
-- !query analysis
Aggregate [collect_list(col#x, 0, 0) AS collect_list(col)#x, collect_list(col#x, 0, 0) AS collect_list(col)#x]
+- SubqueryAlias tab
   +- LocalRelation [col#x]


-- !query
SELECT
  a,
  collect_list(b),
  array_agg(b)
FROM VALUES
  (1,4),(2,3),(1,4),(2,4) AS v(a,b)
GROUP BY a
-- !query analysis
Aggregate [a#x], [a#x, collect_list(b#x, 0, 0) AS collect_list(b)#x, collect_list(b#x, 0, 0) AS collect_list(b)#x]
+- SubqueryAlias v
   +- LocalRelation [a#x, b#x]


-- !query
SELECT c * 2 AS d
FROM (
         SELECT if(b > 1, 1, b) AS c
         FROM (
                  SELECT if(a < 0, 0, a) AS b
                  FROM VALUES (-1), (1), (2) AS t1(a)
              ) t2
         GROUP BY b
     ) t3
GROUP BY c
-- !query analysis
Aggregate [c#x], [(c#x * 2) AS d#x]
+- SubqueryAlias t3
   +- Aggregate [b#x], [if ((b#x > 1)) 1 else b#x AS c#x]
      +- SubqueryAlias t2
         +- Project [if ((a#x < 0)) 0 else a#x AS b#x]
            +- SubqueryAlias t1
               +- LocalRelation [a#x]


-- !query
SELECT col1, count(*) AS cnt
FROM VALUES
  (0.0),
  (-0.0),
  (double('NaN')),
  (double('NaN')),
  (double('Infinity')),
  (double('Infinity')),
  (-double('Infinity')),
  (-double('Infinity'))
GROUP BY col1
ORDER BY col1
-- !query analysis
Sort [col1#x ASC NULLS FIRST], true
+- Aggregate [col1#x], [col1#x, count(1) AS cnt#xL]
   +- LocalRelation [col1#x]