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?
--
Ø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.)
