Google Git
Sign in
apache / incubator-retired-quickstep / a97d7f2c2dfdeb1b5f364bbd87145dd49051236c / . / query_optimizer / tests / physical_generator / Select.test
blob: d653a9ba5cb0f6c90d212d7a68aaa44294c5bb6a [file] [log] [blame]
# Copyright 2011-2015 Quickstep Technologies LLC.
# Copyright 2015 Pivotal Software, Inc.
#
# Licensed 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=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]
| +-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]
| | +-AttributeReference[id=9,name=,alias=$aggregate3,relation=$aggregate,
| | type=Double NULL]
| +-project_expressions=
| +-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]
+-output_attributes=
+-AttributeReference[id=10,name=col,relation=,type=Double 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]
| | | +-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]
| | | +-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
| | | +-Add
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL]
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long]
| | | +-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
| | | +-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]
| | | +-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]
| | +-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]
| | +-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=3,name=double_col,relation=test,type=Double NULL]
| +-project_list=
| +-AttributeReference[id=0,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=0,name=int_col,relation=test,type=Int NULL]
[Physical Plan]
TopLevelPlan
+-plan=Selection
| +-input=SharedSubplanReference[subplan_id=0]
| | +-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=3,name=double_col,relation=test,type=Double NULL]
| +-project_expressions=
| +-AttributeReference[id=0,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=0,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]