Before answering to the question 1, let's look into source codes related
with plan generation. Because the XASL is derived from the plan, exactly
"QO_PLAN" structure, it is necessary to understand how the plan is generated
from the given query. The plan generation can be divided into three parts;
finding possible join paths, finding a optimal path, and converting the plan
to the XASL.
The plan generation is started from the function of "parser_generate_xasl()".
Then, the next function that we should look into is
"parser_generate_xasl_proc()". It calls "pt_plan_query()" that takes a parse
tree and returns a XASL. The result XASL is stored into the root node of the
parse tree; "node->info.query.xasl = xasl".
The "pt_plan_query()" function calls the "qo_optimize_query()" function to get
the optimized query plan, which is represented by the structyre "QO_PLAN".
After getting QO_PLAN from the PT_NODE, it calls "pt_to_buildvalue_proc()" or
"pt_to_buildlist_proc()". You already know the diffrence between BUILDLIST and
BUILDVALUE.
Inside "qo_optimize_query_helper()", you can find that it first builds join
graphs which are candidate for the query plan and then finds argumented terms
from the several parts of the query string such as ON clause for outer join
and WHERE clause. It discovers information about the indexes and partitions
that are related with the tables in the query. Finally, it calls
"qo_planner_search()" to find the optimal(not best) query plan from the
candidates join graphs by computing costs.
Let's return to the "pt_to_buildlist_proc()". At first, it starts from
allocating new XASL node; "xasl = regu_xasl_node_alloc (BUILDLIST_PROC)".
It does some group by related execution but they are not our interests at this
time. The one thing that you have to pay an attention is "pt_to_outlist()"
function. It generates "xasl->outptr_list", which is the structure of the final
query result file(=list file). In the other words, it is column list of the
result set.
We can find that there is "pt_set_aptr()" function, which makes the XASL tree
for the uncorrelated subquery. Do you know what means "uncorrelated subquery"?
An example is;
SELECT * FROM hundred
WHERE col_key = (SELECT col_key FROM uniques WHERE col_int=0);
The function "pt_gen_optimized_plan()" generates the XASL tree from the QO_PLAN.
If the function returns NULL, it means that the XASL generation is failed by
some reason, for example, memory allocation so that it will make a trivial
execution plan by calling "pt_get_simple_plan()".
By returning to "qo_to_xasl()", you can find that "gen_outer()" is the core
function of the plan generation. It traverse QO_PLAN structure, which is a kind
of join graph, and generates the corresponding XASL tree and the related
sub-XASL trees. You can find that there is "pt_set_dptr()" function below the
"gen_outer()". What is the role of 'dptr' in the XASL?
As you can see in the definition of "struct xasl_node", there are some pointers
such as "aptr_list", "bptr_list", "dptr_list", "eptr_list", "fptr_list",
and "scan_ptr".
"aptr" is related with 'uncorrelated subquery', "bptr" and "fptr" are related
with "OBJFETcH_PROC" which is generated from the path expression. "dptr" is
related with 'correlated subquery' and "scan_ptr" is the actual scan of data.
An example of correlated subquery is;
SELECT * FROM hundred h
WHERE col_key > (SELECT col_key FROM tenpct t WHERE t.col_int = h.col_int);
In the "gen_outer()" function, you can find source codes that use
"plan_un.join.join_terms", "plan_un.join.during_join_terms", and
"plan_un.join.after_join_terms". Now we can know that when those terms are
used. Here is a hint for your question.
'Join terms' are divided into two categories; 'during join terms' and 'after
join terms'. These terms is used for outer-join.
CUBRID implements the outer-join functionality by changing the outer-join query
to the equivalent inner-join query with some 'during join term' and
'after join terms'.
Let's follow the references of "after_join_pred". It came from
"after_join_terms". Let's see the "qo_join_new()" function. You can reach to
this function by following "qo_planner_search()" function.
The function of "planner_visit_node()" is what you have to look into. It visits
a node in the join graph then make possible join plans. You can find in the
middle of the function the code block surrounding "bitset_add (&afj_terms, i);".
Now what we have to see is where "QO_TC_DURING_JOIN" and "QO_TC_AFTER_JOIN" are
set.
An example for 'during join term' is simple;
SELECT * FROM tenpct t LEFT OUTER JOIN hundred h
ON t.col_key=h.col_key AND t.col_int>0;
It is hard to find a good example for 'after join term'. Anyway, you can an
example;
SELECt * FROM tenpct t LEFT OUTER JOIN hunred h ON t.col_key=h.col_key
RIGHT LEFT JOIN uniques u ON u.col_key=0
WHERE t.col_int>0;
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Before answering to the question 1, let's look into source codes related
with plan generation. Because the XASL is derived from the plan, exactly
"QO_PLAN" structure, it is necessary to understand how the plan is generated
from the given query. The plan generation can be divided into three parts;
finding possible join paths, finding a optimal path, and converting the plan
to the XASL.
The plan generation is started from the function of "parser_generate_xasl()".
Then, the next function that we should look into is
"parser_generate_xasl_proc()". It calls "pt_plan_query()" that takes a parse
tree and returns a XASL. The result XASL is stored into the root node of the
parse tree; "node->info.query.xasl = xasl".
The "pt_plan_query()" function calls the "qo_optimize_query()" function to get
the optimized query plan, which is represented by the structyre "QO_PLAN".
After getting QO_PLAN from the PT_NODE, it calls "pt_to_buildvalue_proc()" or
"pt_to_buildlist_proc()". You already know the diffrence between BUILDLIST and
BUILDVALUE.
Inside "qo_optimize_query_helper()", you can find that it first builds join
graphs which are candidate for the query plan and then finds argumented terms
from the several parts of the query string such as ON clause for outer join
and WHERE clause. It discovers information about the indexes and partitions
that are related with the tables in the query. Finally, it calls
"qo_planner_search()" to find the optimal(not best) query plan from the
candidates join graphs by computing costs.
Let's return to the "pt_to_buildlist_proc()". At first, it starts from
allocating new XASL node; "xasl = regu_xasl_node_alloc (BUILDLIST_PROC)".
It does some group by related execution but they are not our interests at this
time. The one thing that you have to pay an attention is "pt_to_outlist()"
function. It generates "xasl->outptr_list", which is the structure of the final
query result file(=list file). In the other words, it is column list of the
result set.
We can find that there is "pt_set_aptr()" function, which makes the XASL tree
for the uncorrelated subquery. Do you know what means "uncorrelated subquery"?
An example is;
SELECT * FROM hundred
WHERE col_key = (SELECT col_key FROM uniques WHERE col_int=0);
The function "pt_gen_optimized_plan()" generates the XASL tree from the QO_PLAN.
If the function returns NULL, it means that the XASL generation is failed by
some reason, for example, memory allocation so that it will make a trivial
execution plan by calling "pt_get_simple_plan()".
By returning to "qo_to_xasl()", you can find that "gen_outer()" is the core
function of the plan generation. It traverse QO_PLAN structure, which is a kind
of join graph, and generates the corresponding XASL tree and the related
sub-XASL trees. You can find that there is "pt_set_dptr()" function below the
"gen_outer()". What is the role of 'dptr' in the XASL?
As you can see in the definition of "struct xasl_node", there are some pointers
such as "aptr_list", "bptr_list", "dptr_list", "eptr_list", "fptr_list",
and "scan_ptr".
"aptr" is related with 'uncorrelated subquery', "bptr" and "fptr" are related
with "OBJFETcH_PROC" which is generated from the path expression. "dptr" is
related with 'correlated subquery' and "scan_ptr" is the actual scan of data.
An example of correlated subquery is;
SELECT * FROM hundred h
WHERE col_key > (SELECT col_key FROM tenpct t WHERE t.col_int = h.col_int);
In the "gen_outer()" function, you can find source codes that use
"plan_un.join.join_terms", "plan_un.join.during_join_terms", and
"plan_un.join.after_join_terms". Now we can know that when those terms are
used. Here is a hint for your question.
'Join terms' are divided into two categories; 'during join terms' and 'after
join terms'. These terms is used for outer-join.
CUBRID implements the outer-join functionality by changing the outer-join query
to the equivalent inner-join query with some 'during join term' and
'after join terms'.
Let's follow the references of "after_join_pred". It came from
"after_join_terms". Let's see the "qo_join_new()" function. You can reach to
this function by following "qo_planner_search()" function.
The function of "planner_visit_node()" is what you have to look into. It visits
a node in the join graph then make possible join plans. You can find in the
middle of the function the code block surrounding "bitset_add (&afj_terms, i);".
Now what we have to see is where "QO_TC_DURING_JOIN" and "QO_TC_AFTER_JOIN" are
set.
An example for 'during join term' is simple;
SELECT * FROM tenpct t LEFT OUTER JOIN hundred h
ON t.col_key=h.col_key AND t.col_int>0;
It is hard to find a good example for 'after join term'. Anyway, you can an
example;
SELECt * FROM tenpct t LEFT OUTER JOIN hunred h ON t.col_key=h.col_key
RIGHT LEFT JOIN uniques u ON u.col_key=0
WHERE t.col_int>0;