[jira] [Created] (CALCITE-830) VolcanoPlanner may have inconsistent state and may not pick the cheapest plan if stats (e.g. row count) is not monotonic increasing with cost

["Maryann Xue (JIRA)" <ji...@apache.org>]

Maryann Xue created CALCITE-830:
-----------------------------------

             Summary: VolcanoPlanner may have inconsistent state and may not pick the cheapest plan if stats (e.g. row count) is not monotonic increasing with cost
                 Key: CALCITE-830
                 URL: https://issues.apache.org/jira/browse/CALCITE-830
             Project: Calcite
          Issue Type: Bug
            Reporter: Maryann Xue
            Assignee: Julian Hyde


An example can be:
select i.item_id, o.quantity from orderTable o join (select distinct item_id from itemTable) i on o.item_id = i.item_id
where item_id is the primary key of itemTable and thus is unique (distinct).

The AggregateRemoveRule will be applied for the subquery "select distinct item_id from itemTable", and generates a cheaper sub-tree equivalent to "select item_id from itemTable". However the original Rel sub-tree gives a smaller rowCount (it shouldn't have, but let's say it's an independent issue) than the new Rel sub-tree (after applying AggregateRemoveRule). By picking the new Rel sub-tree as the new best of the child subset, the parent RelNode "Join" now has a higher cost, but this change is not reflected in its own enclosing subset, which then causes inconsistency.

Please see the attached log:
line 27099 ~ 27100: 
{code}
         rel#221:Subset#5.PHOENIX_CLIENT.[0], best=rel#220, importance=0.4984472052723946
                 rel#220:PhoenixToClientConverter.PHOENIX_CLIENT.[0](input=rel#50:Subset#5.PHOENIX_SERVERJOIN.[0]), rowcount=525.0, cumulative cost={153.3897966465855 rows, 6      2.0 cpu, 0.0 io}
{code}
line 27092 ~ 27093:
{code}
         rel#50:Subset#5.PHOENIX_SERVERJOIN.[0], best=rel#49, importance=0.4984472052723946
                 rel#49:PhoenixServerJoin.PHOENIX_SERVERJOIN.[[0]](left=rel#47:Subset#1.PHOENIX_SERVER.[0],right=rel#48:Subset#4.PHOENIX_CLIENT.[],condition==($1, $3),joinTyp      e=inner), rowcount=525.0, cumulative cost={193.63272822430963 rows, 62.0 cpu, 0.0 io}
{code}

rel#220 has a lower cumulative cost than its input rel#50.




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)