# 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.

[default optimized_logical_plan physical_plan]
select * from test
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=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=
|   +-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]
+-output_attributes=
  +-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]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=TableReference[relation=Test,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_expressions=
|   +-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]
+-output_attributes=
  +-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]
==

select 1, 2 from test
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=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=1,relation=,type=Int]
|   | +-Literal[value=1,type=Int]
|   +-Alias[id=7,name=,alias=2,relation=,type=Int]
|     +-Literal[value=2,type=Int]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=1,relation=,type=Int]
  +-AttributeReference[id=7,name=,alias=2,relation=,type=Int]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=TableReference[relation=Test,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_expressions=
|   +-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]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=1,relation=,type=Int]
  +-AttributeReference[id=7,name=,alias=2,relation=,type=Int]
==

# The project expression is placed in the project list of the hash join.
select a.int_col+5, b.int_col+a.float_col from test as a, test as b where a.int_col=b.int_col
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=HashJoin
| | +-left=TableReference[relation_name=Test,relation_alias=a]
| | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| | +-right=TableReference[relation_name=Test,relation_alias=b]
| | | +-AttributeReference[id=6,name=int_col,relation=b,type=Int NULL]
| | | +-AttributeReference[id=7,name=long_col,relation=b,type=Long]
| | | +-AttributeReference[id=8,name=float_col,relation=b,type=Float]
| | | +-AttributeReference[id=9,name=double_col,relation=b,type=Double NULL]
| | | +-AttributeReference[id=10,name=char_col,relation=b,type=Char(20)]
| | | +-AttributeReference[id=11,name=vchar_col,relation=b,type=VarChar(20) NULL]
| | +-left_join_attributes=
| | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-right_join_attributes=
| |   +-AttributeReference[id=6,name=int_col,relation=b,type=Int NULL]
| +-project_list=
|   +-Alias[id=12,name=,alias=(a.int_col+5),relation=,type=Int NULL]
|   | +-Add
|   |   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
|   |   +-Literal[value=5,type=Int]
|   +-Alias[id=13,name=,alias=(b.int_col+a.float_col),relation=,type=Float NULL]
|     +-Add
|       +-AttributeReference[id=6,name=int_col,relation=b,type=Int NULL]
|       +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
+-output_attributes=
  +-AttributeReference[id=12,name=,alias=(a.int_col+5),relation=,type=Int NULL]
  +-AttributeReference[id=13,name=,alias=(b.int_col+a.float_col),relation=,
    type=Float NULL]
[Physical Plan]
TopLevelPlan
+-plan=HashJoin
| +-left=TableReference[relation=Test,alias=a]
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| +-right=TableReference[relation=Test,alias=b]
| | +-AttributeReference[id=6,name=int_col,relation=b,type=Int NULL]
| | +-AttributeReference[id=7,name=long_col,relation=b,type=Long]
| | +-AttributeReference[id=8,name=float_col,relation=b,type=Float]
| | +-AttributeReference[id=9,name=double_col,relation=b,type=Double NULL]
| | +-AttributeReference[id=10,name=char_col,relation=b,type=Char(20)]
| | +-AttributeReference[id=11,name=vchar_col,relation=b,type=VarChar(20) NULL]
| +-project_expressions=
| | +-Alias[id=12,name=,alias=(a.int_col+5),relation=,type=Int NULL]
| | | +-Add
| | |   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | |   +-Literal[value=5,type=Int]
| | +-Alias[id=13,name=,alias=(b.int_col+a.float_col),relation=,type=Float NULL]
| |   +-Add
| |     +-AttributeReference[id=6,name=int_col,relation=b,type=Int NULL]
| |     +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| +-left_join_attributes=
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| +-right_join_attributes=
|   +-AttributeReference[id=6,name=int_col,relation=b,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=12,name=,alias=(a.int_col+5),relation=,type=Int NULL]
  +-AttributeReference[id=13,name=,alias=(b.int_col+a.float_col),relation=,
    type=Float NULL]
==

# Nested subqueries are flattened.
select int_col+1 as int_col
from (select int_col+2 as int_col
      from (select int_col+3 as int_col
            from (select int_col+4 as int_col
                  from test) a) a where int_col > 1) a where int_col < 5
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-input=Project
| | | +-input=Filter
| | | | +-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=7,name=int_col,relation=a,type=Int NULL]
| | | | |     +-Add
| | | | |       +-Add
| | | | |       | +-AttributeReference[id=0,name=int_col,relation=test,
| | | | |       | | type=Int NULL]
| | | | |       | +-Literal[value=4,type=Int]
| | | | |       +-Literal[value=3,type=Int]
| | | | +-filter_predicate=Greater
| | | |   +-AttributeReference[id=7,name=int_col,relation=a,type=Int NULL]
| | | |   +-Literal[value=1,type=Int]
| | | +-project_list=
| | |   +-Alias[id=8,name=int_col,relation=a,type=Int NULL]
| | |     +-Add
| | |       +-AttributeReference[id=7,name=int_col,relation=a,type=Int NULL]
| | |       +-Literal[value=2,type=Int]
| | +-filter_predicate=Less
| |   +-AttributeReference[id=8,name=int_col,relation=a,type=Int NULL]
| |   +-Literal[value=5,type=Int]
| +-project_list=
|   +-Alias[id=9,name=int_col,relation=,type=Int NULL]
|     +-Add
|       +-AttributeReference[id=8,name=int_col,relation=a,type=Int NULL]
|       +-Literal[value=1,type=Int]
+-output_attributes=
  +-AttributeReference[id=9,name=int_col,relation=,type=Int NULL]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=TableReference[relation=Test,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]
| +-filter_predicate=And
| | +-Less
| | | +-Add
| | | | +-Add
| | | | | +-Add
| | | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | | | | +-Literal[value=4,type=Int]
| | | | | +-Literal[value=3,type=Int]
| | | | +-Literal[value=2,type=Int]
| | | +-Literal[value=5,type=Int]
| | +-Greater
| |   +-Add
| |   | +-Add
| |   | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| |   | | +-Literal[value=4,type=Int]
| |   | +-Literal[value=3,type=Int]
| |   +-Literal[value=1,type=Int]
| +-project_expressions=
|   +-Alias[id=9,name=int_col,relation=,type=Int NULL]
|     +-Add
|       +-Add
|       | +-Add
|       | | +-Add
|       | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
|       | | | +-Literal[value=4,type=Int]
|       | | +-Literal[value=3,type=Int]
|       | +-Literal[value=2,type=Int]
|       +-Literal[value=1,type=Int]
+-output_attributes=
  +-AttributeReference[id=9,name=int_col,relation=,type=Int NULL]
==

# The nested selection is pulled up and the join predicate is rewritten.
select * from test as a, (select int_col, float_col+5 as float_col from test) as b where a.int_col>b.float_col
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=NestedLoopsJoin
| | +-left=TableReference[relation_name=Test,relation_alias=a]
| | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| | +-right=Project
| | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL]
| | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | | |   type=VarChar(20) NULL]
| | | +-project_list=
| | |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | |   +-Alias[id=12,name=float_col,relation=b,type=Float]
| | |     +-Add
| | |       +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | |       +-Literal[value=5,type=Int]
| | +-join_predicate=Greater
| |   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| |   +-AttributeReference[id=12,name=float_col,relation=b,type=Float]
| +-project_list=
|   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
|   +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
|   +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
|   +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
|   +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
|   +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
|   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
|   +-AttributeReference[id=12,name=float_col,relation=b,type=Float]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
  +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
  +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
  +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
  +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
  +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
  +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
  +-AttributeReference[id=12,name=float_col,relation=b,type=Float]
[Physical Plan]
TopLevelPlan
+-plan=NestedLoopsJoin
| +-left=TableReference[relation=Test,alias=a]
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| +-right=TableReference[relation=Test,alias=test]
| | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL]
| | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | +-AttributeReference[id=11,name=vchar_col,relation=test,
| |   type=VarChar(20) NULL]
| +-join_predicate=Greater
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-Add
| |   +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| |   +-Literal[value=5,type=Int]
| +-project_expressions=
|   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
|   +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
|   +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
|   +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
|   +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
|   +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
|   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
|   +-Alias[id=12,name=float_col,relation=b,type=Float]
|     +-Add
|       +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
|       +-Literal[value=5,type=Int]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
  +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
  +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
  +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
  +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
  +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
  +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
  +-AttributeReference[id=12,name=float_col,relation=b,type=Float]
==

# The nested selection is not pulled up because it contains a filter predicate.
select * from test as a, (select float_col as float_col from test where int_col>4) as b where a.int_col>b.float_col
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=NestedLoopsJoin
| | +-left=TableReference[relation_name=Test,relation_alias=a]
| | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| | +-right=Project
| | | +-input=Filter
| | | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | | | +-AttributeReference[id=9,name=double_col,relation=test,
| | | | | | type=Double NULL]
| | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | | | |   type=VarChar(20) NULL]
| | | | +-filter_predicate=Greater
| | | |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | |   +-Literal[value=4,type=Int]
| | | +-project_list=
| | |   +-Alias[id=8,name=float_col,relation=b,type=Float]
| | |     +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | +-join_predicate=Greater
| |   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| |   +-AttributeReference[id=8,name=float_col,relation=b,type=Float]
| +-project_list=
|   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
|   +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
|   +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
|   +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
|   +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
|   +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
|   +-AttributeReference[id=8,name=float_col,relation=b,type=Float]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
  +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
  +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
  +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
  +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
  +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
  +-AttributeReference[id=8,name=float_col,relation=b,type=Float]
[Physical Plan]
TopLevelPlan
+-plan=NestedLoopsJoin
| +-left=TableReference[relation=Test,alias=a]
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| +-right=Selection
| | +-input=TableReference[relation=Test,alias=test]
| | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL]
| | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | |   type=VarChar(20) NULL]
| | +-filter_predicate=Greater
| | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | +-Literal[value=4,type=Int]
| | +-project_expressions=
| |   +-Alias[id=8,name=float_col,relation=b,type=Float]
| |     +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| +-join_predicate=Greater
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=8,name=float_col,relation=b,type=Float]
| +-project_expressions=
|   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
|   +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
|   +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
|   +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
|   +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
|   +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
|   +-AttributeReference[id=8,name=float_col,relation=b,type=Float]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
  +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
  +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
  +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
  +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
  +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
  +-AttributeReference[id=8,name=float_col,relation=b,type=Float]
==

# The nested selection is not pulled up, because the upper-level join is hash-based.
select * from test as a, (select int_col, float_col+5 as float_col from test) as b where a.int_col=b.float_col
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=HashJoin
| | +-left=TableReference[relation_name=Test,relation_alias=a]
| | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| | +-right=Project
| | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL]
| | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | | |   type=VarChar(20) NULL]
| | | +-project_list=
| | |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | |   +-Alias[id=12,name=float_col,relation=b,type=Float]
| | |     +-Add
| | |       +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | |       +-Literal[value=5,type=Int]
| | +-left_join_attributes=
| | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-right_join_attributes=
| |   +-AttributeReference[id=12,name=float_col,relation=b,type=Float]
| +-project_list=
|   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
|   +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
|   +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
|   +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
|   +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
|   +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
|   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
|   +-AttributeReference[id=12,name=float_col,relation=b,type=Float]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
  +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
  +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
  +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
  +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
  +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
  +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
  +-AttributeReference[id=12,name=float_col,relation=b,type=Float]
[Physical Plan]
TopLevelPlan
+-plan=HashJoin
| +-left=TableReference[relation=Test,alias=a]
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| +-right=Selection
| | +-input=TableReference[relation=Test,alias=test]
| | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL]
| | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | |   type=VarChar(20) NULL]
| | +-project_expressions=
| |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| |   +-Alias[id=12,name=float_col,relation=b,type=Float]
| |     +-Add
| |       +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| |       +-Literal[value=5,type=Int]
| +-project_expressions=
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | +-AttributeReference[id=12,name=float_col,relation=b,type=Float]
| +-left_join_attributes=
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| +-right_join_attributes=
|   +-AttributeReference[id=12,name=float_col,relation=b,type=Float]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
  +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
  +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
  +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
  +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
  +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
  +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
  +-AttributeReference[id=12,name=float_col,relation=b,type=Float]
==

