query_optimizer/tests/resolver/Aggregate.test

# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License.  You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.

# This file contains queries that test aggregation in SELECT.
# Update.test, Delete.test contain other tests for aggregation in UPDATE and DELETE.
[default initial_logical_plan]
select count(*), count(int_col), sum(long_col), avg(int_col), max(double_col), min(float_col)
from test
--
TopLevelPlan
+-plan=Project
| +-input=Aggregate
| | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | |   type=VarChar(20) NULL]
| | +-grouping_expressions=
| | | +-[]
| | +-aggregate_expressions=
| |   +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| |   | +-AggregateFunction[function=COUNT]
| |   |   +-[]
| |   +-Alias[id=7,name=,alias=$aggregate1,relation=$aggregate,type=Long]
| |   | +-AggregateFunction[function=COUNT]
| |   |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| |   +-Alias[id=8,name=,alias=$aggregate2,relation=$aggregate,type=Long NULL]
| |   | +-AggregateFunction[function=SUM]
| |   |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| |   +-Alias[id=9,name=,alias=$aggregate3,relation=$aggregate,type=Double NULL]
| |   | +-AggregateFunction[function=AVG]
| |   |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| |   +-Alias[id=10,name=,alias=$aggregate4,relation=$aggregate,type=Double NULL]
| |   | +-AggregateFunction[function=MAX]
| |   |   +-AttributeReference[id=3,name=double_col,relation=test,
| |   |     type=Double NULL]
| |   +-Alias[id=11,name=,alias=$aggregate5,relation=$aggregate,type=Float NULL]
| |     +-AggregateFunction[function=MIN]
| |       +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| +-project_list=
|   +-Alias[id=6,name=,alias=count(*),relation=,type=Long]
|   | +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
|   |   type=Long]
|   +-Alias[id=7,name=,alias=count(int_col),relation=,type=Long]
|   | +-AttributeReference[id=7,name=,alias=$aggregate1,relation=$aggregate,
|   |   type=Long]
|   +-Alias[id=8,name=,alias=sum(long_col),relation=,type=Long NULL]
|   | +-AttributeReference[id=8,name=,alias=$aggregate2,relation=$aggregate,
|   |   type=Long NULL]
|   +-Alias[id=9,name=,alias=avg(int_col),relation=,type=Double NULL]
|   | +-AttributeReference[id=9,name=,alias=$aggregate3,relation=$aggregate,
|   |   type=Double NULL]
|   +-Alias[id=10,name=,alias=max(double_col),relation=,type=Double NULL]
|   | +-AttributeReference[id=10,name=,alias=$aggregate4,relation=$aggregate,
|   |   type=Double NULL]
|   +-Alias[id=11,name=,alias=min(float_col),relation=,type=Float NULL]
|     +-AttributeReference[id=11,name=,alias=$aggregate5,relation=$aggregate,
|       type=Float NULL]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=count(*),relation=,type=Long]
  +-AttributeReference[id=7,name=,alias=count(int_col),relation=,type=Long]
  +-AttributeReference[id=8,name=,alias=sum(long_col),relation=,type=Long NULL]
  +-AttributeReference[id=9,name=,alias=avg(int_col),relation=,type=Double NULL]
  +-AttributeReference[id=10,name=,alias=max(double_col),relation=,
  | type=Double NULL]
  +-AttributeReference[id=11,name=,alias=min(float_col),relation=,
    type=Float NULL]
==

select count() from test
--
ERROR: COUNT aggregate requires an argument (either scalar or star (*)) (1 : 8)
select count() from test
       ^
==

# COUNT(NULL) is valid, because the return type for COUNT is always int64 and the type of NULL does not matter.
select count(null), count(1) from test
--
TopLevelPlan
+-plan=Project
| +-input=Aggregate
| | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | |   type=VarChar(20) NULL]
| | +-grouping_expressions=
| | | +-[]
| | +-aggregate_expressions=
| |   +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| |   | +-AggregateFunction[function=COUNT]
| |   |   +-Literal[value=NULL,type=NullType NULL]
| |   +-Alias[id=7,name=,alias=$aggregate1,relation=$aggregate,type=Long]
| |     +-AggregateFunction[function=COUNT]
| |       +-Literal[value=1,type=Int]
| +-project_list=
|   +-Alias[id=6,name=,alias=count(NULL),relation=,type=Long]
|   | +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
|   |   type=Long]
|   +-Alias[id=7,name=,alias=count(1),relation=,type=Long]
|     +-AttributeReference[id=7,name=,alias=$aggregate1,relation=$aggregate,
|       type=Long]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=count(NULL),relation=,type=Long]
  +-AttributeReference[id=7,name=,alias=count(1),relation=,type=Long]
==

select sum(null) from test
--
ERROR: Aggregate function SUM can not apply to the given argument(s). (1 : 8)
select sum(null) from test
       ^
==

select sum(char_col) from test
--
ERROR: Aggregate function SUM can not apply to the given argument(s). (1 : 8)
select sum(char_col) from test
       ^
==

select max(vchar_col) from test
--
TopLevelPlan
+-plan=Project
| +-input=Aggregate
| | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | |   type=VarChar(20) NULL]
| | +-grouping_expressions=
| | | +-[]
| | +-aggregate_expressions=
| |   +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,
| |     type=VarChar(20) NULL]
| |     +-AggregateFunction[function=MAX]
| |       +-AttributeReference[id=5,name=vchar_col,relation=test,
| |         type=VarChar(20) NULL]
| +-project_list=
|   +-Alias[id=6,name=,alias=max(vchar_col),relation=,type=VarChar(20) NULL]
|     +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
|       type=VarChar(20) NULL]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=max(vchar_col),relation=,
    type=VarChar(20) NULL]
==

select sum(*) from test
--
ERROR: Only COUNT can have star (*) as an argument (1 : 12)
select sum(*) from test
           ^
==

select sum(int_col, long_col) from test
--
ERROR: Aggregate function SUM can not apply to the given argument(s). (1 : 8)
select sum(int_col, long_col) from tes...
       ^
==

select undefined_fun(int_col) from test
--
ERROR: Unrecognized function name "undefined_fun" (1 : 8)
select undefined_fun(int_col) from tes...
       ^
==

# If there is no GROUP BY, an aggregate is nullable (except Count).
select sum(long_col), avg(float_col), count(int_col) from test
--
TopLevelPlan
+-plan=Project
| +-input=Aggregate
| | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | |   type=VarChar(20) NULL]
| | +-grouping_expressions=
| | | +-[]
| | +-aggregate_expressions=
| |   +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
| |   | +-AggregateFunction[function=SUM]
| |   |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| |   +-Alias[id=7,name=,alias=$aggregate1,relation=$aggregate,type=Double NULL]
| |   | +-AggregateFunction[function=AVG]
| |   |   +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| |   +-Alias[id=8,name=,alias=$aggregate2,relation=$aggregate,type=Long]
| |     +-AggregateFunction[function=COUNT]
| |       +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| +-project_list=
|   +-Alias[id=6,name=,alias=sum(long_col),relation=,type=Long NULL]
|   | +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
|   |   type=Long NULL]
|   +-Alias[id=7,name=,alias=avg(float_col),relation=,type=Double NULL]
|   | +-AttributeReference[id=7,name=,alias=$aggregate1,relation=$aggregate,
|   |   type=Double NULL]
|   +-Alias[id=8,name=,alias=count(int_col),relation=,type=Long]
|     +-AttributeReference[id=8,name=,alias=$aggregate2,relation=$aggregate,
|       type=Long]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=sum(long_col),relation=,type=Long NULL]
  +-AttributeReference[id=7,name=,alias=avg(float_col),relation=,
  | type=Double NULL]
  +-AttributeReference[id=8,name=,alias=count(int_col),relation=,type=Long]
==

