| # Copyright 2011-2015 Quickstep Technologies LLC. |
| # Copyright 2015 Pivotal Software, Inc. |
| # Copyright 2016, Quickstep Research Group, Computer Sciences Department, |
| # University of Wisconsin—Madison. |
| # |
| # 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] |
| == |
| |
| 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] |