select a.int_col from test as a, test as b
where a.int_col=b.int_col and a.int_col<b.long_col and a.long_col = b.long_col
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-input=HashJoin
| | | +-left=TableReference[relation_name=Test,relation_alias=a]
| | | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | | | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | | | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | | | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | | | +-AttributeReference[id=5,name=vchar_col,relation=a,
| | | |   type=VarChar(20) NULL]
| | | +-right=TableReference[relation_name=Test,relation_alias=b]
| | | | +-AttributeReference[id=6,name=int_col,relation=b,type=Int NULL]
| | | | +-AttributeReference[id=7,name=long_col,relation=b,type=Long]
| | | | +-AttributeReference[id=8,name=float_col,relation=b,type=Float]
| | | | +-AttributeReference[id=9,name=double_col,relation=b,type=Double NULL]
| | | | +-AttributeReference[id=10,name=char_col,relation=b,type=Char(20)]
| | | | +-AttributeReference[id=11,name=vchar_col,relation=b,
| | | |   type=VarChar(20) NULL]
| | | +-left_join_attributes=
| | | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | | +-right_join_attributes=
| | |   +-AttributeReference[id=6,name=int_col,relation=b,type=Int NULL]
| | |   +-AttributeReference[id=7,name=long_col,relation=b,type=Long]
| | +-filter_predicate=Less
| |   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| |   +-AttributeReference[id=7,name=long_col,relation=b,type=Long]
| +-project_list=
|   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
[Physical Plan]
TopLevelPlan
+-plan=HashJoin
| +-left=TableReference[relation=Test,alias=a]
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| +-right=TableReference[relation=Test,alias=b]
| | +-AttributeReference[id=6,name=int_col,relation=b,type=Int NULL]
| | +-AttributeReference[id=7,name=long_col,relation=b,type=Long]
| | +-AttributeReference[id=8,name=float_col,relation=b,type=Float]
| | +-AttributeReference[id=9,name=double_col,relation=b,type=Double NULL]
| | +-AttributeReference[id=10,name=char_col,relation=b,type=Char(20)]
| | +-AttributeReference[id=11,name=vchar_col,relation=b,type=VarChar(20) NULL]
| +-residual_predicate=Less
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=7,name=long_col,relation=b,type=Long]
| +-project_expressions=
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| +-left_join_attributes=
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| +-right_join_attributes=
|   +-AttributeReference[id=6,name=int_col,relation=b,type=Int NULL]
|   +-AttributeReference[id=7,name=long_col,relation=b,type=Long]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
==

select a.int_col from test as a, (select int_col, long_col+2 long_col_plus_2 from test) as b
where a.int_col = b.int_col and a.long_col=b.long_col_plus_2
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=HashJoin
| | +-left=TableReference[relation_name=Test,relation_alias=a]
| | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| | +-right=Project
| | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL]
| | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | | |   type=VarChar(20) NULL]
| | | +-project_list=
| | |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | |   +-Alias[id=12,name=long_col_plus_2,relation=b,type=Long]
| | |     +-Add
| | |       +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | |       +-Literal[value=2,type=Int]
| | +-left_join_attributes=
| | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | +-right_join_attributes=
| |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| |   +-AttributeReference[id=12,name=long_col_plus_2,relation=b,type=Long]
| +-project_list=
|   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
[Physical Plan]
TopLevelPlan
+-plan=HashJoin
| +-left=TableReference[relation=Test,alias=a]
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| +-right=Selection
| | +-input=TableReference[relation=Test,alias=test]
| | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL]
| | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | |   type=VarChar(20) NULL]
| | +-project_expressions=
| |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| |   +-Alias[id=12,name=long_col_plus_2,relation=b,type=Long]
| |     +-Add
| |       +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| |       +-Literal[value=2,type=Int]
| +-project_expressions=
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| +-left_join_attributes=
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| +-right_join_attributes=
|   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
|   +-AttributeReference[id=12,name=long_col_plus_2,relation=b,type=Long]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
==

select a.int_col from test as a, (select int_col, long_col+2 long_col_plus_2 from test) as b
where a.int_col = b.int_col and a.long_col>b.long_col_plus_2
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-input=HashJoin
| | | +-left=TableReference[relation_name=Test,relation_alias=a]
| | | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | | | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | | | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | | | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | | | +-AttributeReference[id=5,name=vchar_col,relation=a,
| | | |   type=VarChar(20) NULL]
| | | +-right=Project
| | | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | | | +-AttributeReference[id=9,name=double_col,relation=test,
| | | | | | type=Double NULL]
| | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | | | |   type=VarChar(20) NULL]
| | | | +-project_list=
| | | |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | |   +-Alias[id=12,name=long_col_plus_2,relation=b,type=Long]
| | | |     +-Add
| | | |       +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | |       +-Literal[value=2,type=Int]
| | | +-left_join_attributes=
| | | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | +-right_join_attributes=
| | |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | +-filter_predicate=Greater
| |   +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| |   +-AttributeReference[id=12,name=long_col_plus_2,relation=b,type=Long]
| +-project_list=
|   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
[Physical Plan]
TopLevelPlan
+-plan=HashJoin
| +-left=TableReference[relation=Test,alias=a]
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| +-right=TableReference[relation=Test,alias=test]
| | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL]
| | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | +-AttributeReference[id=11,name=vchar_col,relation=test,
| |   type=VarChar(20) NULL]
| +-residual_predicate=Greater
| | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | +-Add
| |   +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| |   +-Literal[value=2,type=Int]
| +-project_expressions=
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| +-left_join_attributes=
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| +-right_join_attributes=
|   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
==

select a.int_col from test as a, test as b, test c
where a.int_col=b.long_col and a.int_col<c.long_col and c.int_col=4
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=NestedLoopsJoin
| | +-left=HashJoin
| | | +-left=TableReference[relation_name=Test,relation_alias=a]
| | | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | | | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | | | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | | | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | | | +-AttributeReference[id=5,name=vchar_col,relation=a,
| | | |   type=VarChar(20) NULL]
| | | +-right=TableReference[relation_name=Test,relation_alias=b]
| | | | +-AttributeReference[id=6,name=int_col,relation=b,type=Int NULL]
| | | | +-AttributeReference[id=7,name=long_col,relation=b,type=Long]
| | | | +-AttributeReference[id=8,name=float_col,relation=b,type=Float]
| | | | +-AttributeReference[id=9,name=double_col,relation=b,type=Double NULL]
| | | | +-AttributeReference[id=10,name=char_col,relation=b,type=Char(20)]
| | | | +-AttributeReference[id=11,name=vchar_col,relation=b,
| | | |   type=VarChar(20) NULL]
| | | +-left_join_attributes=
| | | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | +-right_join_attributes=
| | |   +-AttributeReference[id=7,name=long_col,relation=b,type=Long]
| | +-right=Filter
| | | +-input=TableReference[relation_name=Test,relation_alias=c]
| | | | +-AttributeReference[id=12,name=int_col,relation=c,type=Int NULL]
| | | | +-AttributeReference[id=13,name=long_col,relation=c,type=Long]
| | | | +-AttributeReference[id=14,name=float_col,relation=c,type=Float]
| | | | +-AttributeReference[id=15,name=double_col,relation=c,type=Double NULL]
| | | | +-AttributeReference[id=16,name=char_col,relation=c,type=Char(20)]
| | | | +-AttributeReference[id=17,name=vchar_col,relation=c,
| | | |   type=VarChar(20) NULL]
| | | +-filter_predicate=Equal
| | |   +-AttributeReference[id=12,name=int_col,relation=c,type=Int NULL]
| | |   +-Literal[value=4,type=Int]
| | +-join_predicate=Less
| |   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| |   +-AttributeReference[id=13,name=long_col,relation=c,type=Long]
| +-project_list=
|   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
[Physical Plan]
TopLevelPlan
+-plan=NestedLoopsJoin
| +-left=HashJoin
| | +-left=TableReference[relation=Test,alias=a]
| | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| | +-right=TableReference[relation=Test,alias=b]
| | | +-AttributeReference[id=6,name=int_col,relation=b,type=Int NULL]
| | | +-AttributeReference[id=7,name=long_col,relation=b,type=Long]
| | | +-AttributeReference[id=8,name=float_col,relation=b,type=Float]
| | | +-AttributeReference[id=9,name=double_col,relation=b,type=Double NULL]
| | | +-AttributeReference[id=10,name=char_col,relation=b,type=Char(20)]
| | | +-AttributeReference[id=11,name=vchar_col,relation=b,type=VarChar(20) NULL]
| | +-project_expressions=
| | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-left_join_attributes=
| | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-right_join_attributes=
| |   +-AttributeReference[id=7,name=long_col,relation=b,type=Long]
| +-right=Selection
| | +-input=TableReference[relation=Test,alias=c]
| | | +-AttributeReference[id=12,name=int_col,relation=c,type=Int NULL]
| | | +-AttributeReference[id=13,name=long_col,relation=c,type=Long]
| | | +-AttributeReference[id=14,name=float_col,relation=c,type=Float]
| | | +-AttributeReference[id=15,name=double_col,relation=c,type=Double NULL]
| | | +-AttributeReference[id=16,name=char_col,relation=c,type=Char(20)]
| | | +-AttributeReference[id=17,name=vchar_col,relation=c,type=VarChar(20) NULL]
| | +-filter_predicate=Equal
| | | +-AttributeReference[id=12,name=int_col,relation=c,type=Int NULL]
| | | +-Literal[value=4,type=Int]
| | +-project_expressions=
| |   +-AttributeReference[id=13,name=long_col,relation=c,type=Long]
| +-join_predicate=Less
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=13,name=long_col,relation=c,type=Long]
| +-project_expressions=
|   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
==

# Empty-column projection.
select a.int_col from test as a, (select float_col from test where float_col>1) as b
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=NestedLoopsJoin
| | +-left=TableReference[relation_name=Test,relation_alias=a]
| | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| | +-right=Project
| | | +-input=Filter
| | | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | | | +-AttributeReference[id=9,name=double_col,relation=test,
| | | | | | type=Double NULL]
| | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | | | |   type=VarChar(20) NULL]
| | | | +-filter_predicate=Greater
| | | |   +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | |   +-Literal[value=1,type=Int]
| | | +-project_list=
| | |   +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | +-join_predicate=Literal[value=true]
| +-project_list=
|   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
[Physical Plan]
TopLevelPlan
+-plan=NestedLoopsJoin
| +-left=TableReference[relation=Test,alias=a]
| | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
| | +-AttributeReference[id=1,name=long_col,relation=a,type=Long]
| | +-AttributeReference[id=2,name=float_col,relation=a,type=Float]
| | +-AttributeReference[id=3,name=double_col,relation=a,type=Double NULL]
| | +-AttributeReference[id=4,name=char_col,relation=a,type=Char(20)]
| | +-AttributeReference[id=5,name=vchar_col,relation=a,type=VarChar(20) NULL]
| +-right=Selection
| | +-input=TableReference[relation=Test,alias=test]
| | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL]
| | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | |   type=VarChar(20) NULL]
| | +-filter_predicate=Greater
| | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | +-Literal[value=1,type=Int]
| | +-project_expressions=
| |   +-[]
| +-join_predicate=Literal[value=true]
| +-project_expressions=
|   +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL]
==

select count(*)+count(int_col)/sum(long_col)*avg(int_col) as col
from test
having max(double_col)+1>col
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-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=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=11,name=,alias=$aggregate4,relation=$aggregate,
| | | |     type=Double NULL]
| | | |     +-AggregateFunction[function=MAX]
| | | |       +-AttributeReference[id=3,name=double_col,relation=test,
| | | |         type=Double NULL]
| | | +-project_list=
| | |   +-Alias[id=10,name=col,relation=,type=Double NULL]
| | |   | +-Add
| | |   |   +-AttributeReference[id=6,name=,alias=$aggregate0,
| | |   |   | relation=$aggregate,type=Long]
| | |   |   +-Multiply
| | |   |     +-Divide
| | |   |     | +-AttributeReference[id=7,name=,alias=$aggregate1,
| | |   |     | | relation=$aggregate,type=Long]
| | |   |     | +-AttributeReference[id=8,name=,alias=$aggregate2,
| | |   |     |   relation=$aggregate,type=Long NULL]
| | |   |     +-AttributeReference[id=9,name=,alias=$aggregate3,
| | |   |       relation=$aggregate,type=Double NULL]
| | |   +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
| | |   | type=Long]
| | |   +-AttributeReference[id=7,name=,alias=$aggregate1,relation=$aggregate,
| | |   | type=Long]
| | |   +-AttributeReference[id=8,name=,alias=$aggregate2,relation=$aggregate,
| | |   | type=Long NULL]
| | |   +-AttributeReference[id=9,name=,alias=$aggregate3,relation=$aggregate,
| | |   | type=Double NULL]
| | |   +-AttributeReference[id=11,name=,alias=$aggregate4,relation=$aggregate,
| | |     type=Double NULL]
| | +-filter_predicate=Greater
| |   +-Add
| |   | +-AttributeReference[id=11,name=,alias=$aggregate4,relation=$aggregate,
| |   | | type=Double NULL]
| |   | +-Literal[value=1,type=Int]
| |   +-AttributeReference[id=10,name=col,relation=,type=Double NULL]
| +-project_list=
|   +-AttributeReference[id=10,name=col,relation=,type=Double NULL]
+-output_attributes=
  +-AttributeReference[id=10,name=col,relation=,type=Double NULL]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=Aggregate
