-- 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<a:int,rand(0):double,sum(b):bigint>
-- !query 13 output
1	0.4048454303385226	2
1	0.8446490682263027	1
2	0.5871875724155838	1
2	0.8865128837019473	2
3	0.742083829230211	1
3	0.9179913208300406	2


-- !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	false


-- !query 18
select sum(b) from data group by -1
-- !query 18 schema
struct<sum(b):bigint>
-- !query 18 output
9