| # 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] |
| 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] |
| | | | | | +-referenced_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] |
| | | | | | +-output_attributes= |
| | | | | | +-AttributeReference[id=36,name=subquery_int0,relation=subquery, |
| | | | | | | type=Int NULL] |
| | | | | | +-AttributeReference[id=37,name=subquery_long,relation=subquery, |
| | | | | | | type=Long] |
| | | | | | +-AttributeReference[id=38,name=subquery_int1,relation=subquery, |
| | | | | | type=Int NULL] |
| | | | | +-filter_predicate=Equal |
| | | | | +-AttributeReference[id=38,name=subquery_int1,relation=subquery, |
| | | | | | type=Int NULL] |
| | | | | +-AttributeReference[id=37,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=36,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] |
| | | +-referenced_attributes= |
| | | | +-AttributeReference[id=7,name=,alias=(int_col+2),relation=subquery, |
| | | | type=Int NULL] |
| | | +-output_attributes= |
| | | +-AttributeReference[id=8,name=,alias=(int_col+2),relation=subquery, |
| | | type=Int NULL] |
| | +-project_list= |
| | +-AttributeReference[id=8,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=8,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] |
| == |
| |
| # IN predicate |
| SELECT char_col |
| FROM test |
| WHERE int_col IN (1, 2, 3); |
| -- |
| 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)] |
| == |
| |
| 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); |
| -- |
| 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)] |
| == |
| |
| SELECT char_col |
| FROM test |
| WHERE int_col IN ( |
| SELECT SUM(long_col) - 10 |
| FROM test |
| GROUP BY vchar_col |
| ); |
| -- |
| 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)] |
| == |
| |
| SELECT char_col |
| FROM test |
| WHERE int_col NOT IN ( |
| SELECT long_col |
| FROM test |
| WHERE long_col IN (1, 2) |
| ); |
| -- |
| 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)] |
| == |
| |
| # Scalar subquery expressions |
| SELECT x + (SELECT SUM(y) FROM c) |
| FROM b; |
| -- |
| 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] |
| == |
| |
| SELECT x + (SELECT SUM(y) FROM c WHERE b.w = c.x) |
| FROM b; |
| -- |
| 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] |
| == |
| |
| SELECT * |
| FROM b |
| WHERE b.x > (SELECT SUM(y) FROM c); |
| -- |
| 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] |
| == |
| |
| SELECT * |
| FROM b |
| WHERE b.x > (SELECT SUM(y) FROM c WHERE b.w = c.x); |
| -- |
| 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] |
| == |
| |
| 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; |
| -- |
| 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] |
| == |
| |
| SELECT x * (SELECT SUM(y) FROM c) + (SELECT AVG(y) FROM d) |
| FROM b; |
| -- |
| 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] |
| == |
| |
| SELECT x * (SELECT SUM(y) FROM c) |
| FROM b |
| WHERE w < (SELECT AVG(y) FROM d); |
| -- |
| 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] |
| == |
| |
| SELECT x + (SELECT SUM(y) FROM c WHERE b.w > c.x) |
| FROM b; |
| -- |
| ERROR: Non-equality join predicate is not allowed in scalar subqueries |
| == |
| |
| SELECT x + (SELECT SUM(y) FROM c WHERE b.w = c.x AND b.x > c.y) |
| FROM b; |
| -- |
| ERROR: Non-equality join predicate is not allowed in scalar subqueries |
| == |
| |
| SELECT x + ( |
| SELECT SUM(y) + ( |
| SELECT SUM(w) |
| FROM a WHERE a.y = b.x) |
| FROM c |
| WHERE b.w = c.x AND c.x < 10) |
| FROM b; |
| -- |
| ERROR: Nested queries can only reference attributes in the outer query one level above |
| == |
| |
| # 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; |
| -- |
| 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] |
| == |
| |
| # 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); |
| -- |
| 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] |
| == |
| |
| 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; |
| -- |
| 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] |
| == |
| |
| 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); |
| -- |
| 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] |
| == |
| |
| SELECT int_col, sum(float_col) OVER w1 FROM test |
| WINDOW w2 AS |
| (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); |
| -- |
| ERROR: Undefined window w1 (1 : 37) |
| SELECT int_col, sum(float_col) OVER w1 FROM test |
| ^ |
| == |
| |
| SELECT sum(avg(int_col)) OVER w FROM test |
| WINDOW w AS |
| (PARTITION BY double_col |
| ORDER BY char_col) |
| -- |
| ERROR: Aggregation of Aggregates are not allowed (1 : 12) |
| SELECT sum(avg(int_col)) OVER w FROM test |
| ^ |