[jira] [Commented] (PHOENIX-2198) Support correlate variable

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

    [ https://issues.apache.org/jira/browse/PHOENIX-2198?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14712431#comment-14712431 ] 

Maryann Xue commented on PHOENIX-2198:
--------------------------------------

An example of a correlated query that cannot be de-correlated is:
SELECT s.student_id, t.score FROM score_table s, UNNEST((SELECT scores FROM score_table s2 where s.student_id = s2.student_id)) AS t(score)

A CorrelatePlan basically performs the following steps:
1. Get next tuple from the outer plan.
2. Evaluate the correlate expression(s) based on the tuple from step 1, and set the correlate variable(s).
3. Restart the inner plan execution (by calling QueryPlan.iterator()).
4. Join the tuple from step 1 with the result (all tuples) from step 3.
5. Repeat all above steps until hitting the end of outer plan.

Aside from implementing the CorrelatePlan, we'll need to make a few other changes:
1. To have a runtime context for writing/reading correlate variables. It should be independent of compilation context.
2. To have a CorrelateVariableExpression[correlation_id, variable_id] which retrieves the value of the corresponding correlate variable from the runtime context for 1) evaluation; 2) serialize/deserialize.
3. Make sure that all existing QueryPlans follow a convention that: 1) a new iterator is always created with an initial state (e.g. UnnestArrayPlan should reset "index"); 2) all expression serialization happens during iterator creation, otherwise the dynamic values will be lost or out of date.
4. To have an extension of ScanPlan (called DynamicScanPlan maybe) which re-compiles the filter before execution. This will benefit cases where the correlation condition contains a key expression (like in the example). We can use the same technique used to test dynamic join key optimization to see if we need to use this plan.

> Support correlate variable
> --------------------------
>
>                 Key: PHOENIX-2198
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-2198
>             Project: Phoenix
>          Issue Type: New Feature
>            Reporter: Maryann Xue
>            Assignee: Maryann Xue
>   Original Estimate: 240h
>  Remaining Estimate: 240h
>
> This will enable the outer query to set a correlate variable as a parameter to restart the inner query for each iteration.



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