| | +-input=TableReference[relation=Test,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=12,name=,alias=$aggregate3,relation=$aggregate,type=Long NULL]
| |   | +-AggregateFunction[function=SUM]
| |   |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| |   +-Alias[id=11,name=,alias=$aggregate4,relation=$aggregate,type=Double NULL]
| |     +-AggregateFunction[function=MAX]
| |       +-AttributeReference[id=3,name=double_col,relation=test,
| |         type=Double NULL]
| +-filter_predicate=Greater
| | +-Add
| | | +-AttributeReference[id=11,name=,alias=$aggregate4,relation=$aggregate,
| | | | type=Double NULL]
| | | +-Literal[value=1,type=Int]
| | +-Add
| |   +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
| |   | type=Long]
| |   +-Multiply
| |     +-Divide
| |     | +-AttributeReference[id=7,name=,alias=$aggregate1,relation=$aggregate,
| |     | | type=Long]
| |     | +-AttributeReference[id=8,name=,alias=$aggregate2,relation=$aggregate,
| |     |   type=Long NULL]
| |     +-Divide
| |       +-AttributeReference[id=12,name=,alias=$aggregate3,
| |       | relation=$aggregate,type=Long NULL]
| |       +-AttributeReference[id=7,name=,alias=$aggregate1,relation=$aggregate,
| |         type=Long]
| +-project_expressions=
|   +-Alias[id=10,name=col,relation=,type=Long NULL]
|     +-Add
|       +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
|       | type=Long]
|       +-Multiply
|         +-Divide
|         | +-AttributeReference[id=7,name=,alias=$aggregate1,
|         | | relation=$aggregate,type=Long]
|         | +-AttributeReference[id=8,name=,alias=$aggregate2,
|         |   relation=$aggregate,type=Long NULL]
|         +-Divide
|           +-AttributeReference[id=12,name=,alias=$aggregate3,
|           | relation=$aggregate,type=Long NULL]
|           +-AttributeReference[id=7,name=,alias=$aggregate1,
|             relation=$aggregate,type=Long]
+-output_attributes=
  +-AttributeReference[id=10,name=col,relation=,type=Long NULL]
==

select long_col as col1, count(*) as col2
from test
group by long_col+float_col, 1, float_col+1
having max(col1+1)+1>col2/2
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-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=1,name=col1,relation=,type=Long]
| | | |   | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | |   +-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=
| | | | +-Alias[id=7,name=,alias=$groupby0,relation=$groupby,type=Double]
| | | | | +-Add
| | | | |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | | |   +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | | +-AttributeReference[id=1,name=col1,relation=,type=Long]
| | | | +-Alias[id=8,name=,alias=$groupby2,relation=$groupby,type=Float]
| | | |   +-Add
| | | |     +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | |     +-Literal[value=1,type=Int]
| | | +-aggregate_expressions=
| | |   +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| | |   | +-AggregateFunction[function=COUNT]
| | |   |   +-[]
| | |   +-Alias[id=9,name=,alias=$aggregate1,relation=$aggregate,type=Long NULL]
| | |     +-AggregateFunction[function=MAX]
| | |       +-Add
| | |         +-AttributeReference[id=1,name=col1,relation=,type=Long]
| | |         +-Literal[value=1,type=Int]
| | +-filter_predicate=Greater
| |   +-Add
| |   | +-AttributeReference[id=9,name=,alias=$aggregate1,relation=$aggregate,
| |   | | type=Long NULL]
| |   | +-Literal[value=1,type=Int]
| |   +-Divide
| |     +-AttributeReference[id=6,name=col2,relation=,type=Long]
| |     +-Literal[value=2,type=Int]
| +-project_list=
|   +-AttributeReference[id=1,name=col1,relation=,type=Long]
|   +-Alias[id=6,name=col2,relation=,type=Long]
|     +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
|       type=Long]
+-output_attributes=
  +-AttributeReference[id=1,name=col1,relation=,type=Long]
  +-AttributeReference[id=6,name=col2,relation=,type=Long]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=Aggregate
| | +-input=TableReference[relation=Test,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=
| | | +-Alias[id=7,name=,alias=$groupby0,relation=$groupby,type=Double]
| | | | +-Add
| | | |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | |   +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | +-Alias[id=1,name=col1,relation=,type=Long]
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | +-Alias[id=8,name=,alias=$groupby2,relation=$groupby,type=Float]
| | |   +-Add
| | |     +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | |     +-Literal[value=1,type=Int]
| | +-aggregate_expressions=
| |   +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| |   | +-AggregateFunction[function=COUNT]
| |   |   +-[]
| |   +-Alias[id=9,name=,alias=$aggregate1,relation=$aggregate,type=Long NULL]
| |     +-AggregateFunction[function=MAX]
| |       +-Add
| |         +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| |         +-Literal[value=1,type=Int]
| +-filter_predicate=Greater
| | +-Add
| | | +-AttributeReference[id=9,name=,alias=$aggregate1,relation=$aggregate,
| | | | type=Long NULL]
| | | +-Literal[value=1,type=Int]
| | +-Divide
| |   +-AttributeReference[id=6,name=col2,relation=,type=Long]
| |   +-Literal[value=2,type=Int]
| +-project_expressions=
|   +-AttributeReference[id=1,name=col1,relation=,type=Long]
|   +-Alias[id=6,name=col2,relation=,type=Long]
|     +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
|       type=Long]
+-output_attributes=
  +-AttributeReference[id=1,name=col1,relation=,type=Long]
  +-AttributeReference[id=6,name=col2,relation=,type=Long]
==

# Pull up Selection.
select count(subquery_col1+subquery_col2)+max(subquery_col1/2), subquery_col3
from (select int_col+long_col subquery_col1, long_col+float_col subquery_col2, char_col subquery_col3, int_col
      from test where int_col > 2) as subquery
group by int_col, subquery_col3
having max(int_col+subquery_col1)>sum(subquery_col2)/2
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-input=Aggregate
| | | +-input=Project
| | | | +-input=Filter
| | | | | +-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]
| | | | | +-filter_predicate=Greater
| | | | |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | | |   +-Literal[value=2,type=Int]
| | | | +-project_list=
| | | |   +-Alias[id=6,name=subquery_col1,relation=subquery,type=Long NULL]
| | | |   | +-Add
| | | |   |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | |   |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | |   +-Alias[id=7,name=subquery_col2,relation=subquery,type=Double]
| | | |   | +-Add
| | | |   |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | |   |   +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | | |   +-Alias[id=4,name=subquery_col3,relation=subquery,type=Char(20)]
| | | |   | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-grouping_expressions=
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | | +-AttributeReference[id=4,name=subquery_col3,relation=subquery,
| | | |   type=Char(20)]
| | | +-aggregate_expressions=
| | |   +-Alias[id=8,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| | |   | +-AggregateFunction[function=COUNT]
| | |   |   +-Add
| | |   |     +-AttributeReference[id=6,name=subquery_col1,relation=subquery,
| | |   |     | type=Long NULL]
| | |   |     +-AttributeReference[id=7,name=subquery_col2,relation=subquery,
| | |   |       type=Double]
| | |   +-Alias[id=9,name=,alias=$aggregate1,relation=$aggregate,type=Long NULL]
| | |   | +-AggregateFunction[function=MAX]
| | |   |   +-Divide
| | |   |     +-AttributeReference[id=6,name=subquery_col1,relation=subquery,
| | |   |     | type=Long NULL]
| | |   |     +-Literal[value=2,type=Int]
| | |   +-Alias[id=11,name=,alias=$aggregate2,relation=$aggregate,type=Long NULL]
| | |   | +-AggregateFunction[function=MAX]
| | |   |   +-Add
| | |   |     +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | |   |     +-AttributeReference[id=6,name=subquery_col1,relation=subquery,
| | |   |       type=Long NULL]
| | |   +-Alias[id=12,name=,alias=$aggregate3,relation=$aggregate,
| | |     type=Double NULL]
| | |     +-AggregateFunction[function=SUM]
| | |       +-AttributeReference[id=7,name=subquery_col2,relation=subquery,
| | |         type=Double]
| | +-filter_predicate=Greater
| |   +-AttributeReference[id=11,name=,alias=$aggregate2,relation=$aggregate,
| |   | type=Long NULL]
| |   +-Divide
| |     +-AttributeReference[id=12,name=,alias=$aggregate3,relation=$aggregate,
| |     | type=Double NULL]
| |     +-Literal[value=2,type=Int]
| +-project_list=
|   +-Alias[id=10,name=,
|   | alias=(count((subquery_col1+subquery_col2))+max((subquery_col1/2))),
|   | relation=,type=Long NULL]
|   | +-Add
|   |   +-AttributeReference[id=8,name=,alias=$aggregate0,relation=$aggregate,
|   |   | type=Long]
|   |   +-AttributeReference[id=9,name=,alias=$aggregate1,relation=$aggregate,
|   |     type=Long NULL]
|   +-AttributeReference[id=4,name=subquery_col3,relation=subquery,type=Char(20)]
+-output_attributes=
  +-AttributeReference[id=10,name=,
  | alias=(count((subquery_col1+subquery_col2))+max((subquery_col1/2))),
  | relation=,type=Long NULL]
  +-AttributeReference[id=4,name=subquery_col3,relation=subquery,type=Char(20)]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=Aggregate
| | +-input=TableReference[relation=Test,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]
| | +-filter_predicate=Greater
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-Literal[value=2,type=Int]
| | +-grouping_expressions=
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-Alias[id=4,name=subquery_col3,relation=subquery,type=Char(20)]
| | |   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | +-aggregate_expressions=
| |   +-Alias[id=8,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| |   | +-AggregateFunction[function=COUNT]
| |   |   +-Add
| |   |     +-CommonSubexpression[common_subexpression_id=13]
| |   |     | +-Operand=Add
| |   |     |   +-AttributeReference[id=0,name=int_col,relation=test,
| |   |     |   | type=Int NULL]
| |   |     |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| |   |     +-CommonSubexpression[common_subexpression_id=14]
| |   |       +-Operand=Add
| |   |         +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| |   |         +-AttributeReference[id=2,name=float_col,relation=test,
| |   |           type=Float]
| |   +-Alias[id=9,name=,alias=$aggregate1,relation=$aggregate,type=Long NULL]
| |   | +-AggregateFunction[function=MAX]
| |   |   +-Divide
| |   |     +-CommonSubexpression[common_subexpression_id=13]
| |   |     | +-Operand=Add
| |   |     |   +-AttributeReference[id=0,name=int_col,relation=test,
| |   |     |   | type=Int NULL]
| |   |     |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| |   |     +-Literal[value=2,type=Int]
| |   +-Alias[id=11,name=,alias=$aggregate2,relation=$aggregate,type=Long NULL]
| |   | +-AggregateFunction[function=MAX]
| |   |   +-Add
| |   |     +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| |   |     +-CommonSubexpression[common_subexpression_id=13]
| |   |       +-Operand=Add
| |   |         +-AttributeReference[id=0,name=int_col,relation=test,
| |   |         | type=Int NULL]
| |   |         +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| |   +-Alias[id=12,name=,alias=$aggregate3,relation=$aggregate,type=Double NULL]
| |     +-AggregateFunction[function=SUM]
| |       +-CommonSubexpression[common_subexpression_id=14]
| |         +-Operand=Add
| |           +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| |           +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| +-filter_predicate=Greater
| | +-AttributeReference[id=11,name=,alias=$aggregate2,relation=$aggregate,
| | | type=Long NULL]
| | +-Divide
| |   +-AttributeReference[id=12,name=,alias=$aggregate3,relation=$aggregate,
| |   | type=Double NULL]
| |   +-Literal[value=2,type=Int]
| +-project_expressions=
|   +-Alias[id=10,name=,
|   | alias=(count((subquery_col1+subquery_col2))+max((subquery_col1/2))),
|   | relation=,type=Long NULL]
|   | +-Add
|   |   +-AttributeReference[id=8,name=,alias=$aggregate0,relation=$aggregate,
|   |   | type=Long]
|   |   +-AttributeReference[id=9,name=,alias=$aggregate1,relation=$aggregate,
|   |     type=Long NULL]
|   +-AttributeReference[id=4,name=subquery_col3,relation=subquery,type=Char(20)]
+-output_attributes=
  +-AttributeReference[id=10,name=,
  | alias=(count((subquery_col1+subquery_col2))+max((subquery_col1/2))),
  | relation=,type=Long NULL]
  +-AttributeReference[id=4,name=subquery_col3,relation=subquery,type=Char(20)]
==

select int_col, count(*)
from test
group by int_col
--
[Optimized Logical Plan]
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=
| |   +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| |     +-AggregateFunction[function=COUNT]
| |       +-[]
| +-project_list=
|   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
|   +-Alias[id=6,name=,alias=count(*),relation=,type=Long]
|     +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
|       type=Long]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
  +-AttributeReference[id=6,name=,alias=count(*),relation=,type=Long]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=Aggregate
| | +-input=TableReference[relation=Test,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=6,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| |     +-AggregateFunction[function=COUNT]
| |       +-[]
| +-project_expressions=
|   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
|   +-Alias[id=6,name=,alias=count(*),relation=,type=Long]
|     +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
|       type=Long]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
  +-AttributeReference[id=6,name=,alias=count(*),relation=,type=Long]
==

# We have a Selection on top of the Aggregate to filter out the
# grouping expression.
select count(*)
from test
group by int_col
--
[Optimized Logical Plan]
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=
| |   +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| |     +-AggregateFunction[function=COUNT]
| |       +-[]
| +-project_list=
|   +-Alias[id=6,name=,alias=count(*),relation=,type=Long]
|     +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
|       type=Long]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=count(*),relation=,type=Long]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=Aggregate
| | +-input=TableReference[relation=Test,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=6,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| |     +-AggregateFunction[function=COUNT]
| |       +-[]
| +-project_expressions=
|   +-Alias[id=6,name=,alias=count(*),relation=,type=Long]
|     +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate,
|       type=Long]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=count(*),relation=,type=Long]
==

