You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Johannes Schulte <jo...@gmail.com> on 2016/07/15 19:32:16 UTC
Filters in Subselect
Hi,
I am trying to build something similar to drills way of querying directly
on multiple files, without the columnar layer. I am using the
ProjectableFilterableTable to filter out files not part of the query. No i
got some problems and I hope i can express what I want to say:
select max(path) from y
allows for only checking the path column since it is projected
[EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])
EnumerableInterpreter
BindableTableScan(table=[[ourSchema, ourTable]], projects=[[0]])
]
but when trying to query the maximum path and using it as an filter, the
projects are gone
select * from y where path = (select max(path) from y).
The explain plan then looks like this:
[EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"],
expr#4=[CAST($t2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"], expr#5=[=($t3, $t4)], proj#0..1=[{exprs}],
$condition=[$t5])
EnumerableJoin(condition=[true], joinType=[left])
EnumerableInterpreter
BindableTableScan(table=[[ourSchema, ourTable]])
EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])
EnumerableInterpreter
BindableTableScan(table=[[ourSchema, ourTable]])
]]
Is there a way to alter the query so ProjectableFilterableTable can still
be used? Or is the only way for getting this a translatable table?
Thanks so far,
Johannes
Re: Filters in Subselect
Posted by Johannes Schulte <jo...@gmail.com>.
Hi Julian,
thanks for the quick response and the issue. Here ist the debug output:
2016-07-16 09:54:04,977 [main] DEBUG - call#86: Apply rule
[SubQueryRemoveRule:Filter] to
[rel#12:LogicalFilter.NONE.[](input=HepRelVertex#11,condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))]
2016-07-16 09:54:05,025 [main] DEBUG - call#86: Rule
SubQueryRemoveRule:Filter arguments
[rel#12:LogicalFilter.NONE.[](input=HepRelVertex#11,condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))] produced LogicalProject#18
2016-07-16 09:54:05,027 [main] DEBUG - For final plan, using
rel#14:LogicalProject.NONE.[](input=HepRelVertex#29,field1=$0,field=$1)
2016-07-16 09:54:05,027 [main] DEBUG - For final plan, using
rel#28:LogicalProject.NONE.[](input=HepRelVertex#27,field1=$0,field=$1)
2016-07-16 09:54:05,028 [main] DEBUG - For final plan, using
rel#26:LogicalFilter.NONE.[](input=HepRelVertex#25,condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))
2016-07-16 09:54:05,028 [main] DEBUG - For final plan, using
rel#24:LogicalJoin.NONE.[](left=HepRelVertex#11,right=HepRelVertex#23,condition=true,joinType=left)
2016-07-16 09:54:05,028 [main] DEBUG - For final plan, using
rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])
2016-07-16 09:54:05,028 [main] DEBUG - For final plan, using
rel#22:LogicalAggregate.NONE.[](input=HepRelVertex#21,group={},EXPR$0=MAX($0))
2016-07-16 09:54:05,028 [main] DEBUG - For final plan, using
rel#20:LogicalProject.NONE.[](input=HepRelVertex#19,field1=$0)
2016-07-16 09:54:05,028 [main] DEBUG - For final plan, using
rel#1:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])
2016-07-16 09:54:05,048 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 1/1; PHASE
= PRE_PROCESS_MDR; COST = {inf}
2016-07-16 09:54:05,048 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 2/1; PHASE
= PRE_PROCESS; COST = {inf}
2016-07-16 09:54:05,048 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 3/1; PHASE
= OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,050 [main] DEBUG - Pop match: rule [ProjectRemoveRule]
rels
[rel#9:LogicalProject.NONE.[](input=rel#8:Subset#1.NONE.[],field1=$0,field=$1)]
2016-07-16 09:54:05,050 [main] DEBUG - call#69: Apply rule
[ProjectRemoveRule] to
[rel#9:LogicalProject.NONE.[](input=rel#8:Subset#1.NONE.[],field1=$0,field=$1)]
2016-07-16 09:54:05,052 [main] DEBUG - Transform to: rel#8 via
ProjectRemoveRule
2016-07-16 09:54:05,052 [main] DEBUG - call#69 generated 1 successors:
[rel#8:Subset#1.NONE.[]]
2016-07-16 09:54:05,052 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 4/2; PHASE
= OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,053 [main] DEBUG - Skip match: rule
[ProjectFilterTransposeRule] rels
[rel#9:LogicalProject.NONE.[](input=rel#8:Subset#1.NONE.[],field1=$0,field=$1),
rel#7:LogicalFilter.NONE.[](input=rel#6:Subset#0.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))]
2016-07-16 09:54:05,053 [main] DEBUG - Pop match: rule
[ExpandConversionRule] rels
[rel#46:AbstractConverter.ENUMERABLE.[](input=rel#44:Subset#7.NONE.[],convention=ENUMERABLE,sort=[])]
2016-07-16 09:54:05,053 [main] DEBUG - call#247: Apply rule
[ExpandConversionRule] to
[rel#46:AbstractConverter.ENUMERABLE.[](input=rel#44:Subset#7.NONE.[],convention=ENUMERABLE,sort=[])]
2016-07-16 09:54:05,054 [main] DEBUG - call#247 generated 0 successors.
2016-07-16 09:54:05,054 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 5/3; PHASE
= OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,054 [main] DEBUG - Pop match: rule
[EnumerableProjectRule] rels
[rel#9:LogicalProject.NONE.[](input=rel#8:Subset#1.NONE.[],field1=$0,field=$1)]
2016-07-16 09:54:05,055 [main] DEBUG - call#67: Apply rule
[EnumerableProjectRule] to
[rel#9:LogicalProject.NONE.[](input=rel#8:Subset#1.NONE.[],field1=$0,field=$1)]
2016-07-16 09:54:05,057 [main] DEBUG - Transform to: rel#48 via
EnumerableProjectRule
2016-07-16 09:54:05,060 [main] DEBUG - call#67 generated 1 successors:
[rel#48:EnumerableProject.ENUMERABLE.[](input=rel#47:Subset#1.ENUMERABLE.[],field1=$0,field=$1)]
2016-07-16 09:54:05,060 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 6/4; PHASE
= OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,060 [main] DEBUG - Pop match: rule
[ProjectFilterTransposeRule] rels
[rel#43:LogicalProject.NONE.[](input=rel#42:Subset#6.NONE.[],field1=$0,field=$1),
rel#41:LogicalFilter.NONE.[](input=rel#40:Subset#5.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))]
2016-07-16 09:54:05,061 [main] DEBUG - call#226: Apply rule
[ProjectFilterTransposeRule] to
[rel#43:LogicalProject.NONE.[](input=rel#42:Subset#6.NONE.[],field1=$0,field=$1),
rel#41:LogicalFilter.NONE.[](input=rel#40:Subset#5.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))]
2016-07-16 09:54:05,063 [main] DEBUG - call#226 generated 0 successors.
2016-07-16 09:54:05,063 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 7/5; PHASE
= OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,064 [main] DEBUG - Pop match: rule
[EnumerableProjectRule] rels
[rel#43:LogicalProject.NONE.[](input=rel#42:Subset#6.NONE.[],field1=$0,field=$1)]
2016-07-16 09:54:05,064 [main] DEBUG - call#223: Apply rule
[EnumerableProjectRule] to
[rel#43:LogicalProject.NONE.[](input=rel#42:Subset#6.NONE.[],field1=$0,field=$1)]
2016-07-16 09:54:05,064 [main] DEBUG - Transform to: rel#50 via
EnumerableProjectRule
2016-07-16 09:54:05,086 [main] DEBUG - call#223 generated 1 successors:
[rel#50:EnumerableProject.ENUMERABLE.[](input=rel#49:Subset#6.ENUMERABLE.[],field1=$0,field=$1)]
2016-07-16 09:54:05,086 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 8/6; PHASE
= OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,086 [main] DEBUG - Pop match: rule
[FilterJoinRule:FilterJoinRule:filter] rels
[rel#41:LogicalFilter.NONE.[](input=rel#40:Subset#5.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary")),
rel#39:LogicalJoin.NONE.[](left=rel#6:Subset#0.NONE.[],right=rel#38:Subset#4.NONE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,087 [main] DEBUG - call#188: Apply rule
[FilterJoinRule:FilterJoinRule:filter] to
[rel#41:LogicalFilter.NONE.[](input=rel#40:Subset#5.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary")),
rel#39:LogicalJoin.NONE.[](left=rel#6:Subset#0.NONE.[],right=rel#38:Subset#4.NONE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,094 [main] DEBUG - call#188 generated 0 successors.
2016-07-16 09:54:05,095 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 9/7; PHASE
= OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,095 [main] DEBUG - Pop match: rule
[EnumerableFilterRule] rels
[rel#41:LogicalFilter.NONE.[](input=rel#40:Subset#5.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))]
2016-07-16 09:54:05,095 [main] DEBUG - call#196: Apply rule
[EnumerableFilterRule] to
[rel#41:LogicalFilter.NONE.[](input=rel#40:Subset#5.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))]
2016-07-16 09:54:05,096 [main] DEBUG - Transform to: rel#52 via
EnumerableFilterRule
2016-07-16 09:54:05,098 [main] DEBUG - call#196 generated 1 successors:
[rel#52:EnumerableFilter.ENUMERABLE.[](input=rel#51:Subset#5.ENUMERABLE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))]
2016-07-16 09:54:05,098 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 10/8; PHASE
= OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,099 [main] DEBUG - Pop match: rule
[JoinPushExpressionsRule] rels
[rel#39:LogicalJoin.NONE.[](left=rel#6:Subset#0.NONE.[],right=rel#38:Subset#4.NONE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,099 [main] DEBUG - call#157: Apply rule
[JoinPushExpressionsRule] to
[rel#39:LogicalJoin.NONE.[](left=rel#6:Subset#0.NONE.[],right=rel#38:Subset#4.NONE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,100 [main] DEBUG - call#157 generated 0 successors.
2016-07-16 09:54:05,100 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 11/9; PHASE
= OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,100 [main] DEBUG - Pop match: rule [JoinCommuteRule]
rels
[rel#39:LogicalJoin.NONE.[](left=rel#6:Subset#0.NONE.[],right=rel#38:Subset#4.NONE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,100 [main] DEBUG - call#167: Apply rule
[JoinCommuteRule] to
[rel#39:LogicalJoin.NONE.[](left=rel#6:Subset#0.NONE.[],right=rel#38:Subset#4.NONE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,100 [main] DEBUG - call#167 generated 0 successors.
2016-07-16 09:54:05,101 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 12/10;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,102 [main] DEBUG - Pop match: rule
[FilterJoinRule:FilterJoinRule:no-filter] rels
[rel#39:LogicalJoin.NONE.[](left=rel#6:Subset#0.NONE.[],right=rel#38:Subset#4.NONE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,102 [main] DEBUG - call#159: Apply rule
[FilterJoinRule:FilterJoinRule:no-filter] to
[rel#39:LogicalJoin.NONE.[](left=rel#6:Subset#0.NONE.[],right=rel#38:Subset#4.NONE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,102 [main] DEBUG - call#159 generated 0 successors.
2016-07-16 09:54:05,102 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 13/11;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,103 [main] DEBUG - Pop match: rule
[EnumerableMergeJoinRule] rels
[rel#39:LogicalJoin.NONE.[](left=rel#6:Subset#0.NONE.[],right=rel#38:Subset#4.NONE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,103 [main] DEBUG - call#173: Apply rule
[EnumerableMergeJoinRule] to
[rel#39:LogicalJoin.NONE.[](left=rel#6:Subset#0.NONE.[],right=rel#38:Subset#4.NONE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,104 [main] DEBUG - call#173 generated 0 successors.
2016-07-16 09:54:05,104 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 14/12;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,105 [main] DEBUG - Pop match: rule [EnumerableJoinRule]
rels
[rel#39:LogicalJoin.NONE.[](left=rel#6:Subset#0.NONE.[],right=rel#38:Subset#4.NONE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,105 [main] DEBUG - call#165: Apply rule
[EnumerableJoinRule] to
[rel#39:LogicalJoin.NONE.[](left=rel#6:Subset#0.NONE.[],right=rel#38:Subset#4.NONE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,107 [main] DEBUG - Transform to: rel#56 via
EnumerableJoinRule
2016-07-16 09:54:05,111 [main] DEBUG - call#165 generated 1 successors:
[rel#56:EnumerableJoin.ENUMERABLE.[](left=rel#54:Subset#0.ENUMERABLE.[],right=rel#55:Subset#4.ENUMERABLE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,111 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 15/13;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,111 [main] DEBUG - Pop match: rule
[FilterJoinRule:FilterJoinRule:filter] rels
[rel#52:EnumerableFilter.ENUMERABLE.[](input=rel#51:Subset#5.ENUMERABLE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary")),
rel#56:EnumerableJoin.ENUMERABLE.[](left=rel#54:Subset#0.ENUMERABLE.[],right=rel#55:Subset#4.ENUMERABLE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,111 [main] DEBUG - call#357: Apply rule
[FilterJoinRule:FilterJoinRule:filter] to
[rel#52:EnumerableFilter.ENUMERABLE.[](input=rel#51:Subset#5.ENUMERABLE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary")),
rel#56:EnumerableJoin.ENUMERABLE.[](left=rel#54:Subset#0.ENUMERABLE.[],right=rel#55:Subset#4.ENUMERABLE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,111 [main] DEBUG - call#357 generated 0 successors.
2016-07-16 09:54:05,112 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 16/14;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,112 [main] DEBUG - Pop match: rule
[JoinPushExpressionsRule] rels
[rel#56:EnumerableJoin.ENUMERABLE.[](left=rel#54:Subset#0.ENUMERABLE.[],right=rel#55:Subset#4.ENUMERABLE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,112 [main] DEBUG - call#359: Apply rule
[JoinPushExpressionsRule] to
[rel#56:EnumerableJoin.ENUMERABLE.[](left=rel#54:Subset#0.ENUMERABLE.[],right=rel#55:Subset#4.ENUMERABLE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,112 [main] DEBUG - call#359 generated 0 successors.
2016-07-16 09:54:05,112 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 17/15;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,112 [main] DEBUG - Pop match: rule
[FilterJoinRule:FilterJoinRule:no-filter] rels
[rel#56:EnumerableJoin.ENUMERABLE.[](left=rel#54:Subset#0.ENUMERABLE.[],right=rel#55:Subset#4.ENUMERABLE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,112 [main] DEBUG - call#361: Apply rule
[FilterJoinRule:FilterJoinRule:no-filter] to
[rel#56:EnumerableJoin.ENUMERABLE.[](left=rel#54:Subset#0.ENUMERABLE.[],right=rel#55:Subset#4.ENUMERABLE.[],condition=true,joinType=left)]
2016-07-16 09:54:05,112 [main] DEBUG - call#361 generated 0 successors.
2016-07-16 09:54:05,112 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 18/16;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,112 [main] DEBUG - Pop match: rule [TableScanRule] rels
[rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,113 [main] DEBUG - call#16: Apply rule [TableScanRule]
to [rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,113 [main] DEBUG - Transform to: rel#58 via
TableScanRule
2016-07-16 09:54:05,114 [main] DEBUG - call#16 generated 1 successors:
[rel#58:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,114 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 19/17;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,114 [main] DEBUG - Pop match: rule
[AggregateRemoveRule] rels
[rel#37:LogicalAggregate.NONE.[](input=rel#36:Subset#3.NONE.[],group={},EXPR$0=MAX($0)),
rel#35:LogicalProject.NONE.[](input=rel#6:Subset#0.NONE.[],field1=$0)]
2016-07-16 09:54:05,114 [main] DEBUG - call#133: Apply rule
[AggregateRemoveRule] to
[rel#37:LogicalAggregate.NONE.[](input=rel#36:Subset#3.NONE.[],group={},EXPR$0=MAX($0)),
rel#35:LogicalProject.NONE.[](input=rel#6:Subset#0.NONE.[],field1=$0)]
2016-07-16 09:54:05,115 [main] DEBUG - call#133 generated 0 successors.
2016-07-16 09:54:05,115 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 20/18;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,115 [main] DEBUG - Pop match: rule
[AggregatePullUpConstantsRule] rels
[rel#37:LogicalAggregate.NONE.[](input=rel#36:Subset#3.NONE.[],group={},EXPR$0=MAX($0)),
rel#35:LogicalProject.NONE.[](input=rel#6:Subset#0.NONE.[],field1=$0)]
2016-07-16 09:54:05,115 [main] DEBUG - call#129: Apply rule
[AggregatePullUpConstantsRule] to
[rel#37:LogicalAggregate.NONE.[](input=rel#36:Subset#3.NONE.[],group={},EXPR$0=MAX($0)),
rel#35:LogicalProject.NONE.[](input=rel#6:Subset#0.NONE.[],field1=$0)]
2016-07-16 09:54:05,132 [main] DEBUG - call#129 generated 0 successors.
2016-07-16 09:54:05,133 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 21/19;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,133 [main] DEBUG - Pop match: rule
[AggregateProjectMergeRule] rels
[rel#37:LogicalAggregate.NONE.[](input=rel#36:Subset#3.NONE.[],group={},EXPR$0=MAX($0)),
rel#35:LogicalProject.NONE.[](input=rel#6:Subset#0.NONE.[],field1=$0)]
2016-07-16 09:54:05,133 [main] DEBUG - call#118: Apply rule
[AggregateProjectMergeRule] to
[rel#37:LogicalAggregate.NONE.[](input=rel#36:Subset#3.NONE.[],group={},EXPR$0=MAX($0)),
rel#35:LogicalProject.NONE.[](input=rel#6:Subset#0.NONE.[],field1=$0)]
2016-07-16 09:54:05,134 [main] DEBUG - Transform to: rel#59 via
AggregateProjectMergeRule
2016-07-16 09:54:05,136 [main] DEBUG - call#118 generated 1 successors:
[rel#59:LogicalAggregate.NONE.[](input=rel#6:Subset#0.NONE.[],group={},EXPR$0=MAX($0))]
2016-07-16 09:54:05,137 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 22/20;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,137 [main] DEBUG - Pop match: rule
[AggregateRemoveRule] rels
[rel#59:LogicalAggregate.NONE.[](input=rel#6:Subset#0.NONE.[],group={},EXPR$0=MAX($0)),
rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,137 [main] DEBUG - call#384: Apply rule
[AggregateRemoveRule] to
[rel#59:LogicalAggregate.NONE.[](input=rel#6:Subset#0.NONE.[],group={},EXPR$0=MAX($0)),
rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,137 [main] DEBUG - call#384 generated 0 successors.
2016-07-16 09:54:05,137 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 23/21;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,138 [main] DEBUG - Pop match: rule
[AggregatePullUpConstantsRule] rels
[rel#59:LogicalAggregate.NONE.[](input=rel#6:Subset#0.NONE.[],group={},EXPR$0=MAX($0)),
rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,138 [main] DEBUG - call#380: Apply rule
[AggregatePullUpConstantsRule] to
[rel#59:LogicalAggregate.NONE.[](input=rel#6:Subset#0.NONE.[],group={},EXPR$0=MAX($0)),
rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,138 [main] DEBUG - call#380 generated 0 successors.
2016-07-16 09:54:05,138 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 24/22;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,138 [main] DEBUG - Pop match: rule
[AggregateExpandDistinctAggregatesRule] rels
[rel#59:LogicalAggregate.NONE.[](input=rel#6:Subset#0.NONE.[],group={},EXPR$0=MAX($0))]
2016-07-16 09:54:05,138 [main] DEBUG - call#374: Apply rule
[AggregateExpandDistinctAggregatesRule] to
[rel#59:LogicalAggregate.NONE.[](input=rel#6:Subset#0.NONE.[],group={},EXPR$0=MAX($0))]
2016-07-16 09:54:05,138 [main] DEBUG - call#374 generated 0 successors.
2016-07-16 09:54:05,139 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 25/23;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,139 [main] DEBUG - Pop match: rule
[AggregateExpandDistinctAggregatesRule] rels
[rel#37:LogicalAggregate.NONE.[](input=rel#36:Subset#3.NONE.[],group={},EXPR$0=MAX($0))]
2016-07-16 09:54:05,139 [main] DEBUG - call#123: Apply rule
[AggregateExpandDistinctAggregatesRule] to
[rel#37:LogicalAggregate.NONE.[](input=rel#36:Subset#3.NONE.[],group={},EXPR$0=MAX($0))]
2016-07-16 09:54:05,139 [main] DEBUG - call#123 generated 0 successors.
2016-07-16 09:54:05,139 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 26/24;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,139 [main] DEBUG - Pop match: rule
[BindableTableScanRule] rels
[rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,139 [main] DEBUG - call#14: Apply rule
[BindableTableScanRule] to
[rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,140 [main] DEBUG - Transform to: rel#60 via
BindableTableScanRule
2016-07-16 09:54:05,316 [main] DEBUG - call#14 generated 1 successors:
[rel#60:BindableTableScan.BINDABLE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,329 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 27/25;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,330 [main] DEBUG - Pop match: rule
[EnumerableTableScanRule] rels
[rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,330 [main] DEBUG - call#11: Apply rule
[EnumerableTableScanRule] to
[rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,331 [main] DEBUG - call#11 generated 0 successors.
2016-07-16 09:54:05,331 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 28/26;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,331 [main] DEBUG - Pop match: rule
[EnumerableAggregateRule] rels
[rel#59:LogicalAggregate.NONE.[](input=rel#6:Subset#0.NONE.[],group={},EXPR$0=MAX($0))]
2016-07-16 09:54:05,331 [main] DEBUG - call#389: Apply rule
[EnumerableAggregateRule] to
[rel#59:LogicalAggregate.NONE.[](input=rel#6:Subset#0.NONE.[],group={},EXPR$0=MAX($0))]
2016-07-16 09:54:05,383 [main] DEBUG - Transform to: rel#62 via
EnumerableAggregateRule
2016-07-16 09:54:05,389 [main] DEBUG - call#389 generated 1 successors:
[rel#62:EnumerableAggregate.ENUMERABLE.[](input=rel#54:Subset#0.ENUMERABLE.[],group={},EXPR$0=MAX($0))]
2016-07-16 09:54:05,389 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 29/27;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,391 [main] DEBUG - Pop match: rule
[EnumerableAggregateRule] rels
[rel#37:LogicalAggregate.NONE.[](input=rel#36:Subset#3.NONE.[],group={},EXPR$0=MAX($0))]
2016-07-16 09:54:05,392 [main] DEBUG - call#138: Apply rule
[EnumerableAggregateRule] to
[rel#37:LogicalAggregate.NONE.[](input=rel#36:Subset#3.NONE.[],group={},EXPR$0=MAX($0))]
2016-07-16 09:54:05,392 [main] DEBUG - Transform to: rel#64 via
EnumerableAggregateRule
2016-07-16 09:54:05,394 [main] DEBUG - call#138 generated 1 successors:
[rel#64:EnumerableAggregate.ENUMERABLE.[](input=rel#63:Subset#3.ENUMERABLE.[],group={},EXPR$0=MAX($0))]
2016-07-16 09:54:05,394 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 30/28;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,394 [main] DEBUG - Pop match: rule [ProjectScanRule]
rels
[rel#35:LogicalProject.NONE.[](input=rel#6:Subset#0.NONE.[],field1=$0),
rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,395 [main] DEBUG - call#102: Apply rule
[ProjectScanRule] to
[rel#35:LogicalProject.NONE.[](input=rel#6:Subset#0.NONE.[],field1=$0),
rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,395 [main] DEBUG - Transform to: rel#65 via
ProjectScanRule
2016-07-16 09:54:05,397 [main] DEBUG - call#102 generated 1 successors:
[rel#65:BindableTableScan.BINDABLE.[](table=[ourSchema,
ourTable],projects=[0])]
2016-07-16 09:54:05,397 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 31/29;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,397 [main] DEBUG - Pop match: rule
[EnumerableProjectRule] rels
[rel#35:LogicalProject.NONE.[](input=rel#6:Subset#0.NONE.[],field1=$0)]
2016-07-16 09:54:05,397 [main] DEBUG - call#100: Apply rule
[EnumerableProjectRule] to
[rel#35:LogicalProject.NONE.[](input=rel#6:Subset#0.NONE.[],field1=$0)]
2016-07-16 09:54:05,414 [main] DEBUG - Transform to: rel#67 via
EnumerableProjectRule
2016-07-16 09:54:05,416 [main] DEBUG - call#100 generated 1 successors:
[rel#67:EnumerableProject.ENUMERABLE.[](input=rel#54:Subset#0.ENUMERABLE.[],field1=$0)]
2016-07-16 09:54:05,417 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 32/30;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,417 [main] DEBUG - Pop match: rule
[AggregateProjectMergeRule] rels
[rel#64:EnumerableAggregate.ENUMERABLE.[](input=rel#63:Subset#3.ENUMERABLE.[],group={},EXPR$0=MAX($0)),
rel#67:EnumerableProject.ENUMERABLE.[](input=rel#54:Subset#0.ENUMERABLE.[],field1=$0)]
2016-07-16 09:54:05,417 [main] DEBUG - call#467: Apply rule
[AggregateProjectMergeRule] to
[rel#64:EnumerableAggregate.ENUMERABLE.[](input=rel#63:Subset#3.ENUMERABLE.[],group={},EXPR$0=MAX($0)),
rel#67:EnumerableProject.ENUMERABLE.[](input=rel#54:Subset#0.ENUMERABLE.[],field1=$0)]
2016-07-16 09:54:05,417 [main] DEBUG - Transform to: rel#68 via
AggregateProjectMergeRule
2016-07-16 09:54:05,419 [main] DEBUG - call#467 generated 1 successors:
[rel#68:EnumerableAggregate.ENUMERABLE.[](input=rel#54:Subset#0.ENUMERABLE.[],group={},EXPR$0=MAX($0))]
2016-07-16 09:54:05,419 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 33/31;
PHASE = OPTIMIZE; COST = {inf}
2016-07-16 09:54:05,419 [main] DEBUG - Pop match: rule
[EnumerableInterpreterRule] rels [rel#61:Subset#0.BINDABLE.[]]
2016-07-16 09:54:05,419 [main] DEBUG - call#415: Apply rule
[EnumerableInterpreterRule] to [rel#61:Subset#0.BINDABLE.[]]
2016-07-16 09:54:05,420 [main] DEBUG - Transform to: rel#69 via
EnumerableInterpreterRule
2016-07-16 09:54:05,515 [main] DEBUG - call#415 generated 1 successors:
[rel#69:EnumerableInterpreter.ENUMERABLE.[](input=rel#61:Subset#0.BINDABLE.[])]
2016-07-16 09:54:05,516 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 34/32;
PHASE = OPTIMIZE; COST = {1883.767018598809 rows, 1402.02 cpu, 0.0 io}
2016-07-16 09:54:05,516 [main] DEBUG - Pop match: rule
[ProjectScanRule:interpreter] rels
[rel#67:EnumerableProject.ENUMERABLE.[](input=rel#54:Subset#0.ENUMERABLE.[],field1=$0),
rel#69:EnumerableInterpreter.ENUMERABLE.[](input=rel#61:Subset#0.BINDABLE.[]),
rel#60:BindableTableScan.BINDABLE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,516 [main] DEBUG - call#487: Apply rule
[ProjectScanRule:interpreter] to
[rel#67:EnumerableProject.ENUMERABLE.[](input=rel#54:Subset#0.ENUMERABLE.[],field1=$0),
rel#69:EnumerableInterpreter.ENUMERABLE.[](input=rel#61:Subset#0.BINDABLE.[]),
rel#60:BindableTableScan.BINDABLE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,516 [main] DEBUG - Transform to: rel#70 via
ProjectScanRule:interpreter
2016-07-16 09:54:05,517 [main] DEBUG - call#487 generated 1 successors:
[rel#70:BindableTableScan.BINDABLE.[](table=[ourSchema,
ourTable],projects=[0])]
2016-07-16 09:54:05,518 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 35/33;
PHASE = OPTIMIZE; COST = {1883.767018598809 rows, 1402.02 cpu, 0.0 io}
2016-07-16 09:54:05,518 [main] DEBUG - Pop match: rule
[EnumerableInterpreterRule] rels
[rel#60:BindableTableScan.BINDABLE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,518 [main] DEBUG - call#395: Apply rule
[EnumerableInterpreterRule] to
[rel#60:BindableTableScan.BINDABLE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,518 [main] DEBUG - Transform to: rel#71 via
EnumerableInterpreterRule
2016-07-16 09:54:05,529 [main] DEBUG - call#395 generated 1 successors:
[EnumerableInterpreter#71]
2016-07-16 09:54:05,529 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 36/34;
PHASE = OPTIMIZE; COST = {1883.767018598809 rows, 1402.02 cpu, 0.0 io}
2016-07-16 09:54:05,529 [main] DEBUG - Pop match: rule
[EnumerableInterpreterRule] rels [rel#66:Subset#3.BINDABLE.[]]
2016-07-16 09:54:05,529 [main] DEBUG - call#465: Apply rule
[EnumerableInterpreterRule] to [rel#66:Subset#3.BINDABLE.[]]
2016-07-16 09:54:05,529 [main] DEBUG - Transform to: rel#73 via
EnumerableInterpreterRule
2016-07-16 09:54:05,531 [main] DEBUG - call#465 generated 1 successors:
[rel#73:EnumerableInterpreter.ENUMERABLE.[](input=rel#66:Subset#3.BINDABLE.[])]
2016-07-16 09:54:05,531 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 37/35;
PHASE = OPTIMIZE; COST = {1883.767018598809 rows, 1402.02 cpu, 0.0 io}
2016-07-16 09:54:05,531 [main] DEBUG - Pop match: rule
[EnumerableInterpreterRule] rels
[rel#65:BindableTableScan.BINDABLE.[](table=[ourSchema,
ourTable],projects=[0])]
2016-07-16 09:54:05,531 [main] DEBUG - call#445: Apply rule
[EnumerableInterpreterRule] to
[rel#65:BindableTableScan.BINDABLE.[](table=[ourSchema,
ourTable],projects=[0])]
2016-07-16 09:54:05,531 [main] DEBUG - Transform to: rel#74 via
EnumerableInterpreterRule
2016-07-16 09:54:05,532 [main] DEBUG - call#445 generated 1 successors:
[EnumerableInterpreter#74]
2016-07-16 09:54:05,532 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 38/36;
PHASE = OPTIMIZE; COST = {1883.767018598809 rows, 1402.02 cpu, 0.0 io}
2016-07-16 09:54:05,533 [main] DEBUG - Pop match: rule [ProjectRemoveRule]
rels
[rel#48:EnumerableProject.ENUMERABLE.[](input=rel#47:Subset#1.ENUMERABLE.[],field1=$0,field=$1)]
2016-07-16 09:54:05,533 [main] DEBUG - call#312: Apply rule
[ProjectRemoveRule] to
[rel#48:EnumerableProject.ENUMERABLE.[](input=rel#47:Subset#1.ENUMERABLE.[],field1=$0,field=$1)]
2016-07-16 09:54:05,534 [main] DEBUG - Transform to: rel#47 via
ProjectRemoveRule
2016-07-16 09:54:05,534 [main] DEBUG - call#312 generated 1 successors:
[rel#47:Subset#1.ENUMERABLE.[]]
2016-07-16 09:54:05,534 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 39/37;
PHASE = OPTIMIZE; COST = {1883.767018598809 rows, 1402.02 cpu, 0.0 io}
2016-07-16 09:54:05,534 [main] DEBUG - Skip match: rule
[ProjectMergeRule:force_mode] rels
[rel#48:EnumerableProject.ENUMERABLE.[](input=rel#47:Subset#1.ENUMERABLE.[],field1=$0,field=$1),
rel#48:EnumerableProject.ENUMERABLE.[](input=rel#47:Subset#1.ENUMERABLE.[],field1=$0,field=$1)]
2016-07-16 09:54:05,534 [main] DEBUG - Skip match: rule
[ProjectMergeRule:force_mode] rels
[rel#9:LogicalProject.NONE.[](input=rel#8:Subset#1.NONE.[],field1=$0,field=$1),
rel#9:LogicalProject.NONE.[](input=rel#8:Subset#1.NONE.[],field1=$0,field=$1)]
2016-07-16 09:54:05,534 [main] DEBUG - Pop match: rule
[MaterializedViewFilterScanRule] rels
[rel#7:LogicalFilter.NONE.[](input=rel#6:Subset#0.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary")),
rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,534 [main] DEBUG - call#45: Apply rule
[MaterializedViewFilterScanRule] to
[rel#7:LogicalFilter.NONE.[](input=rel#6:Subset#0.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary")),
rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,534 [main] DEBUG - call#45 generated 0 successors.
2016-07-16 09:54:05,534 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 40/38;
PHASE = OPTIMIZE; COST = {1883.767018598809 rows, 1402.02 cpu, 0.0 io}
2016-07-16 09:54:05,534 [main] DEBUG - Pop match: rule [FilterTableRule]
rels
[rel#7:LogicalFilter.NONE.[](input=rel#6:Subset#0.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary")),
rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,534 [main] DEBUG - call#36: Apply rule
[FilterTableRule] to
[rel#7:LogicalFilter.NONE.[](input=rel#6:Subset#0.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary")),
rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])]
2016-07-16 09:54:05,535 [main] DEBUG - Transform to: rel#76 via
FilterTableRule
2016-07-16 09:54:05,537 [main] DEBUG - call#36 generated 1 successors:
[rel#76:BindableTableScan.BINDABLE.[](table=[ourSchema,
ourTable],filters=[=(CAST($0):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary", CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary")])]
2016-07-16 09:54:05,541 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 41/39;
PHASE = OPTIMIZE; COST = {1883.767018598809 rows, 1402.02 cpu, 0.0 io}
2016-07-16 09:54:05,541 [main] DEBUG - Pop match: rule
[EnumerableInterpreterRule] rels [rel#77:Subset#1.BINDABLE.[]]
2016-07-16 09:54:05,541 [main] DEBUG - call#524: Apply rule
[EnumerableInterpreterRule] to [rel#77:Subset#1.BINDABLE.[]]
2016-07-16 09:54:05,542 [main] DEBUG - Transform to: rel#78 via
EnumerableInterpreterRule
2016-07-16 09:54:05,545 [main] DEBUG - call#524 generated 1 successors:
[rel#78:EnumerableInterpreter.ENUMERABLE.[](input=rel#77:Subset#1.BINDABLE.[])]
2016-07-16 09:54:05,545 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 42/40;
PHASE = OPTIMIZE; COST = {1883.767018598809 rows, 1402.02 cpu, 0.0 io}
2016-07-16 09:54:05,545 [main] DEBUG - Skip match: rule
[ProjectScanRule:interpreter] rels
[rel#48:EnumerableProject.ENUMERABLE.[](input=rel#47:Subset#1.ENUMERABLE.[],field1=$0,field=$1),
rel#78:EnumerableInterpreter.ENUMERABLE.[](input=rel#77:Subset#1.BINDABLE.[]),
rel#76:BindableTableScan.BINDABLE.[](table=[ourSchema,
ourTable],filters=[=(CAST($0):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary", CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary")])]
2016-07-16 09:54:05,545 [main] DEBUG - Pop match: rule
[EnumerableInterpreterRule] rels
[rel#76:BindableTableScan.BINDABLE.[](table=[ourSchema,
ourTable],filters=[=(CAST($0):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary", CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary")])]
2016-07-16 09:54:05,545 [main] DEBUG - call#504: Apply rule
[EnumerableInterpreterRule] to
[rel#76:BindableTableScan.BINDABLE.[](table=[ourSchema,
ourTable],filters=[=(CAST($0):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary", CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary")])]
2016-07-16 09:54:05,545 [main] DEBUG - Transform to: rel#79 via
EnumerableInterpreterRule
2016-07-16 09:54:05,549 [main] DEBUG - call#504 generated 1 successors:
[EnumerableInterpreter#79]
2016-07-16 09:54:05,552 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 43/41;
PHASE = OPTIMIZE; COST = {1883.767018598809 rows, 1402.02 cpu, 0.0 io}
2016-07-16 09:54:05,552 [main] DEBUG - Pop match: rule
[EnumerableFilterRule] rels
[rel#7:LogicalFilter.NONE.[](input=rel#6:Subset#0.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))]
2016-07-16 09:54:05,552 [main] DEBUG - call#39: Apply rule
[EnumerableFilterRule] to
[rel#7:LogicalFilter.NONE.[](input=rel#6:Subset#0.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))]
2016-07-16 09:54:05,552 [main] DEBUG - Transform to: rel#81 via
EnumerableFilterRule
2016-07-16 09:54:05,555 [main] DEBUG - call#39 generated 1 successors:
[rel#81:EnumerableFilter.ENUMERABLE.[](input=rel#54:Subset#0.ENUMERABLE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
LogicalProject(field1=[$0])
LogicalTableScan(table=[[ourSchema, ourTable]])
})):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))]
2016-07-16 09:54:05,557 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 44/42;
PHASE = OPTIMIZE; COST = {1883.767018598809 rows, 1402.02 cpu, 0.0 io}
2016-07-16 09:54:05,557 [main] DEBUG - PLANNER =
org.apache.calcite.plan.volcano.VolcanoPlanner@31d0e481; TICK = 45/1; PHASE
= CLEANUP; COST = {1883.767018598809 rows, 1402.02 cpu, 0.0 io}
2016-07-16 09:54:05,585 [main] DEBUG - Cheapest plan:
EnumerableProject(field1=[$0], field=[$1]): rowcount = 150.0, cumulative
cost = {1872.767018598809 rows, 1400.0 cpu, 0.0 io}, id = 87
EnumerableFilter(condition=[=(CAST($0):VARCHAR(1) CHARACTER SET
"ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($2):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary")]): rowcount
= 150.0, cumulative cost = {1722.767018598809 rows, 1100.0 cpu, 0.0 io}, id
= 86
EnumerableJoin(condition=[true], joinType=[left]): rowcount = 1000.0,
cumulative cost = {1572.767018598809 rows, 100.0 cpu, 0.0 io}, id = 85
EnumerableInterpreter: rowcount = 100.0, cumulative cost = {50.0
rows, 50.0 cpu, 0.0 io}, id = 82
BindableTableScan(table=[[ourSchema, ourTable]]): rowcount = 100.0,
cumulative cost = {1.0 rows, 1.01 cpu, 0.0 io}, id = 60
EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)]): rowcount = 10.0,
cumulative cost = {61.25 rows, 50.0 cpu, 0.0 io}, id = 84
EnumerableInterpreter: rowcount = 100.0, cumulative cost = {50.0
rows, 50.0 cpu, 0.0 io}, id = 83
BindableTableScan(table=[[ourSchema, ourTable]]): rowcount =
100.0, cumulative cost = {1.0 rows, 1.01 cpu, 0.0 io}, id = 60
2016-07-16 09:54:05,588 [main] DEBUG - Provenance:
EnumerableProject#87
direct
rel#50:EnumerableProject.ENUMERABLE.[](input=rel#49:Subset#6.ENUMERABLE.[],field1=$0,field=$1)
call#223 rule [EnumerableProjectRule]
rel#43:LogicalProject.NONE.[](input=rel#42:Subset#6.NONE.[],field1=$0,field=$1)
no parent
EnumerableFilter#86
direct
rel#52:EnumerableFilter.ENUMERABLE.[](input=rel#51:Subset#5.ENUMERABLE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))
call#196 rule [EnumerableFilterRule]
rel#41:LogicalFilter.NONE.[](input=rel#40:Subset#5.NONE.[],condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))
no parent
EnumerableJoin#85
direct
rel#56:EnumerableJoin.ENUMERABLE.[](left=rel#54:Subset#0.ENUMERABLE.[],right=rel#55:Subset#4.ENUMERABLE.[],condition=true,joinType=left)
call#165 rule [EnumerableJoinRule]
rel#39:LogicalJoin.NONE.[](left=rel#6:Subset#0.NONE.[],right=rel#38:Subset#4.NONE.[],condition=true,joinType=left)
no parent
EnumerableInterpreter#82
direct
rel#69:EnumerableInterpreter.ENUMERABLE.[](input=rel#61:Subset#0.BINDABLE.[])
call#415 rule [EnumerableInterpreterRule]
rel#61:Subset#0.BINDABLE.[]
subset rel#61:Subset#0.BINDABLE.[]
rel#60:BindableTableScan.BINDABLE.[](table=[ourSchema,
ourTable])
call#14 rule [BindableTableScanRule]
rel#0:LogicalTableScan.NONE.[](table=[ourSchema, ourTable])
no parent
rel#60 (see above)
EnumerableAggregate#84
direct
rel#62:EnumerableAggregate.ENUMERABLE.[](input=rel#54:Subset#0.ENUMERABLE.[],group={},EXPR$0=MAX($0))
call#389 rule [EnumerableAggregateRule]
rel#59:LogicalAggregate.NONE.[](input=rel#6:Subset#0.NONE.[],group={},EXPR$0=MAX($0))
call#118 rule [AggregateProjectMergeRule]
rel#37:LogicalAggregate.NONE.[](input=rel#36:Subset#3.NONE.[],group={},EXPR$0=MAX($0))
no parent
rel#35:LogicalProject.NONE.[](input=rel#6:Subset#0.NONE.[],field1=$0)
no parent
EnumerableInterpreter#83
direct
rel#69 (see above)
rel#60 (see above)
2016-07-16 09:54:05,590 [main] DEBUG - call#550: Apply rule
[EnumerableFilterToCalcRule] to
[rel#98:EnumerableFilter.ENUMERABLE.[](input=HepRelVertex#97,condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))]
2016-07-16 09:54:05,630 [main] DEBUG - call#550: Rule
EnumerableFilterToCalcRule arguments
[rel#98:EnumerableFilter.ENUMERABLE.[](input=HepRelVertex#97,condition==(CAST($0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",
CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"))] produced EnumerableCalc#102
2016-07-16 09:54:05,631 [main] DEBUG - call#551: Apply rule
[EnumerableProjectToCalcRule] to
[rel#100:EnumerableProject.ENUMERABLE.[](input=HepRelVertex#103,field1=$0,field=$1)]
2016-07-16 09:54:05,663 [main] DEBUG - call#551: Rule
EnumerableProjectToCalcRule arguments
[rel#100:EnumerableProject.ENUMERABLE.[](input=HepRelVertex#103,field1=$0,field=$1)]
produced EnumerableCalc#104
2016-07-16 09:54:05,666 [main] DEBUG - call#552: Apply rule [CalcMergeRule]
to
[rel#104:EnumerableCalc.ENUMERABLE.[](input=HepRelVertex#103,expr#0..2={inputs},field1=$t0,field=$t1),
rel#102:EnumerableCalc.ENUMERABLE.[](input=HepRelVertex#97,expr#0..2={inputs},expr#3=CAST($t0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary",expr#4=CAST($t2):VARCHAR(1) CHARACTER SET
"ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",expr#5==($t3,
$t4),field1=$t0,field=$t1,EXPR$0=$t2,$condition=$t5)]
2016-07-16 09:54:05,667 [main] DEBUG - call#552: Rule CalcMergeRule
arguments
[rel#104:EnumerableCalc.ENUMERABLE.[](input=HepRelVertex#103,expr#0..2={inputs},field1=$t0,field=$t1),
rel#102:EnumerableCalc.ENUMERABLE.[](input=HepRelVertex#97,expr#0..2={inputs},expr#3=CAST($t0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary",expr#4=CAST($t2):VARCHAR(1) CHARACTER SET
"ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",expr#5==($t3,
$t4),field1=$t0,field=$t1,EXPR$0=$t2,$condition=$t5)] produced
EnumerableCalc#106
2016-07-16 09:54:05,670 [main] DEBUG - For final plan, using
rel#106:EnumerableCalc.ENUMERABLE.[](input=HepRelVertex#97,expr#0..2={inputs},expr#3=CAST($t0):VARCHAR(1)
CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary",expr#4=CAST($t2):VARCHAR(1) CHARACTER SET
"ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",expr#5==($t3,
$t4),field1=$t0,field=$t1,$condition=$t5)
2016-07-16 09:54:05,670 [main] DEBUG - For final plan, using
rel#96:EnumerableJoin.ENUMERABLE.[](left=HepRelVertex#90,right=HepRelVertex#95,condition=true,joinType=left)
2016-07-16 09:54:05,670 [main] DEBUG - For final plan, using
rel#89:EnumerableInterpreter.ENUMERABLE.[](input=HepRelVertex#88)
2016-07-16 09:54:05,670 [main] DEBUG - For final plan, using
rel#60:BindableTableScan.BINDABLE.[](table=[ourSchema, ourTable])
2016-07-16 09:54:05,673 [main] DEBUG - For final plan, using
rel#94:EnumerableAggregate.ENUMERABLE.[](input=HepRelVertex#93,group={},EXPR$0=MAX($0))
2016-07-16 09:54:05,673 [main] DEBUG - For final plan, using
rel#92:EnumerableInterpreter.ENUMERABLE.[](input=HepRelVertex#91)
2016-07-16 09:54:05,673 [main] DEBUG - For final plan, using
rel#60:BindableTableScan.BINDABLE.[](table=[ourSchema, ourTable])
2016-07-16 09:54:06,084 [main] DEBUG - For final plan, using
rel#60:BindableTableScan.BINDABLE.[](table=[ourSchema, ourTable])
On Sat, Jul 16, 2016 at 2:27 AM, Julian Hyde <jh...@apache.org> wrote:
> By the way, I'd like one day to be able to optimize
>
> select * from t
> where y = (select max(y) from t)
>
> to
>
> select * from t order by y desc limit 1
>
> if y is unique and not null, or something a little more complex if it
> is not unique. I don't know whether this optimization would help all
> of the cases you are looking at. I logged
> https://issues.apache.org/jira/browse/CALCITE-1317.
>
> Julian
>
> On Fri, Jul 15, 2016 at 5:04 PM, Julian Hyde <jh...@apache.org> wrote:
> > Can you turn on tracing and post the final planner state? (If you
> > like, log a JIRA case, and attach the output if it is large.)
> >
> > I would have expected there to be a Project immediately above the
> > BindableTableScan. (Just as in the first query, there was almost
> > certainly a Project above a TableScan, and then the projects were
> > correctly pushed into the TableScan.)
> >
> > Why is that Project not present? Maybe it is present, but the cost is
> > wrong. Or maybe the Project was not pushed through the Join (the fact
> > that the join condition is [true] is a worrying sign).
> >
> > The planner state should help answer these questions.
> >
> > Julian
> >
> >
> > On Fri, Jul 15, 2016 at 12:32 PM, Johannes Schulte
> > <jo...@gmail.com> wrote:
> >> Hi,
> >>
> >> I am trying to build something similar to drills way of querying
> directly
> >> on multiple files, without the columnar layer. I am using the
> >> ProjectableFilterableTable to filter out files not part of the query.
> No i
> >> got some problems and I hope i can express what I want to say:
> >>
> >>
> >> select max(path) from y
> >> allows for only checking the path column since it is projected
> >>
> >> [EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])
> >> EnumerableInterpreter
> >> BindableTableScan(table=[[ourSchema, ourTable]], projects=[[0]])
> >> ]
> >>
> >> but when trying to query the maximum path and using it as an filter, the
> >> projects are gone
> >>
> >> select * from y where path = (select max(path) from y).
> >>
> >> The explain plan then looks like this:
> >>
> >> [EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):VARCHAR(1)
> >> CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"],
> >> expr#4=[CAST($t2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
> >> "ISO-8859-1$en_US$primary"], expr#5=[=($t3, $t4)], proj#0..1=[{exprs}],
> >> $condition=[$t5])
> >> EnumerableJoin(condition=[true], joinType=[left])
> >> EnumerableInterpreter
> >> BindableTableScan(table=[[ourSchema, ourTable]])
> >> EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])
> >> EnumerableInterpreter
> >> BindableTableScan(table=[[ourSchema, ourTable]])
> >> ]]
> >>
> >> Is there a way to alter the query so ProjectableFilterableTable can
> still
> >> be used? Or is the only way for getting this a translatable table?
> >>
> >> Thanks so far,
> >>
> >> Johannes
>
Re: Filters in Subselect
Posted by Johannes Schulte <jo...@gmail.com>.
Julian,
do you have some hints on how to debug this more fruitfully? I spent some
time to figure out whats happening and saw that the always-true
joinCondition is created in
org.apache.calcite.rel.rules.SubQueryRemoveRule.apply(RexSubQuery,
Set<CorrelationId>, Logic, RelBuilder, int, int)
when the rule is fired. The push of the project onto the table scan seems
to happen with the ProjectTableScanRule in the simple case. In the subquery
case, this rule is also fired but the successor doesn't seem to be picked
up by later rules (Cant find the rel#70 in any subsequent output)
2016-07-16 09:54:05,516 [main] DEBUG - Transform to: rel#70 via
ProjectScanRule:interpreter
2016-07-16 09:54:05,517 [main] DEBUG - call#487 generated 1 successors:
[rel#70:BindableTableScan.BINDABLE.[](table=[ourSchema,
ourTable],projects=[0])]
2016-07-16 09:54:05,518 [main] DEBUG - PLANNER = org.apache.calcite.plan.
volcano.VolcanoPlanner@31d0e481; TICK = 35/33; PHASE = OPTIMIZE; COST =
{1883.767018598809 rows, 1402.02 cpu, 0.0 io}
2016-07-16 09:54:05,518 [main] DEBUG - Pop match: rule
[EnumerableInterpreterRule] rels
[rel#60:BindableTableScan.BINDABLE.[](table=[ourSchema,
ourTable])]
Is this related to some cost associated with the result?
Thanks,
Johannes
On Sat, Jul 16, 2016 at 10:00 AM, Atri Sharma <at...@apache.org> wrote:
> +1 on that
>
> On 16 Jul 2016 5:57 a.m., "Julian Hyde" <jh...@apache.org> wrote:
>
> > By the way, I'd like one day to be able to optimize
> >
> > select * from t
> > where y = (select max(y) from t)
> >
> > to
> >
> > select * from t order by y desc limit 1
> >
> > if y is unique and not null, or something a little more complex if it
> > is not unique. I don't know whether this optimization would help all
> > of the cases you are looking at. I logged
> > https://issues.apache.org/jira/browse/CALCITE-1317.
> >
> > Julian
> >
> > On Fri, Jul 15, 2016 at 5:04 PM, Julian Hyde <jh...@apache.org> wrote:
> > > Can you turn on tracing and post the final planner state? (If you
> > > like, log a JIRA case, and attach the output if it is large.)
> > >
> > > I would have expected there to be a Project immediately above the
> > > BindableTableScan. (Just as in the first query, there was almost
> > > certainly a Project above a TableScan, and then the projects were
> > > correctly pushed into the TableScan.)
> > >
> > > Why is that Project not present? Maybe it is present, but the cost is
> > > wrong. Or maybe the Project was not pushed through the Join (the fact
> > > that the join condition is [true] is a worrying sign).
> > >
> > > The planner state should help answer these questions.
> > >
> > > Julian
> > >
> > >
> > > On Fri, Jul 15, 2016 at 12:32 PM, Johannes Schulte
> > > <jo...@gmail.com> wrote:
> > >> Hi,
> > >>
> > >> I am trying to build something similar to drills way of querying
> > directly
> > >> on multiple files, without the columnar layer. I am using the
> > >> ProjectableFilterableTable to filter out files not part of the query.
> > No i
> > >> got some problems and I hope i can express what I want to say:
> > >>
> > >>
> > >> select max(path) from y
> > >> allows for only checking the path column since it is projected
> > >>
> > >> [EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])
> > >> EnumerableInterpreter
> > >> BindableTableScan(table=[[ourSchema, ourTable]], projects=[[0]])
> > >> ]
> > >>
> > >> but when trying to query the maximum path and using it as an filter,
> the
> > >> projects are gone
> > >>
> > >> select * from y where path = (select max(path) from y).
> > >>
> > >> The explain plan then looks like this:
> > >>
> > >> [EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):VARCHAR(1)
> > >> CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"],
> > >> expr#4=[CAST($t2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
> > >> "ISO-8859-1$en_US$primary"], expr#5=[=($t3, $t4)],
> proj#0..1=[{exprs}],
> > >> $condition=[$t5])
> > >> EnumerableJoin(condition=[true], joinType=[left])
> > >> EnumerableInterpreter
> > >> BindableTableScan(table=[[ourSchema, ourTable]])
> > >> EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])
> > >> EnumerableInterpreter
> > >> BindableTableScan(table=[[ourSchema, ourTable]])
> > >> ]]
> > >>
> > >> Is there a way to alter the query so ProjectableFilterableTable can
> > still
> > >> be used? Or is the only way for getting this a translatable table?
> > >>
> > >> Thanks so far,
> > >>
> > >> Johannes
> >
>
Re: Filters in Subselect
Posted by Atri Sharma <at...@apache.org>.
+1 on that
On 16 Jul 2016 5:57 a.m., "Julian Hyde" <jh...@apache.org> wrote:
> By the way, I'd like one day to be able to optimize
>
> select * from t
> where y = (select max(y) from t)
>
> to
>
> select * from t order by y desc limit 1
>
> if y is unique and not null, or something a little more complex if it
> is not unique. I don't know whether this optimization would help all
> of the cases you are looking at. I logged
> https://issues.apache.org/jira/browse/CALCITE-1317.
>
> Julian
>
> On Fri, Jul 15, 2016 at 5:04 PM, Julian Hyde <jh...@apache.org> wrote:
> > Can you turn on tracing and post the final planner state? (If you
> > like, log a JIRA case, and attach the output if it is large.)
> >
> > I would have expected there to be a Project immediately above the
> > BindableTableScan. (Just as in the first query, there was almost
> > certainly a Project above a TableScan, and then the projects were
> > correctly pushed into the TableScan.)
> >
> > Why is that Project not present? Maybe it is present, but the cost is
> > wrong. Or maybe the Project was not pushed through the Join (the fact
> > that the join condition is [true] is a worrying sign).
> >
> > The planner state should help answer these questions.
> >
> > Julian
> >
> >
> > On Fri, Jul 15, 2016 at 12:32 PM, Johannes Schulte
> > <jo...@gmail.com> wrote:
> >> Hi,
> >>
> >> I am trying to build something similar to drills way of querying
> directly
> >> on multiple files, without the columnar layer. I am using the
> >> ProjectableFilterableTable to filter out files not part of the query.
> No i
> >> got some problems and I hope i can express what I want to say:
> >>
> >>
> >> select max(path) from y
> >> allows for only checking the path column since it is projected
> >>
> >> [EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])
> >> EnumerableInterpreter
> >> BindableTableScan(table=[[ourSchema, ourTable]], projects=[[0]])
> >> ]
> >>
> >> but when trying to query the maximum path and using it as an filter, the
> >> projects are gone
> >>
> >> select * from y where path = (select max(path) from y).
> >>
> >> The explain plan then looks like this:
> >>
> >> [EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):VARCHAR(1)
> >> CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"],
> >> expr#4=[CAST($t2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
> >> "ISO-8859-1$en_US$primary"], expr#5=[=($t3, $t4)], proj#0..1=[{exprs}],
> >> $condition=[$t5])
> >> EnumerableJoin(condition=[true], joinType=[left])
> >> EnumerableInterpreter
> >> BindableTableScan(table=[[ourSchema, ourTable]])
> >> EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])
> >> EnumerableInterpreter
> >> BindableTableScan(table=[[ourSchema, ourTable]])
> >> ]]
> >>
> >> Is there a way to alter the query so ProjectableFilterableTable can
> still
> >> be used? Or is the only way for getting this a translatable table?
> >>
> >> Thanks so far,
> >>
> >> Johannes
>
Re: Filters in Subselect
Posted by Julian Hyde <jh...@apache.org>.
By the way, I'd like one day to be able to optimize
select * from t
where y = (select max(y) from t)
to
select * from t order by y desc limit 1
if y is unique and not null, or something a little more complex if it
is not unique. I don't know whether this optimization would help all
of the cases you are looking at. I logged
https://issues.apache.org/jira/browse/CALCITE-1317.
Julian
On Fri, Jul 15, 2016 at 5:04 PM, Julian Hyde <jh...@apache.org> wrote:
> Can you turn on tracing and post the final planner state? (If you
> like, log a JIRA case, and attach the output if it is large.)
>
> I would have expected there to be a Project immediately above the
> BindableTableScan. (Just as in the first query, there was almost
> certainly a Project above a TableScan, and then the projects were
> correctly pushed into the TableScan.)
>
> Why is that Project not present? Maybe it is present, but the cost is
> wrong. Or maybe the Project was not pushed through the Join (the fact
> that the join condition is [true] is a worrying sign).
>
> The planner state should help answer these questions.
>
> Julian
>
>
> On Fri, Jul 15, 2016 at 12:32 PM, Johannes Schulte
> <jo...@gmail.com> wrote:
>> Hi,
>>
>> I am trying to build something similar to drills way of querying directly
>> on multiple files, without the columnar layer. I am using the
>> ProjectableFilterableTable to filter out files not part of the query. No i
>> got some problems and I hope i can express what I want to say:
>>
>>
>> select max(path) from y
>> allows for only checking the path column since it is projected
>>
>> [EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])
>> EnumerableInterpreter
>> BindableTableScan(table=[[ourSchema, ourTable]], projects=[[0]])
>> ]
>>
>> but when trying to query the maximum path and using it as an filter, the
>> projects are gone
>>
>> select * from y where path = (select max(path) from y).
>>
>> The explain plan then looks like this:
>>
>> [EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):VARCHAR(1)
>> CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"],
>> expr#4=[CAST($t2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
>> "ISO-8859-1$en_US$primary"], expr#5=[=($t3, $t4)], proj#0..1=[{exprs}],
>> $condition=[$t5])
>> EnumerableJoin(condition=[true], joinType=[left])
>> EnumerableInterpreter
>> BindableTableScan(table=[[ourSchema, ourTable]])
>> EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])
>> EnumerableInterpreter
>> BindableTableScan(table=[[ourSchema, ourTable]])
>> ]]
>>
>> Is there a way to alter the query so ProjectableFilterableTable can still
>> be used? Or is the only way for getting this a translatable table?
>>
>> Thanks so far,
>>
>> Johannes
Re: Filters in Subselect
Posted by Julian Hyde <jh...@apache.org>.
Can you turn on tracing and post the final planner state? (If you
like, log a JIRA case, and attach the output if it is large.)
I would have expected there to be a Project immediately above the
BindableTableScan. (Just as in the first query, there was almost
certainly a Project above a TableScan, and then the projects were
correctly pushed into the TableScan.)
Why is that Project not present? Maybe it is present, but the cost is
wrong. Or maybe the Project was not pushed through the Join (the fact
that the join condition is [true] is a worrying sign).
The planner state should help answer these questions.
Julian
On Fri, Jul 15, 2016 at 12:32 PM, Johannes Schulte
<jo...@gmail.com> wrote:
> Hi,
>
> I am trying to build something similar to drills way of querying directly
> on multiple files, without the columnar layer. I am using the
> ProjectableFilterableTable to filter out files not part of the query. No i
> got some problems and I hope i can express what I want to say:
>
>
> select max(path) from y
> allows for only checking the path column since it is projected
>
> [EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])
> EnumerableInterpreter
> BindableTableScan(table=[[ourSchema, ourTable]], projects=[[0]])
> ]
>
> but when trying to query the maximum path and using it as an filter, the
> projects are gone
>
> select * from y where path = (select max(path) from y).
>
> The explain plan then looks like this:
>
> [EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):VARCHAR(1)
> CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"],
> expr#4=[CAST($t2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
> "ISO-8859-1$en_US$primary"], expr#5=[=($t3, $t4)], proj#0..1=[{exprs}],
> $condition=[$t5])
> EnumerableJoin(condition=[true], joinType=[left])
> EnumerableInterpreter
> BindableTableScan(table=[[ourSchema, ourTable]])
> EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])
> EnumerableInterpreter
> BindableTableScan(table=[[ourSchema, ourTable]])
> ]]
>
> Is there a way to alter the query so ProjectableFilterableTable can still
> be used? Or is the only way for getting this a translatable table?
>
> Thanks so far,
>
> Johannes