| # Licensed to the Apache Software Foundation (ASF) under one |
| # or more contributor license agreements. See the NOTICE file |
| # distributed with this work for additional information |
| # regarding copyright ownership. The ASF licenses this file |
| # to you under the Apache License, Version 2.0 (the |
| # "License"); you may not use this file except in compliance |
| # with the License. You may obtain a copy of the License at |
| # |
| # http://www.apache.org/licenses/LICENSE-2.0 |
| # |
| # Unless required by applicable law or agreed to in writing, |
| # software distributed under the License is distributed on an |
| # "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| # KIND, either express or implied. See the License for the |
| # specific language governing permissions and limitations |
| # under the License. |
| |
| [default optimized_logical_plan physical_plan] |
| select * from test |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,type=VarChar(20) NULL] |
| | +-project_list= |
| | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | +-AttributeReference[id=5,name=vchar_col,relation=test,type=VarChar(20) NULL] |
| +-output_attributes= |
| +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| +-AttributeReference[id=5,name=vchar_col,relation=test,type=VarChar(20) NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Selection[has_repartition=false] |
| | +-input=TableReference[relation=Test,alias=test] |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,type=VarChar(20) NULL] |
| | +-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[has_repartition=false] |
| | +-input=TableReference[relation=Test,alias=test] |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,type=VarChar(20) NULL] |
| | +-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[has_repartition=false] |
| | +-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[has_repartition=false] |
| | +-input=TableReference[relation=Test,alias=test] |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,type=VarChar(20) NULL] |
| | +-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[has_repartition=false] |
| | +-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[has_repartition=false] |
| | +-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[has_repartition=false] |
| | | +-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[has_repartition=false] |
| | +-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[has_repartition=false] |
| | | +-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[has_repartition=false] |
| | +-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[has_repartition=false] |
| | +-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[has_repartition=false] |
| | | +-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[has_repartition=false] |
| | +-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[has_repartition=false] |
| | +-left=HashJoin[has_repartition=false] |
| | | +-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[has_repartition=false] |
| | | +-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[has_repartition=false] |
| | +-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[has_repartition=false] |
| | | +-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[has_repartition=false] |
| | +-input=Aggregate[has_repartition=false] |
| | | +-input=TableReference[relation=Test,alias=test] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-grouping_expressions= |
| | | | +-[] |
| | | +-aggregate_expressions= |
| | | +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long] |
| | | | +-AggregateFunction[function=COUNT] |
| | | | +-[] |
| | | +-Alias[id=7,name=,alias=$aggregate1,relation=$aggregate,type=Long] |
| | | | +-AggregateFunction[function=COUNT] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-Alias[id=8,name=,alias=$aggregate2,relation=$aggregate,type=Long NULL] |
| | | | +-AggregateFunction[function=SUM] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-Alias[id=12,name=,alias=$aggregate3,relation=$aggregate,type=Long NULL] |
| | | | +-AggregateFunction[function=SUM] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-Alias[id=11,name=,alias=$aggregate4,relation=$aggregate,type=Double NULL] |
| | | +-AggregateFunction[function=MAX] |
| | | +-AttributeReference[id=3,name=double_col,relation=test, |
| | | type=Double NULL] |
| | +-filter_predicate=Greater |
| | | +-Add |
| | | | +-AttributeReference[id=11,name=,alias=$aggregate4,relation=$aggregate, |
| | | | | type=Double NULL] |
| | | | +-Literal[value=1,type=Int] |
| | | +-Add |
| | | +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate, |
| | | | type=Long] |
| | | +-Multiply |
| | | +-Divide |
| | | | +-AttributeReference[id=7,name=,alias=$aggregate1,relation=$aggregate, |
| | | | | type=Long] |
| | | | +-AttributeReference[id=8,name=,alias=$aggregate2,relation=$aggregate, |
| | | | type=Long NULL] |
| | | +-Divide |
| | | +-AttributeReference[id=12,name=,alias=$aggregate3, |
| | | | relation=$aggregate,type=Long NULL] |
| | | +-AttributeReference[id=7,name=,alias=$aggregate1,relation=$aggregate, |
| | | type=Long] |
| | +-project_expressions= |
| | +-Alias[id=10,name=col,relation=,type=Long NULL] |
| | +-Add |
| | +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate, |
| | | type=Long] |
| | +-Multiply |
| | +-Divide |
| | | +-AttributeReference[id=7,name=,alias=$aggregate1, |
| | | | relation=$aggregate,type=Long] |
| | | +-AttributeReference[id=8,name=,alias=$aggregate2, |
| | | relation=$aggregate,type=Long NULL] |
| | +-Divide |
| | +-AttributeReference[id=12,name=,alias=$aggregate3, |
| | | relation=$aggregate,type=Long NULL] |
| | +-AttributeReference[id=7,name=,alias=$aggregate1, |
| | relation=$aggregate,type=Long] |
| +-output_attributes= |
| +-AttributeReference[id=10,name=col,relation=,type=Long NULL] |
| == |
| |
| select long_col as col1, count(*) as col2 |
| from test |
| group by long_col+float_col, 1, float_col+1 |
| having max(col1+1)+1>col2/2 |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=Filter |
| | | +-input=Aggregate |
| | | | +-input=Project |
| | | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | | +-AttributeReference[id=3,name=double_col,relation=test, |
| | | | | | | type=Double NULL] |
| | | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | | type=VarChar(20) NULL] |
| | | | | +-project_list= |
| | | | | +-Alias[id=1,name=col1,relation=,type=Long] |
| | | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | +-AttributeReference[id=3,name=double_col,relation=test, |
| | | | | | type=Double NULL] |
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-grouping_expressions= |
| | | | | +-Alias[id=7,name=,alias=$groupby0,relation=$groupby,type=Double] |
| | | | | | +-Add |
| | | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | +-AttributeReference[id=1,name=col1,relation=,type=Long] |
| | | | | +-Alias[id=8,name=,alias=$groupby2,relation=$groupby,type=Float] |
| | | | | +-Add |
| | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | +-Literal[value=1,type=Int] |
| | | | +-aggregate_expressions= |
| | | | +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long] |
| | | | | +-AggregateFunction[function=COUNT] |
| | | | | +-[] |
| | | | +-Alias[id=9,name=,alias=$aggregate1,relation=$aggregate,type=Long NULL] |
| | | | +-AggregateFunction[function=MAX] |
| | | | +-Add |
| | | | +-AttributeReference[id=1,name=col1,relation=,type=Long] |
| | | | +-Literal[value=1,type=Int] |
| | | +-filter_predicate=Greater |
| | | +-Add |
| | | | +-AttributeReference[id=9,name=,alias=$aggregate1,relation=$aggregate, |
| | | | | type=Long NULL] |
| | | | +-Literal[value=1,type=Int] |
| | | +-Divide |
| | | +-AttributeReference[id=6,name=col2,relation=,type=Long] |
| | | +-Literal[value=2,type=Int] |
| | +-project_list= |
| | +-AttributeReference[id=1,name=col1,relation=,type=Long] |
| | +-Alias[id=6,name=col2,relation=,type=Long] |
| | +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate, |
| | type=Long] |
| +-output_attributes= |
| +-AttributeReference[id=1,name=col1,relation=,type=Long] |
| +-AttributeReference[id=6,name=col2,relation=,type=Long] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Selection[has_repartition=false] |
| | +-input=Aggregate[has_repartition=false] |
| | | +-input=TableReference[relation=Test,alias=test] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-grouping_expressions= |
| | | | +-Alias[id=7,name=,alias=$groupby0,relation=$groupby,type=Double] |
| | | | | +-Add |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-Alias[id=1,name=col1,relation=,type=Long] |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-Alias[id=8,name=,alias=$groupby2,relation=$groupby,type=Float] |
| | | | +-Add |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-Literal[value=1,type=Int] |
| | | +-aggregate_expressions= |
| | | +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long] |
| | | | +-AggregateFunction[function=COUNT] |
| | | | +-[] |
| | | +-Alias[id=9,name=,alias=$aggregate1,relation=$aggregate,type=Long NULL] |
| | | +-AggregateFunction[function=MAX] |
| | | +-Add |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-Literal[value=1,type=Int] |
| | +-filter_predicate=Greater |
| | | +-Add |
| | | | +-AttributeReference[id=9,name=,alias=$aggregate1,relation=$aggregate, |
| | | | | type=Long NULL] |
| | | | +-Literal[value=1,type=Int] |
| | | +-Divide |
| | | +-AttributeReference[id=6,name=col2,relation=,type=Long] |
| | | +-Literal[value=2,type=Int] |
| | +-project_expressions= |
| | +-AttributeReference[id=1,name=col1,relation=,type=Long] |
| | +-Alias[id=6,name=col2,relation=,type=Long] |
| | +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate, |
| | type=Long] |
| +-output_attributes= |
| +-AttributeReference[id=1,name=col1,relation=,type=Long] |
| +-AttributeReference[id=6,name=col2,relation=,type=Long] |
| == |
| |
| # Pull up Selection. |
| select count(subquery_col1+subquery_col2)+max(subquery_col1/2), subquery_col3 |
| from (select int_col+long_col subquery_col1, long_col+float_col subquery_col2, char_col subquery_col3, int_col |
| from test where int_col > 2) as subquery |
| group by int_col, subquery_col3 |
| having max(int_col+subquery_col1)>sum(subquery_col2)/2 |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=Filter |
| | | +-input=Aggregate |
| | | | +-input=Project |
| | | | | +-input=Filter |
| | | | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | | | +-AttributeReference[id=3,name=double_col,relation=test, |
| | | | | | | | type=Double NULL] |
| | | | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | | | type=VarChar(20) NULL] |
| | | | | | +-filter_predicate=Greater |
| | | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | | +-Literal[value=2,type=Int] |
| | | | | +-project_list= |
| | | | | +-Alias[id=6,name=subquery_col1,relation=subquery,type=Long NULL] |
| | | | | | +-Add |
| | | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | +-Alias[id=7,name=subquery_col2,relation=subquery,type=Double] |
| | | | | | +-Add |
| | | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | +-Alias[id=4,name=subquery_col3,relation=subquery,type=Char(20)] |
| | | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-grouping_expressions= |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=4,name=subquery_col3,relation=subquery, |
| | | | | type=Char(20)] |
| | | | +-aggregate_expressions= |
| | | | +-Alias[id=8,name=,alias=$aggregate0,relation=$aggregate,type=Long] |
| | | | | +-AggregateFunction[function=COUNT] |
| | | | | +-Add |
| | | | | +-AttributeReference[id=6,name=subquery_col1,relation=subquery, |
| | | | | | type=Long NULL] |
| | | | | +-AttributeReference[id=7,name=subquery_col2,relation=subquery, |
| | | | | type=Double] |
| | | | +-Alias[id=9,name=,alias=$aggregate1,relation=$aggregate,type=Long NULL] |
| | | | | +-AggregateFunction[function=MAX] |
| | | | | +-Divide |
| | | | | +-AttributeReference[id=6,name=subquery_col1,relation=subquery, |
| | | | | | type=Long NULL] |
| | | | | +-Literal[value=2,type=Int] |
| | | | +-Alias[id=11,name=,alias=$aggregate2,relation=$aggregate,type=Long NULL] |
| | | | | +-AggregateFunction[function=MAX] |
| | | | | +-Add |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=6,name=subquery_col1,relation=subquery, |
| | | | | type=Long NULL] |
| | | | +-Alias[id=12,name=,alias=$aggregate3,relation=$aggregate, |
| | | | type=Double NULL] |
| | | | +-AggregateFunction[function=SUM] |
| | | | +-AttributeReference[id=7,name=subquery_col2,relation=subquery, |
| | | | type=Double] |
| | | +-filter_predicate=Greater |
| | | +-AttributeReference[id=11,name=,alias=$aggregate2,relation=$aggregate, |
| | | | type=Long NULL] |
| | | +-Divide |
| | | +-AttributeReference[id=12,name=,alias=$aggregate3,relation=$aggregate, |
| | | | type=Double NULL] |
| | | +-Literal[value=2,type=Int] |
| | +-project_list= |
| | +-Alias[id=10,name=, |
| | | alias=(count((subquery_col1+subquery_col2))+max((subquery_col1/2))), |
| | | relation=,type=Long NULL] |
| | | +-Add |
| | | +-AttributeReference[id=8,name=,alias=$aggregate0,relation=$aggregate, |
| | | | type=Long] |
| | | +-AttributeReference[id=9,name=,alias=$aggregate1,relation=$aggregate, |
| | | type=Long NULL] |
| | +-AttributeReference[id=4,name=subquery_col3,relation=subquery,type=Char(20)] |
| +-output_attributes= |
| +-AttributeReference[id=10,name=, |
| | alias=(count((subquery_col1+subquery_col2))+max((subquery_col1/2))), |
| | relation=,type=Long NULL] |
| +-AttributeReference[id=4,name=subquery_col3,relation=subquery,type=Char(20)] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Selection[has_repartition=false] |
| | +-input=Aggregate[has_repartition=false] |
| | | +-input=TableReference[relation=Test,alias=test] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-filter_predicate=Greater |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-Literal[value=2,type=Int] |
| | | +-grouping_expressions= |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-Alias[id=4,name=subquery_col3,relation=subquery,type=Char(20)] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | +-aggregate_expressions= |
| | | +-Alias[id=8,name=,alias=$aggregate0,relation=$aggregate,type=Long] |
| | | | +-AggregateFunction[function=COUNT] |
| | | | +-Add |
| | | | +-CommonSubexpression[common_subexpression_id=13] |
| | | | | +-Operand=Add |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test, |
| | | | | | type=Int NULL] |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-CommonSubexpression[common_subexpression_id=14] |
| | | | +-Operand=Add |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test, |
| | | | type=Float] |
| | | +-Alias[id=9,name=,alias=$aggregate1,relation=$aggregate,type=Long NULL] |
| | | | +-AggregateFunction[function=MAX] |
| | | | +-Divide |
| | | | +-CommonSubexpression[common_subexpression_id=13] |
| | | | | +-Operand=Add |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test, |
| | | | | | type=Int NULL] |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-Literal[value=2,type=Int] |
| | | +-Alias[id=11,name=,alias=$aggregate2,relation=$aggregate,type=Long NULL] |
| | | | +-AggregateFunction[function=MAX] |
| | | | +-Add |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-CommonSubexpression[common_subexpression_id=13] |
| | | | +-Operand=Add |
| | | | +-AttributeReference[id=0,name=int_col,relation=test, |
| | | | | type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-Alias[id=12,name=,alias=$aggregate3,relation=$aggregate,type=Double NULL] |
| | | +-AggregateFunction[function=SUM] |
| | | +-CommonSubexpression[common_subexpression_id=14] |
| | | +-Operand=Add |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | +-filter_predicate=Greater |
| | | +-AttributeReference[id=11,name=,alias=$aggregate2,relation=$aggregate, |
| | | | type=Long NULL] |
| | | +-Divide |
| | | +-AttributeReference[id=12,name=,alias=$aggregate3,relation=$aggregate, |
| | | | type=Double NULL] |
| | | +-Literal[value=2,type=Int] |
| | +-project_expressions= |
| | +-Alias[id=10,name=, |
| | | alias=(count((subquery_col1+subquery_col2))+max((subquery_col1/2))), |
| | | relation=,type=Long NULL] |
| | | +-Add |
| | | +-AttributeReference[id=8,name=,alias=$aggregate0,relation=$aggregate, |
| | | | type=Long] |
| | | +-AttributeReference[id=9,name=,alias=$aggregate1,relation=$aggregate, |
| | | type=Long NULL] |
| | +-AttributeReference[id=4,name=subquery_col3,relation=subquery,type=Char(20)] |
| +-output_attributes= |
| +-AttributeReference[id=10,name=, |
| | alias=(count((subquery_col1+subquery_col2))+max((subquery_col1/2))), |
| | relation=,type=Long NULL] |
| +-AttributeReference[id=4,name=subquery_col3,relation=subquery,type=Char(20)] |
| == |
| |
| select int_col, count(*) |
| from test |
| group by int_col |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=Aggregate |
| | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-grouping_expressions= |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-aggregate_expressions= |
| | | +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long] |
| | | +-AggregateFunction[function=COUNT] |
| | | +-[] |
| | +-project_list= |
| | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | +-Alias[id=6,name=,alias=count(*),relation=,type=Long] |
| | +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate, |
| | type=Long] |
| +-output_attributes= |
| +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| +-AttributeReference[id=6,name=,alias=count(*),relation=,type=Long] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Selection[has_repartition=false] |
| | +-input=Aggregate[has_repartition=false] |
| | | +-input=TableReference[relation=Test,alias=test] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-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[has_repartition=false] |
| | +-input=Aggregate[has_repartition=false] |
| | | +-input=TableReference[relation=Test,alias=test] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-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[has_repartition=false] |
| | +-input=Aggregate[has_repartition=false] |
| | | +-input=TableReference[relation=Test,alias=test] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-grouping_expressions= |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-aggregate_expressions= |
| | | +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,type=Long] |
| | | +-AggregateFunction[function=COUNT] |
| | | +-[] |
| | +-project_expressions= |
| | +-Alias[id=6,name=,alias=1,relation=,type=Int] |
| | | +-Literal[value=1,type=Int] |
| | +-Alias[id=7,name=,alias=count(*),relation=,type=Long] |
| | +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate, |
| | type=Long] |
| +-output_attributes= |
| +-AttributeReference[id=6,name=,alias=1,relation=,type=Int] |
| +-AttributeReference[id=7,name=,alias=count(*),relation=,type=Long] |
| == |
| |
| # WITH queries and the main query are currently optimized separately. |
| with subquery as (select int_col, long_col, double_col from test) |
| select int_col from subquery |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=SharedSubplanReference[subplan_id=0] |
| | | +-referenced_attributes= |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | +-output_attributes= |
| | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | +-AttributeReference[id=8,name=double_col,relation=test,type=Double NULL] |
| | +-project_list= |
| | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| +-shared_subplans= |
| | +-Project |
| | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | type=VarChar(20) NULL] |
| | +-project_list= |
| | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| +-output_attributes= |
| +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Selection[has_repartition=false] |
| | +-input=SharedSubplanReference[subplan_id=0] |
| | | +-referenced_attributes= |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | +-output_attributes= |
| | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | +-AttributeReference[id=8,name=double_col,relation=test,type=Double NULL] |
| | +-project_expressions= |
| | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| +-shared_subplans= |
| | +-Selection[has_repartition=false] |
| | +-input=TableReference[relation=Test,alias=test] |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | type=VarChar(20) NULL] |
| | +-project_expressions= |
| | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| +-output_attributes= |
| +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| == |
| |
| SELECT COUNT(DISTINCT int_col), SUM(float_col) |
| FROM test |
| GROUP BY long_col |
| HAVING AVG(DISTINCT int_col + double_col) > AVG(DISTINCT float_col); |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=Filter |
| | | +-input=Aggregate |
| | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-grouping_expressions= |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-aggregate_expressions= |
| | | | +-Alias[id=6,name=,alias=$aggregate0,relation=$aggregate,type=Long] |
| | | | | +-AggregateFunction[function=COUNT,is_distinct=true] |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-Alias[id=7,name=,alias=$aggregate1,relation=$aggregate, |
| | | | | type=Double NULL] |
| | | | | +-AggregateFunction[function=SUM] |
| | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-Alias[id=8,name=,alias=$aggregate2,relation=$aggregate, |
| | | | | type=Double NULL] |
| | | | | +-AggregateFunction[function=AVG,is_distinct=true] |
| | | | | +-Add |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=3,name=double_col,relation=test, |
| | | | | type=Double NULL] |
| | | | +-Alias[id=9,name=,alias=$aggregate3,relation=$aggregate, |
| | | | type=Double NULL] |
| | | | +-AggregateFunction[function=AVG,is_distinct=true] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | +-filter_predicate=Greater |
| | | +-AttributeReference[id=8,name=,alias=$aggregate2,relation=$aggregate, |
| | | | type=Double NULL] |
| | | +-AttributeReference[id=9,name=,alias=$aggregate3,relation=$aggregate, |
| | | type=Double NULL] |
| | +-project_list= |
| | +-Alias[id=6,name=,alias=COUNT(DISTINCT int_col),relation=,type=Long] |
| | | +-AttributeReference[id=6,name=,alias=$aggregate0,relation=$aggregate, |
| | | type=Long] |
| | +-Alias[id=7,name=,alias=SUM(float_col),relation=,type=Double NULL] |
| | +-AttributeReference[id=7,name=,alias=$aggregate1,relation=$aggregate, |
| | type=Double NULL] |
| +-output_attributes= |
| +-AttributeReference[id=6,name=,alias=COUNT(DISTINCT int_col),relation=, |
| | type=Long] |
| +-AttributeReference[id=7,name=,alias=SUM(float_col),relation=, |
| type=Double NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Selection[has_repartition=false] |
| | +-input=Aggregate[has_repartition=false] |
| | | +-input=TableReference[relation=Test,alias=test] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-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[has_repartition=false] |
| | +-left=HashLeftAntiJoin[has_repartition=false] |
| | | +-left=Selection[has_repartition=false] |
| | | | +-input=TableGenerator[function_name=generate_series,table_alias=gs1] |
| | | | | +-AttributeReference[id=0,name=generate_series,alias=gs1, |
| | | | | relation=generate_series,type=Int] |
| | | | +-filter_predicate=Or |
| | | | | +-Less |
| | | | | | +-AttributeReference[id=0,name=generate_series,alias=gs1, |
| | | | | | | relation=generate_series,type=Int] |
| | | | | | +-Literal[value=40,type=Int] |
| | | | | +-Greater |
| | | | | +-AttributeReference[id=0,name=generate_series,alias=gs1, |
| | | | | | relation=generate_series,type=Int] |
| | | | | +-Literal[value=60,type=Int] |
| | | | +-project_expressions= |
| | | | +-Alias[id=1,name=i,relation=,type=Int] |
| | | | +-AttributeReference[id=0,name=generate_series,alias=gs1, |
| | | | relation=generate_series,type=Int] |
| | | +-right=TableGenerator[function_name=generate_series,table_alias=gs3] |
| | | | +-AttributeReference[id=4,name=generate_series,alias=gs3, |
| | | | relation=generate_series,type=Int] |
| | | +-project_expressions= |
| | | | +-AttributeReference[id=1,name=i,relation=,type=Int] |
| | | +-left_join_attributes= |
| | | | +-AttributeReference[id=1,name=i,relation=,type=Int] |
| | | +-right_join_attributes= |
| | | +-AttributeReference[id=4,name=generate_series,alias=gs3, |
| | | relation=generate_series,type=Int] |
| | +-right=TableGenerator[function_name=generate_series,table_alias=gs2] |
| | | +-AttributeReference[id=2,name=generate_series,alias=gs2, |
| | | relation=generate_series,type=Int] |
| | +-project_expressions= |
| | | +-AttributeReference[id=1,name=i,relation=,type=Int] |
| | +-left_join_attributes= |
| | | +-AttributeReference[id=1,name=i,relation=,type=Int] |
| | +-right_join_attributes= |
| | +-AttributeReference[id=2,name=generate_series,alias=gs2, |
| | relation=generate_series,type=Int] |
| +-output_attributes= |
| +-AttributeReference[id=1,name=i,relation=,type=Int] |
| == |
| |
| # IN predicate |
| SELECT char_col |
| FROM test |
| WHERE int_col IN (1, 2, 3); |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=Filter |
| | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-filter_predicate=InValueList |
| | | +-test_expression=AttributeReference[id=0,name=int_col,relation=test, |
| | | | type=Int NULL] |
| | | +-match_expressions= |
| | | +-Literal[value=1,type=Int] |
| | | +-Literal[value=2,type=Int] |
| | | +-Literal[value=3,type=Int] |
| | +-project_list= |
| | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| +-output_attributes= |
| +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Selection[has_repartition=false] |
| | +-input=TableReference[relation=Test,alias=test] |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,type=VarChar(20) NULL] |
| | +-filter_predicate=InValueList |
| | | +-test_expression=AttributeReference[id=0,name=int_col,relation=test, |
| | | | type=Int NULL] |
| | | +-match_expressions= |
| | | +-Literal[value=1,type=Int] |
| | | +-Literal[value=2,type=Int] |
| | | +-Literal[value=3,type=Int] |
| | +-project_expressions= |
| | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| +-output_attributes= |
| +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| == |
| |
| SELECT char_col |
| FROM test |
| WHERE int_col*2 NOT IN ( |
| long_col+1, |
| CASE WHEN float_col > 0 THEN 1 |
| ELSE double_col END); |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=Filter |
| | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-filter_predicate=NOT |
| | | +-InValueList |
| | | +-test_expression=Multiply |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-Literal[value=2,type=Int] |
| | | +-match_expressions= |
| | | +-Add |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-Literal[value=1,type=Int] |
| | | +-SearchedCase |
| | | +-else_result_expression=AttributeReference[id=3,name=double_col, |
| | | | relation=test,type=Double NULL] |
| | | +-condition_perdicates= |
| | | | +-Greater |
| | | | +-AttributeReference[id=2,name=float_col,relation=test, |
| | | | | type=Float] |
| | | | +-Literal[value=0,type=Int] |
| | | +-conditional_result_expressions= |
| | | +-Cast[target_type=Double NULL] |
| | | +-operand=Literal[value=1,type=Int] |
| | +-project_list= |
| | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| +-output_attributes= |
| +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Selection[has_repartition=false] |
| | +-input=TableReference[relation=Test,alias=test] |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,type=VarChar(20) NULL] |
| | +-filter_predicate=NOT |
| | | +-InValueList |
| | | +-test_expression=Multiply |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-Literal[value=2,type=Int] |
| | | +-match_expressions= |
| | | +-Add |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-Literal[value=1,type=Int] |
| | | +-SearchedCase |
| | | +-else_result_expression=AttributeReference[id=3,name=double_col, |
| | | | relation=test,type=Double NULL] |
| | | +-condition_perdicates= |
| | | | +-Greater |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-Literal[value=0,type=Int] |
| | | +-conditional_result_expressions= |
| | | +-Cast[target_type=Double NULL] |
| | | +-operand=Literal[value=1,type=Int] |
| | +-project_expressions= |
| | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| +-output_attributes= |
| +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| == |
| |
| SELECT char_col |
| FROM test |
| WHERE int_col IN ( |
| SELECT SUM(long_col) - 10 |
| FROM test |
| GROUP BY vchar_col |
| ); |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=HashLeftSemiJoin |
| | | +-left=TableReference[relation_name=Test,relation_alias=test] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-right=Project |
| | | | +-input=Aggregate |
| | | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float] |
| | | | | | +-AttributeReference[id=9,name=double_col,relation=test, |
| | | | | | | type=Double NULL] |
| | | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)] |
| | | | | | +-AttributeReference[id=11,name=vchar_col,relation=test, |
| | | | | | type=VarChar(20) NULL] |
| | | | | +-grouping_expressions= |
| | | | | | +-AttributeReference[id=11,name=vchar_col,relation=test, |
| | | | | | type=VarChar(20) NULL] |
| | | | | +-aggregate_expressions= |
| | | | | +-Alias[id=12,name=,alias=$aggregate0,relation=$aggregate, |
| | | | | type=Long NULL] |
| | | | | +-AggregateFunction[function=SUM] |
| | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | | +-project_list= |
| | | | +-Alias[id=13,name=,alias=(SUM(long_col)-10),relation=,type=Long NULL] |
| | | | +-Subtract |
| | | | +-AttributeReference[id=12,name=,alias=$aggregate0, |
| | | | | relation=$aggregate,type=Long NULL] |
| | | | +-Literal[value=10,type=Int] |
| | | +-left_join_attributes= |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-right_join_attributes= |
| | | +-AttributeReference[id=13,name=,alias=(SUM(long_col)-10),relation=, |
| | | type=Long NULL] |
| | +-project_list= |
| | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| +-output_attributes= |
| +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=HashLeftSemiJoin[has_repartition=false] |
| | +-left=TableReference[relation=Test,alias=test] |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,type=VarChar(20) NULL] |
| | +-right=Selection[has_repartition=false] |
| | | +-input=Aggregate[has_repartition=false] |
| | | | +-input=TableReference[relation=Test,alias=test] |
| | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float] |
| | | | | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL] |
| | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-grouping_expressions= |
| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-aggregate_expressions= |
| | | | +-Alias[id=12,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL] |
| | | | +-AggregateFunction[function=SUM] |
| | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | +-project_expressions= |
| | | +-Alias[id=13,name=,alias=(SUM(long_col)-10),relation=,type=Long NULL] |
| | | +-Subtract |
| | | +-AttributeReference[id=12,name=,alias=$aggregate0, |
| | | | relation=$aggregate,type=Long NULL] |
| | | +-Literal[value=10,type=Int] |
| | +-project_expressions= |
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | +-left_join_attributes= |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | +-right_join_attributes= |
| | +-AttributeReference[id=13,name=,alias=(SUM(long_col)-10),relation=, |
| | type=Long NULL] |
| +-output_attributes= |
| +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| == |
| |
| SELECT char_col |
| FROM test |
| WHERE int_col NOT IN ( |
| SELECT long_col |
| FROM test |
| WHERE long_col IN (1, 2) |
| ); |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=HashLeftAntiJoin |
| | | +-left=TableReference[relation_name=Test,relation_alias=test] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-right=Project |
| | | | +-input=Filter |
| | | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float] |
| | | | | | +-AttributeReference[id=9,name=double_col,relation=test, |
| | | | | | | type=Double NULL] |
| | | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)] |
| | | | | | +-AttributeReference[id=11,name=vchar_col,relation=test, |
| | | | | | type=VarChar(20) NULL] |
| | | | | +-filter_predicate=InValueList |
| | | | | +-test_expression=AttributeReference[id=7,name=long_col,relation=test, |
| | | | | | type=Long] |
| | | | | +-match_expressions= |
| | | | | +-Literal[value=1,type=Long] |
| | | | | +-Literal[value=2,type=Long] |
| | | | +-project_list= |
| | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | +-left_join_attributes= |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-right_join_attributes= |
| | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | +-project_list= |
| | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| +-output_attributes= |
| +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=HashLeftAntiJoin[has_repartition=false] |
| | +-left=TableReference[relation=Test,alias=test] |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | +-AttributeReference[id=5,name=vchar_col,relation=test,type=VarChar(20) NULL] |
| | +-right=Selection[has_repartition=false] |
| | | +-input=TableReference[relation=Test,alias=test] |
| | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=11,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-filter_predicate=InValueList |
| | | | +-test_expression=AttributeReference[id=7,name=long_col,relation=test, |
| | | | | type=Long] |
| | | | +-match_expressions= |
| | | | +-Literal[value=1,type=Long] |
| | | | +-Literal[value=2,type=Long] |
| | | +-project_expressions= |
| | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | +-project_expressions= |
| | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | +-left_join_attributes= |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | +-right_join_attributes= |
| | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| +-output_attributes= |
| +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| == |
| |
| |
| # Scalar subquery expressions |
| SELECT x + (SELECT SUM(y) FROM c) |
| FROM b; |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=NestedLoopsJoin |
| | | +-left=TableReference[relation_name=b] |
| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-right=Project |
| | | | +-input=Aggregate |
| | | | | +-input=TableReference[relation_name=c] |
| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | | +-grouping_expressions= |
| | | | | | +-[] |
| | | | | +-aggregate_expressions= |
| | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | | type=Long NULL] |
| | | | | +-AggregateFunction[function=SUM] |
| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | +-project_list= |
| | | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL] |
| | | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | type=Long NULL] |
| | | +-join_predicate=Literal[value=true] |
| | +-project_list= |
| | +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL] |
| | +-Add |
| | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL] |
| +-output_attributes= |
| +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=, |
| type=Long NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=NestedLoopsJoin[has_repartition=false] |
| | +-left=TableReference[relation=b] |
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-right=Aggregate[has_repartition=false] |
| | | +-input=TableReference[relation=c] |
| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | +-grouping_expressions= |
| | | | +-[] |
| | | +-aggregate_expressions= |
| | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL] |
| | | +-AggregateFunction[function=SUM] |
| | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | +-join_predicate=Literal[value=true] |
| | +-project_expressions= |
| | +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL] |
| | +-Add |
| | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | type=Long NULL] |
| +-output_attributes= |
| +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=, |
| type=Long NULL] |
| == |
| |
| SELECT x + (SELECT SUM(y) FROM c WHERE b.w = c.x) |
| FROM b; |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=HashJoin |
| | | +-left=TableReference[relation_name=b] |
| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-right=Project |
| | | | +-input=Aggregate |
| | | | | +-input=TableReference[relation_name=c] |
| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | | +-grouping_expressions= |
| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | +-aggregate_expressions= |
| | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | | type=Long NULL] |
| | | | | +-AggregateFunction[function=SUM] |
| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | +-project_list= |
| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL] |
| | | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | type=Long NULL] |
| | | +-left_join_attributes= |
| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | +-right_join_attributes= |
| | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | +-project_list= |
| | +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL] |
| | +-Add |
| | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL] |
| +-output_attributes= |
| +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=, |
| type=Long NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=HashJoin[has_repartition=false] |
| | +-left=Aggregate[has_repartition=false] |
| | | +-input=TableReference[relation=c] |
| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | +-grouping_expressions= |
| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | +-aggregate_expressions= |
| | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL] |
| | | +-AggregateFunction[function=SUM] |
| | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | +-right=TableReference[relation=b] |
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-project_expressions= |
| | | +-Alias[id=5,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL] |
| | | +-Add |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | type=Long NULL] |
| | +-left_join_attributes= |
| | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | +-right_join_attributes= |
| | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| +-output_attributes= |
| +-AttributeReference[id=5,name=,alias=(x+SubqueryExpression),relation=, |
| type=Long NULL] |
| == |
| |
| SELECT * |
| FROM b |
| WHERE b.x > (SELECT SUM(y) FROM c); |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=NestedLoopsJoin |
| | | +-left=TableReference[relation_name=b] |
| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-right=Project |
| | | | +-input=Aggregate |
| | | | | +-input=TableReference[relation_name=c] |
| | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | | +-grouping_expressions= |
| | | | | | +-[] |
| | | | | +-aggregate_expressions= |
| | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | | type=Long NULL] |
| | | | | +-AggregateFunction[function=SUM] |
| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | +-project_list= |
| | | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL] |
| | | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | type=Long NULL] |
| | | +-join_predicate=Greater |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL] |
| | +-project_list= |
| | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| +-output_attributes= |
| +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=NestedLoopsJoin[has_repartition=false] |
| | +-left=TableReference[relation=b] |
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-right=Aggregate[has_repartition=false] |
| | | +-input=TableReference[relation=c] |
| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | +-grouping_expressions= |
| | | | +-[] |
| | | +-aggregate_expressions= |
| | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL] |
| | | +-AggregateFunction[function=SUM] |
| | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | +-join_predicate=Greater |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | type=Long NULL] |
| | +-project_expressions= |
| | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| +-output_attributes= |
| +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| == |
| |
| SELECT * |
| FROM b |
| WHERE b.x > (SELECT SUM(y) FROM c WHERE b.w = c.x); |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=Filter |
| | | +-input=HashJoin |
| | | | +-left=TableReference[relation_name=b] |
| | | | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | | +-right=Project |
| | | | | +-input=Aggregate |
| | | | | | +-input=TableReference[relation_name=c] |
| | | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | | | +-grouping_expressions= |
| | | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | | +-aggregate_expressions= |
| | | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | | | type=Long NULL] |
| | | | | | +-AggregateFunction[function=SUM] |
| | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | | +-project_list= |
| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL] |
| | | | | +-AttributeReference[id=4,name=,alias=$aggregate0, |
| | | | | relation=$aggregate,type=Long NULL] |
| | | | +-left_join_attributes= |
| | | | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | | +-right_join_attributes= |
| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | +-filter_predicate=Greater |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL] |
| | +-project_list= |
| | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| +-output_attributes= |
| +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=HashJoin[has_repartition=false] |
| | +-left=Aggregate[has_repartition=false] |
| | | +-input=TableReference[relation=c] |
| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | +-grouping_expressions= |
| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | +-aggregate_expressions= |
| | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL] |
| | | +-AggregateFunction[function=SUM] |
| | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | +-right=TableReference[relation=b] |
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-residual_predicate=Greater |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | type=Long NULL] |
| | +-project_expressions= |
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-left_join_attributes= |
| | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | +-right_join_attributes= |
| | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| +-output_attributes= |
| +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| == |
| |
| SELECT x + ( |
| SELECT SUM(y) + (SELECT SUM(w) FROM a WHERE a.y > 10) |
| FROM c |
| WHERE b.w = c.x AND c.x < 10) |
| FROM b; |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=HashJoin |
| | | +-left=TableReference[relation_name=b] |
| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-right=Project |
| | | | +-input=NestedLoopsJoin |
| | | | | +-left=Aggregate |
| | | | | | +-input=Filter |
| | | | | | | +-input=TableReference[relation_name=c] |
| | | | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | | | | +-filter_predicate=Less |
| | | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | | | +-Literal[value=10,type=Int] |
| | | | | | +-grouping_expressions= |
| | | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | | +-aggregate_expressions= |
| | | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | | | type=Long NULL] |
| | | | | | +-AggregateFunction[function=SUM] |
| | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | | +-right=Project |
| | | | | | +-input=Aggregate |
| | | | | | | +-input=Filter |
| | | | | | | | +-input=TableReference[relation_name=a] |
| | | | | | | | | +-AttributeReference[id=5,name=w,relation=a,type=Int] |
| | | | | | | | | +-AttributeReference[id=6,name=x,relation=a,type=Int] |
| | | | | | | | | +-AttributeReference[id=7,name=y,relation=a,type=Int] |
| | | | | | | | | +-AttributeReference[id=8,name=z,relation=a,type=Int] |
| | | | | | | | +-filter_predicate=Greater |
| | | | | | | | +-AttributeReference[id=7,name=y,relation=a,type=Int] |
| | | | | | | | +-Literal[value=10,type=Int] |
| | | | | | | +-grouping_expressions= |
| | | | | | | | +-[] |
| | | | | | | +-aggregate_expressions= |
| | | | | | | +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate, |
| | | | | | | type=Long NULL] |
| | | | | | | +-AggregateFunction[function=SUM] |
| | | | | | | +-AttributeReference[id=5,name=w,relation=a,type=Int] |
| | | | | | +-project_list= |
| | | | | | +-Alias[id=9,name=,alias=SUM(w),relation=,type=Long NULL] |
| | | | | | +-AttributeReference[id=9,name=,alias=$aggregate0, |
| | | | | | relation=$aggregate,type=Long NULL] |
| | | | | +-join_predicate=Literal[value=true] |
| | | | +-project_list= |
| | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | +-Alias[id=10,name=,alias=(SUM(y)+SubqueryExpression),relation=, |
| | | | type=Long NULL] |
| | | | +-Add |
| | | | +-AttributeReference[id=4,name=,alias=$aggregate0, |
| | | | | relation=$aggregate,type=Long NULL] |
| | | | +-AttributeReference[id=9,name=,alias=SUM(w),relation=, |
| | | | type=Long NULL] |
| | | +-left_join_attributes= |
| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | +-right_join_attributes= |
| | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | +-project_list= |
| | +-Alias[id=11,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL] |
| | +-Add |
| | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-AttributeReference[id=10,name=,alias=(SUM(y)+SubqueryExpression), |
| | relation=,type=Long NULL] |
| +-output_attributes= |
| +-AttributeReference[id=11,name=,alias=(x+SubqueryExpression),relation=, |
| type=Long NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=HashJoin[has_repartition=false] |
| | +-left=NestedLoopsJoin[has_repartition=false] |
| | | +-left=Aggregate[has_repartition=false] |
| | | | +-input=TableReference[relation=c] |
| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | +-filter_predicate=Less |
| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | +-Literal[value=10,type=Int] |
| | | | +-grouping_expressions= |
| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | +-aggregate_expressions= |
| | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL] |
| | | | +-AggregateFunction[function=SUM] |
| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | +-right=Aggregate[has_repartition=false] |
| | | | +-input=TableReference[relation=a] |
| | | | | +-AttributeReference[id=5,name=w,relation=a,type=Int] |
| | | | | +-AttributeReference[id=6,name=x,relation=a,type=Int] |
| | | | | +-AttributeReference[id=7,name=y,relation=a,type=Int] |
| | | | | +-AttributeReference[id=8,name=z,relation=a,type=Int] |
| | | | +-filter_predicate=Greater |
| | | | | +-AttributeReference[id=7,name=y,relation=a,type=Int] |
| | | | | +-Literal[value=10,type=Int] |
| | | | +-grouping_expressions= |
| | | | | +-[] |
| | | | +-aggregate_expressions= |
| | | | +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL] |
| | | | +-AggregateFunction[function=SUM] |
| | | | +-AttributeReference[id=5,name=w,relation=a,type=Int] |
| | | +-join_predicate=Literal[value=true] |
| | | +-project_expressions= |
| | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | +-Alias[id=10,name=,alias=(SUM(y)+SubqueryExpression),relation=, |
| | | type=Long NULL] |
| | | +-Add |
| | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | type=Long NULL] |
| | | +-AttributeReference[id=9,name=,alias=$aggregate0,relation=$aggregate, |
| | | type=Long NULL] |
| | +-right=TableReference[relation=b] |
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-project_expressions= |
| | | +-Alias[id=11,name=,alias=(x+SubqueryExpression),relation=,type=Long NULL] |
| | | +-Add |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-AttributeReference[id=10,name=,alias=(SUM(y)+SubqueryExpression), |
| | | relation=,type=Long NULL] |
| | +-left_join_attributes= |
| | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | +-right_join_attributes= |
| | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| +-output_attributes= |
| +-AttributeReference[id=11,name=,alias=(x+SubqueryExpression),relation=, |
| type=Long NULL] |
| == |
| |
| SELECT x * (SELECT SUM(y) FROM c) + (SELECT AVG(y) FROM d) |
| FROM b; |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=NestedLoopsJoin |
| | | +-left=TableReference[relation_name=b] |
| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-right=NestedLoopsJoin |
| | | | +-left=Project |
| | | | | +-input=Aggregate |
| | | | | | +-input=TableReference[relation_name=c] |
| | | | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | | | +-grouping_expressions= |
| | | | | | | +-[] |
| | | | | | +-aggregate_expressions= |
| | | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | | | type=Long NULL] |
| | | | | | +-AggregateFunction[function=SUM] |
| | | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | | +-project_list= |
| | | | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL] |
| | | | | +-AttributeReference[id=4,name=,alias=$aggregate0, |
| | | | | relation=$aggregate,type=Long NULL] |
| | | | +-right=Project |
| | | | | +-input=Aggregate |
| | | | | | +-input=TableReference[relation_name=d] |
| | | | | | | +-AttributeReference[id=5,name=y,relation=d,type=Int] |
| | | | | | | +-AttributeReference[id=6,name=z,relation=d,type=Int] |
| | | | | | +-grouping_expressions= |
| | | | | | | +-[] |
| | | | | | +-aggregate_expressions= |
| | | | | | +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate, |
| | | | | | type=Double NULL] |
| | | | | | +-AggregateFunction[function=AVG] |
| | | | | | +-AttributeReference[id=5,name=y,relation=d,type=Int] |
| | | | | +-project_list= |
| | | | | +-Alias[id=7,name=,alias=AVG(y),relation=,type=Double NULL] |
| | | | | +-AttributeReference[id=7,name=,alias=$aggregate0, |
| | | | | relation=$aggregate,type=Double NULL] |
| | | | +-join_predicate=Literal[value=true] |
| | | +-join_predicate=Literal[value=true] |
| | +-project_list= |
| | +-Alias[id=8,name=,alias=((x*SubqueryExpression)+SubqueryExpression), |
| | relation=,type=Double NULL] |
| | +-Add |
| | +-Multiply |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL] |
| | +-AttributeReference[id=7,name=,alias=AVG(y),relation=,type=Double NULL] |
| +-output_attributes= |
| +-AttributeReference[id=8,name=, |
| alias=((x*SubqueryExpression)+SubqueryExpression),relation=,type=Double NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=NestedLoopsJoin[has_repartition=false] |
| | +-left=TableReference[relation=b] |
| | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-right=NestedLoopsJoin[has_repartition=false] |
| | | +-left=Aggregate[has_repartition=false] |
| | | | +-input=TableReference[relation=c] |
| | | | | +-AttributeReference[id=2,name=x,relation=c,type=Int] |
| | | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | | +-grouping_expressions= |
| | | | | +-[] |
| | | | +-aggregate_expressions= |
| | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL] |
| | | | +-AggregateFunction[function=SUM] |
| | | | +-AttributeReference[id=3,name=y,relation=c,type=Int] |
| | | +-right=Aggregate[has_repartition=false] |
| | | | +-input=TableReference[relation=d] |
| | | | | +-AttributeReference[id=5,name=y,relation=d,type=Int] |
| | | | | +-AttributeReference[id=6,name=z,relation=d,type=Int] |
| | | | +-grouping_expressions= |
| | | | | +-[] |
| | | | +-aggregate_expressions= |
| | | | +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate, |
| | | | type=Double NULL] |
| | | | +-AggregateFunction[function=AVG] |
| | | | +-AttributeReference[id=5,name=y,relation=d,type=Int] |
| | | +-join_predicate=Literal[value=true] |
| | | +-project_expressions= |
| | | +-Alias[id=4,name=,alias=SUM(y),relation=,type=Long NULL] |
| | | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | type=Long NULL] |
| | | +-Alias[id=7,name=,alias=AVG(y),relation=,type=Double NULL] |
| | | +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate, |
| | | type=Double NULL] |
| | +-join_predicate=Literal[value=true] |
| | +-project_expressions= |
| | +-Alias[id=8,name=,alias=((x*SubqueryExpression)+SubqueryExpression), |
| | relation=,type=Double NULL] |
| | +-Add |
| | +-Multiply |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-AttributeReference[id=4,name=,alias=SUM(y),relation=,type=Long NULL] |
| | +-AttributeReference[id=7,name=,alias=AVG(y),relation=,type=Double NULL] |
| +-output_attributes= |
| +-AttributeReference[id=8,name=, |
| alias=((x*SubqueryExpression)+SubqueryExpression),relation=,type=Double NULL] |
| == |
| |
| SELECT x * (SELECT SUM(y) FROM c) |
| FROM b |
| WHERE w < (SELECT AVG(y) FROM d); |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=NestedLoopsJoin |
| | | +-left=NestedLoopsJoin |
| | | | +-left=TableReference[relation_name=b] |
| | | | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | | +-right=Project |
| | | | | +-input=Aggregate |
| | | | | | +-input=TableReference[relation_name=d] |
| | | | | | | +-AttributeReference[id=2,name=y,relation=d,type=Int] |
| | | | | | | +-AttributeReference[id=3,name=z,relation=d,type=Int] |
| | | | | | +-grouping_expressions= |
| | | | | | | +-[] |
| | | | | | +-aggregate_expressions= |
| | | | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | | | type=Double NULL] |
| | | | | | +-AggregateFunction[function=AVG] |
| | | | | | +-AttributeReference[id=2,name=y,relation=d,type=Int] |
| | | | | +-project_list= |
| | | | | +-Alias[id=4,name=,alias=AVG(y),relation=,type=Double NULL] |
| | | | | +-AttributeReference[id=4,name=,alias=$aggregate0, |
| | | | | relation=$aggregate,type=Double NULL] |
| | | | +-join_predicate=Less |
| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | | +-AttributeReference[id=4,name=,alias=AVG(y),relation=,type=Double NULL] |
| | | +-right=Project |
| | | | +-input=Aggregate |
| | | | | +-input=TableReference[relation_name=c] |
| | | | | | +-AttributeReference[id=5,name=x,relation=c,type=Int] |
| | | | | | +-AttributeReference[id=6,name=y,relation=c,type=Int] |
| | | | | +-grouping_expressions= |
| | | | | | +-[] |
| | | | | +-aggregate_expressions= |
| | | | | +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate, |
| | | | | type=Long NULL] |
| | | | | +-AggregateFunction[function=SUM] |
| | | | | +-AttributeReference[id=6,name=y,relation=c,type=Int] |
| | | | +-project_list= |
| | | | +-Alias[id=7,name=,alias=SUM(y),relation=,type=Long NULL] |
| | | | +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate, |
| | | | type=Long NULL] |
| | | +-join_predicate=Literal[value=true] |
| | +-project_list= |
| | +-Alias[id=8,name=,alias=(x*SubqueryExpression),relation=,type=Long NULL] |
| | +-Multiply |
| | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-AttributeReference[id=7,name=,alias=SUM(y),relation=,type=Long NULL] |
| +-output_attributes= |
| +-AttributeReference[id=8,name=,alias=(x*SubqueryExpression),relation=, |
| type=Long NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=NestedLoopsJoin[has_repartition=false] |
| | +-left=NestedLoopsJoin[has_repartition=false] |
| | | +-left=TableReference[relation=b] |
| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | | +-right=Aggregate[has_repartition=false] |
| | | | +-input=TableReference[relation=d] |
| | | | | +-AttributeReference[id=2,name=y,relation=d,type=Int] |
| | | | | +-AttributeReference[id=3,name=z,relation=d,type=Int] |
| | | | +-grouping_expressions= |
| | | | | +-[] |
| | | | +-aggregate_expressions= |
| | | | +-Alias[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | type=Double NULL] |
| | | | +-AggregateFunction[function=AVG] |
| | | | +-AttributeReference[id=2,name=y,relation=d,type=Int] |
| | | +-join_predicate=Less |
| | | | +-AttributeReference[id=0,name=w,relation=b,type=Int] |
| | | | +-AttributeReference[id=4,name=,alias=$aggregate0,relation=$aggregate, |
| | | | type=Double NULL] |
| | | +-project_expressions= |
| | | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-right=Aggregate[has_repartition=false] |
| | | +-input=TableReference[relation=c] |
| | | | +-AttributeReference[id=5,name=x,relation=c,type=Int] |
| | | | +-AttributeReference[id=6,name=y,relation=c,type=Int] |
| | | +-grouping_expressions= |
| | | | +-[] |
| | | +-aggregate_expressions= |
| | | +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,type=Long NULL] |
| | | +-AggregateFunction[function=SUM] |
| | | +-AttributeReference[id=6,name=y,relation=c,type=Int] |
| | +-join_predicate=Literal[value=true] |
| | +-project_expressions= |
| | +-Alias[id=8,name=,alias=(x*SubqueryExpression),relation=,type=Long NULL] |
| | +-Multiply |
| | +-AttributeReference[id=1,name=x,relation=b,type=Int] |
| | +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate, |
| | type=Long NULL] |
| +-output_attributes= |
| +-AttributeReference[id=8,name=,alias=(x*SubqueryExpression),relation=, |
| type=Long NULL] |
| == |
| |
| # Same shared subplan referenced multiple times. |
| WITH t(x, y) AS ( |
| SELECT i % 5, i |
| FROM generate_series(1, 20) AS g(i) |
| ) |
| SELECT * |
| FROM t |
| WHERE t.y = ( |
| SELECT MAX(y) |
| FROM t t1 |
| WHERE t.x = t1.x |
| ) |
| ORDER BY x; |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=Sort[is_ascending=[true],nulls_first=[false]] |
| | | +-input=Filter |
| | | | +-input=HashJoin |
| | | | | +-left=SharedSubplanReference[subplan_id=0] |
| | | | | | +-referenced_attributes= |
| | | | | | | +-AttributeReference[id=3,name=x,relation=,type=Int] |
| | | | | | | +-AttributeReference[id=4,name=y,relation=,type=Int] |
| | | | | | +-output_attributes= |
| | | | | | +-AttributeReference[id=5,name=x,relation=,type=Int] |
| | | | | | +-AttributeReference[id=6,name=y,relation=,type=Int] |
| | | | | +-right=Project |
| | | | | | +-input=Aggregate |
| | | | | | | +-input=SharedSubplanReference[subplan_id=0] |
| | | | | | | | +-referenced_attributes= |
| | | | | | | | | +-AttributeReference[id=3,name=x,relation=,type=Int] |
| | | | | | | | | +-AttributeReference[id=4,name=y,relation=,type=Int] |
| | | | | | | | +-output_attributes= |
| | | | | | | | +-AttributeReference[id=7,name=x,relation=,type=Int] |
| | | | | | | | +-AttributeReference[id=8,name=y,relation=,type=Int] |
| | | | | | | +-grouping_expressions= |
| | | | | | | | +-AttributeReference[id=7,name=x,relation=,type=Int] |
| | | | | | | +-aggregate_expressions= |
| | | | | | | +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate, |
| | | | | | | type=Int NULL] |
| | | | | | | +-AggregateFunction[function=MAX] |
| | | | | | | +-AttributeReference[id=8,name=y,relation=,type=Int] |
| | | | | | +-project_list= |
| | | | | | +-AttributeReference[id=7,name=x,relation=,type=Int] |
| | | | | | +-Alias[id=9,name=,alias=MAX(y),relation=,type=Int NULL] |
| | | | | | +-AttributeReference[id=9,name=,alias=$aggregate0, |
| | | | | | relation=$aggregate,type=Int NULL] |
| | | | | +-left_join_attributes= |
| | | | | | +-AttributeReference[id=5,name=x,relation=,type=Int] |
| | | | | +-right_join_attributes= |
| | | | | +-AttributeReference[id=7,name=x,relation=,type=Int] |
| | | | +-filter_predicate=Equal |
| | | | +-AttributeReference[id=6,name=y,relation=,type=Int] |
| | | | +-AttributeReference[id=9,name=,alias=MAX(y),relation=,type=Int NULL] |
| | | +-sort_expressions= |
| | | +-AttributeReference[id=5,name=x,relation=,type=Int] |
| | +-project_list= |
| | +-AttributeReference[id=5,name=x,relation=,type=Int] |
| | +-AttributeReference[id=6,name=y,relation=,type=Int] |
| +-shared_subplans= |
| | +-Project |
| | +-input=TableGenerator[function_name=generate_series,table_alias=g] |
| | | +-AttributeReference[id=0,name=generate_series,alias=g, |
| | | relation=generate_series,type=Int] |
| | +-project_list= |
| | +-Alias[id=3,name=x,relation=,type=Int] |
| | | +-Modulo |
| | | +-AttributeReference[id=0,name=generate_series,alias=g, |
| | | | relation=generate_series,type=Int] |
| | | +-Literal[value=5,type=Int] |
| | +-Alias[id=4,name=y,relation=,type=Int] |
| | +-AttributeReference[id=0,name=generate_series,alias=g, |
| | relation=generate_series,type=Int] |
| +-output_attributes= |
| +-AttributeReference[id=5,name=x,relation=,type=Int] |
| +-AttributeReference[id=6,name=y,relation=,type=Int] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Selection[has_repartition=false] |
| | +-input=Sort[is_ascending=[true],nulls_first=[false]] |
| | | +-input=HashJoin[has_repartition=false] |
| | | | +-left=SharedSubplanReference[subplan_id=0] |
| | | | | +-referenced_attributes= |
| | | | | | +-AttributeReference[id=3,name=x,relation=,type=Int] |
| | | | | | +-AttributeReference[id=4,name=y,relation=,type=Int] |
| | | | | +-output_attributes= |
| | | | | +-AttributeReference[id=5,name=x,relation=,type=Int] |
| | | | | +-AttributeReference[id=6,name=y,relation=,type=Int] |
| | | | +-right=Aggregate[has_repartition=false] |
| | | | | +-input=SharedSubplanReference[subplan_id=0] |
| | | | | | +-referenced_attributes= |
| | | | | | | +-AttributeReference[id=3,name=x,relation=,type=Int] |
| | | | | | | +-AttributeReference[id=4,name=y,relation=,type=Int] |
| | | | | | +-output_attributes= |
| | | | | | +-AttributeReference[id=7,name=x,relation=,type=Int] |
| | | | | | +-AttributeReference[id=8,name=y,relation=,type=Int] |
| | | | | +-grouping_expressions= |
| | | | | | +-AttributeReference[id=7,name=x,relation=,type=Int] |
| | | | | +-aggregate_expressions= |
| | | | | +-Alias[id=9,name=,alias=$aggregate0,relation=$aggregate,type=Int NULL] |
| | | | | +-AggregateFunction[function=MAX] |
| | | | | +-AttributeReference[id=8,name=y,relation=,type=Int] |
| | | | +-project_expressions= |
| | | | | +-AttributeReference[id=5,name=x,relation=,type=Int] |
| | | | | +-AttributeReference[id=6,name=y,relation=,type=Int] |
| | | | +-left_join_attributes= |
| | | | | +-AttributeReference[id=5,name=x,relation=,type=Int] |
| | | | | +-AttributeReference[id=6,name=y,relation=,type=Int] |
| | | | +-right_join_attributes= |
| | | | +-AttributeReference[id=7,name=x,relation=,type=Int] |
| | | | +-AttributeReference[id=9,name=,alias=$aggregate0,relation=$aggregate, |
| | | | type=Int NULL] |
| | | +-sort_attributes= |
| | | +-AttributeReference[id=5,name=x,relation=,type=Int] |
| | +-project_expressions= |
| | +-AttributeReference[id=5,name=x,relation=,type=Int] |
| | +-AttributeReference[id=6,name=y,relation=,type=Int] |
| +-shared_subplans= |
| | +-Selection[has_repartition=false] |
| | +-input=TableGenerator[function_name=generate_series,table_alias=g] |
| | | +-AttributeReference[id=0,name=generate_series,alias=g, |
| | | relation=generate_series,type=Int] |
| | +-project_expressions= |
| | +-Alias[id=3,name=x,relation=,type=Int] |
| | | +-Modulo |
| | | +-AttributeReference[id=0,name=generate_series,alias=g, |
| | | | relation=generate_series,type=Int] |
| | | +-Literal[value=5,type=Int] |
| | +-Alias[id=4,name=y,relation=,type=Int] |
| | +-AttributeReference[id=0,name=generate_series,alias=g, |
| | relation=generate_series,type=Int] |
| +-output_attributes= |
| +-AttributeReference[id=5,name=x,relation=,type=Int] |
| +-AttributeReference[id=6,name=y,relation=,type=Int] |
| == |
| |
| # Window Aggregate Function Test. |
| SELECT avg(int_col) OVER w FROM test |
| WINDOW w AS |
| (PARTITION BY char_col |
| ORDER BY long_col DESC NULLS LAST |
| ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW); |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=WindowAggregate |
| | | +-input=Sort[is_ascending=[true,false],nulls_first=[false,false]] |
| | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-sort_expressions= |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-window_aggregate_expression=Alias[id=6,name=,alias=$window_aggregate0, |
| | | relation=$window_aggregate,type=Double NULL] |
| | | +-WindowAggregateFunction[function=AVG,window_name=w,is_ascending=[false], |
| | | nulls_first=[false],frame_mode=row,num_preceding=-1,num_following=0] |
| | | +-arguments= |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-partition_by= |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | +-order_by= |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | +-project_list= |
| | +-Alias[id=6,name=,alias=avg(int_col),relation=,type=Double NULL] |
| | +-AttributeReference[id=6,name=,alias=$window_aggregate0, |
| | relation=$window_aggregate,type=Double NULL] |
| +-output_attributes= |
| +-AttributeReference[id=6,name=,alias=avg(int_col),relation=,type=Double NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Selection[has_repartition=false] |
| | +-input=WindowAggregate |
| | | +-input=Sort[is_ascending=[true,false],nulls_first=[false,false]] |
| | | | +-input=TableReference[relation=Test,alias=test] |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-sort_attributes= |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-window_aggregate_expression=Alias[id=6,name=,alias=$window_aggregate0, |
| | | relation=$window_aggregate,type=Double NULL] |
| | | +-WindowAggregateFunction[function=AVG,window_name=w,is_ascending=[false], |
| | | nulls_first=[false],frame_mode=row,num_preceding=-1,num_following=0] |
| | | +-arguments= |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-partition_by= |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | +-order_by= |
| | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | +-project_expressions= |
| | +-Alias[id=6,name=,alias=avg(int_col),relation=,type=Double NULL] |
| | +-AttributeReference[id=6,name=,alias=$window_aggregate0, |
| | relation=$window_aggregate,type=Double NULL] |
| +-output_attributes= |
| +-AttributeReference[id=6,name=,alias=avg(int_col),relation=,type=Double NULL] |
| == |
| |
| SELECT int_col, sum(float_col) OVER |
| (PARTITION BY vchar_col, long_col |
| ORDER BY double_col DESC NULLS LAST, int_col ASC NULLS FIRST |
| RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING) |
| FROM test; |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=WindowAggregate |
| | | +-input=Sort[is_ascending=[true,true,false,true], |
| | | | nulls_first=[false,false,false,true]] |
| | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-sort_expressions= |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-window_aggregate_expression=Alias[id=6,name=,alias=$window_aggregate0, |
| | | relation=$window_aggregate,type=Double NULL] |
| | | +-WindowAggregateFunction[function=SUM,window_name=, |
| | | is_ascending=[false,true],nulls_first=[false,true],frame_mode=range, |
| | | num_preceding=3,num_following=3] |
| | | +-arguments= |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | +-partition_by= |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-order_by= |
| | | +-AttributeReference[id=3,name=double_col,relation=test, |
| | | | type=Double NULL] |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | +-project_list= |
| | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | +-Alias[id=6,name=,alias=sum(float_col),relation=,type=Double NULL] |
| | +-AttributeReference[id=6,name=,alias=$window_aggregate0, |
| | relation=$window_aggregate,type=Double NULL] |
| +-output_attributes= |
| +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| +-AttributeReference[id=6,name=,alias=sum(float_col),relation=, |
| type=Double NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Selection[has_repartition=false] |
| | +-input=WindowAggregate |
| | | +-input=Sort[is_ascending=[true,true,false,true], |
| | | | nulls_first=[false,false,false,true]] |
| | | | +-input=TableReference[relation=Test,alias=test] |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-sort_attributes= |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-window_aggregate_expression=Alias[id=6,name=,alias=$window_aggregate0, |
| | | relation=$window_aggregate,type=Double NULL] |
| | | +-WindowAggregateFunction[function=SUM,window_name=, |
| | | is_ascending=[false,true],nulls_first=[false,true],frame_mode=range, |
| | | num_preceding=3,num_following=3] |
| | | +-arguments= |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | +-partition_by= |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-order_by= |
| | | +-AttributeReference[id=3,name=double_col,relation=test, |
| | | | type=Double NULL] |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | +-project_expressions= |
| | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | +-Alias[id=6,name=,alias=sum(float_col),relation=,type=Double NULL] |
| | +-AttributeReference[id=6,name=,alias=$window_aggregate0, |
| | relation=$window_aggregate,type=Double NULL] |
| +-output_attributes= |
| +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| +-AttributeReference[id=6,name=,alias=sum(float_col),relation=, |
| type=Double NULL] |
| == |
| |
| SELECT sum(avg(int_col) OVER w) FROM test |
| WINDOW w AS |
| (PARTITION BY char_col |
| ORDER BY long_col |
| ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW); |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=Aggregate |
| | | +-input=WindowAggregate |
| | | | +-input=Sort[is_ascending=[true,true],nulls_first=[false,false]] |
| | | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | | +-AttributeReference[id=3,name=double_col,relation=test, |
| | | | | | | type=Double NULL] |
| | | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | | type=VarChar(20) NULL] |
| | | | | +-sort_expressions= |
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-window_aggregate_expression=Alias[id=6,name=,alias=$window_aggregate0, |
| | | | relation=$window_aggregate,type=Double NULL] |
| | | | +-WindowAggregateFunction[function=AVG,window_name=w, |
| | | | is_ascending=[true],nulls_first=[false],frame_mode=row, |
| | | | num_preceding=-1,num_following=0] |
| | | | +-arguments= |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-partition_by= |
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-order_by= |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-grouping_expressions= |
| | | | +-[] |
| | | +-aggregate_expressions= |
| | | +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,type=Double NULL] |
| | | +-AggregateFunction[function=SUM] |
| | | +-AttributeReference[id=6,name=,alias=$window_aggregate0, |
| | | relation=$window_aggregate,type=Double NULL] |
| | +-project_list= |
| | +-Alias[id=7,name=,alias=sum(avg(int_col)),relation=,type=Double NULL] |
| | +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate, |
| | type=Double NULL] |
| +-output_attributes= |
| +-AttributeReference[id=7,name=,alias=sum(avg(int_col)),relation=, |
| type=Double NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Selection[has_repartition=false] |
| | +-input=Aggregate[has_repartition=false] |
| | | +-input=WindowAggregate |
| | | | +-input=Sort[is_ascending=[true,true],nulls_first=[false,false]] |
| | | | | +-input=TableReference[relation=Test,alias=test] |
| | | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | | +-AttributeReference[id=3,name=double_col,relation=test, |
| | | | | | | type=Double NULL] |
| | | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | | type=VarChar(20) NULL] |
| | | | | +-sort_attributes= |
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-window_aggregate_expression=Alias[id=6,name=,alias=$window_aggregate0, |
| | | | relation=$window_aggregate,type=Double NULL] |
| | | | +-WindowAggregateFunction[function=AVG,window_name=w, |
| | | | is_ascending=[true],nulls_first=[false],frame_mode=row, |
| | | | num_preceding=-1,num_following=0] |
| | | | +-arguments= |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-partition_by= |
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-order_by= |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | +-grouping_expressions= |
| | | | +-[] |
| | | +-aggregate_expressions= |
| | | +-Alias[id=7,name=,alias=$aggregate0,relation=$aggregate,type=Double NULL] |
| | | +-AggregateFunction[function=SUM] |
| | | +-AttributeReference[id=6,name=,alias=$window_aggregate0, |
| | | relation=$window_aggregate,type=Double NULL] |
| | +-project_expressions= |
| | +-Alias[id=7,name=,alias=sum(avg(int_col)),relation=,type=Double NULL] |
| | +-AttributeReference[id=7,name=,alias=$aggregate0,relation=$aggregate, |
| | type=Double NULL] |
| +-output_attributes= |
| +-AttributeReference[id=7,name=,alias=sum(avg(int_col)),relation=, |
| type=Double NULL] |
| == |
| |
| SELECT int_col FROM test |
| UNION |
| SELECT int_col FROM test |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Union[set_operation_type=Union] |
| | +-operands= |
| | | +-Project |
| | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-project_list= |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-Project |
| | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=9,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=11,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-project_list= |
| | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | +-project_attributes= |
| | +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL] |
| +-output_attributes= |
| +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Aggregate[has_repartition=false] |
| | +-input=UnionAll |
| | | +-operands= |
| | | | +-Selection[has_repartition=false] |
| | | | | +-input=TableReference[relation=Test,alias=test] |
| | | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | | +-AttributeReference[id=3,name=double_col,relation=test, |
| | | | | | | type=Double NULL] |
| | | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | | type=VarChar(20) NULL] |
| | | | | +-project_expressions= |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-Selection[has_repartition=false] |
| | | | +-input=TableReference[relation=Test,alias=test] |
| | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float] |
| | | | | +-AttributeReference[id=9,name=double_col,relation=test, |
| | | | | | type=Double NULL] |
| | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-project_expressions= |
| | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | +-project_attributes= |
| | | +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL] |
| | +-grouping_expressions= |
| | | +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL] |
| | +-aggregate_expressions= |
| | +-[] |
| +-output_attributes= |
| +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL] |
| == |
| |
| SELECT intv FROM |
| (SELECT int_col, double_col FROM test |
| UNION ALL |
| SELECT int_col, double_col FROM test |
| ) AS temp(intv, doublev) |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Project |
| | +-input=UnionAll[set_operation_type=UnionAll] |
| | | +-operands= |
| | | | +-Project |
| | | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | | +-AttributeReference[id=3,name=double_col,relation=test, |
| | | | | | | type=Double NULL] |
| | | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | | type=VarChar(20) NULL] |
| | | | | +-project_list= |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=3,name=double_col,relation=test, |
| | | | | type=Double NULL] |
| | | | +-Project |
| | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float] |
| | | | | +-AttributeReference[id=9,name=double_col,relation=test, |
| | | | | | type=Double NULL] |
| | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-project_list= |
| | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=9,name=double_col,relation=test, |
| | | | type=Double NULL] |
| | | +-project_attributes= |
| | | +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL] |
| | | +-AttributeReference[id=13,name=double_col,relation=,type=Double NULL] |
| | +-project_list= |
| | +-Alias[id=14,name=intv,relation=,type=Int NULL] |
| | +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL] |
| +-output_attributes= |
| +-AttributeReference[id=14,name=intv,relation=,type=Int NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Selection[has_repartition=false] |
| | +-input=UnionAll |
| | | +-operands= |
| | | | +-Selection[has_repartition=false] |
| | | | | +-input=TableReference[relation=Test,alias=test] |
| | | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | | +-AttributeReference[id=3,name=double_col,relation=test, |
| | | | | | | type=Double NULL] |
| | | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | | type=VarChar(20) NULL] |
| | | | | +-project_expressions= |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-Selection[has_repartition=false] |
| | | | +-input=TableReference[relation=Test,alias=test] |
| | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float] |
| | | | | +-AttributeReference[id=9,name=double_col,relation=test, |
| | | | | | type=Double NULL] |
| | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=11,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-project_expressions= |
| | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | +-project_attributes= |
| | | +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL] |
| | +-project_expressions= |
| | +-Alias[id=14,name=intv,relation=,type=Int NULL] |
| | +-AttributeReference[id=12,name=int_col,relation=,type=Int NULL] |
| +-output_attributes= |
| +-AttributeReference[id=14,name=intv,relation=,type=Int NULL] |
| == |
| |
| SELECT int_col FROM test |
| INTERSECT |
| SELECT intv FROM |
| (SELECT int_col, double_col FROM test |
| UNION ALL |
| SELECT int_col, double_col FROM test |
| ) AS temp(intv, doublev) |
| -- |
| [Optimized Logical Plan] |
| TopLevelPlan |
| +-plan=Intersect[set_operation_type=Intersect] |
| | +-operands= |
| | | +-Project |
| | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | | type=VarChar(20) NULL] |
| | | | +-project_list= |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-Project |
| | | +-input=UnionAll[set_operation_type=UnionAll] |
| | | | +-operands= |
| | | | | +-Project |
| | | | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float] |
| | | | | | | +-AttributeReference[id=9,name=double_col,relation=test, |
| | | | | | | | type=Double NULL] |
| | | | | | | +-AttributeReference[id=10,name=char_col,relation=test, |
| | | | | | | | type=Char(20)] |
| | | | | | | +-AttributeReference[id=11,name=vchar_col,relation=test, |
| | | | | | | type=VarChar(20) NULL] |
| | | | | | +-project_list= |
| | | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | | | | +-AttributeReference[id=9,name=double_col,relation=test, |
| | | | | | type=Double NULL] |
| | | | | +-Project |
| | | | | +-input=TableReference[relation_name=Test,relation_alias=test] |
| | | | | | +-AttributeReference[id=12,name=int_col,relation=test, |
| | | | | | | type=Int NULL] |
| | | | | | +-AttributeReference[id=13,name=long_col,relation=test,type=Long] |
| | | | | | +-AttributeReference[id=14,name=float_col,relation=test,type=Float] |
| | | | | | +-AttributeReference[id=15,name=double_col,relation=test, |
| | | | | | | type=Double NULL] |
| | | | | | +-AttributeReference[id=16,name=char_col,relation=test, |
| | | | | | | type=Char(20)] |
| | | | | | +-AttributeReference[id=17,name=vchar_col,relation=test, |
| | | | | | type=VarChar(20) NULL] |
| | | | | +-project_list= |
| | | | | +-AttributeReference[id=12,name=int_col,relation=test, |
| | | | | | type=Int NULL] |
| | | | | +-AttributeReference[id=15,name=double_col,relation=test, |
| | | | | type=Double NULL] |
| | | | +-project_attributes= |
| | | | +-AttributeReference[id=18,name=int_col,relation=,type=Int NULL] |
| | | | +-AttributeReference[id=19,name=double_col,relation=,type=Double NULL] |
| | | +-project_list= |
| | | +-Alias[id=20,name=intv,relation=,type=Int NULL] |
| | | +-AttributeReference[id=18,name=int_col,relation=,type=Int NULL] |
| | +-project_attributes= |
| | +-AttributeReference[id=22,name=int_col,relation=,type=Int NULL] |
| +-output_attributes= |
| +-AttributeReference[id=22,name=int_col,relation=,type=Int NULL] |
| [Physical Plan] |
| TopLevelPlan |
| +-plan=Aggregate[has_repartition=false] |
| | +-input=HashLeftSemiJoin[has_repartition=false] |
| | | +-left=TableReference[relation=Test,alias=test] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=2,name=float_col,relation=test,type=Float] |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-AttributeReference[id=4,name=char_col,relation=test,type=Char(20)] |
| | | | +-AttributeReference[id=5,name=vchar_col,relation=test, |
| | | | type=VarChar(20) NULL] |
| | | +-right=UnionAll |
| | | | +-operands= |
| | | | | +-Selection[has_repartition=false] |
| | | | | | +-input=TableReference[relation=Test,alias=test] |
| | | | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | | | | | +-AttributeReference[id=7,name=long_col,relation=test,type=Long] |
| | | | | | | +-AttributeReference[id=8,name=float_col,relation=test,type=Float] |
| | | | | | | +-AttributeReference[id=9,name=double_col,relation=test, |
| | | | | | | | type=Double NULL] |
| | | | | | | +-AttributeReference[id=10,name=char_col,relation=test,type=Char(20)] |
| | | | | | | +-AttributeReference[id=11,name=vchar_col,relation=test, |
| | | | | | | type=VarChar(20) NULL] |
| | | | | | +-project_expressions= |
| | | | | | +-AttributeReference[id=6,name=int_col,relation=test,type=Int NULL] |
| | | | | +-Selection[has_repartition=false] |
| | | | | +-input=TableReference[relation=Test,alias=test] |
| | | | | | +-AttributeReference[id=12,name=int_col,relation=test,type=Int NULL] |
| | | | | | +-AttributeReference[id=13,name=long_col,relation=test,type=Long] |
| | | | | | +-AttributeReference[id=14,name=float_col,relation=test,type=Float] |
| | | | | | +-AttributeReference[id=15,name=double_col,relation=test, |
| | | | | | | type=Double NULL] |
| | | | | | +-AttributeReference[id=16,name=char_col,relation=test,type=Char(20)] |
| | | | | | +-AttributeReference[id=17,name=vchar_col,relation=test, |
| | | | | | type=VarChar(20) NULL] |
| | | | | +-project_expressions= |
| | | | | +-AttributeReference[id=12,name=int_col,relation=test,type=Int NULL] |
| | | | +-project_attributes= |
| | | | +-AttributeReference[id=18,name=int_col,relation=,type=Int NULL] |
| | | +-project_expressions= |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-left_join_attributes= |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-right_join_attributes= |
| | | +-AttributeReference[id=18,name=int_col,relation=,type=Int NULL] |
| | +-grouping_expressions= |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | +-aggregate_expressions= |
| | +-[] |
| +-output_attributes= |
| +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |