| # Copyright 2011-2015 Quickstep Technologies LLC. |
| # Copyright 2015 Pivotal Software, Inc. |
| # Copyright 2016, Quickstep Research Group, Computer Sciences Department, |
| # University of Wisconsin—Madison. |
| # |
| # Licensed under the Apache License, Version 2.0 (the "License"); |
| # you may not use this file except in compliance with the License. |
| # You may obtain a copy of the License at |
| # |
| # http://www.apache.org/licenses/LICENSE-2.0 |
| # |
| # Unless required by applicable law or agreed to in writing, software |
| # distributed under the License is distributed on an "AS IS" BASIS, |
| # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| # See the License for the specific language governing permissions and |
| # limitations under the License. |
| |
| [default optimized_logical_plan] |
| select * from test |
| -- |
| 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] |
| == |
| |
| select 1, 2 from test |
| -- |
| 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] |
| == |
| |
| # Push down the where predicates into the subquery. |
| select int_col, long_col from (select * from test where int_col=1) a where long_col!=int_col and double_col>1 |
| -- |
| 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=And |
| | | +-NotEqual |
| | | | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| | | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-Greater |
| | | | +-AttributeReference[id=3,name=double_col,relation=test,type=Double NULL] |
| | | | +-Literal[value=1,type=Int] |
| | | +-Equal |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-Literal[value=1,type=Int] |
| | +-project_list= |
| | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| +-output_attributes= |
| +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| +-AttributeReference[id=1,name=long_col,relation=test,type=Long] |
| == |
| |
| select * from test a, test b |
| -- |
| 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=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] |
| | | +-join_predicate=Literal[value=true] |
| | +-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=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] |
| +-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=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] |
| == |
| |
| # Hash join. |
| select a.int_col, b.long_col from test a, test b where a.long_col=b.long_col and a.int_col+b.long_col=a.long_col |
| -- |
| 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=1,name=long_col,relation=a,type=Long] |
| | | | +-right_join_attributes= |
| | | | +-AttributeReference[id=7,name=long_col,relation=b,type=Long] |
| | | +-filter_predicate=Equal |
| | | +-Add |
| | | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL] |
| | | | +-AttributeReference[id=7,name=long_col,relation=b,type=Long] |
| | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long] |
| | +-project_list= |
| | +-AttributeReference[id=0,name=int_col,relation=a,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] |
| +-AttributeReference[id=7,name=long_col,relation=b,type=Long] |
| == |
| |
| # Nested loops join. |
| select a.int_col, b.long_col from test a, test b where a.long_col!=b.long_col and a.int_col+b.long_col=a.long_col |
| -- |
| TopLevelPlan |
| +-plan=Project |
| | +-input=Filter |
| | | +-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=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] |
| | | | +-join_predicate=NotEqual |
| | | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long] |
| | | | +-AttributeReference[id=7,name=long_col,relation=b,type=Long] |
| | | +-filter_predicate=Equal |
| | | +-Add |
| | | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL] |
| | | | +-AttributeReference[id=7,name=long_col,relation=b,type=Long] |
| | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long] |
| | +-project_list= |
| | +-AttributeReference[id=0,name=int_col,relation=a,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] |
| +-AttributeReference[id=7,name=long_col,relation=b,type=Long] |
| == |
| |
| select a.int_col |
| from test a, test b, test c, test d |
| where a.int_col=b.int_col and |
| c.int_col=d.int_col and |
| a.long_col=b.long_col and |
| a.long_col+b.long_col>d.long_col |
| -- |
| 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] |
| | | | | +-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] |
| | | +-right=HashJoin |
| | | | +-left=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] |
| | | | +-right=TableReference[relation_name=Test,relation_alias=d] |
| | | | | +-AttributeReference[id=18,name=int_col,relation=d,type=Int NULL] |
| | | | | +-AttributeReference[id=19,name=long_col,relation=d,type=Long] |
| | | | | +-AttributeReference[id=20,name=float_col,relation=d,type=Float] |
| | | | | +-AttributeReference[id=21,name=double_col,relation=d,type=Double NULL] |
| | | | | +-AttributeReference[id=22,name=char_col,relation=d,type=Char(20)] |
| | | | | +-AttributeReference[id=23,name=vchar_col,relation=d, |
| | | | | type=VarChar(20) NULL] |
| | | | +-left_join_attributes= |
| | | | | +-AttributeReference[id=12,name=int_col,relation=c,type=Int NULL] |
| | | | +-right_join_attributes= |
| | | | +-AttributeReference[id=18,name=int_col,relation=d,type=Int NULL] |
| | | +-join_predicate=Greater |
| | | +-Add |
| | | | +-AttributeReference[id=1,name=long_col,relation=a,type=Long] |
| | | | +-AttributeReference[id=7,name=long_col,relation=b,type=Long] |
| | | +-AttributeReference[id=19,name=long_col,relation=d,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] |
| == |
| |
| select a.int_col |
| from test a, test b, test c, test d |
| where a.int_col=d.int_col and a.int_col+b.int_col=c.int_col and (d.double_col>1 or d.int_col=1) and |
| a.int_col+b.long_col=c.long_col |
| -- |
| TopLevelPlan |
| +-plan=Project |
| | +-input=Filter |
| | | +-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=Filter |
| | | | | | +-input=TableReference[relation_name=Test,relation_alias=d] |
| | | | | | | +-AttributeReference[id=18,name=int_col,relation=d,type=Int NULL] |
| | | | | | | +-AttributeReference[id=19,name=long_col,relation=d,type=Long] |
| | | | | | | +-AttributeReference[id=20,name=float_col,relation=d,type=Float] |
| | | | | | | +-AttributeReference[id=21,name=double_col,relation=d, |
| | | | | | | | type=Double NULL] |
| | | | | | | +-AttributeReference[id=22,name=char_col,relation=d,type=Char(20)] |
| | | | | | | +-AttributeReference[id=23,name=vchar_col,relation=d, |
| | | | | | | type=VarChar(20) NULL] |
| | | | | | +-filter_predicate=Or |
| | | | | | +-Greater |
| | | | | | | +-AttributeReference[id=21,name=double_col,relation=d, |
| | | | | | | | type=Double NULL] |
| | | | | | | +-Literal[value=1,type=Int] |
| | | | | | +-Equal |
| | | | | | +-AttributeReference[id=18,name=int_col,relation=d,type=Int NULL] |
| | | | | | +-Literal[value=1,type=Int] |
| | | | | +-left_join_attributes= |
| | | | | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL] |
| | | | | +-right_join_attributes= |
| | | | | +-AttributeReference[id=18,name=int_col,relation=d,type=Int NULL] |
| | | | +-right=NestedLoopsJoin |
| | | | | +-left=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] |
| | | | | +-right=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] |
| | | | | +-join_predicate=Literal[value=true] |
| | | | +-join_predicate=Equal |
| | | | +-Add |
| | | | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL] |
| | | | | +-AttributeReference[id=6,name=int_col,relation=b,type=Int NULL] |
| | | | +-AttributeReference[id=12,name=int_col,relation=c,type=Int NULL] |
| | | +-filter_predicate=Equal |
| | | +-Add |
| | | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL] |
| | | | +-AttributeReference[id=7,name=long_col,relation=b,type=Long] |
| | | +-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] |
| == |
| |
| select a.int_col |
| from test a, test b, test c, |
| (select a.int_col as subquery_int0, b.long_col as subquery_long, c.int_col as subquery_int1 |
| from test a, test b, test c |
| where a.int_col = b.int_col) as d |
| where a.int_col = b.int_col and subquery_int1=subquery_long and c.int_col=subquery_int0 |
| -- |
| 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=6,name=int_col,relation=b,type=Int NULL] |
| | | +-right=HashJoin |
| | | | +-left=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] |
| | | | +-right=Project |
| | | | | +-input=HashJoin |
| | | | | | +-left=TableReference[relation_name=Test,relation_alias=a] |
| | | | | | | +-AttributeReference[id=18,name=int_col,relation=a,type=Int NULL] |
| | | | | | | +-AttributeReference[id=19,name=long_col,relation=a,type=Long] |
| | | | | | | +-AttributeReference[id=20,name=float_col,relation=a,type=Float] |
| | | | | | | +-AttributeReference[id=21,name=double_col,relation=a, |
| | | | | | | | type=Double NULL] |
| | | | | | | +-AttributeReference[id=22,name=char_col,relation=a,type=Char(20)] |
| | | | | | | +-AttributeReference[id=23,name=vchar_col,relation=a, |
| | | | | | | type=VarChar(20) NULL] |
| | | | | | +-right=HashJoin |
| | | | | | | +-left=TableReference[relation_name=Test,relation_alias=c] |
| | | | | | | | +-AttributeReference[id=30,name=int_col,relation=c,type=Int NULL] |
| | | | | | | | +-AttributeReference[id=31,name=long_col,relation=c,type=Long] |
| | | | | | | | +-AttributeReference[id=32,name=float_col,relation=c,type=Float] |
| | | | | | | | +-AttributeReference[id=33,name=double_col,relation=c, |
| | | | | | | | | type=Double NULL] |
| | | | | | | | +-AttributeReference[id=34,name=char_col,relation=c,type=Char(20)] |
| | | | | | | | +-AttributeReference[id=35,name=vchar_col,relation=c, |
| | | | | | | | type=VarChar(20) NULL] |
| | | | | | | +-right=TableReference[relation_name=Test,relation_alias=b] |
| | | | | | | | +-AttributeReference[id=24,name=int_col,relation=b,type=Int NULL] |
| | | | | | | | +-AttributeReference[id=25,name=long_col,relation=b,type=Long] |
| | | | | | | | +-AttributeReference[id=26,name=float_col,relation=b,type=Float] |
| | | | | | | | +-AttributeReference[id=27,name=double_col,relation=b, |
| | | | | | | | | type=Double NULL] |
| | | | | | | | +-AttributeReference[id=28,name=char_col,relation=b,type=Char(20)] |
| | | | | | | | +-AttributeReference[id=29,name=vchar_col,relation=b, |
| | | | | | | | type=VarChar(20) NULL] |
| | | | | | | +-left_join_attributes= |
| | | | | | | | +-AttributeReference[id=30,name=subquery_int1,relation=d, |
| | | | | | | | type=Int NULL] |
| | | | | | | +-right_join_attributes= |
| | | | | | | +-AttributeReference[id=25,name=subquery_long,relation=d,type=Long] |
| | | | | | +-left_join_attributes= |
| | | | | | | +-AttributeReference[id=18,name=int_col,relation=a,type=Int NULL] |
| | | | | | +-right_join_attributes= |
| | | | | | +-AttributeReference[id=24,name=int_col,relation=b,type=Int NULL] |
| | | | | +-project_list= |
| | | | | +-Alias[id=18,name=subquery_int0,relation=d,type=Int NULL] |
| | | | | | +-AttributeReference[id=18,name=int_col,relation=a,type=Int NULL] |
| | | | | +-Alias[id=25,name=subquery_long,relation=d,type=Long] |
| | | | | | +-AttributeReference[id=25,name=long_col,relation=b,type=Long] |
| | | | | +-Alias[id=30,name=subquery_int1,relation=d,type=Int NULL] |
| | | | | +-AttributeReference[id=30,name=int_col,relation=c,type=Int NULL] |
| | | | +-left_join_attributes= |
| | | | | +-AttributeReference[id=12,name=int_col,relation=c,type=Int NULL] |
| | | | +-right_join_attributes= |
| | | | +-AttributeReference[id=18,name=subquery_int0,relation=d,type=Int NULL] |
| | | +-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] |
| == |
| |
| # This is an semantically equivalent query as above, but we generate a different plan. |
| # It is because we do not optimize the WITH query and the main query holistically. |
| with subquery as (select a.int_col as subquery_int0, b.long_col as subquery_long, c.int_col as subquery_int1 |
| from test a, test b, test c where a.int_col = b.int_col) |
| select a.int_col |
| from test a, test b, test c, subquery as d |
| where a.int_col = b.int_col and subquery_int1=subquery_long and c.int_col=subquery_int0 |
| -- |
| TopLevelPlan |
| +-plan=Project |
| | +-input=NestedLoopsJoin |
| | | +-left=HashJoin |
| | | | +-left=TableReference[relation_name=Test,relation_alias=a] |
| | | | | +-AttributeReference[id=18,name=int_col,relation=a,type=Int NULL] |
| | | | | +-AttributeReference[id=19,name=long_col,relation=a,type=Long] |
| | | | | +-AttributeReference[id=20,name=float_col,relation=a,type=Float] |
| | | | | +-AttributeReference[id=21,name=double_col,relation=a,type=Double NULL] |
| | | | | +-AttributeReference[id=22,name=char_col,relation=a,type=Char(20)] |
| | | | | +-AttributeReference[id=23,name=vchar_col,relation=a, |
| | | | | type=VarChar(20) NULL] |
| | | | +-right=TableReference[relation_name=Test,relation_alias=b] |
| | | | | +-AttributeReference[id=24,name=int_col,relation=b,type=Int NULL] |
| | | | | +-AttributeReference[id=25,name=long_col,relation=b,type=Long] |
| | | | | +-AttributeReference[id=26,name=float_col,relation=b,type=Float] |
| | | | | +-AttributeReference[id=27,name=double_col,relation=b,type=Double NULL] |
| | | | | +-AttributeReference[id=28,name=char_col,relation=b,type=Char(20)] |
| | | | | +-AttributeReference[id=29,name=vchar_col,relation=b, |
| | | | | type=VarChar(20) NULL] |
| | | | +-left_join_attributes= |
| | | | | +-AttributeReference[id=18,name=int_col,relation=a,type=Int NULL] |
| | | | +-right_join_attributes= |
| | | | +-AttributeReference[id=24,name=int_col,relation=b,type=Int NULL] |
| | | +-right=HashJoin |
| | | | +-left=TableReference[relation_name=Test,relation_alias=c] |
| | | | | +-AttributeReference[id=30,name=int_col,relation=c,type=Int NULL] |
| | | | | +-AttributeReference[id=31,name=long_col,relation=c,type=Long] |
| | | | | +-AttributeReference[id=32,name=float_col,relation=c,type=Float] |
| | | | | +-AttributeReference[id=33,name=double_col,relation=c,type=Double NULL] |
| | | | | +-AttributeReference[id=34,name=char_col,relation=c,type=Char(20)] |
| | | | | +-AttributeReference[id=35,name=vchar_col,relation=c, |
| | | | | type=VarChar(20) NULL] |
| | | | +-right=Filter |
| | | | | +-input=SharedSubplanReference[subplan_id=0] |
| | | | | | +-output_attributes= |
| | | | | | +-AttributeReference[id=0,name=subquery_int0,relation=subquery, |
| | | | | | | type=Int NULL] |
| | | | | | +-AttributeReference[id=7,name=subquery_long,relation=subquery, |
| | | | | | | type=Long] |
| | | | | | +-AttributeReference[id=12,name=subquery_int1,relation=subquery, |
| | | | | | type=Int NULL] |
| | | | | +-filter_predicate=Equal |
| | | | | +-AttributeReference[id=12,name=subquery_int1,relation=subquery, |
| | | | | | type=Int NULL] |
| | | | | +-AttributeReference[id=7,name=subquery_long,relation=subquery, |
| | | | | type=Long] |
| | | | +-left_join_attributes= |
| | | | | +-AttributeReference[id=30,name=int_col,relation=c,type=Int NULL] |
| | | | +-right_join_attributes= |
| | | | +-AttributeReference[id=0,name=subquery_int0,relation=subquery, |
| | | | type=Int NULL] |
| | | +-join_predicate=Literal[value=true] |
| | +-project_list= |
| | +-AttributeReference[id=18,name=int_col,relation=a,type=Int NULL] |
| +-shared_subplans= |
| | +-Project |
| | +-input=NestedLoopsJoin |
| | | +-left=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] |
| | | +-right=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] |
| | | +-join_predicate=Literal[value=true] |
| | +-project_list= |
| | +-Alias[id=0,name=subquery_int0,relation=subquery,type=Int NULL] |
| | | +-AttributeReference[id=0,name=int_col,relation=a,type=Int NULL] |
| | +-Alias[id=7,name=subquery_long,relation=subquery,type=Long] |
| | | +-AttributeReference[id=7,name=long_col,relation=b,type=Long] |
| | +-Alias[id=12,name=subquery_int1,relation=subquery,type=Int NULL] |
| | +-AttributeReference[id=12,name=int_col,relation=c,type=Int NULL] |
| +-output_attributes= |
| +-AttributeReference[id=18,name=int_col,relation=a,type=Int NULL] |
| == |
| |
| with subquery as (select int_col+2 from (select int_col+1 int_col from test) test) |
| select * from subquery |
| -- |
| TopLevelPlan |
| +-plan=Project |
| | +-input=SharedSubplanReference[subplan_id=0] |
| | | +-output_attributes= |
| | | +-AttributeReference[id=7,name=,alias=(int_col+2),relation=subquery, |
| | | type=Int NULL] |
| | +-project_list= |
| | +-AttributeReference[id=7,name=,alias=(int_col+2),relation=subquery, |
| | 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= |
| | +-Alias[id=7,name=,alias=(int_col+2),relation=subquery,type=Int NULL] |
| | +-Add |
| | +-Add |
| | | +-AttributeReference[id=0,name=int_col,relation=test,type=Int NULL] |
| | | +-Literal[value=1,type=Int] |
| | +-Literal[value=2,type=Int] |
| +-output_attributes= |
| +-AttributeReference[id=7,name=,alias=(int_col+2),relation=subquery, |
| 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); |
| -- |
| 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] |
| == |
| |
| 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); |
| -- |
| 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] |