Roy,
thank you for the patch. I agree to the logic of setting OUTER_REF_TABLE_BIT in
all selects that have subselects with outer dependencies. However, I wonder if
this is the correct place to fix the bug. See comment below.
> === modified file 'sql/item.cc'
> --- a/sql/item.cc 2010-11-14 18:09:32 +0000
> +++ b/sql/item.cc 2010-11-18 09:47:04 +0000
> @@ -2359,17 +2366,16 @@ void Item_field::fix_after_pullout(st_se
> */
> st_select_lex *child_select= context->select_lex;
>
> - if (child_select->outer_select() != depended_from)
> + while (child_select->outer_select() != depended_from)
> {
> /*
> The subquery on this level is outer-correlated with respect to the field
> */
> Item_subselect *subq_predicate= child_select->master_unit()->item;
> - subq_predicate->used_tables_cache|= OUTER_REF_TABLE_BIT;
> - }
>
> - while (child_select->outer_select() != depended_from)
> + subq_predicate->used_tables_cache|= OUTER_REF_TABLE_BIT;
> child_select= child_select->outer_select();
> + }
AFAIU, the problem in this bug was that SELECT C did not have ORTB set after
semijoin transformation but with the patch it does:
Before semijoin
---------------
A - t1 <- OUTER_REF_TABLE_BIT not set
\
B - t2 <- OUTER_REF_TABLE_BIT not set
\
C - t3 <- OUTER_REF_TABLE_BIT not set
\
D - t4 <- ORTB set, depended_from points to A
where t1.x=t4.y
After semijoining B into A
--------------------------
A - t1, t2 <- OUTER_REF_TABLE_BIT not set
\
C - t3 <- pre-patch: OUTER_REF_TABLE_BIT not set
\ post-patch: OUTER_REF_TABLE_BIT set
D - t4 <- ORTB set, depended_from points to A
where t1.x=t4.y
With the logic that SELECT C should have ORTB set because it contains a
subselect that has a dependency to ot1, wouldn't it be more correct to set this
bit both for SELECT B and C during the prepare stage rather than during semijoin
transformation?
--
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway
