group-by-ordinal.sql.out

-- Automatically generated by SQLQueryTestSuite
-- Number of queries: 20


-- !query
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 schema
struct<>
-- !query output



-- !query
select a, sum(b) from data group by 1
-- !query schema
struct<a:int,sum(b):bigint>
-- !query output
1	3
2	3
3	3


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


-- !query
select a, 1, sum(b) from data group by a, 1
-- !query schema
struct<a:int,1:int,sum(b):bigint>
-- !query output
1	1	3
2	1	3
3	1	3


-- !query
select a, 1, sum(b) from data group by 1, 2
-- !query schema
struct<a:int,1:int,sum(b):bigint>
-- !query output
1	1	3
2	1	3
3	1	3


-- !query
select a, b + 2, count(2) from data group by a, 2
-- !query schema
struct<a:int,(b + 2):int,count(2):bigint>
-- !query output
1	3	1
1	4	1
2	3	1
2	4	1
3	3	1
3	4	1


-- !query
select a as aa, b + 2 as bb, count(2) from data group by 1, 2
-- !query schema
struct<aa:int,bb:int,count(2):bigint>
-- !query output
1	3	1
1	4	1
2	3	1
2	4	1
3	3	1
3	4	1


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


-- !query
select a, b from data group by -1
-- !query schema
struct<>
-- !query 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
select a, b from data group by 0
-- !query schema
struct<>
-- !query 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
select a, b from data group by 3
-- !query schema
struct<>
-- !query 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
select a, b, sum(b) from data group by 3
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
aggregate functions are not allowed in GROUP BY, but found sum(data.`b`)


-- !query
select a, b, sum(b) + 2 from data group by 3
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
aggregate functions are not allowed in GROUP BY, but found (sum(data.`b`) + CAST(2 AS BIGINT))


-- !query
select a, rand(0), sum(b)
from 
(select /*+ REPARTITION(1) */ a, b from data) group by a, 2
-- !query schema
struct<a:int,rand(0):double,sum(b):bigint>
-- !query output
1	0.5234194256885571	2
1	0.7604953758285915	1
2	0.0953472826424725	1
2	0.3163249920547614	2
3	0.2710259815484829	2
3	0.7141011170991605	1


-- !query
select * from data group by a, b, 1
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
Star (*) is not allowed in select list when GROUP BY ordinal position is used


-- !query
select a, count(a) from (select 1 as a) tmp group by 1 order by 1
-- !query schema
struct<a:int,count(a):bigint>
-- !query output
1	1


-- !query
select count(a), a from (select 1 as a) tmp group by 2 having a > 0
-- !query schema
struct<count(a):bigint,a:int>
-- !query output
1	1


-- !query
select a, a AS k, count(b) from data group by k, 1
-- !query schema
struct<a:int,k:int,count(b):bigint>
-- !query output
1	1	2
2	2	2
3	3	2


-- !query
set spark.sql.groupByOrdinal=false
-- !query schema
struct<key:string,value:string>
-- !query output
spark.sql.groupByOrdinal	false


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