Hello Oystein,
Øystein Grøvlen a écrit, Le 15.03.2010 14:40:
> 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.
>
> You are right that it prevents materialization scan alternative for BLOB
> /GEOMETRY. My reasoning for not trying to fix this was that AFAIK there
> is currently not a mechanism for allowing scan, but not look-up. There
> is for the other way around; look-up, but not scan. I guess I could add
> a similar field like Item_in_subselect::sjm_scan_allowed.
>
> However, my reasoning was that joining on blob_column is probably rare
> and that the next days are better spent fixing another bug than fixing
> this. If you disagree, I will spend some more time figuring out how to
> enable materialization scan for BLOBs.
I am a bit anxious to disable a possible optimization strategy
(materialization scan with blobs).
I suggest spending a few hours seeing if it is possible/difficult to
avoid this, and then we discuss again with your findings.
>>> 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?
>
> In subselect_hash_sj_engine::init_permanent(). If there are BLOB
> columns, create_result_table will fail to create a key to be used for
> look-up. However, I see now that in=>exists transformation is done
> anyway on return to Item_in_subselect::setup_engine(). I guess the late
> detection only affects whether it is reflected in the extended QEP.
Which may be a candidate for a bug report then - extended QEP is
expected to reflect the final decisions, not some intermediate decision
abandoned later.