# If there is a GROUP BY, the nullability of an aggregate usually depends on the input expression (except Count which
# is always not nullable).
select sum(long_col), sum(double_col), count(int_col) from test group by char_col
--
TopLevelPlan
+-plan=Project
| +-input=Aggregate
| | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | |   type=VarChar(20) NULL]
| | +-grouping_expressions=
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | +-aggregate_expressions=
| |   +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
| |   | +-AggregateFunction[function=SUM]
| |   |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| |   +-Alias[id=7,name=,alias=$aggregate1,relation=$aggregate,type=Double NULL]
| |   | +-AggregateFunction[function=SUM]
| |   |   +-AttributeReference[id=3,name=double_col,relation=test,
| |   |     type=Double NULL]
| |   +-Alias[id=8,name=,alias=$aggregate2,relation=$aggregate,type=Long]
| |     +-AggregateFunction[function=COUNT]
| |       +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| +-project_list=
|   +-Alias[id=6,name=,alias=sum(long_col),relation=,type=Long NULL]
|   | +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
|   |   type=Long NULL]
|   +-Alias[id=7,name=,alias=sum(double_col),relation=,type=Double NULL]
|   | +-AttributeReference[id=7,name=,alias=$aggregate1,relation=$aggregate,
|   |   type=Double NULL]
|   +-Alias[id=8,name=,alias=count(int_col),relation=,type=Long]
|     +-AttributeReference[id=8,name=,alias=$aggregate2,relation=$aggregate,
|       type=Long]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=sum(long_col),relation=,type=Long NULL]
  +-AttributeReference[id=7,name=,alias=sum(double_col),relation=,
  | type=Double NULL]
  +-AttributeReference[id=8,name=,alias=count(int_col),relation=,type=Long]
==

select 1+count(*)/sum(long_col+float_col/2)*count(float_col) from test
having max(float_col+int_col)+min(double_col) > count(int_col)
--
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-input=Aggregate
| | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | | |   type=VarChar(20) NULL]
| | | +-grouping_expressions=
| | | | +-[]
| | | +-aggregate_expressions=
| | |   +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| | |   | +-AggregateFunction[function=COUNT]
| | |   |   +-[]
| | |   +-Alias[id=7,name=,alias=$aggregate1,relation=$aggregate,
| | |   | type=Double NULL]
| | |   | +-AggregateFunction[function=SUM]
| | |   |   +-Add
| | |   |     +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | |   |     +-Divide
| | |   |       +-AttributeReference[id=2,name=float_col,relation=test,
| | |   |       | type=Float]
| | |   |       +-Literal[value=2,type=Int]
| | |   +-Alias[id=8,name=,alias=$aggregate2,relation=$aggregate,type=Long]
| | |   | +-AggregateFunction[function=COUNT]
| | |   |   +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | |   +-Alias[id=10,name=,alias=$aggregate3,relation=$aggregate,
| | |   | type=Float NULL]
| | |   | +-AggregateFunction[function=MAX]
| | |   |   +-Add
| | |   |     +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | |   |     +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | |   +-Alias[id=11,name=,alias=$aggregate4,relation=$aggregate,
| | |   | type=Double NULL]
| | |   | +-AggregateFunction[function=MIN]
| | |   |   +-AttributeReference[id=3,name=double_col,relation=test,
| | |   |     type=Double NULL]
| | |   +-Alias[id=12,name=,alias=$aggregate5,relation=$aggregate,type=Long]
| | |     +-AggregateFunction[function=COUNT]
| | |       +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | +-filter_predicate=Greater
| |   +-Add
| |   | +-AttributeReference[id=10,name=,alias=$aggregate3,relation=$aggregate,
| |   | | type=Float NULL]
| |   | +-AttributeReference[id=11,name=,alias=$aggregate4,relation=$aggregate,
| |   |   type=Double NULL]
| |   +-AttributeReference[id=12,name=,alias=$aggregate5,relation=$aggregate,
| |     type=Long]
| +-project_list=
|   +-Alias[id=9,name=,
|     alias=(1+((count(*)/sum((long_col+(float_col/2))))*count(float_col))),
|     relation=,type=Double NULL]
|     +-Add
|       +-Literal[value=1,type=Int]
|       +-Multiply
|         +-Divide
|         | +-AttributeReference[id=6,name=,alias=$aggregate0,
|         | | relation=$aggregate,type=Long]
|         | +-AttributeReference[id=7,name=,alias=$aggregate1,
|         |   relation=$aggregate,type=Double NULL]
|         +-AttributeReference[id=8,name=,alias=$aggregate2,relation=$aggregate,
|           type=Long]
+-output_attributes=
  +-AttributeReference[id=9,name=,
    alias=(1+((count(*)/sum((long_col+(float_col/2))))*count(float_col))),
    relation=,type=Double NULL]
==

# WHERE cannot have an aggregation.
select 1 from test where sum(int_col)>1
--
ERROR: Aggregate function not allowed in WHERE clause (1 : 26)
select 1 from test where sum(int_col)>1
                         ^