select 1, count(*)
from test
group by int_col
--
[Optimized Logical Plan]
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=
| |   +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| |     +-AggregateFunction[function=COUNT]
| |       +-[]
| +-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]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=Aggregate
| | +-input=TableReference[relation=Test,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=7,name=,alias=$aggregate0,relation=$aggregate,type=Long]
| |     +-AggregateFunction[function=COUNT]
| |       +-[]
| +-project_expressions=
|   +-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]
==

# WITH queries and the main query are currently optimized separately.
with subquery as (select int_col, long_col, double_col from test)
select int_col from subquery
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=SharedSubplanReference[subplan_id=0]
| | +-referenced_attributes=
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | +-output_attributes=
| |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| |   +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| |   +-AttributeReference[id=8,name=double_col,relation=test,type=Double NULL]
| +-project_list=
|   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
+-shared_subplans=
| +-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=
|     +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
|     +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
|     +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
+-output_attributes=
  +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=SharedSubplanReference[subplan_id=0]
| | +-referenced_attributes=
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | +-output_attributes=
| |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| |   +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| |   +-AttributeReference[id=8,name=double_col,relation=test,type=Double NULL]
| +-project_expressions=
|   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
+-shared_subplans=
| +-Selection
|   +-input=TableReference[relation=Test,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_expressions=
|     +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
|     +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
|     +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
+-output_attributes=
  +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
==

SELECT COUNT(DISTINCT int_col), SUM(float_col)
FROM test
GROUP BY long_col
HAVING AVG(DISTINCT int_col + double_col) > AVG(DISTINCT float_col);
--
[Optimized Logical Plan]
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=1,name=long_col,relation=test,type=Long]
| | | +-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]
| | |   |   +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | |   +-Alias[id=8,name=,alias=$aggregate2,relation=$aggregate,
| | |   | type=Double NULL]
| | |   | +-AggregateFunction[function=AVG,is_distinct=true]
| | |   |   +-Add
| | |   |     +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | |   |     +-AttributeReference[id=3,name=double_col,relation=test,
| | |   |       type=Double NULL]
| | |   +-Alias[id=9,name=,alias=$aggregate3,relation=$aggregate,
| | |     type=Double NULL]
| | |     +-AggregateFunction[function=AVG,is_distinct=true]
| | |       +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| | +-filter_predicate=Greater
| |   +-AttributeReference[id=8,name=,alias=$aggregate2,relation=$aggregate,
| |   | type=Double NULL]
| |   +-AttributeReference[id=9,name=,alias=$aggregate3,relation=$aggregate,
| |     type=Double 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(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(float_col),relation=,
    type=Double NULL]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=Aggregate
| | +-input=TableReference[relation=Test,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=1,name=long_col,relation=test,type=Long]
| | +-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]
| |   |   +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| |   +-Alias[id=8,name=,alias=$aggregate2,relation=$aggregate,type=Double NULL]
| |   | +-AggregateFunction[function=AVG,is_distinct=true]
| |   |   +-Add
| |   |     +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| |   |     +-AttributeReference[id=3,name=double_col,relation=test,
| |   |       type=Double NULL]
| |   +-Alias[id=9,name=,alias=$aggregate3,relation=$aggregate,type=Double NULL]
| |     +-AggregateFunction[function=AVG,is_distinct=true]
| |       +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| +-filter_predicate=Greater
| | +-AttributeReference[id=8,name=,alias=$aggregate2,relation=$aggregate,
| | | type=Double NULL]
| | +-AttributeReference[id=9,name=,alias=$aggregate3,relation=$aggregate,
| |   type=Double NULL]
| +-project_expressions=
|   +-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(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(float_col),relation=,
    type=Double NULL]
==

SELECT i
FROM generate_series(0, 100, 3) AS gs1(i)
WHERE
  EXISTS (
    SELECT *
    FROM generate_series(0, 100, 5) AS gs2(i)
    WHERE gs1.i = gs2.i
  )
  AND NOT EXISTS (
    SELECT *
    FROM generate_series(0, 100, 10) AS gs3(i)
    WHERE gs1.i = gs3.i
  )
  AND (i < 40 OR i > 60);
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=HashLeftSemiJoin
| | +-left=HashLeftAntiJoin
| | | +-left=Filter
| | | | +-input=Project
| | | | | +-input=TableGenerator[function_name=generate_series,table_alias=gs1]
| | | | | | +-AttributeReference[id=0,name=generate_series,alias=gs1,
| | | | | |   relation=generate_series,type=Int]
| | | | | +-project_list=
| | | | |   +-Alias[id=1,name=i,relation=,type=Int]
| | | | |     +-AttributeReference[id=0,name=generate_series,alias=gs1,
| | | | |       relation=generate_series,type=Int]
| | | | +-filter_predicate=Or
| | | |   +-Less
| | | |   | +-AttributeReference[id=1,name=i,relation=,type=Int]
| | | |   | +-Literal[value=40,type=Int]
| | | |   +-Greater
| | | |     +-AttributeReference[id=1,name=i,relation=,type=Int]
| | | |     +-Literal[value=60,type=Int]
| | | +-right=Project
| | | | +-input=TableGenerator[function_name=generate_series,table_alias=gs3]
| | | | | +-AttributeReference[id=4,name=generate_series,alias=gs3,
| | | | |   relation=generate_series,type=Int]
| | | | +-project_list=
| | | |   +-Alias[id=5,name=i,relation=,type=Int]
| | | |     +-AttributeReference[id=4,name=generate_series,alias=gs3,
| | | |       relation=generate_series,type=Int]
| | | +-left_join_attributes=
| | | | +-AttributeReference[id=1,name=i,relation=,type=Int]
| | | +-right_join_attributes=
| | |   +-AttributeReference[id=5,name=i,relation=,type=Int]
| | +-right=Project
| | | +-input=TableGenerator[function_name=generate_series,table_alias=gs2]
| | | | +-AttributeReference[id=2,name=generate_series,alias=gs2,
| | | |   relation=generate_series,type=Int]
| | | +-project_list=
| | |   +-Alias[id=3,name=i,relation=,type=Int]
| | |     +-AttributeReference[id=2,name=generate_series,alias=gs2,
| | |       relation=generate_series,type=Int]
| | +-left_join_attributes=
| | | +-AttributeReference[id=1,name=i,relation=,type=Int]
| | +-right_join_attributes=
| |   +-AttributeReference[id=3,name=i,relation=,type=Int]
| +-project_list=
|   +-AttributeReference[id=1,name=i,relation=,type=Int]
+-output_attributes=
  +-AttributeReference[id=1,name=i,relation=,type=Int]
[Physical Plan]
TopLevelPlan
+-plan=HashLeftSemiJoin
| +-left=HashLeftAntiJoin
| | +-left=Selection
| | | +-input=TableGenerator[function_name=generate_series,table_alias=gs1]
| | | | +-AttributeReference[id=0,name=generate_series,alias=gs1,
| | | |   relation=generate_series,type=Int]
| | | +-filter_predicate=Or
| | | | +-Less
| | | | | +-AttributeReference[id=0,name=generate_series,alias=gs1,
| | | | | | relation=generate_series,type=Int]
| | | | | +-Literal[value=40,type=Int]
| | | | +-Greater
| | | |   +-AttributeReference[id=0,name=generate_series,alias=gs1,
| | | |   | relation=generate_series,type=Int]
| | | |   +-Literal[value=60,type=Int]
| | | +-project_expressions=
| | |   +-Alias[id=1,name=i,relation=,type=Int]
| | |     +-AttributeReference[id=0,name=generate_series,alias=gs1,
| | |       relation=generate_series,type=Int]
| | +-right=TableGenerator[function_name=generate_series,table_alias=gs3]
| | | +-AttributeReference[id=4,name=generate_series,alias=gs3,
| | |   relation=generate_series,type=Int]
| | +-project_expressions=
| | | +-AttributeReference[id=1,name=i,relation=,type=Int]
| | +-left_join_attributes=
| | | +-AttributeReference[id=1,name=i,relation=,type=Int]
| | +-right_join_attributes=
| |   +-AttributeReference[id=4,name=generate_series,alias=gs3,
| |     relation=generate_series,type=Int]
| +-right=TableGenerator[function_name=generate_series,table_alias=gs2]
| | +-AttributeReference[id=2,name=generate_series,alias=gs2,
| |   relation=generate_series,type=Int]
| +-project_expressions=
| | +-AttributeReference[id=1,name=i,relation=,type=Int]
| +-left_join_attributes=
| | +-AttributeReference[id=1,name=i,relation=,type=Int]
| +-right_join_attributes=
|   +-AttributeReference[id=2,name=generate_series,alias=gs2,
|     relation=generate_series,type=Int]
+-output_attributes=
  +-AttributeReference[id=1,name=i,relation=,type=Int]
==

# IN predicate
SELECT char_col
FROM test
WHERE int_col IN (1, 2, 3);
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-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]
| | +-filter_predicate=InValueList
| |   +-test_expression=AttributeReference[id=0,name=int_col,relation=test,
| |   | type=Int NULL]
| |   +-match_expressions=
| |     +-Literal[value=1,type=Int]
| |     +-Literal[value=2,type=Int]
| |     +-Literal[value=3,type=Int]
| +-project_list=
|   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+-output_attributes=
  +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=TableReference[relation=Test,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]
| +-filter_predicate=InValueList
| | +-test_expression=AttributeReference[id=0,name=int_col,relation=test,
| | | type=Int NULL]
| | +-match_expressions=
| |   +-Literal[value=1,type=Int]
| |   +-Literal[value=2,type=Int]
| |   +-Literal[value=3,type=Int]
| +-project_expressions=
|   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+-output_attributes=
  +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
