-- Automatically generated by SQLQueryTestSuite
-- Number of queries: 19
-- !query 0
create temporary view data as select * from values
(1, 1),
(1, 2),
(2, 1),
(2, 2),
(3, 1),
(3, 2)
as data(a, b)
-- !query 0 schema
struct<>
-- !query 0 output
-- !query 1
select a, sum(b) from data group by 1
-- !query 1 schema
struct<a:int,sum(b):bigint>
-- !query 1 output
1 3
2 3
3 3
-- !query 2
select 1, 2, sum(b) from data group by 1, 2
-- !query 2 schema
struct<1:int,2:int,sum(b):bigint>
-- !query 2 output
1 2 9
-- !query 3
select a, 1, sum(b) from data group by a, 1
-- !query 3 schema
struct<a:int,1:int,sum(b):bigint>
-- !query 3 output
1 1 3
2 1 3
3 1 3
-- !query 4
select a, 1, sum(b) from data group by 1, 2
-- !query 4 schema
struct<a:int,1:int,sum(b):bigint>
-- !query 4 output
1 1 3
2 1 3
3 1 3
-- !query 5
select a, b + 2, count(2) from data group by a, 2
-- !query 5 schema
struct<a:int,(b + 2):int,count(2):bigint>
-- !query 5 output
1 3 1
1 4 1
2 3 1
2 4 1
3 3 1
3 4 1
-- !query 6
select a as aa, b + 2 as bb, count(2) from data group by 1, 2
-- !query 6 schema
struct<aa:int,bb:int,count(2):bigint>
-- !query 6 output
1 3 1
1 4 1
2 3 1
2 4 1
3 3 1
3 4 1
-- !query 7
select sum(b) from data group by 1 + 0
-- !query 7 schema
struct<sum(b):bigint>
-- !query 7 output
9
-- !query 8
select a, b from data group by -1
-- !query 8 schema
struct<>
-- !query 8 output
org.apache.spark.sql.AnalysisException
GROUP BY position -1 is not in select list (valid range is [1, 2]); line 1 pos 31
-- !query 9
select a, b from data group by 0
-- !query 9 schema
struct<>
-- !query 9 output
org.apache.spark.sql.AnalysisException
GROUP BY position 0 is not in select list (valid range is [1, 2]); line 1 pos 31
-- !query 10
select a, b from data group by 3
-- !query 10 schema
struct<>
-- !query 10 output
org.apache.spark.sql.AnalysisException
GROUP BY position 3 is not in select list (valid range is [1, 2]); line 1 pos 31
-- !query 11
select a, b, sum(b) from data group by 3
-- !query 11 schema
struct<>
-- !query 11 output
org.apache.spark.sql.AnalysisException
GROUP BY position 3 is an aggregate function, and aggregate functions are not allowed in GROUP BY; line 1 pos 39
-- !query 12
select a, b, sum(b) + 2 from data group by 3
-- !query 12 schema
struct<>
-- !query 12 output
org.apache.spark.sql.AnalysisException
GROUP BY position 3 is an aggregate function, and aggregate functions are not allowed in GROUP BY; line 1 pos 43
-- !query 13
select a, rand(0), sum(b) from data group by a, 2
-- !query 13 schema
struct<>
-- !query 13 output
org.apache.spark.sql.AnalysisException
nondeterministic expression rand(0) should not appear in grouping expression.;
-- !query 14
select * from data group by a, b, 1
-- !query 14 schema
struct<>
-- !query 14 output
org.apache.spark.sql.AnalysisException
Star (*) is not allowed in select list when GROUP BY ordinal position is used;
-- !query 15
select a, count(a) from (select 1 as a) tmp group by 1 order by 1
-- !query 15 schema
struct<a:int,count(a):bigint>
-- !query 15 output
1 1
-- !query 16
select count(a), a from (select 1 as a) tmp group by 2 having a > 0
-- !query 16 schema
struct<count(a):bigint,a:int>
-- !query 16 output
1 1
-- !query 17
set spark.sql.groupByOrdinal=false
-- !query 17 schema
struct<key:string,value:string>
-- !query 17 output
spark.sql.groupByOrdinal
-- !query 18
select sum(b) from data group by -1
-- !query 18 schema
struct<sum(b):bigint>
-- !query 18 output
9