==

# GROUP BY cannot have an aggregation.
select 1 from test group by count(*)+1
--
ERROR: Aggregate function not allowed in GROUP BY clause (1 : 29)
select 1 from test group by count(*)+1
                            ^
==

# Aggregates cannot be in another aggregate expression.
select count(1-1/(1+sum(int_col))) from test
--
ERROR: Aggregation of Aggregates are not allowed (1 : 21)
select count(1-1/(1+sum(int_col))) from test
                    ^
==

# If there are multiple aggregate expressions, point the error to the first one.
select count(1-sum(int_col)+count(*)) from test
--
ERROR: Aggregation of Aggregates are not allowed (1 : 16)
select count(1-sum(int_col)+count(*)) from tes...
               ^
==

# Aggregate in ORDER BY.
select 1 from test order by count(*)/2 desc, sum(int_col)+1 asc
--
TopLevelPlan
+-plan=Project
| +-input=Sort[is_ascending=[false,true],nulls_first=[true,false]]
| | +-input=Project
| | | +-input=Aggregate
| | | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | | | +-AttributeReference[id=3,name=double_col,relation=test,
| | | | | | type=Double NULL]
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | | | |   type=VarChar(20) NULL]
| | | | +-grouping_expressions=
| | | | | +-[]
| | | | +-aggregate_expressions=
| | | |   +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| | | |   | +-AggregateFunction[function=COUNT]
| | | |   |   +-[]
| | | |   +-Alias[id=8,name=,alias=$aggregate1,relation=$aggregate,
| | | |     type=Long NULL]
| | | |     +-AggregateFunction[function=SUM]
| | | |       +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-project_list=
| | |   +-Alias[id=9,name=,alias=$orderby0,relation=$orderby,type=Long]
| | |   | +-Divide
| | |   |   +-AttributeReference[id=7,name=,alias=$aggregate0,
| | |   |   | relation=$aggregate,type=Long]
| | |   |   +-Literal[value=2,type=Int]
| | |   +-Alias[id=10,name=,alias=$orderby1,relation=$orderby,type=Long NULL]
| | |   | +-Add
| | |   |   +-AttributeReference[id=8,name=,alias=$aggregate1,
| | |   |   | relation=$aggregate,type=Long NULL]
| | |   |   +-Literal[value=1,type=Int]
| | |   +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate,
| | |   | type=Long]
| | |   +-AttributeReference[id=8,name=,alias=$aggregate1,relation=$aggregate,
| | |     type=Long NULL]
| | +-sort_expressions=
| |   +-AttributeReference[id=9,name=,alias=$orderby0,relation=$orderby,
| |   | type=Long]
| |   +-AttributeReference[id=10,name=,alias=$orderby1,relation=$orderby,
| |     type=Long NULL]
| +-project_list=
|   +-Alias[id=6,name=,alias=1,relation=,type=Int]
|     +-Literal[value=1,type=Int]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=1,relation=,type=Int]
==

select 1, 2, int_col, sum(long_col+double_col) from test group by int_col having count(char_col)>5
--
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-input=Aggregate
| | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | | |   type=VarChar(20) NULL]
| | | +-grouping_expressions=
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-aggregate_expressions=
| | |   +-Alias[id=8,name=,alias=$aggregate0,relation=$aggregate,
| | |   | type=Double NULL]
| | |   | +-AggregateFunction[function=SUM]
| | |   |   +-Add
| | |   |     +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | |   |     +-AttributeReference[id=3,name=double_col,relation=test,
| | |   |       type=Double NULL]
| | |   +-Alias[id=9,name=,alias=$aggregate1,relation=$aggregate,type=Long]
| | |     +-AggregateFunction[function=COUNT]
| | |       +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | +-filter_predicate=Greater
| |   +-AttributeReference[id=9,name=,alias=$aggregate1,relation=$aggregate,
| |   | type=Long]
| |   +-Literal[value=5,type=Int]
| +-project_list=
|   +-Alias[id=6,name=,alias=1,relation=,type=Int]
|   | +-Literal[value=1,type=Int]
|   +-Alias[id=7,name=,alias=2,relation=,type=Int]
|   | +-Literal[value=2,type=Int]
|   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
|   +-Alias[id=8,name=,alias=sum((long_col+double_col)),relation=,
|     type=Double NULL]
|     +-AttributeReference[id=8,name=,alias=$aggregate0,relation=$aggregate,
|       type=Double NULL]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=1,relation=,type=Int]
  +-AttributeReference[id=7,name=,alias=2,relation=,type=Int]
  +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
  +-AttributeReference[id=8,name=,alias=sum((long_col+double_col)),relation=,
    type=Double NULL]