==

SELECT char_col
FROM test
WHERE int_col*2 NOT IN (
        long_col+1,
        CASE WHEN float_col > 0 THEN 1
             ELSE double_col END);
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-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]
| | +-filter_predicate=NOT
| |   +-InValueList
| |     +-test_expression=Multiply
| |     | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| |     | +-Literal[value=2,type=Int]
| |     +-match_expressions=
| |       +-Add
| |       | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| |       | +-Literal[value=1,type=Int]
| |       +-SearchedCase
| |         +-else_result_expression=AttributeReference[id=3,name=double_col,
| |         | relation=test,type=Double NULL]
| |         +-condition_perdicates=
| |         | +-Greater
| |         |   +-AttributeReference[id=2,name=float_col,relation=test,
| |         |   | type=Float]
| |         |   +-Literal[value=0,type=Int]
| |         +-conditional_result_expressions=
| |           +-Cast[target_type=Double NULL]
| |             +-operand=Literal[value=1,type=Int]
| +-project_list=
|   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+-output_attributes=
  +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=TableReference[relation=Test,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]
| +-filter_predicate=NOT
| | +-InValueList
| |   +-test_expression=Multiply
| |   | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| |   | +-Literal[value=2,type=Int]
| |   +-match_expressions=
| |     +-Add
| |     | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| |     | +-Literal[value=1,type=Int]
| |     +-SearchedCase
| |       +-else_result_expression=AttributeReference[id=3,name=double_col,
| |       | relation=test,type=Double NULL]
| |       +-condition_perdicates=
| |       | +-Greater
| |       |   +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| |       |   +-Literal[value=0,type=Int]
| |       +-conditional_result_expressions=
| |         +-Cast[target_type=Double NULL]
| |           +-operand=Literal[value=1,type=Int]
| +-project_expressions=
|   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+-output_attributes=
  +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
==

SELECT char_col
FROM test
WHERE int_col IN (
  SELECT SUM(long_col) - 10
  FROM test
  GROUP BY vchar_col
);
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=HashLeftSemiJoin
| | +-left=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]
| | +-right=Project
| | | +-input=Aggregate
| | | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | | | +-AttributeReference[id=9,name=double_col,relation=test,
| | | | | | type=Double NULL]
| | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | | | |   type=VarChar(20) NULL]
| | | | +-grouping_expressions=
| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | | | |   type=VarChar(20) NULL]
| | | | +-aggregate_expressions=
| | | |   +-Alias[id=12,name=,alias=$aggregate0,relation=$aggregate,
| | | |     type=Long NULL]
| | | |     +-AggregateFunction[function=SUM]
| | | |       +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | +-project_list=
| | |   +-Alias[id=13,name=,alias=(SUM(long_col)-10),relation=,type=Long NULL]
| | |     +-Subtract
| | |       +-AttributeReference[id=12,name=,alias=$aggregate0,
| | |       | relation=$aggregate,type=Long NULL]
| | |       +-Literal[value=10,type=Int]
| | +-left_join_attributes=
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | +-right_join_attributes=
| |   +-AttributeReference[id=13,name=,alias=(SUM(long_col)-10),relation=,
| |     type=Long NULL]
| +-project_list=
|   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+-output_attributes=
  +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
[Physical Plan]
TopLevelPlan
+-plan=HashLeftSemiJoin
| +-left=TableReference[relation=Test,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]
| +-right=Selection
| | +-input=Aggregate
| | | +-input=TableReference[relation=Test,alias=test]
| | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL]
| | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | | |   type=VarChar(20) NULL]
| | | +-grouping_expressions=
| | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | | |   type=VarChar(20) NULL]
| | | +-aggregate_expressions=
| | |   +-Alias[id=12,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
| | |     +-AggregateFunction[function=SUM]
| | |       +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | +-project_expressions=
| |   +-Alias[id=13,name=,alias=(SUM(long_col)-10),relation=,type=Long NULL]
| |     +-Subtract
| |       +-AttributeReference[id=12,name=,alias=$aggregate0,
| |       | relation=$aggregate,type=Long NULL]
| |       +-Literal[value=10,type=Int]
| +-project_expressions=
| | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| +-left_join_attributes=
| | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| +-right_join_attributes=
|   +-AttributeReference[id=13,name=,alias=(SUM(long_col)-10),relation=,
|     type=Long NULL]
+-output_attributes=
  +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
==

SELECT char_col
FROM test
WHERE int_col NOT IN (
  SELECT long_col
  FROM test
  WHERE long_col IN (1, 2)
);
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=HashLeftAntiJoin
| | +-left=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]
| | +-right=Project
| | | +-input=Filter
| | | | +-input=TableReference[relation_name=Test,relation_alias=test]
| | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | | | +-AttributeReference[id=9,name=double_col,relation=test,
| | | | | | type=Double NULL]
| | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | | | |   type=VarChar(20) NULL]
| | | | +-filter_predicate=InValueList
| | | |   +-test_expression=AttributeReference[id=7,name=long_col,relation=test,
| | | |   | type=Long]
| | | |   +-match_expressions=
| | | |     +-Literal[value=1,type=Long]
| | | |     +-Literal[value=2,type=Long]
| | | +-project_list=
| | |   +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | +-left_join_attributes=
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | +-right_join_attributes=
| |   +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| +-project_list=
|   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
+-output_attributes=
  +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
[Physical Plan]
TopLevelPlan
+-plan=HashLeftAntiJoin
| +-left=TableReference[relation=Test,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]
| +-right=Selection
| | +-input=TableReference[relation=Test,alias=test]
| | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL]
| | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | |   type=VarChar(20) NULL]
| | +-filter_predicate=InValueList
| | | +-test_expression=AttributeReference[id=7,name=long_col,relation=test,
| | | | type=Long]
| | | +-match_expressions=
| | |   +-Literal[value=1,type=Long]
| | |   +-Literal[value=2,type=Long]
| | +-project_expressions=
| |   +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| +-project_expressions=
| | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| +-left_join_attributes=
| | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| +-right_join_attributes=
|   +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
+-output_attributes=
  +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
==


# Scalar subquery expressions
SELECT x + (SELECT SUM(y) FROM c)
FROM b;
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=NestedLoopsJoin
| | +-left=TableReference[relation_name=b]
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| | +-right=Project
| | | +-input=Aggregate
| | | | +-input=TableReference[relation_name=c]
| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | | +-grouping_expressions=
| | | | | +-[]
| | | | +-aggregate_expressions=
| | | |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
| | | |     type=Long NULL]
| | | |     +-AggregateFunction[function=SUM]
| | | |       +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | +-project_list=
| | |   +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
| | |     +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
| | |       type=Long NULL]
| | +-join_predicate=Literal[value=true]
| +-project_list=
|   +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
|     +-Add
|       +-AttributeReference[id=1,name=x,relation=b,type=Int]
|       +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+-output_attributes=
  +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=,
    type=Long NULL]
[Physical Plan]
TopLevelPlan
+-plan=NestedLoopsJoin
| +-left=TableReference[relation=b]
| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| +-right=Aggregate
| | +-input=TableReference[relation=c]
| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | +-grouping_expressions=
| | | +-[]
| | +-aggregate_expressions=
| |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
| |     +-AggregateFunction[function=SUM]
| |       +-AttributeReference[id=3,name=y,relation=c,type=Int]
| +-join_predicate=Literal[value=true]
| +-project_expressions=
|   +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
|     +-Add
|       +-AttributeReference[id=1,name=x,relation=b,type=Int]
|       +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
|         type=Long NULL]
+-output_attributes=
  +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=,
    type=Long NULL]
==

SELECT x + (SELECT SUM(y) FROM c WHERE b.w = c.x)
FROM b;
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=HashJoin
| | +-left=TableReference[relation_name=b]
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| | +-right=Project
| | | +-input=Aggregate
| | | | +-input=TableReference[relation_name=c]
| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | | +-grouping_expressions=
| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | | +-aggregate_expressions=
| | | |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
| | | |     type=Long NULL]
| | | |     +-AggregateFunction[function=SUM]
| | | |       +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | +-project_list=
| | |   +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | |   +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
| | |     +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
| | |       type=Long NULL]
| | +-left_join_attributes=
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | +-right_join_attributes=
| |   +-AttributeReference[id=2,name=x,relation=c,type=Int]
| +-project_list=
|   +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
|     +-Add
|       +-AttributeReference[id=1,name=x,relation=b,type=Int]
|       +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
+-output_attributes=
  +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=,
    type=Long NULL]
[Physical Plan]
TopLevelPlan
+-plan=HashJoin
| +-left=Aggregate
| | +-input=TableReference[relation=c]
| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | +-grouping_expressions=
| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | +-aggregate_expressions=
| |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
| |     +-AggregateFunction[function=SUM]
| |       +-AttributeReference[id=3,name=y,relation=c,type=Int]
| +-right=TableReference[relation=b]
| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| +-project_expressions=
| | +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
| |   +-Add
| |     +-AttributeReference[id=1,name=x,relation=b,type=Int]
| |     +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
| |       type=Long NULL]
| +-left_join_attributes=
| | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| +-right_join_attributes=
|   +-AttributeReference[id=0,name=w,relation=b,type=Int]
+-output_attributes=
  +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=,
    type=Long NULL]
==

SELECT *
FROM b
WHERE b.x > (SELECT SUM(y) FROM c);
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=NestedLoopsJoin
| | +-left=TableReference[relation_name=b]
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| | +-right=Project
| | | +-input=Aggregate
| | | | +-input=TableReference[relation_name=c]
| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | | +-grouping_expressions=
| | | | | +-[]
| | | | +-aggregate_expressions=
| | | |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
| | | |     type=Long NULL]
| | | |     +-AggregateFunction[function=SUM]
| | | |       +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | +-project_list=
| | |   +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
| | |     +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
| | |       type=Long NULL]
| | +-join_predicate=Greater
| |   +-AttributeReference[id=1,name=x,relation=b,type=Int]
| |   +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
| +-project_list=
|   +-AttributeReference[id=0,name=w,relation=b,type=Int]
|   +-AttributeReference[id=1,name=x,relation=b,type=Int]
+-output_attributes=
  +-AttributeReference[id=0,name=w,relation=b,type=Int]
  +-AttributeReference[id=1,name=x,relation=b,type=Int]
[Physical Plan]
TopLevelPlan
+-plan=NestedLoopsJoin
| +-left=TableReference[relation=b]
| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| +-right=Aggregate
| | +-input=TableReference[relation=c]
| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | +-grouping_expressions=
| | | +-[]
| | +-aggregate_expressions=
| |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
| |     +-AggregateFunction[function=SUM]
| |       +-AttributeReference[id=3,name=y,relation=c,type=Int]
| +-join_predicate=Greater
| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
| |   type=Long NULL]
| +-project_expressions=
|   +-AttributeReference[id=0,name=w,relation=b,type=Int]
|   +-AttributeReference[id=1,name=x,relation=b,type=Int]
+-output_attributes=
  +-AttributeReference[id=0,name=w,relation=b,type=Int]
  +-AttributeReference[id=1,name=x,relation=b,type=Int]
==

SELECT *
FROM b
WHERE b.x > (SELECT SUM(y) FROM c WHERE b.w = c.x);
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=Filter
| | +-input=HashJoin
| | | +-left=TableReference[relation_name=b]
| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| | | +-right=Project
| | | | +-input=Aggregate
| | | | | +-input=TableReference[relation_name=c]
| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | | | +-grouping_expressions=
| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | | | +-aggregate_expressions=
| | | | |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
| | | | |     type=Long NULL]
| | | | |     +-AggregateFunction[function=SUM]
| | | | |       +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | | +-project_list=
| | | |   +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | |   +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
| | | |     +-AttributeReference[id=4,name=,alias=$aggregate0,
| | | |       relation=$aggregate,type=Long NULL]
| | | +-left_join_attributes=
| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | | +-right_join_attributes=
| | |   +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | +-filter_predicate=Greater
| |   +-AttributeReference[id=1,name=x,relation=b,type=Int]
| |   +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
| +-project_list=
|   +-AttributeReference[id=0,name=w,relation=b,type=Int]
|   +-AttributeReference[id=1,name=x,relation=b,type=Int]
+-output_attributes=
  +-AttributeReference[id=0,name=w,relation=b,type=Int]
  +-AttributeReference[id=1,name=x,relation=b,type=Int]
