python/pyspark/sql/tests/df_golden/group_by.test - master - githubmirror/spark

python/pyspark/sql/tests/df_golden/group_by.test
1 106 строк47 KB
Mihailo Aleksic
[SPARK-58037][PYTHON][TEST] Add DataFrame API golden file test framework for PySpark
15 июл 2026, 21:00
15 июл 2026, 21:00cd3b583
--! name
__file_metadata__
--! source
df_golden/group_by
!-- end


--! name
create testData view
--! script
scripts/group_by/setup_test_data.py
--! expected_analysis_output
LocalRelation <empty>
--! expected_optimized_output
LocalRelation <empty>
--! expected_output_schema
struct<>
--! expected_result
printed all 0 rows.
--! expected_result_hash
e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
!-- end


--! name
select non-grouping column without GROUP BY (error)
--! script
scripts/group_by/select_non_grouping_column.py
--! expected_error
[MISSING_GROUP_BY] The query does not include a GROUP BY clause. Add GROUP BY or turn it into the window functions using OVER clauses. SQLSTATE: 42803
!-- end


--! name
aggregate with empty GroupBy expressions
--! script
scripts/group_by/agg_counts_no_grouping.py
--! expected_analysis_output
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]
--! expected_optimized_output
Aggregate [count(a#x) AS count(a)#xL, count(b#x) AS count(b)#xL]
+- LocalRelation [a#x, b#x]
--! expected_output_schema
struct<count(a):bigint,count(b):bigint>
--! expected_result
+----------+----------+
| count(a) | count(b) |
+----------+----------+
| 7        | 7        |
+----------+----------+
printed all 1 rows.
--! expected_result_hash
f68f701c7611c45f2e32de9394884c63089bd8174e53a863a1b61168442dbaac
!-- end


--! name
aggregate with non-empty GroupBy expressions
--! tags
unordered
--! script
scripts/group_by/group_by_count.py
--! expected_analysis_output
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]
--! expected_optimized_output
Aggregate [a#x], [a#x, count(b#x) AS count(b)#xL]
+- LocalRelation [a#x, b#x]
--! expected_output_schema
struct<a:int,count(b):bigint>
--! expected_result
+------+----------+
| a    | count(b) |
+------+----------+
| 1    | 2        |
| 2    | 2        |
| 3    | 2        |
| NULL | 1        |
+------+----------+
printed all 4 rows.
--! expected_result_hash
18c7a56d93a03d040c2ccb08307e9d9b38d789a0cd654823cbe7659cb182fcf9
!-- end


--! name
non-aggregate column not in GROUP BY (error)
--! script
scripts/group_by/group_by_missing_aggregation.py
--! expected_error
[MISSING_AGGREGATION] The non-aggregating expression "a" is based on columns which are not participating in the GROUP BY clause.
Add the columns or the expression to the GROUP BY, aggregate the expression, or use "any_value(a)" if you do not care which of the values within a group is returned. SQLSTATE: 42803
!-- end


--! name
group by with multiple aggregates
--! tags
unordered
--! script
scripts/group_by/group_by_multiple_counts.py
--! expected_analysis_output
Aggregate [a#x], [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]
--! expected_optimized_output
Aggregate [a#x], [a#x, count(a#x) AS count(a)#xL, count(b#x) AS count(b)#xL]
+- LocalRelation [a#x, b#x]
--! expected_output_schema
struct<a:int,count(a):bigint,count(b):bigint>
--! expected_result
+------+----------+----------+
| a    | count(a) | count(b) |
+------+----------+----------+
| 1    | 2        | 2        |
| 2    | 2        | 2        |
| 3    | 3        | 2        |
| NULL | 0        | 1        |
+------+----------+----------+
printed all 4 rows.
--! expected_result_hash
978157032220537c5982e8c4ceda91f71f8cc1159b71bf8023c71622b591dc9e
!-- end


--! name
aggregate grouped by literal
--! script
scripts/group_by/group_by_literal.py
--! expected_analysis_output
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]
--! expected_optimized_output
Aggregate [0], [foo AS foo#x, count(a#x) AS count(a)#xL]
+- LocalRelation [a#x]
--! expected_output_schema
struct<foo:string,count(a):bigint>
--! expected_result
+-----+----------+
| foo | count(a) |
+-----+----------+
| foo | 7        |
+-----+----------+
printed all 1 rows.
--! expected_result_hash
56f7c8b96bd39b7def5a4b948f8551ccb63c76b3adbd7b7e2f3ea1e174312343
!-- end


--! name
aggregate grouped by literal (hash aggregate)
--! script
scripts/group_by/group_by_literal_hash_agg.py
--! expected_analysis_output
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]
--! expected_optimized_output
LocalRelation <empty>, [foo#x, approx_count_distinct(a)#xL]
--! expected_output_schema
struct<foo:string,approx_count_distinct(a):bigint>
--! expected_result
+-----+--------------------------+
| foo | approx_count_distinct(a) |
+-----+--------------------------+
+-----+--------------------------+
printed all 0 rows.
--! expected_result_hash
e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
!-- end


--! name
aggregate grouped by literal (sort aggregate)
--! script
scripts/group_by/group_by_literal_sort_agg.py
--! expected_analysis_output
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]
--! expected_optimized_output
LocalRelation <empty>, [foo#x, max(struct(a))#x]
--! expected_output_schema
struct<foo:string,max(struct(a)):struct<a:int>>
--! expected_result
+-----+----------------+
| foo | max(struct(a)) |
+-----+----------------+
+-----+----------------+
printed all 0 rows.
--! expected_result_hash
e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
!-- end


--! name
aggregate with complex GroupBy expression
--! tags
unordered
--! script
scripts/group_by/group_by_complex_expr.py
--! expected_analysis_output
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]
--! expected_optimized_output
Aggregate [_groupingexpression#x], [_groupingexpression#x AS (a + b)#x, count(b#x) AS count(b)#xL]
+- LocalRelation [b#x, _groupingexpression#x]
--! expected_output_schema
struct<(a + b):int,count(b):bigint>
--! expected_result
+---------+----------+
| (a + b) | count(b) |
+---------+----------+
| 2       | 1        |
| 3       | 2        |
| 4       | 2        |
| 5       | 1        |
| NULL    | 1        |
+---------+----------+
printed all 5 rows.
--! expected_result_hash
e5a84602fefae835a3f9378c2cb5e6c8a653f258181aca8e9e88f1653b75823a
!-- end


--! name
struct() in group by
--! tags
unordered
--! script
scripts/group_by/group_by_struct.py
--! expected_analysis_output
Aggregate [struct(aa, (cast(a#x as double) + 0.1))], [struct(aa, (cast(a#x as double) + 0.1)) AS struct((a + 0.1) AS aa)#x, 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]
--! expected_optimized_output
Aggregate [_groupingexpression#x], [_groupingexpression#x AS struct((a + 0.1) AS aa)#x, count(1) AS count(1)#xL]
+- LocalRelation [_groupingexpression#x]
--! expected_output_schema
struct<struct((a + 0.1) AS aa):struct<aa:double>,count(1):bigint>
--! expected_result
+-------------------------+----------+
| struct((a + 0.1) AS aa) | count(1) |
+-------------------------+----------+
| {"aa":1.1}              | 2        |
| {"aa":2.1}              | 2        |
| {"aa":3.1}              | 3        |
| {"aa":null}             | 2        |
+-------------------------+----------+
printed all 4 rows.
--! expected_result_hash
109ec364586f8c9f18e50baa49085b25107bad900acfe53183feff0968f4cc33
!-- end


--! name
aggregate with nulls
--! script
scripts/group_by/agg_stats_with_nulls.py
--! expected_analysis_output
Aggregate [round(skewness(cast(a#x as double)), 12) AS round(skewness(a), 12)#x, round(kurtosis(cast(a#x as double)), 12) AS round(kurtosis(a), 12)#x, min(a#x) AS min(a)#x, max(a#x) AS max(a)#x, round(avg(a#x), 12) AS round(avg(a), 12)#x, round(variance(cast(a#x as double)), 12) AS round(variance(a), 12)#x, round(stddev(cast(a#x as double)), 12) AS round(stddev(a), 12)#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]
--! expected_optimized_output
Aggregate [round(skewness(cast(a#x as double)), 12) AS round(skewness(a), 12)#x, round(kurtosis(cast(a#x as double)), 12) AS round(kurtosis(a), 12)#x, min(a#x) AS min(a)#x, max(a#x) AS max(a)#x, round(avg(a#x), 12) AS round(avg(a), 12)#x, round(variance(cast(a#x as double)), 12) AS round(variance(a), 12)#x, round(stddev(cast(a#x as double)), 12) AS round(stddev(a), 12)#x, sum(a#x) AS sum(a)#xL, count(a#x) AS count(a)#xL]
+- LocalRelation [a#x]
--! expected_output_schema
struct<round(skewness(a), 12):double,round(kurtosis(a), 12):double,min(a):int,max(a):int,round(avg(a), 12):double,round(variance(a), 12):double,round(stddev(a), 12):double,sum(a):bigint,count(a):bigint>
--! expected_result
+------------------------+------------------------+--------+--------+-------------------+------------------------+----------------------+--------+----------+
| round(skewness(a), 12) | round(kurtosis(a), 12) | min(a) | max(a) | round(avg(a), 12) | round(variance(a), 12) | round(stddev(a), 12) | sum(a) | count(a) |
+------------------------+------------------------+--------+--------+-------------------+------------------------+----------------------+--------+----------+
| -0.272380105815        | -1.506920415225        | 1      | 3      | 2.142857142857    | 0.809523809524         | 0.899735410842       | 15     | 7        |
+------------------------+------------------------+--------+--------+-------------------+------------------------+----------------------+--------+----------+
printed all 1 rows.
--! expected_result_hash
5b9fa3c2109f35b123ebd7b633649e02c09e73ed40574556e19a05e532838ab1
!-- end


--! name
aggregate with empty input and non-empty GroupBy expressions
--! script
scripts/group_by/empty_input_group_by.py
--! expected_analysis_output
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]
--! expected_optimized_output
LocalRelation <empty>, [a#x, count(1)#xL]
--! expected_output_schema
struct<a:int,count(1):bigint>
--! expected_result
+---+----------+
| a | count(1) |
+---+----------+
+---+----------+
printed all 0 rows.
--! expected_result_hash
e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
!-- end


--! name
aggregate with empty input and empty GroupBy expressions
--! script
scripts/group_by/empty_input_agg.py
--! expected_analysis_output
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]
--! expected_optimized_output
Aggregate [count(1) AS count(1)#xL]
+- LocalRelation <empty>
--! expected_output_schema
struct<count(1):bigint>
--! expected_result
+----------+
| count(1) |
+----------+
| 0        |
+----------+
printed all 1 rows.
--! expected_result_hash
5feceb66ffc86f38d952786c6d696c79c2dbc239dd4e91b46729d73a27fb57e9
!-- end


--! name
aggregate with empty input and constant projection
--! script
scripts/group_by/empty_input_agg_select.py
--! expected_analysis_output
Project [1 AS 1#x]
+- 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]
--! expected_optimized_output
Aggregate [1 AS 1#x]
+- LocalRelation <empty>
--! expected_output_schema
struct<1:int>
--! expected_result
+---+
| 1 |
+---+
| 1 |
+---+
printed all 1 rows.
--! expected_result_hash
6b86b273ff34fce19d6b804eff5a3f5747ada4eaa22f1d49c01e52ddb7875b4b
!-- end


--! name
create test_agg view
--! script
scripts/group_by/setup_test_agg.py
--! expected_analysis_output
LocalRelation <empty>
--! expected_optimized_output
LocalRelation <empty>
--! expected_output_schema
struct<>
--! expected_result
printed all 0 rows.
--! expected_result_hash
e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
!-- end


--! name
bool aggregates over empty table
--! script
scripts/group_by/bool_agg_empty_table.py
--! expected_analysis_output
Aggregate [every(v#x) AS every(v)#x, some(v#x) AS some(v)#x, bool_or(v#x) AS bool_or(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]
--! expected_optimized_output
Aggregate [min(v#x) AS every(v)#x, max(v#x) AS some(v)#x, max(v#x) AS bool_or(v)#x, min(v#x) AS bool_and(v)#x, max(v#x) AS bool_or(v)#x]
+- LocalRelation <empty>, [v#x]
--! expected_output_schema
struct<every(v):boolean,some(v):boolean,bool_or(v):boolean,bool_and(v):boolean,bool_or(v):boolean>
--! expected_result
+----------+---------+------------+-------------+------------+
| every(v) | some(v) | bool_or(v) | bool_and(v) | bool_or(v) |
+----------+---------+------------+-------------+------------+
| NULL     | NULL    | NULL       | NULL        | NULL       |
+----------+---------+------------+-------------+------------+
printed all 1 rows.
--! expected_result_hash
ad29e33ee1f3f4cfe263272e81d7c89ea08bdfb7aa45758a6adfc7752d80ab7e
!-- end


--! name
bool aggregates over all null values
--! script
scripts/group_by/bool_agg_all_nulls.py
--! expected_analysis_output
Aggregate [every(v#x) AS every(v)#x, some(v#x) AS some(v)#x, bool_or(v#x) AS bool_or(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]
--! expected_optimized_output
Aggregate [min(v#x) AS every(v)#x, max(v#x) AS some(v)#x, max(v#x) AS bool_or(v)#x, min(v#x) AS bool_and(v)#x, max(v#x) AS bool_or(v)#x]
+- LocalRelation [v#x]
--! expected_output_schema
struct<every(v):boolean,some(v):boolean,bool_or(v):boolean,bool_and(v):boolean,bool_or(v):boolean>
--! expected_result
+----------+---------+------------+-------------+------------+
| every(v) | some(v) | bool_or(v) | bool_and(v) | bool_or(v) |
+----------+---------+------------+-------------+------------+
| NULL     | NULL    | NULL       | NULL        | NULL       |
+----------+---------+------------+-------------+------------+
printed all 1 rows.
--! expected_result_hash
ad29e33ee1f3f4cfe263272e81d7c89ea08bdfb7aa45758a6adfc7752d80ab7e
!-- end


--! name
bool aggregates null filtering
--! script
scripts/group_by/bool_agg_null_filtering.py
--! expected_analysis_output
Aggregate [every(v#x) AS every(v)#x, some(v#x) AS some(v)#x, bool_or(v#x) AS bool_or(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]
--! expected_optimized_output
Aggregate [min(v#x) AS every(v)#x, max(v#x) AS some(v)#x, max(v#x) AS bool_or(v)#x, min(v#x) AS bool_and(v)#x, max(v#x) AS bool_or(v)#x]
+- LocalRelation [v#x]
--! expected_output_schema
struct<every(v):boolean,some(v):boolean,bool_or(v):boolean,bool_and(v):boolean,bool_or(v):boolean>
--! expected_result
+----------+---------+------------+-------------+------------+
| every(v) | some(v) | bool_or(v) | bool_and(v) | bool_or(v) |
+----------+---------+------------+-------------+------------+
| false    | true    | true       | false       | true       |
+----------+---------+------------+-------------+------------+
printed all 1 rows.
--! expected_result_hash
3b46bd9f792f9e10cf44f4797d0882661bf2ce33e75e52319637876a858c6eab
!-- end


--! name
bool aggregates with group by
--! tags
unordered
--! script
scripts/group_by/bool_agg_group_by.py
--! expected_analysis_output
Aggregate [k#x], [k#x, every(v#x) AS every(v)#x, some(v#x) AS some(v)#x, bool_or(v#x) AS bool_or(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]
--! expected_optimized_output
Aggregate [k#x], [k#x, min(v#x) AS every(v)#x, max(v#x) AS some(v)#x, max(v#x) AS bool_or(v)#x, min(v#x) AS bool_and(v)#x, max(v#x) AS bool_or(v)#x]
+- LocalRelation [k#x, v#x]
--! expected_output_schema
struct<k:int,every(v):boolean,some(v):boolean,bool_or(v):boolean,bool_and(v):boolean,bool_or(v):boolean>
--! expected_result
+---+----------+---------+------------+-------------+------------+
| k | every(v) | some(v) | bool_or(v) | bool_and(v) | bool_or(v) |
+---+----------+---------+------------+-------------+------------+
| 1 | false    | true    | true       | false       | true       |
| 2 | true     | true    | true       | true        | true       |
| 3 | false    | false   | false      | false       | false      |
| 4 | NULL     | NULL    | NULL       | NULL        | NULL       |
| 5 | false    | true    | true       | false       | true       |
+---+----------+---------+------------+-------------+------------+
printed all 5 rows.
--! expected_result_hash
5dc9ed24b5544acb569b58286aec4265e8f921fe147a7ff12e5d456d4d5ec282
!-- end


--! name
bool aggregates with having false
--! tags
unordered
--! script
scripts/group_by/bool_agg_having_false.py
--! expected_analysis_output
Project [k#x, every_v#x]
+- 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]
--! expected_optimized_output
Filter (isnotnull(every_v#x) AND NOT every_v#x)
+- Aggregate [k#x], [k#x, min(v#x) AS every_v#x]
   +- LocalRelation [k#x, v#x]
--! expected_output_schema
struct<k:int,every_v:boolean>
--! expected_result
+---+---------+
| k | every_v |
+---+---------+
| 1 | false   |
| 3 | false   |
| 5 | false   |
+---+---------+
printed all 3 rows.
--! expected_result_hash
b38b91ef7aec43d0957f73891494f8c09519ffe4731fb729a0569c7c2d970118
!-- end


--! name
bool aggregates with having null
--! script
scripts/group_by/bool_agg_having_null.py
--! expected_analysis_output
Project [k#x, every_v#x]
+- 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]
--! expected_optimized_output
Filter isnull(every_v#x)
+- Aggregate [k#x], [k#x, min(v#x) AS every_v#x]
   +- LocalRelation [k#x, v#x]
--! expected_output_schema
struct<k:int,every_v:boolean>
--! expected_result
+---+---------+
| k | every_v |
+---+---------+
| 4 | NULL    |
+---+---------+
printed all 1 rows.
--! expected_result_hash
3c3c8c2bd86b08b2f86cf8a0f0796bed2e3989c7d939ee2630e6dbace881b365
!-- end


--! name
every input type checking: int (error)
--! script
scripts/group_by/every_int_error.py
--! expected_error
[DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] Cannot resolve "every(1)" due to data type mismatch: The first parameter requires the "BOOLEAN" type, however "1" has the type "INT". SQLSTATE: 42K09
!-- end


--! name
some input type checking: int (error)
--! script
scripts/group_by/some_int_error.py
--! expected_error
[DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] Cannot resolve "some(1)" due to data type mismatch: The first parameter requires the "BOOLEAN" type, however "1" has the type "INT". SQLSTATE: 42K09
!-- end


--! name
bool_or input type checking: int (error)
--! script
scripts/group_by/bool_or_int_error.py
--! expected_error
[DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] Cannot resolve "bool_or(1)" due to data type mismatch: The first parameter requires the "BOOLEAN" type, however "1" has the type "INT". SQLSTATE: 42K09
!-- end


--! name
every input type checking: string
--! script
scripts/group_by/every_string.py
--! expected_analysis_output
Aggregate [every(cast(true as boolean)) AS every(true)#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]
--! expected_optimized_output
Aggregate [min(true) AS every(true)#x]
+- LocalRelation
--! expected_output_schema
struct<every(true):boolean>
--! expected_result
+-------------+
| every(true) |
+-------------+
| true        |
+-------------+
printed all 1 rows.
--! expected_result_hash
b5bea41b6c623f7c09f1bf24dcae58ebab3c0cdd90ad966bc43a45b44867e12b
!-- end


--! name
bool_and input type checking: decimal (error)
--! script
scripts/group_by/bool_and_decimal_error.py
--! expected_error
[DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] Cannot resolve "bool_and(1.0)" due to data type mismatch: The first parameter requires the "BOOLEAN" type, however "1.0" has the type "DECIMAL(10,1)". SQLSTATE: 42K09
!-- end


--! name
bool_or input type checking: double (error)
--! script
scripts/group_by/bool_or_double_error.py
--! expected_error
[DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] Cannot resolve "bool_or(1.0)" due to data type mismatch: The first parameter requires the "BOOLEAN" type, however "1.0" has the type "DOUBLE". SQLSTATE: 42K09
!-- end


--! name
every as window expression
--! tags
unordered
--! script
scripts/group_by/every_window.py
--! expected_analysis_output
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]
--! expected_optimized_output
Window [min(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]
+- LocalRelation [k#x, v#x]
--! expected_output_schema
struct<k:int,v:boolean,every(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):boolean>
--! expected_result
+---+-------+-------------------------------------------------------------------------------------------------------------+
| k | v     | every(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
+---+-------+-------------------------------------------------------------------------------------------------------------+
| 1 | false | false                                                                                                       |
| 1 | true  | false                                                                                                       |
| 2 | true  | true                                                                                                        |
| 3 | NULL  | NULL                                                                                                        |
| 3 | false | false                                                                                                       |
| 4 | NULL  | NULL                                                                                                        |
| 4 | NULL  | NULL                                                                                                        |
| 5 | NULL  | NULL                                                                                                        |
| 5 | false | false                                                                                                       |
| 5 | true  | false                                                                                                       |
+---+-------+-------------------------------------------------------------------------------------------------------------+
printed all 10 rows.
--! expected_result_hash
1ad0a462e3a29e3f796db5ad23d8d3970b0d1eb9085231fcc4fcf8e128cf2b2d
!-- end


--! name
some as window expression
--! tags
unordered
--! script
scripts/group_by/some_window.py
--! expected_analysis_output
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]
--! expected_optimized_output
Window [max(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]
+- LocalRelation [k#x, v#x]
--! expected_output_schema
struct<k:int,v:boolean,some(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):boolean>
--! expected_result
+---+-------+------------------------------------------------------------------------------------------------------------+
| k | v     | some(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
+---+-------+------------------------------------------------------------------------------------------------------------+
| 1 | false | false                                                                                                      |
| 1 | true  | true                                                                                                       |
| 2 | true  | true                                                                                                       |
| 3 | NULL  | NULL                                                                                                       |
| 3 | false | false                                                                                                      |
| 4 | NULL  | NULL                                                                                                       |
| 4 | NULL  | NULL                                                                                                       |
| 5 | NULL  | NULL                                                                                                       |
| 5 | false | false                                                                                                      |
| 5 | true  | true                                                                                                       |
+---+-------+------------------------------------------------------------------------------------------------------------+
printed all 10 rows.
--! expected_result_hash
9a682e38910e199dade7fbe0fd8eb481cf3e924ef37e09ef0c6143b82e9b660e
!-- end


--! name
bool_or as window expression
--! tags
unordered
--! script
scripts/group_by/bool_or_window.py
--! expected_analysis_output
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]
--! expected_optimized_output
Window [max(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]
+- LocalRelation [k#x, v#x]
--! expected_output_schema
struct<k:int,v:boolean,bool_or(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):boolean>
--! expected_result
+---+-------+---------------------------------------------------------------------------------------------------------------+
| k | v     | bool_or(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
+---+-------+---------------------------------------------------------------------------------------------------------------+
| 1 | false | false                                                                                                         |
| 1 | true  | true                                                                                                          |
| 2 | true  | true                                                                                                          |
| 3 | NULL  | NULL                                                                                                          |
| 3 | false | false                                                                                                         |
| 4 | NULL  | NULL                                                                                                          |
| 4 | NULL  | NULL                                                                                                          |
| 5 | NULL  | NULL                                                                                                          |
| 5 | false | false                                                                                                         |
| 5 | true  | true                                                                                                          |
+---+-------+---------------------------------------------------------------------------------------------------------------+
printed all 10 rows.
--! expected_result_hash
9a682e38910e199dade7fbe0fd8eb481cf3e924ef37e09ef0c6143b82e9b660e
!-- end


--! name
bool_and as window expression
--! tags
unordered
--! script
scripts/group_by/bool_and_window.py
--! expected_analysis_output
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]
--! expected_optimized_output
Window [min(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]
+- LocalRelation [k#x, v#x]
--! expected_output_schema
struct<k:int,v:boolean,bool_and(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):boolean>
--! expected_result
+---+-------+----------------------------------------------------------------------------------------------------------------+
| k | v     | bool_and(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
+---+-------+----------------------------------------------------------------------------------------------------------------+
| 1 | false | false                                                                                                          |
| 1 | true  | false                                                                                                          |
| 2 | true  | true                                                                                                           |
| 3 | NULL  | NULL                                                                                                           |
| 3 | false | false                                                                                                          |
| 4 | NULL  | NULL                                                                                                           |
| 4 | NULL  | NULL                                                                                                           |
| 5 | NULL  | NULL                                                                                                           |
| 5 | false | false                                                                                                          |
| 5 | true  | false                                                                                                          |
+---+-------+----------------------------------------------------------------------------------------------------------------+
printed all 10 rows.
--! expected_result_hash
1ad0a462e3a29e3f796db5ad23d8d3970b0d1eb9085231fcc4fcf8e128cf2b2d
!-- end


--! name
bool_or as window expression (repeated)
--! tags
unordered
--! script
scripts/group_by/bool_or_window_repeated.py
--! expected_analysis_output
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]
--! expected_optimized_output
Window [max(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]
+- LocalRelation [k#x, v#x]
--! expected_output_schema
struct<k:int,v:boolean,bool_or(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):boolean>
--! expected_result
+---+-------+---------------------------------------------------------------------------------------------------------------+
| k | v     | bool_or(v) OVER (PARTITION BY k ORDER BY v ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
+---+-------+---------------------------------------------------------------------------------------------------------------+
| 1 | false | false                                                                                                         |
| 1 | true  | true                                                                                                          |
| 2 | true  | true                                                                                                          |
| 3 | NULL  | NULL                                                                                                          |
| 3 | false | false                                                                                                         |
| 4 | NULL  | NULL                                                                                                          |
| 4 | NULL  | NULL                                                                                                          |
| 5 | NULL  | NULL                                                                                                          |
| 5 | false | false                                                                                                         |
| 5 | true  | true                                                                                                          |
+---+-------+---------------------------------------------------------------------------------------------------------------+
printed all 10 rows.
--! expected_result_hash
9a682e38910e199dade7fbe0fd8eb481cf3e924ef37e09ef0c6143b82e9b660e
!-- end


--! name
having referencing aggregate expression
--! script
scripts/group_by/having_agg_count.py
--! expected_analysis_output
Project [cnt#xL]
+- Filter (cnt#xL > cast(1 as bigint))
   +- Aggregate [count(k#x) 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]
--! expected_optimized_output
Filter (cnt#xL > 1)
+- Aggregate [count(1) AS cnt#xL]
   +- LocalRelation
--! expected_output_schema
struct<cnt:bigint>
--! expected_result
+-----+
| cnt |
+-----+
| 10  |
+-----+
printed all 1 rows.
--! expected_result_hash
4a44dc15364204a80fe80e9039455cc1608281820fe2b24f1e5233ade6af1dd5
!-- end


--! name
having on aliased max
--! tags
unordered
--! script
scripts/group_by/having_max_v.py
--! expected_analysis_output
Project [k#x, max_v#x]
+- 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]
--! expected_optimized_output
Filter (isnotnull(max_v#x) AND max_v#x)
+- Aggregate [k#x], [k#x, max(v#x) AS max_v#x]
   +- LocalRelation [k#x, v#x]
--! expected_output_schema
struct<k:int,max_v:boolean>
--! expected_result
+---+-------+
| k | max_v |
+---+-------+
| 1 | true  |
| 2 | true  |
| 5 | true  |
+---+-------+
printed all 3 rows.
--! expected_result_hash
d6212a5d216fffb25342120cad425c6ebe96fc8485c6b082be8624faf6a531c4
!-- end


--! name
aggregate expression referenced through alias
--! script
scripts/group_by/agg_alias_filter.py
--! expected_analysis_output
Filter (cnt#xL > cast(1 as bigint))
+- Aggregate [count(k#x) 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]
--! expected_optimized_output
Filter (cnt#xL > 1)
+- Aggregate [count(1) AS cnt#xL]
   +- LocalRelation
--! expected_output_schema
struct<cnt:bigint>
--! expected_result
+-----+
| cnt |
+-----+
| 10  |
+-----+
printed all 1 rows.
--! expected_result_hash
4a44dc15364204a80fe80e9039455cc1608281820fe2b24f1e5233ade6af1dd5
!-- end


--! name
SPARK-34581: do not optimize out grouping expressions
--! tags
unordered
--! script
scripts/group_by/grouping_exprs_not_optimized.py
--! expected_analysis_output
Aggregate [isnull(a#x)], [isnull(a#x) AS (a IS NULL)#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]
--! expected_optimized_output
Aggregate [_groupingexpression#x], [_groupingexpression#x AS (a IS NULL)#x, NOT _groupingexpression#x AS (NOT (a IS NULL))#x, count(1) AS c#xL]
+- LocalRelation [_groupingexpression#x]
--! expected_output_schema
struct<(a IS NULL):boolean,(NOT (a IS NULL)):boolean,c:bigint>
--! expected_result
+-------------+-------------------+---+
| (a IS NULL) | (NOT (a IS NULL)) | c |
+-------------+-------------------+---+
| false       | true              | 7 |
| true        | false             | 2 |
+-------------+-------------------+---+
printed all 2 rows.
--! expected_result_hash
c5421419b0d389431648774e2aa0156b8472da926b1dd3cd50c94821001e7bcd
!-- end


--! name
PullOutGroupingExpressions pulls out grouping expressions
--! tags
unordered
--! script
scripts/group_by/pull_out_grouping_exprs.py
--! expected_analysis_output
Aggregate [isnull(a#x)], [isnull(a#x) AS (a IS NULL)#x, CASE WHEN NOT isnull(a#x) THEN 0 ELSE 1 END AS CASE WHEN (NOT (a IS NULL)) THEN 0 ELSE 1 END#x, first(isnull(a#x), false) AS c#x]
+- 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]
--! expected_optimized_output
Aggregate [_groupingexpression#x], [_groupingexpression#x AS (a IS NULL)#x, CASE WHEN NOT _groupingexpression#x THEN 0 ELSE 1 END AS CASE WHEN (NOT (a IS NULL)) THEN 0 ELSE 1 END#x, first(isnull(a#x), false) AS c#x]
+- LocalRelation [a#x, _groupingexpression#x]
--! expected_output_schema
struct<(a IS NULL):boolean,CASE WHEN (NOT (a IS NULL)) THEN 0 ELSE 1 END:int,c:boolean>
--! expected_result
+-------------+-----------------------------------------------+-------+
| (a IS NULL) | CASE WHEN (NOT (a IS NULL)) THEN 0 ELSE 1 END | c     |
+-------------+-----------------------------------------------+-------+
| false       | 0                                             | false |
| true        | 1                                             | true  |
+-------------+-----------------------------------------------+-------+
printed all 2 rows.
--! expected_result_hash
ce61427fcddb697480c2921f7450c541ac0bf1fe284b7e88bd9d784a84a2120a
!-- end


--! name
count_if with grouping expression reference
--! tags
unordered
--! script
scripts/group_by/count_if_group_by.py
--! expected_analysis_output
Aggregate [(a#x + 1)], [(a#x + 1) AS (a + 1)#x, count_if(((a#x + 1) = b#x)) AS count_if(((a + 1) = 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]
--! expected_optimized_output
Aggregate [_groupingexpression#x], [_groupingexpression#x AS (a + 1)#x, count(if (NOT _common_expr_0#x) null else _common_expr_0#x) AS count_if(((a + 1) = b))#xL]
+- LocalRelation [_groupingexpression#x, _common_expr_0#x]
--! expected_output_schema
struct<(a + 1):int,count_if(((a + 1) = b)):bigint>
--! expected_result
+---------+-------------------------+
| (a + 1) | count_if(((a + 1) = b)) |
+---------+-------------------------+
| 2       | 1                       |
| 3       | 0                       |
| 4       | 0                       |
| NULL    | 0                       |
+---------+-------------------------+
printed all 4 rows.
--! expected_result_hash
a71fb4e2edd0d56bc2b849747cc6dc084d03786bfaed0681f6ee9b564f09f089
!-- end