==

# Currently, we cannot point the error to the exact location of the violated attribute reference.
select 1+2/float_col from test group by int_col
--
ERROR: Expression contains an attribute float_col, which is neither a GROUP BY expression nor in an aggregate expression (1 : 8)
select 1+2/float_col from test group b...
       ^
==

# Without group by, SELECT-list, HAVING and ORDER BY expressions cannot reference an attribute unless they are
# aggregate expressions.
select 1+int_col, count(*) from test
--
ERROR: Expression contains an attribute int_col, which is neither a GROUP BY expression nor in an aggregate expression (1 : 8)
select 1+int_col, count(*) from test
       ^
==

select 1, count(*) from test having sum(int_col)>2 order by max(int_col)
--
TopLevelPlan
+-plan=Project
| +-input=Sort[is_ascending=[true],nulls_first=[false]]
| | +-input=Filter
| | | +-input=Aggregate
| | | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | | | +-AttributeReference[id=3,name=double_col,relation=test,
| | | | | | type=Double NULL]
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | | | |   type=VarChar(20) NULL]
| | | | +-grouping_expressions=
| | | | | +-[]
| | | | +-aggregate_expressions=
| | | |   +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| | | |   | +-AggregateFunction[function=COUNT]
| | | |   |   +-[]
| | | |   +-Alias[id=8,name=,alias=$aggregate1,relation=$aggregate,
| | | |   | type=Long NULL]
| | | |   | +-AggregateFunction[function=SUM]
| | | |   |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | |   +-Alias[id=9,name=,alias=$aggregate2,relation=$aggregate,type=Int NULL]
| | | |     +-AggregateFunction[function=MAX]
| | | |       +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-filter_predicate=Greater
| | |   +-AttributeReference[id=8,name=,alias=$aggregate1,relation=$aggregate,
| | |   | type=Long NULL]
| | |   +-Literal[value=2,type=Int]
| | +-sort_expressions=
| |   +-AttributeReference[id=9,name=,alias=$aggregate2,relation=$aggregate,
| |     type=Int NULL]
| +-project_list=
|   +-Alias[id=6,name=,alias=1,relation=,type=Int]
|   | +-Literal[value=1,type=Int]
|   +-Alias[id=7,name=,alias=count(*),relation=,type=Long]
|     +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate,
|       type=Long]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=1,relation=,type=Int]
  +-AttributeReference[id=7,name=,alias=count(*),relation=,type=Long]
==

select 1+int_col from test having count(*)>1
--
ERROR: Expression contains an attribute int_col, which is neither a GROUP BY expression nor in an aggregate expression (1 : 8)
select 1+int_col from test having coun...
       ^
==

select 1 from test having count(*)>int_col
--
ERROR: Expression contains an attribute int_col, which is neither a GROUP BY expression nor in an aggregate expression (1 : 35)
select 1 from test having count(*)>int_col
                                  ^
==

select 1 from test order by count(*)+int_col
--
ERROR: Expression contains an attribute int_col, which is neither a GROUP BY expression nor in an aggregate expression (1 : 29)
select 1 from test order by count(*)+int_col
                            ^
==

select count(*) from test order by 1+int_col
--
ERROR: Expression contains an attribute int_col, which is neither a GROUP BY expression nor in an aggregate expression (1 : 36)
select count(*) from test order by 1+int_col
                                   ^
==

select 1 from test having int_col>1
--
ERROR: Expression contains an attribute int_col, which is neither a GROUP BY expression nor in an aggregate expression (1 : 34)
select 1 from test having int_col>1
                                 ^
==