[Physical Plan]
TopLevelPlan
+-plan=HashJoin
| +-left=Aggregate
| | +-input=TableReference[relation=c]
| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | +-grouping_expressions=
| | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | +-aggregate_expressions=
| |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
| |     +-AggregateFunction[function=SUM]
| |       +-AttributeReference[id=3,name=y,relation=c,type=Int]
| +-right=TableReference[relation=b]
| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| +-residual_predicate=Greater
| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
| |   type=Long NULL]
| +-project_expressions=
| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| +-left_join_attributes=
| | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| +-right_join_attributes=
|   +-AttributeReference[id=0,name=w,relation=b,type=Int]
+-output_attributes=
  +-AttributeReference[id=0,name=w,relation=b,type=Int]
  +-AttributeReference[id=1,name=x,relation=b,type=Int]
==

SELECT x + (
  SELECT SUM(y) + (SELECT SUM(w) FROM a WHERE a.y > 10)
  FROM c
  WHERE b.w = c.x AND c.x < 10)
FROM b;
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=HashJoin
| | +-left=TableReference[relation_name=b]
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| | +-right=Project
| | | +-input=NestedLoopsJoin
| | | | +-left=Aggregate
| | | | | +-input=Filter
| | | | | | +-input=TableReference[relation_name=c]
| | | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | | | | +-filter_predicate=Less
| | | | | |   +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | | | |   +-Literal[value=10,type=Int]
| | | | | +-grouping_expressions=
| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | | | +-aggregate_expressions=
| | | | |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
| | | | |     type=Long NULL]
| | | | |     +-AggregateFunction[function=SUM]
| | | | |       +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | | +-right=Project
| | | | | +-input=Aggregate
| | | | | | +-input=Filter
| | | | | | | +-input=TableReference[relation_name=a]
| | | | | | | | +-AttributeReference[id=5,name=w,relation=a,type=Int]
| | | | | | | | +-AttributeReference[id=6,name=x,relation=a,type=Int]
| | | | | | | | +-AttributeReference[id=7,name=y,relation=a,type=Int]
| | | | | | | | +-AttributeReference[id=8,name=z,relation=a,type=Int]
| | | | | | | +-filter_predicate=Greater
| | | | | | |   +-AttributeReference[id=7,name=y,relation=a,type=Int]
| | | | | | |   +-Literal[value=10,type=Int]
| | | | | | +-grouping_expressions=
| | | | | | | +-[]
| | | | | | +-aggregate_expressions=
| | | | | |   +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate,
| | | | | |     type=Long NULL]
| | | | | |     +-AggregateFunction[function=SUM]
| | | | | |       +-AttributeReference[id=5,name=w,relation=a,type=Int]
| | | | | +-project_list=
| | | | |   +-Alias[id=9,name=,alias=SUM(w),relation=,type=Long NULL]
| | | | |     +-AttributeReference[id=9,name=,alias=$aggregate0,
| | | | |       relation=$aggregate,type=Long NULL]
| | | | +-join_predicate=Literal[value=true]
| | | +-project_list=
| | |   +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | |   +-Alias[id=10,name=,alias=(SUM(y)+SubqueryExpression),relation=,
| | |     type=Long NULL]
| | |     +-Add
| | |       +-AttributeReference[id=4,name=,alias=$aggregate0,
| | |       | relation=$aggregate,type=Long NULL]
| | |       +-AttributeReference[id=9,name=,alias=SUM(w),relation=,
| | |         type=Long NULL]
| | +-left_join_attributes=
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | +-right_join_attributes=
| |   +-AttributeReference[id=2,name=x,relation=c,type=Int]
| +-project_list=
|   +-Alias[id=11,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
|     +-Add
|       +-AttributeReference[id=1,name=x,relation=b,type=Int]
|       +-AttributeReference[id=10,name=,alias=(SUM(y)+SubqueryExpression),
|         relation=,type=Long NULL]
+-output_attributes=
  +-AttributeReference[id=11,name=,alias=(x+SubqueryExpression),relation=,
    type=Long NULL]
[Physical Plan]
TopLevelPlan
+-plan=HashJoin
| +-left=NestedLoopsJoin
| | +-left=Aggregate
| | | +-input=TableReference[relation=c]
| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | +-filter_predicate=Less
| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | | +-Literal[value=10,type=Int]
| | | +-grouping_expressions=
| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | +-aggregate_expressions=
| | |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
| | |     +-AggregateFunction[function=SUM]
| | |       +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | +-right=Aggregate
| | | +-input=TableReference[relation=a]
| | | | +-AttributeReference[id=5,name=w,relation=a,type=Int]
| | | | +-AttributeReference[id=6,name=x,relation=a,type=Int]
| | | | +-AttributeReference[id=7,name=y,relation=a,type=Int]
| | | | +-AttributeReference[id=8,name=z,relation=a,type=Int]
| | | +-filter_predicate=Greater
| | | | +-AttributeReference[id=7,name=y,relation=a,type=Int]
| | | | +-Literal[value=10,type=Int]
| | | +-grouping_expressions=
| | | | +-[]
| | | +-aggregate_expressions=
| | |   +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
| | |     +-AggregateFunction[function=SUM]
| | |       +-AttributeReference[id=5,name=w,relation=a,type=Int]
| | +-join_predicate=Literal[value=true]
| | +-project_expressions=
| |   +-AttributeReference[id=2,name=x,relation=c,type=Int]
| |   +-Alias[id=10,name=,alias=(SUM(y)+SubqueryExpression),relation=,
| |     type=Long NULL]
| |     +-Add
| |       +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
| |       | type=Long NULL]
| |       +-AttributeReference[id=9,name=,alias=$aggregate0,relation=$aggregate,
| |         type=Long NULL]
| +-right=TableReference[relation=b]
| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| +-project_expressions=
| | +-Alias[id=11,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL]
| |   +-Add
| |     +-AttributeReference[id=1,name=x,relation=b,type=Int]
| |     +-AttributeReference[id=10,name=,alias=(SUM(y)+SubqueryExpression),
| |       relation=,type=Long NULL]
| +-left_join_attributes=
| | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| +-right_join_attributes=
|   +-AttributeReference[id=0,name=w,relation=b,type=Int]
+-output_attributes=
  +-AttributeReference[id=11,name=,alias=(x+SubqueryExpression),relation=,
    type=Long NULL]
==

SELECT x * (SELECT SUM(y) FROM c) + (SELECT AVG(y) FROM d)
FROM b;
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=NestedLoopsJoin
| | +-left=TableReference[relation_name=b]
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| | +-right=NestedLoopsJoin
| | | +-left=Project
| | | | +-input=Aggregate
| | | | | +-input=TableReference[relation_name=c]
| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | | | +-grouping_expressions=
| | | | | | +-[]
| | | | | +-aggregate_expressions=
| | | | |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
| | | | |     type=Long NULL]
| | | | |     +-AggregateFunction[function=SUM]
| | | | |       +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | | +-project_list=
| | | |   +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
| | | |     +-AttributeReference[id=4,name=,alias=$aggregate0,
| | | |       relation=$aggregate,type=Long NULL]
| | | +-right=Project
| | | | +-input=Aggregate
| | | | | +-input=TableReference[relation_name=d]
| | | | | | +-AttributeReference[id=5,name=y,relation=d,type=Int]
| | | | | | +-AttributeReference[id=6,name=z,relation=d,type=Int]
| | | | | +-grouping_expressions=
| | | | | | +-[]
| | | | | +-aggregate_expressions=
| | | | |   +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,
| | | | |     type=Double NULL]
| | | | |     +-AggregateFunction[function=AVG]
| | | | |       +-AttributeReference[id=5,name=y,relation=d,type=Int]
| | | | +-project_list=
| | | |   +-Alias[id=7,name=,alias=AVG(y),relation=,type=Double NULL]
| | | |     +-AttributeReference[id=7,name=,alias=$aggregate0,
| | | |       relation=$aggregate,type=Double NULL]
| | | +-join_predicate=Literal[value=true]
| | +-join_predicate=Literal[value=true]
| +-project_list=
|   +-Alias[id=8,name=,alias=((x*SubqueryExpression)+SubqueryExpression),
|     relation=,type=Double NULL]
|     +-Add
|       +-Multiply
|       | +-AttributeReference[id=1,name=x,relation=b,type=Int]
|       | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
|       +-AttributeReference[id=7,name=,alias=AVG(y),relation=,type=Double NULL]
+-output_attributes=
  +-AttributeReference[id=8,name=,
    alias=((x*SubqueryExpression)+SubqueryExpression),relation=,type=Double NULL]
[Physical Plan]
TopLevelPlan
+-plan=NestedLoopsJoin
| +-left=TableReference[relation=b]
| | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| +-right=NestedLoopsJoin
| | +-left=Aggregate
| | | +-input=TableReference[relation=c]
| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int]
| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | | +-grouping_expressions=
| | | | +-[]
| | | +-aggregate_expressions=
| | |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
| | |     +-AggregateFunction[function=SUM]
| | |       +-AttributeReference[id=3,name=y,relation=c,type=Int]
| | +-right=Aggregate
| | | +-input=TableReference[relation=d]
| | | | +-AttributeReference[id=5,name=y,relation=d,type=Int]
| | | | +-AttributeReference[id=6,name=z,relation=d,type=Int]
| | | +-grouping_expressions=
| | | | +-[]
| | | +-aggregate_expressions=
| | |   +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,
| | |     type=Double NULL]
| | |     +-AggregateFunction[function=AVG]
| | |       +-AttributeReference[id=5,name=y,relation=d,type=Int]
| | +-join_predicate=Literal[value=true]
| | +-project_expressions=
| |   +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
| |   | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
| |   |   type=Long NULL]
| |   +-Alias[id=7,name=,alias=AVG(y),relation=,type=Double NULL]
| |     +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate,
| |       type=Double NULL]
| +-join_predicate=Literal[value=true]
| +-project_expressions=
|   +-Alias[id=8,name=,alias=((x*SubqueryExpression)+SubqueryExpression),
|     relation=,type=Double NULL]
|     +-Add
|       +-Multiply
|       | +-AttributeReference[id=1,name=x,relation=b,type=Int]
|       | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL]
|       +-AttributeReference[id=7,name=,alias=AVG(y),relation=,type=Double NULL]
+-output_attributes=
  +-AttributeReference[id=8,name=,
    alias=((x*SubqueryExpression)+SubqueryExpression),relation=,type=Double NULL]
==

SELECT x * (SELECT SUM(y) FROM c)
FROM b
WHERE w < (SELECT AVG(y) FROM d);
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=NestedLoopsJoin
| | +-left=NestedLoopsJoin
| | | +-left=TableReference[relation_name=b]
| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| | | +-right=Project
| | | | +-input=Aggregate
| | | | | +-input=TableReference[relation_name=d]
| | | | | | +-AttributeReference[id=2,name=y,relation=d,type=Int]
| | | | | | +-AttributeReference[id=3,name=z,relation=d,type=Int]
| | | | | +-grouping_expressions=
| | | | | | +-[]
| | | | | +-aggregate_expressions=
| | | | |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
| | | | |     type=Double NULL]
| | | | |     +-AggregateFunction[function=AVG]
| | | | |       +-AttributeReference[id=2,name=y,relation=d,type=Int]
| | | | +-project_list=
| | | |   +-Alias[id=4,name=,alias=AVG(y),relation=,type=Double NULL]
| | | |     +-AttributeReference[id=4,name=,alias=$aggregate0,
| | | |       relation=$aggregate,type=Double NULL]
| | | +-join_predicate=Less
| | |   +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | |   +-AttributeReference[id=4,name=,alias=AVG(y),relation=,type=Double NULL]
| | +-right=Project
| | | +-input=Aggregate
| | | | +-input=TableReference[relation_name=c]
| | | | | +-AttributeReference[id=5,name=x,relation=c,type=Int]
| | | | | +-AttributeReference[id=6,name=y,relation=c,type=Int]
| | | | +-grouping_expressions=
| | | | | +-[]
| | | | +-aggregate_expressions=
| | | |   +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,
| | | |     type=Long NULL]
| | | |     +-AggregateFunction[function=SUM]
| | | |       +-AttributeReference[id=6,name=y,relation=c,type=Int]
| | | +-project_list=
| | |   +-Alias[id=7,name=,alias=SUM(y),relation=,type=Long NULL]
| | |     +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate,
| | |       type=Long NULL]
| | +-join_predicate=Literal[value=true]
| +-project_list=
|   +-Alias[id=8,name=,alias=(x*SubqueryExpression),relation=,type=Long NULL]
|     +-Multiply
|       +-AttributeReference[id=1,name=x,relation=b,type=Int]
|       +-AttributeReference[id=7,name=,alias=SUM(y),relation=,type=Long NULL]
+-output_attributes=
  +-AttributeReference[id=8,name=,alias=(x*SubqueryExpression),relation=,
    type=Long NULL]
