Hi Guilhem, Øystein,
a couple of followup comments below.
Guilhem Bichot wrote:
> Hello Oystein,
>
> Oystein.Grovlen@stripped a écrit, Le 12.03.2010 18:22:
>> #At file:///home/oysteing/mysql/mysql-6.0-codebase-bugfixing-2/ based
>> on revid:luis.soares@stripped
>>
>> 3819 oystein.grovlen@stripped 2010-03-12
>> Bug#48213 Materialized subselect crashes if using GEOMETRY type
>> The problem occurred because during semi-join a
>> materialized table
>> was created which contained a GEOMETRY column, which is a
>> specialized
>> BLOB column. This caused an segmentation fault because such
>> tables will
>> have extra columns, and the semi-join code was not prepared for
>> that.
>> The solution is to disable materialization when
>> Blob/Geometry columns would need to be materialized. Blob
>> columns cannot be used for index look-up anyway, so it does not
>> makes sense to use materialization.
>
> Semijoin materialization can also be "materialization scan": in that
> case, we materialize the "inner table" part, put it first in the query
> execution plan, put the outer table second, and when we execute, we scan
> the materialized table, and for each row, look for a match in the outer
> table.
> I think the patch would prevent this materialization scan strategy to be
> used with BLOB/GEOMETRY, and I wonder if this isn't an excessive
> limitation (there is not necessarily an index lookup in this case?).
> I am not sure of this, but believe it should be investigated.
I agree with Guilhem that it should be investigated, but I do not think that it
is essential that we support optimized handling of BLOBs in subquery comparisons.
>
>> This fix implies that it is detected earlier that subquery
>> materialization
>> can not be used.
>
> For my education: without the fix, which code line detected (later) that
> subquery materialization cannot be used?
Please add to the commit records more details about this early detection, and
why it is actually an improvement over original code.
>
>> The result of that is that in->exist optimization may
>> be performed for such queries. Hence, extended query plans for
>> such
>> queries had to be updated.
>> @ mysql-test/r/subselect_mat.result
>> Update extended query plan for subqueries that cannot use
>> materialization
>> due to Blobs.
>> @ mysql-test/r/subselect_sj.result
>> Updated result file.
>> @ mysql-test/r/subselect_sj_jcl6.result
>> Update result file.
>> @ mysql-test/t/subselect_sj.test
>> Add test case for Bug#48213 that verifies that semi-join works
>> when subquery select list contain Blob columns. Also verify that
>> materialization is not
>> used.
>> @ sql/sql_select.cc
>> Disable materialization for semi-join/subqueries when the
>> subquery select list
>> contain Blob columns.
>>
>> modified:
>> mysql-test/r/subselect_mat.result
>> mysql-test/r/subselect_sj.result
>> mysql-test/r/subselect_sj_jcl6.result
>> mysql-test/t/subselect_sj.test
>> sql/sql_select.cc
>
>
Thanks,
Roy