select 1 from test having 1>2
--
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-input=Aggregate
| | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | | |   type=VarChar(20) NULL]
| | | +-grouping_expressions=
| | | | +-[]
| | | +-aggregate_expressions=
| | |   +-[]
| | +-filter_predicate=Greater
| |   +-Literal[value=1,type=Int]
| |   +-Literal[value=2,type=Int]
| +-project_list=
|   +-Alias[id=6,name=,alias=1,relation=,type=Int]
|     +-Literal[value=1,type=Int]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=1,relation=,type=Int]
==

select 1 from test having count(*)>1
--
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-input=Aggregate
| | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | | |   type=VarChar(20) NULL]
| | | +-grouping_expressions=
| | | | +-[]
| | | +-aggregate_expressions=
| | |   +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| | |     +-AggregateFunction[function=COUNT]
| | |       +-[]
| | +-filter_predicate=Greater
| |   +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate,
| |   | type=Long]
| |   +-Literal[value=1,type=Int]
| +-project_list=
|   +-Alias[id=6,name=,alias=1,relation=,type=Int]
|     +-Literal[value=1,type=Int]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=1,relation=,type=Int]
==

select int_col+long_col/(1+double_col) from test group by int_col, long_col
--
ERROR: Expression contains an attribute double_col, which is neither a GROUP BY expression nor in an aggregate expression (1 : 8)
select int_col+long_col/(1+double_col...
       ^
==

# Although the SELECT expression is identical to the grouping expression, they are not considered the same.
select 1+int_col from test group by 1+int_col
--
ERROR: Expression contains an attribute int_col, which is neither a GROUP BY expression nor in an aggregate expression (1 : 8)
select 1+int_col from test group by 1...
       ^
==

# Although int_col is functionally determined by 1+int_col, int_col is not added to the grouping column.
select int_col from test group by 1+int_col
--
ERROR: Expression contains an attribute int_col, which is neither a GROUP BY expression nor in an aggregate expression (1 : 8)
select int_col from test group by 1+in...
       ^
==

#
# Constant expressions.
#

# Integer literals are ordinal references.
select int_col from test group by 1
--
TopLevelPlan
+-plan=Project
| +-input=Aggregate
| | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | |   type=VarChar(20) NULL]
| | +-grouping_expressions=
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | +-aggregate_expressions=
| |   +-[]
| +-project_list=
|   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
==

select 1 from test group by 1+2-3.2
--
ERROR: Constant expression not allowed in GROUP BY (1 : 32)
select 1 from test group by 1+2-3.2
                               ^
==

select int_col from test group by 'abc'
--
ERROR: Constant expression not allowed in GROUP BY (1 : 35)
select int_col from test group by 'abc'
                                  ^
==

select int_col from test group by null
--
ERROR: Constant expression not allowed in GROUP BY (1 : 35)
select int_col from test group by null
                                  ^
==

#
# More tests for ordinal and alias references are in Select.test.
#