[Physical Plan]
TopLevelPlan
+-plan=NestedLoopsJoin
| +-left=NestedLoopsJoin
| | +-left=TableReference[relation=b]
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int]
| | +-right=Aggregate
| | | +-input=TableReference[relation=d]
| | | | +-AttributeReference[id=2,name=y,relation=d,type=Int]
| | | | +-AttributeReference[id=3,name=z,relation=d,type=Int]
| | | +-grouping_expressions=
| | | | +-[]
| | | +-aggregate_expressions=
| | |   +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,
| | |     type=Double NULL]
| | |     +-AggregateFunction[function=AVG]
| | |       +-AttributeReference[id=2,name=y,relation=d,type=Int]
| | +-join_predicate=Less
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int]
| | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate,
| | |   type=Double NULL]
| | +-project_expressions=
| |   +-AttributeReference[id=1,name=x,relation=b,type=Int]
| +-right=Aggregate
| | +-input=TableReference[relation=c]
| | | +-AttributeReference[id=5,name=x,relation=c,type=Int]
| | | +-AttributeReference[id=6,name=y,relation=c,type=Int]
| | +-grouping_expressions=
| | | +-[]
| | +-aggregate_expressions=
| |   +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL]
| |     +-AggregateFunction[function=SUM]
| |       +-AttributeReference[id=6,name=y,relation=c,type=Int]
| +-join_predicate=Literal[value=true]
| +-project_expressions=
|   +-Alias[id=8,name=,alias=(x*SubqueryExpression),relation=,type=Long NULL]
|     +-Multiply
|       +-AttributeReference[id=1,name=x,relation=b,type=Int]
|       +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate,
|         type=Long NULL]
+-output_attributes=
  +-AttributeReference[id=8,name=,alias=(x*SubqueryExpression),relation=,
    type=Long NULL]
==

# Same shared subplan referenced multiple times.
WITH t(x, y) AS (
  SELECT i % 5, i
  FROM generate_series(1, 20) AS g(i)
)
SELECT *
FROM t
WHERE t.y = (
  SELECT MAX(y)
  FROM t t1
  WHERE t.x = t1.x
)
ORDER BY x;
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=Sort[is_ascending=[true],nulls_first=[false]]
| | +-input=Filter
| | | +-input=HashJoin
| | | | +-left=SharedSubplanReference[subplan_id=0]
| | | | | +-referenced_attributes=
| | | | | | +-AttributeReference[id=3,name=x,relation=,type=Int]
| | | | | | +-AttributeReference[id=4,name=y,relation=,type=Int]
| | | | | +-output_attributes=
| | | | |   +-AttributeReference[id=5,name=x,relation=,type=Int]
| | | | |   +-AttributeReference[id=6,name=y,relation=,type=Int]
| | | | +-right=Project
| | | | | +-input=Aggregate
| | | | | | +-input=SharedSubplanReference[subplan_id=0]
| | | | | | | +-referenced_attributes=
| | | | | | | | +-AttributeReference[id=3,name=x,relation=,type=Int]
| | | | | | | | +-AttributeReference[id=4,name=y,relation=,type=Int]
| | | | | | | +-output_attributes=
| | | | | | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
| | | | | | |   +-AttributeReference[id=8,name=y,relation=,type=Int]
| | | | | | +-grouping_expressions=
| | | | | | | +-AttributeReference[id=7,name=x,relation=,type=Int]
| | | | | | +-aggregate_expressions=
| | | | | |   +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate,
| | | | | |     type=Int NULL]
| | | | | |     +-AggregateFunction[function=MAX]
| | | | | |       +-AttributeReference[id=8,name=y,relation=,type=Int]
| | | | | +-project_list=
| | | | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
| | | | |   +-Alias[id=9,name=,alias=MAX(y),relation=,type=Int NULL]
| | | | |     +-AttributeReference[id=9,name=,alias=$aggregate0,
| | | | |       relation=$aggregate,type=Int NULL]
| | | | +-left_join_attributes=
| | | | | +-AttributeReference[id=5,name=x,relation=,type=Int]
| | | | +-right_join_attributes=
| | | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
| | | +-filter_predicate=Equal
| | |   +-AttributeReference[id=6,name=y,relation=,type=Int]
| | |   +-AttributeReference[id=9,name=,alias=MAX(y),relation=,type=Int NULL]
| | +-sort_expressions=
| |   +-AttributeReference[id=5,name=x,relation=,type=Int]
| +-project_list=
|   +-AttributeReference[id=5,name=x,relation=,type=Int]
|   +-AttributeReference[id=6,name=y,relation=,type=Int]
+-shared_subplans=
| +-Project
|   +-input=TableGenerator[function_name=generate_series,table_alias=g]
|   | +-AttributeReference[id=0,name=generate_series,alias=g,
|   |   relation=generate_series,type=Int]
|   +-project_list=
|     +-Alias[id=3,name=x,relation=,type=Int]
|     | +-Modulo
|     |   +-AttributeReference[id=0,name=generate_series,alias=g,
|     |   | relation=generate_series,type=Int]
|     |   +-Literal[value=5,type=Int]
|     +-Alias[id=4,name=y,relation=,type=Int]
|       +-AttributeReference[id=0,name=generate_series,alias=g,
|         relation=generate_series,type=Int]
+-output_attributes=
  +-AttributeReference[id=5,name=x,relation=,type=Int]
  +-AttributeReference[id=6,name=y,relation=,type=Int]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=Sort[is_ascending=[true],nulls_first=[false]]
| | +-input=HashJoin
| | | +-left=SharedSubplanReference[subplan_id=0]
| | | | +-referenced_attributes=
| | | | | +-AttributeReference[id=3,name=x,relation=,type=Int]
| | | | | +-AttributeReference[id=4,name=y,relation=,type=Int]
| | | | +-output_attributes=
| | | |   +-AttributeReference[id=5,name=x,relation=,type=Int]
| | | |   +-AttributeReference[id=6,name=y,relation=,type=Int]
| | | +-right=Aggregate
| | | | +-input=SharedSubplanReference[subplan_id=0]
| | | | | +-referenced_attributes=
| | | | | | +-AttributeReference[id=3,name=x,relation=,type=Int]
| | | | | | +-AttributeReference[id=4,name=y,relation=,type=Int]
| | | | | +-output_attributes=
| | | | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
| | | | |   +-AttributeReference[id=8,name=y,relation=,type=Int]
| | | | +-grouping_expressions=
| | | | | +-AttributeReference[id=7,name=x,relation=,type=Int]
| | | | +-aggregate_expressions=
| | | |   +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate,type=Int NULL]
| | | |     +-AggregateFunction[function=MAX]
| | | |       +-AttributeReference[id=8,name=y,relation=,type=Int]
| | | +-project_expressions=
| | | | +-AttributeReference[id=5,name=x,relation=,type=Int]
| | | | +-AttributeReference[id=6,name=y,relation=,type=Int]
| | | +-left_join_attributes=
| | | | +-AttributeReference[id=5,name=x,relation=,type=Int]
| | | | +-AttributeReference[id=6,name=y,relation=,type=Int]
| | | +-right_join_attributes=
| | |   +-AttributeReference[id=7,name=x,relation=,type=Int]
| | |   +-AttributeReference[id=9,name=,alias=$aggregate0,relation=$aggregate,
| | |     type=Int NULL]
| | +-sort_attributes=
| |   +-AttributeReference[id=5,name=x,relation=,type=Int]
| +-project_expressions=
|   +-AttributeReference[id=5,name=x,relation=,type=Int]
|   +-AttributeReference[id=6,name=y,relation=,type=Int]
+-shared_subplans=
| +-Selection
|   +-input=TableGenerator[function_name=generate_series,table_alias=g]
|   | +-AttributeReference[id=0,name=generate_series,alias=g,
|   |   relation=generate_series,type=Int]
|   +-project_expressions=
|     +-Alias[id=3,name=x,relation=,type=Int]
|     | +-Modulo
|     |   +-AttributeReference[id=0,name=generate_series,alias=g,
|     |   | relation=generate_series,type=Int]
|     |   +-Literal[value=5,type=Int]
|     +-Alias[id=4,name=y,relation=,type=Int]
|       +-AttributeReference[id=0,name=generate_series,alias=g,
|         relation=generate_series,type=Int]
+-output_attributes=
  +-AttributeReference[id=5,name=x,relation=,type=Int]
  +-AttributeReference[id=6,name=y,relation=,type=Int]
==

# Window Aggregate Function Test.
SELECT avg(int_col) OVER w FROM test
WINDOW w AS
(PARTITION BY char_col
 ORDER BY long_col DESC NULLS LAST
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=WindowAggregate
| | +-input=Sort[is_ascending=[true,false],nulls_first=[false,false]]
| | | +-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]
| | | +-sort_expressions=
| | |   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | +-window_aggregate_expression=Alias[id=6,name=,alias=$window_aggregate0,
| |   relation=$window_aggregate,type=Double NULL]
| |   +-WindowAggregateFunction[function=AVG,window_name=w,is_ascending=[false],
| |     nulls_first=[false],frame_mode=row,num_preceding=-1,num_following=0]
| |     +-arguments=
| |     | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| |     +-partition_by=
| |     | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| |     +-order_by=
| |       +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| +-project_list=
|   +-Alias[id=6,name=,alias=avg(int_col),relation=,type=Double NULL]
|     +-AttributeReference[id=6,name=,alias=$window_aggregate0,
|       relation=$window_aggregate,type=Double NULL]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=avg(int_col),relation=,type=Double NULL]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=WindowAggregate
| | +-input=Sort[is_ascending=[true,false],nulls_first=[false,false]]
| | | +-input=TableReference[relation=Test,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]
| | | +-sort_attributes=
| | |   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | +-window_aggregate_expression=Alias[id=6,name=,alias=$window_aggregate0,
| |   relation=$window_aggregate,type=Double NULL]
| |   +-WindowAggregateFunction[function=AVG,window_name=w,is_ascending=[false],
| |     nulls_first=[false],frame_mode=row,num_preceding=-1,num_following=0]
| |     +-arguments=
| |     | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| |     +-partition_by=
| |     | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| |     +-order_by=
| |       +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| +-project_expressions=
|   +-Alias[id=6,name=,alias=avg(int_col),relation=,type=Double NULL]
|     +-AttributeReference[id=6,name=,alias=$window_aggregate0,
|       relation=$window_aggregate,type=Double NULL]
+-output_attributes=
  +-AttributeReference[id=6,name=,alias=avg(int_col),relation=,type=Double NULL]
==

SELECT int_col, sum(float_col) OVER
(PARTITION BY vchar_col, long_col
 ORDER BY double_col DESC NULLS LAST, int_col ASC NULLS FIRST
 RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING)
FROM test;
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=WindowAggregate
| | +-input=Sort[is_ascending=[true,true,false,true],
| | | nulls_first=[false,false,false,true]]
| | | +-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]
| | | +-sort_expressions=
| | |   +-AttributeReference[id=5,name=vchar_col,relation=test,
| | |   | type=VarChar(20) NULL]
| | |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | |   +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | +-window_aggregate_expression=Alias[id=6,name=,alias=$window_aggregate0,
| |   relation=$window_aggregate,type=Double NULL]
| |   +-WindowAggregateFunction[function=SUM,window_name=,
| |     is_ascending=[false,true],nulls_first=[false,true],frame_mode=range,
| |     num_preceding=3,num_following=3]
| |     +-arguments=
| |     | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| |     +-partition_by=
| |     | +-AttributeReference[id=5,name=vchar_col,relation=test,
| |     | | type=VarChar(20) NULL]
| |     | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| |     +-order_by=
| |       +-AttributeReference[id=3,name=double_col,relation=test,
| |       | type=Double NULL]
| |       +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| +-project_list=
|   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
|   +-Alias[id=6,name=,alias=sum(float_col),relation=,type=Double NULL]
|     +-AttributeReference[id=6,name=,alias=$window_aggregate0,
|       relation=$window_aggregate,type=Double NULL]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
  +-AttributeReference[id=6,name=,alias=sum(float_col),relation=,
    type=Double NULL]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=WindowAggregate
