Øystein Grøvlen wrote:
> Hi,
>
> I would like people's opinion on how to deal with table pull-out for
> queries like
> SELECT * FROM t1 where (1) in (SELECT * FROM t2 STRAIGHT_JOIN t3)
>
> Work log 3740 (http://forge.mysql.com/worklog/task.php?id=3740) contains
> a description of table pull-out, but STRAIGHT_JOIN is not discussed.
>
> The current 6.0 behavior seems to be that if table t2 is a so-called
> const table (i.e., has 0 or 1 row), it is pulled out. If t3 is const,
> however, it will not be pulled out, even when t2 is.
>
> From my point of view, one should try to pull out t3 if t2 is
> pulled-out, but not otherwise. Does anyone disagree?
No, that makes sense to me. :) It is also what the WL#3740 seems to
indicate is the correct behaviour:
// Action #2: Find which tables we can pull out based on
// update_ref_and_keys() data. Note that pulling one table out
// can allow us to pull out some other tables too.
do {
pulled_a_table= FALSE;
for each possible ref access to some inner table T
{
if (ref access is eq_ref &&
all used tables are outer tables)
{
pulled_a_table=TRUE;
pulled_tables.insert(T)
}
}
} while (pulled_a_table);
So, perhaps the current code has a bug in it somewhere?
Cheers!
Jay
> --
> Øystein
>
> PS:
> Note that due to bug#46692, this behavior cannot currently be explored
> with EXPLAIN since whenever t2 is const or t3 is empty, the above query
> will cause segmentation fault during optimization. As part of fixing
> this bug, I am trying to understand what is the correct behavior for
> different combinations of tables being const.
>
> There seems to be an assumption in optimize_semijoin_nests() that there
> will not be semi-join nests with only const tables. This indicates that
> when both t2 and t3 is const, both tables should be pulled out. (The
> current assertion in optimize_semijoin_nests() has no effect since
> tables that are not pulled out will not be marked as const in the first
> place.)
>
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe: http://lists.mysql.com/internals?unsub=1
>