# Alias reference.
select int_col+1 as col1, count(float_col)+2 as col2, 1+1
from test group by col1 having count(col1+float_col)>sum(double_col)
order by col2+col1, max(col1*long_col) desc
--
TopLevelPlan
+-plan=Project
| +-input=Sort[is_ascending=[true,false],nulls_first=[false,true]]
| | +-input=Project
| | | +-input=Filter
| | | | +-input=Project
| | | | | +-input=Aggregate
| | | | | | +-input=Project
| | | | | | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | | | | | +-AttributeReference[id=0,name=int_col,relation=test,
| | | | | | | | | type=Int NULL]
| | | | | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | | | | | | +-AttributeReference[id=2,name=float_col,relation=test,
| | | | | | | | | type=Float]
| | | | | | | | +-AttributeReference[id=3,name=double_col,relation=test,
| | | | | | | | | type=Double NULL]
| | | | | | | | +-AttributeReference[id=4,name=char_col,relation=test,
| | | | | | | | | type=Char(20)]
| | | | | | | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | | | | | | |   type=VarChar(20) NULL]
| | | | | | | +-project_list=
| | | | | | |   +-Alias[id=6,name=col1,relation=,type=Int NULL]
| | | | | | |   | +-Add
| | | | | | |   |   +-AttributeReference[id=0,name=int_col,relation=test,
| | | | | | |   |   | type=Int NULL]
| | | | | | |   |   +-Literal[value=1,type=Int]
| | | | | | |   +-AttributeReference[id=0,name=int_col,relation=test,
| | | | | | |   | type=Int NULL]
| | | | | | |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | | | | |   +-AttributeReference[id=2,name=float_col,relation=test,
| | | | | | |   | type=Float]
| | | | | | |   +-AttributeReference[id=3,name=double_col,relation=test,
| | | | | | |   | type=Double NULL]
| | | | | | |   +-AttributeReference[id=4,name=char_col,relation=test,
| | | | | | |   | type=Char(20)]
| | | | | | |   +-AttributeReference[id=5,name=vchar_col,relation=test,
| | | | | | |     type=VarChar(20) NULL]
| | | | | | +-grouping_expressions=
| | | | | | | +-AttributeReference[id=6,name=col1,relation=,type=Int NULL]
| | | | | | +-aggregate_expressions=
| | | | | |   +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| | | | | |   | +-AggregateFunction[function=COUNT]
| | | | | |   |   +-AttributeReference[id=2,name=float_col,relation=test,
| | | | | |   |     type=Float]
| | | | | |   +-Alias[id=10,name=,alias=$aggregate1,relation=$aggregate,
| | | | | |   | type=Long]
| | | | | |   | +-AggregateFunction[function=COUNT]
| | | | | |   |   +-Add
| | | | | |   |     +-AttributeReference[id=6,name=col1,relation=,type=Int NULL]
| | | | | |   |     +-AttributeReference[id=2,name=float_col,relation=test,
| | | | | |   |       type=Float]
| | | | | |   +-Alias[id=11,name=,alias=$aggregate2,relation=$aggregate,
| | | | | |   | type=Double NULL]
| | | | | |   | +-AggregateFunction[function=SUM]
| | | | | |   |   +-AttributeReference[id=3,name=double_col,relation=test,
| | | | | |   |     type=Double NULL]
| | | | | |   +-Alias[id=12,name=,alias=$aggregate3,relation=$aggregate,
| | | | | |     type=Long NULL]
| | | | | |     +-AggregateFunction[function=MAX]
| | | | | |       +-Multiply
| | | | | |         +-AttributeReference[id=6,name=col1,relation=,type=Int NULL]
| | | | | |         +-AttributeReference[id=1,name=long_col,relation=test,
| | | | | |           type=Long]
| | | | | +-project_list=
| | | | |   +-Alias[id=8,name=col2,relation=,type=Long]
| | | | |   | +-Add
| | | | |   |   +-AttributeReference[id=7,name=,alias=$aggregate0,
| | | | |   |   | relation=$aggregate,type=Long]
| | | | |   |   +-Literal[value=2,type=Int]
| | | | |   +-AttributeReference[id=6,name=col1,relation=,type=Int NULL]
| | | | |   +-AttributeReference[id=7,name=,alias=$aggregate0,
| | | | |   | relation=$aggregate,type=Long]
| | | | |   +-AttributeReference[id=10,name=,alias=$aggregate1,
| | | | |   | relation=$aggregate,type=Long]
| | | | |   +-AttributeReference[id=11,name=,alias=$aggregate2,
| | | | |   | relation=$aggregate,type=Double NULL]
| | | | |   +-AttributeReference[id=12,name=,alias=$aggregate3,
| | | | |     relation=$aggregate,type=Long NULL]
| | | | +-filter_predicate=Greater
| | | |   +-AttributeReference[id=10,name=,alias=$aggregate1,
| | | |   | relation=$aggregate,type=Long]
| | | |   +-AttributeReference[id=11,name=,alias=$aggregate2,
| | | |     relation=$aggregate,type=Double NULL]
| | | +-project_list=
| | |   +-Alias[id=13,name=,alias=$orderby0,relation=$orderby,type=Long NULL]
| | |   | +-Add
| | |   |   +-AttributeReference[id=8,name=col2,relation=,type=Long]
| | |   |   +-AttributeReference[id=6,name=col1,relation=,type=Int NULL]
| | |   +-AttributeReference[id=8,name=col2,relation=,type=Long]
| | |   +-AttributeReference[id=6,name=col1,relation=,type=Int NULL]
| | |   +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate,
| | |   | type=Long]
| | |   +-AttributeReference[id=10,name=,alias=$aggregate1,relation=$aggregate,
| | |   | type=Long]
| | |   +-AttributeReference[id=11,name=,alias=$aggregate2,relation=$aggregate,
| | |   | type=Double NULL]
| | |   +-AttributeReference[id=12,name=,alias=$aggregate3,relation=$aggregate,
| | |     type=Long NULL]
| | +-sort_expressions=
| |   +-AttributeReference[id=13,name=,alias=$orderby0,relation=$orderby,
| |   | type=Long NULL]
| |   +-AttributeReference[id=12,name=,alias=$aggregate3,relation=$aggregate,
| |     type=Long NULL]
| +-project_list=
|   +-AttributeReference[id=6,name=col1,relation=,type=Int NULL]
|   +-AttributeReference[id=8,name=col2,relation=,type=Long]
|   +-Alias[id=9,name=,alias=(1+1),relation=,type=Int]
|     +-Add
|       +-Literal[value=1,type=Int]
|       +-Literal[value=1,type=Int]
+-output_attributes=
  +-AttributeReference[id=6,name=col1,relation=,type=Int NULL]
  +-AttributeReference[id=8,name=col2,relation=,type=Long]
  +-AttributeReference[id=9,name=,alias=(1+1),relation=,type=Int]
==

# Ordinal reference.
select int_col+1 as alias_col from test group by 1
--
TopLevelPlan
+-plan=Project
| +-input=Aggregate
| | +-input=Project
| | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | | |   type=VarChar(20) NULL]
| | | +-project_list=
| | |   +-Alias[id=6,name=alias_col,relation=,type=Int NULL]
| | |   | +-Add
| | |   |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | |   |   +-Literal[value=1,type=Int]
| | |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | |   +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | |   +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | |   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | |   +-AttributeReference[id=5,name=vchar_col,relation=test,
| | |     type=VarChar(20) NULL]
| | +-grouping_expressions=
| | | +-AttributeReference[id=6,name=alias_col,relation=,type=Int NULL]
| | +-aggregate_expressions=
| |   +-[]
| +-project_list=
|   +-AttributeReference[id=6,name=alias_col,relation=,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=6,name=alias_col,relation=,type=Int NULL]
==

# DISTINCT aggregation
SELECT COUNT(DISTINCT int_col),
       SUM(DISTINCT long_col * float_col)
FROM test
GROUP BY char_col
HAVING AVG(double_col) > MIN(DISTINCT int_col);
--
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-input=Aggregate
| | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test,
| | | |   type=VarChar(20) NULL]
| | | +-grouping_expressions=
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | +-aggregate_expressions=
| | |   +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| | |   | +-AggregateFunction[function=COUNT,is_distinct=true]
| | |   |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | |   +-Alias[id=7,name=,alias=$aggregate1,relation=$aggregate,
| | |   | type=Double NULL]
| | |   | +-AggregateFunction[function=SUM,is_distinct=true]
| | |   |   +-Multiply
| | |   |     +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | |   |     +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | |   +-Alias[id=8,name=,alias=$aggregate2,relation=$aggregate,
| | |   | type=Double NULL]
| | |   | +-AggregateFunction[function=AVG]
| | |   |   +-AttributeReference[id=3,name=double_col,relation=test,
| | |   |     type=Double NULL]
| | |   +-Alias[id=9,name=,alias=$aggregate3,relation=$aggregate,type=Int NULL]
| | |     +-AggregateFunction[function=MIN,is_distinct=true]
| | |       +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | +-filter_predicate=Greater
| |   +-AttributeReference[id=8,name=,alias=$aggregate2,relation=$aggregate,
| |   | type=Double NULL]
| |   +-AttributeReference[id=9,name=,alias=$aggregate3,relation=$aggregate,
| |     type=Int NULL]
| +-project_list=
|   +-Alias[id=6,name=,alias=COUNT(DISTINCT int_col),relation=,type=Long]
|   | +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
|   |   type=Long]
|   +-Alias[id=7,name=,alias=SUM(DISTINCT (long_col*float_col)),relation=,
|     type=Double NULL]
|     +-AttributeReference[id=7,name=,alias=$aggregate1,relation=$aggregate,
|       type=Double NULL]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=COUNT(DISTINCT int_col),relation=,
  | type=Long]
  +-AttributeReference[id=7,name=,alias=SUM(DISTINCT (long_col*float_col)),
    relation=,type=Double NULL]
==