| | +-input=Sort[is_ascending=[true,true,false,true],
| | | nulls_first=[false,false,false,true]]
| | | +-input=TableReference[relation=Test,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]
| | | +-sort_attributes=
| | |   +-AttributeReference[id=5,name=vchar_col,relation=test,
| | |   | type=VarChar(20) NULL]
| | |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | |   +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL]
| | |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | +-window_aggregate_expression=Alias[id=6,name=,alias=$window_aggregate0,
| |   relation=$window_aggregate,type=Double NULL]
| |   +-WindowAggregateFunction[function=SUM,window_name=,
| |     is_ascending=[false,true],nulls_first=[false,true],frame_mode=range,
| |     num_preceding=3,num_following=3]
| |     +-arguments=
| |     | +-AttributeReference[id=2,name=float_col,relation=test,type=Float]
| |     +-partition_by=
| |     | +-AttributeReference[id=5,name=vchar_col,relation=test,
| |     | | type=VarChar(20) NULL]
| |     | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| |     +-order_by=
| |       +-AttributeReference[id=3,name=double_col,relation=test,
| |       | type=Double NULL]
| |       +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| +-project_expressions=
|   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
|   +-Alias[id=6,name=,alias=sum(float_col),relation=,type=Double NULL]
|     +-AttributeReference[id=6,name=,alias=$window_aggregate0,
|       relation=$window_aggregate,type=Double NULL]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
  +-AttributeReference[id=6,name=,alias=sum(float_col),relation=,
    type=Double NULL]
==

SELECT sum(avg(int_col) OVER w) FROM test
WINDOW w AS
(PARTITION BY char_col
 ORDER BY long_col
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=Aggregate
| | +-input=WindowAggregate
| | | +-input=Sort[is_ascending=[true,true],nulls_first=[false,false]]
| | | | +-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]
| | | | +-sort_expressions=
| | | |   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | +-window_aggregate_expression=Alias[id=6,name=,alias=$window_aggregate0,
| | |   relation=$window_aggregate,type=Double NULL]
| | |   +-WindowAggregateFunction[function=AVG,window_name=w,
| | |     is_ascending=[true],nulls_first=[false],frame_mode=row,
| | |     num_preceding=-1,num_following=0]
| | |     +-arguments=
| | |     | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | |     +-partition_by=
| | |     | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | |     +-order_by=
| | |       +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | +-grouping_expressions=
| | | +-[]
| | +-aggregate_expressions=
| |   +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,type=Double NULL]
| |     +-AggregateFunction[function=SUM]
| |       +-AttributeReference[id=6,name=,alias=$window_aggregate0,
| |         relation=$window_aggregate,type=Double NULL]
| +-project_list=
|   +-Alias[id=7,name=,alias=sum(avg(int_col)),relation=,type=Double NULL]
|     +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate,
|       type=Double NULL]
+-output_attributes=
  +-AttributeReference[id=7,name=,alias=sum(avg(int_col)),relation=,
    type=Double NULL]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=Aggregate
| | +-input=WindowAggregate
| | | +-input=Sort[is_ascending=[true,true],nulls_first=[false,false]]
| | | | +-input=TableReference[relation=Test,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]
| | | | +-sort_attributes=
| | | |   +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | | |   +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | +-window_aggregate_expression=Alias[id=6,name=,alias=$window_aggregate0,
| | |   relation=$window_aggregate,type=Double NULL]
| | |   +-WindowAggregateFunction[function=AVG,window_name=w,
| | |     is_ascending=[true],nulls_first=[false],frame_mode=row,
| | |     num_preceding=-1,num_following=0]
| | |     +-arguments=
| | |     | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | |     +-partition_by=
| | |     | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)]
| | |     +-order_by=
| | |       +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | +-grouping_expressions=
| | | +-[]
| | +-aggregate_expressions=
| |   +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,type=Double NULL]
| |     +-AggregateFunction[function=SUM]
| |       +-AttributeReference[id=6,name=,alias=$window_aggregate0,
| |         relation=$window_aggregate,type=Double NULL]
| +-project_expressions=
|   +-Alias[id=7,name=,alias=sum(avg(int_col)),relation=,type=Double NULL]
|     +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate,
|       type=Double NULL]
+-output_attributes=
  +-AttributeReference[id=7,name=,alias=sum(avg(int_col)),relation=,
    type=Double NULL]
==

SELECT int_col FROM test
UNION
SELECT int_col FROM test
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Union[set_operation_type=Union]
| +-operands=
| | +-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=
| | |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | +-Project
| |   +-input=TableReference[relation_name=Test,relation_alias=test]
| |   | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| |   | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| |   | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| |   | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL]
| |   | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| |   | +-AttributeReference[id=11,name=vchar_col,relation=test,
| |   |   type=VarChar(20) NULL]
| |   +-project_list=
| |     +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| +-project_attributes=
|   +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL]
[Physical Plan]
TopLevelPlan
+-plan=Aggregate
| +-input=UnionAll
| | +-operands=
| | | +-Selection
| | | | +-input=TableReference[relation=Test,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_expressions=
| | | |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-Selection
| | |   +-input=TableReference[relation=Test,alias=test]
| | |   | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | |   | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | |   | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | |   | +-AttributeReference[id=9,name=double_col,relation=test,
| | |   | | type=Double NULL]
| | |   | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | |   | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | |   |   type=VarChar(20) NULL]
| | |   +-project_expressions=
| | |     +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | +-project_attributes=
| |   +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL]
| +-grouping_expressions=
| | +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL]
| +-aggregate_expressions=
|   +-[]
+-output_attributes=
  +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL]
==

SELECT intv FROM
  (SELECT int_col, double_col FROM test
   UNION ALL
   SELECT int_col, double_col FROM test
  ) AS temp(intv, doublev)
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Project
| +-input=UnionAll[set_operation_type=UnionAll]
| | +-operands=
| | | +-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=
| | | |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | |   +-AttributeReference[id=3,name=double_col,relation=test,
| | | |     type=Double NULL]
| | | +-Project
| | |   +-input=TableReference[relation_name=Test,relation_alias=test]
| | |   | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | |   | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | |   | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | |   | +-AttributeReference[id=9,name=double_col,relation=test,
| | |   | | type=Double NULL]
| | |   | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | |   | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | |   |   type=VarChar(20) NULL]
| | |   +-project_list=
| | |     +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | |     +-AttributeReference[id=9,name=double_col,relation=test,
| | |       type=Double NULL]
| | +-project_attributes=
| |   +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL]
| |   +-AttributeReference[id=13,name=double_col,relation=,type=Double NULL]
| +-project_list=
|   +-Alias[id=14,name=intv,relation=,type=Int NULL]
|     +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=14,name=intv,relation=,type=Int NULL]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=UnionAll
| | +-operands=
| | | +-Selection
| | | | +-input=TableReference[relation=Test,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_expressions=
| | | |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | +-Selection
| | |   +-input=TableReference[relation=Test,alias=test]
| | |   | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | |   | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | |   | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | |   | +-AttributeReference[id=9,name=double_col,relation=test,
| | |   | | type=Double NULL]
| | |   | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | |   | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | |   |   type=VarChar(20) NULL]
| | |   +-project_expressions=
| | |     +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | +-project_attributes=
| |   +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL]
| +-project_expressions=
|   +-Alias[id=14,name=intv,relation=,type=Int NULL]
|     +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=14,name=intv,relation=,type=Int NULL]
==

SELECT int_col FROM test
INTERSECT
SELECT intv FROM
  (SELECT int_col, double_col FROM test
   UNION ALL
   SELECT int_col, double_col FROM test
  ) AS temp(intv, doublev)
--
[Optimized Logical Plan]
TopLevelPlan
+-plan=Intersect[set_operation_type=Intersect]
| +-operands=
| | +-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=
| | |   +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | +-Project
| |   +-input=UnionAll[set_operation_type=UnionAll]
| |   | +-operands=
| |   | | +-Project
| |   | | | +-input=TableReference[relation_name=Test,relation_alias=test]
| |   | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| |   | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| |   | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| |   | | | | +-AttributeReference[id=9,name=double_col,relation=test,
| |   | | | | | type=Double NULL]
| |   | | | | +-AttributeReference[id=10,name=char_col,relation=test,
| |   | | | | | type=Char(20)]
| |   | | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| |   | | | |   type=VarChar(20) NULL]
| |   | | | +-project_list=
| |   | | |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| |   | | |   +-AttributeReference[id=9,name=double_col,relation=test,
| |   | | |     type=Double NULL]
| |   | | +-Project
| |   | |   +-input=TableReference[relation_name=Test,relation_alias=test]
| |   | |   | +-AttributeReference[id=12,name=int_col,relation=test,
| |   | |   | | type=Int NULL]
| |   | |   | +-AttributeReference[id=13,name=long_col,relation=test,type=Long]
| |   | |   | +-AttributeReference[id=14,name=float_col,relation=test,type=Float]
| |   | |   | +-AttributeReference[id=15,name=double_col,relation=test,
| |   | |   | | type=Double NULL]
| |   | |   | +-AttributeReference[id=16,name=char_col,relation=test,
| |   | |   | | type=Char(20)]
| |   | |   | +-AttributeReference[id=17,name=vchar_col,relation=test,
| |   | |   |   type=VarChar(20) NULL]
| |   | |   +-project_list=
| |   | |     +-AttributeReference[id=12,name=int_col,relation=test,
| |   | |     | type=Int NULL]
| |   | |     +-AttributeReference[id=15,name=double_col,relation=test,
| |   | |       type=Double NULL]
| |   | +-project_attributes=
| |   |   +-AttributeReference[id=18,name=int_col,relation=,type=Int NULL]
| |   |   +-AttributeReference[id=19,name=double_col,relation=,type=Double NULL]
| |   +-project_list=
| |     +-Alias[id=20,name=intv,relation=,type=Int NULL]
| |       +-AttributeReference[id=18,name=int_col,relation=,type=Int NULL]
| +-project_attributes=
|   +-AttributeReference[id=22,name=int_col,relation=,type=Int NULL]
+-output_attributes=
  +-AttributeReference[id=22,name=int_col,relation=,type=Int NULL]
[Physical Plan]
TopLevelPlan
+-plan=Aggregate
| +-input=HashLeftSemiJoin
| | +-left=TableReference[relation=Test,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]
| | +-right=UnionAll
| | | +-operands=
| | | | +-Selection
| | | | | +-input=TableReference[relation=Test,alias=test]
| | | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long]
| | | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float]
| | | | | | +-AttributeReference[id=9,name=double_col,relation=test,
| | | | | | | type=Double NULL]
| | | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)]
| | | | | | +-AttributeReference[id=11,name=vchar_col,relation=test,
| | | | | |   type=VarChar(20) NULL]
| | | | | +-project_expressions=
| | | | |   +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL]
| | | | +-Selection
| | | |   +-input=TableReference[relation=Test,alias=test]
| | | |   | +-AttributeReference[id=12,name=int_col,relation=test,type=Int NULL]
| | | |   | +-AttributeReference[id=13,name=long_col,relation=test,type=Long]
| | | |   | +-AttributeReference[id=14,name=float_col,relation=test,type=Float]
| | | |   | +-AttributeReference[id=15,name=double_col,relation=test,
| | | |   | | type=Double NULL]
| | | |   | +-AttributeReference[id=16,name=char_col,relation=test,type=Char(20)]
| | | |   | +-AttributeReference[id=17,name=vchar_col,relation=test,
| | | |   |   type=VarChar(20) NULL]
| | | |   +-project_expressions=
| | | |     +-AttributeReference[id=12,name=int_col,relation=test,type=Int NULL]
| | | +-project_attributes=
| | |   +-AttributeReference[id=18,name=int_col,relation=,type=Int NULL]
| | +-project_expressions=
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | +-left_join_attributes=
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | +-right_join_attributes=
| |   +-AttributeReference[id=18,name=int_col,relation=,type=Int NULL]
| +-grouping_expressions=
| | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| +-aggregate_expressions=
|   +-[]
+-output_attributes=
  +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]