Bug #48213 Materialized subselect crashes if using GEOMETRY type
Submitted: 21 Oct 2009 19:29 Modified: 23 Nov 2010 3:03
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0 OS:Linux
Assigned to: Øystein Grøvlen CPU Architecture:Any
Tags: materialization, optimizer_switch

[21 Oct 2009 19:29] Guilhem Bichot
Description:
Tested with 6.0-codebase-bugfixing.
This is a testcase derived from the one of BUG#46548, but is a different bug (patch of BUG#46548 does not help here).

Crash:
#2  0x00000000006f6ad6 in handle_segfault (sig=11) at mysqld.cc:2754
#3  <signal handler called>
#4  0x00000000006a5692 in store_key_item::copy_inner (this=0x2608338) at sql_select.h:1930
#5  0x00000000006a5622 in store_key::copy (this=0x2608338) at sql_select.h:1861
#6  0x000000000077aad3 in cp_buffer_from_ref (thd=0x2441190, table=0x25f4fb8, ref=0x2607db8) at sql_select.cc:19576
#7  0x0000000000784a55 in cmp_buffer_with_ref (thd=0x2441190, table=0x25f4fb8, tab_ref=0x2607db8) at sql_select.cc:19558
#8  0x0000000000786fa0 in join_read_key2 (tab=0x25f3a28, table=0x25f4fb8, table_ref=0x2607db8) at sql_select.cc:16908
#9  0x000000000078a683 in sub_select_sjm (join=0x25e3108, join_tab=0x25f3a28, end_of_records=false) at sql_select.cc:16030
#10 0x0000000000789ce2 in evaluate_join_record (join=0x25e3108, join_tab=0x25f3788, error=0) at sql_select.cc:16554
#11 0x0000000000789fbd in sub_select (join=0x25e3108, join_tab=0x25f3788, end_of_records=false) at sql_select.cc:16289
#12 0x000000000079802e in do_select (join=0x25e3108, fields=0x24432b0, table=0x0, procedure=0x0) at sql_select.cc:15847
#13 0x00000000007b2821 in JOIN::exec (this=0x25e3108) at sql_select.cc:2906
#14 0x00000000007acf5c in mysql_select (thd=0x2441190, rref_pointer_array=0x24433b8, tables=0x24895d8, wild_num=0, fields=@0x24432b0, conds=0x2609e18, og_num=0, order=0x0, group=0x0, 
    having=0x0, proc_param=0x0, select_options=2147764736, result=0x25e30b8, unit=0x2442b00, select_lex=0x24431a0) at sql_select.cc:3097
#15 0x00000000007b2b5a in handle_select (thd=0x2441190, lex=0x2442a60, result=0x25e30b8, setup_tables_done_option=0) at sql_select.cc:307
#16 0x0000000000707e2b in execute_sqlcom_select (thd=0x2441190, all_tables=0x24895d8) at sql_parse.cc:4976

If materialization is turned off, no crash, good results.

How to repeat:
Using debug binary.

CREATE TABLE t1 (
  pk int,
  a varchar(1),
  b varchar(4),
  c varchar(4),
  d varchar(4),
  e geometry,
  PRIMARY KEY (pk)
);
INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo',GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')),(2,'f','ffff','ffff','ffff',GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));

CREATE TABLE t2 LIKE t1;
INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii',GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')),(2,'f','ffff','ffff','ffff',GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))'));

SET @@optimizer_switch='default,semijoin=on,materialization=on';
EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
SELECT pk FROM t1 WHERE (b,c,d,e) IN (SELECT b,c,d,e FROM t2 WHERE pk > 0);
DROP TABLE t1, t2;
[22 Oct 2009 1:28] MySQL Verification Team
Thank you for the bug report.

091021 23:25:05 [Note] 6.0bf/libexec/mysqld: ready for connections.
Version: '6.0.14-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
091021 23:27:06 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338486 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x3c63ae8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f37269f80c8 thread_stack 0x40000
6.0bf/libexec/mysqld(my_print_stacktrace+0x35) [0xbbaae3]
6.0bf/libexec/mysqld(handle_segfault+0x2a7) [0x6fd462]
/lib/libpthread.so.0 [0x7f3733b0a190]
6.0bf/libexec/mysqld(store_key_item::copy_inner()+0x68) [0x6ad636]
6.0bf/libexec/mysqld(store_key::copy()+0x81) [0x6ad511]
6.0bf/libexec/mysqld(cp_buffer_from_ref(THD*, TABLE*, st_table_ref*)+0x6e) [0x7b798c]
6.0bf/libexec/mysqld [0x7b78c5]
6.0bf/libexec/mysqld [0x7b05d7]
6.0bf/libexec/mysqld(sub_select_sjm(JOIN*, st_join_table*, bool)+0x3ec) [0x7aec66]
6.0bf/libexec/mysqld [0x7af8c2]
6.0bf/libexec/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x1cb) [0x7af06a]
6.0bf/libexec/mysqld [0x7ae5de]
6.0bf/libexec/mysqld(JOIN::exec()+0x25f8) [0x78e616]
6.0bf/libexec/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x31d) [0x78ed3f]
6.0bf/libexec/mysqld(handle_select(THD*, LEX*, select_result*, unsigned long)+0x1b0) [0x785c5f]
6.0bf/libexec/mysqld [0x718fa0]
6.0bf/libexec/mysqld(mysql_execute_command(THD*)+0xa0a) [0x7105ea]
6.0bf/libexec/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x293) [0x71b3f9]
6.0bf/libexec/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xb14) [0x70dd62]
6.0bf/libexec/mysqld(do_command(THD*)+0x25e) [0x70cfb7]
6.0bf/libexec/mysqld(handle_one_connection+0x14e) [0x70b625]
/lib/libpthread.so.0 [0x7f3733b01a04]
/lib/libc.so.6(clone+0x6d) [0x7f3732a697bd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x3ceaed0 = SELECT pk FROM t1 WHERE (b,c,d,e) IN (SELECT b,c,d,e FROM t2 WHERE pk > 0)
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
miguel@laras:~/dbs$
[22 Oct 2009 10:09] Øystein Grøvlen
I'll take this since it is related to BUG#46548.
[20 Nov 2009 10:28] Øystein Grøvlen
The same issue occurs if the geometry column is replaced with a BLOB column.
In other words, this seem to be a general issue for BLOBS.
I have noticed a few things when debugging so far:

1. The temporary table for materialization will be MyIsam instead Memory when 
   BLOBS are involved.

2. If the BLOB is replaced by a VARCHAR, there are the key has 4 parts.  (As I 
   would expect).  For BLOBs there are 5 parts.

3. The segmentation occur when copying the 5th field item of the key.  There 
   seem to be only four valid items.
[23 Nov 2009 8:22] Guilhem Bichot
Hello Oystein; just to confirm what you wrote:
- GEOMETRY uses BLOB internally
- MEMORY tables cannot have BLOB fields, which is probably why the table is then MyISAM.
[12 Mar 2010 17:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103116

3819 oystein.grovlen@sun.com	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.
      
      This fix implies that it is detected earlier that subquery materialization
      can not be used.  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.
[22 Mar 2010 14:04] Øystein Grøvlen
Will investigate further whether it is possible to still allow join on blob
columns for semi-join scan materialization.  (Ref. Guilhem's review)

Will consider suggestions received on IRC from Timour on how to adjust my change
so that it also addresses subquery materialization.

Will wait with this until WL#5266 has been pushed since it contains some 
refactoring of the methods I am changing.
[29 Aug 2010 6:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117077

3232 oystein.grovlen@sun.com	2010-08-29
      Bug#48213 Materialized subselect crashes if using GEOMETRY type
            
      The problem occurred because during execution of MaterializationLookup
      strategy for semi-join, a materialized table was created which
      contained a GEOMETRY column, 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 MaterializationLookup strategy 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 MaterializationLookup.
      
      This fix also implies that it is detected earlier that subquery
      materialization can not be used either.  This means that code to
      revert the decision to use subquery materialization is no longer
      needed, and this has been removed.
      
      In addition to adding a new test case for this bug, some existing
      tests for subqueries involving BLOB columns have been moved from
      subquery_mat.inc to subquery_sj.inc.  This way, these tests will also
      be run for more cases involving semijoin materialization.  Also, the
      extended query plans for many of these tests had to be updated since
      it is now reflected that in->exist optimization will be performed for
      the involved queries.
     @ mysql-test/include/subquery_mat.inc
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/include/subquery_sj.inc
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_mat.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_mat_all.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_mat_none.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_all.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_all_jcl6.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_all_jcl7.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_dupsweed.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_dupsweed_jcl6.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_dupsweed_jcl7.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_firstmatch.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_firstmatch_jcl6.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_firstmatch_jcl7.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_loosescan.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_loosescan_jcl6.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_loosescan_jcl7.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_mat.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_mat_jcl6.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_mat_jcl7.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_mat_nosj.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.  Some
        extended query plans for the moved tests had to be updated since
        it is now reflected that in->exist optimization will be performed for
        the involved queries.
     @ mysql-test/r/subquery_sj_none.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_none_jcl6.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_none_jcl7.result
        Added new test case for Bug#48213
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ sql/item.cc
        Added method Item::allows_materialization_lookup() that checks if this item 
        allows MaterializationLookup strategy to be used for semijoin. That is,
        if BLOB type is involved, MaterializationLookup cannot be used.
     @ sql/item.h
        Added method Item::allows_materialization_lookup() that checks if this item 
        allows MaterializationLookup strategy to be used for semijoin.
     @ sql/item_subselect.cc
        Removed code to revert the decision to use subquery materialization.
        It will now be detected earlier that subquery materialization is not possible,
        so this code is no longer needed.
     @ sql/sql_select.cc
        Added check for whether column types of subquery allow index to be created
        on materialized table.  If not, subquery materialization and
        MaterializationLookup strategy for semijoin cannot be used.
     @ sql/table.h
        Added Semijoin_mat_optimize::lookup_allowed to record whether it is possible
        to use MaterializationLookup strategy for this semijoin materialization.
[7 Sep 2010 11:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117700

3232 oystein.grovlen@sun.com	2010-09-07
      Bug#48213 Materialized subselect crashes if using GEOMETRY type
            
      The problem occurred because during execution of MaterializeLookup
      strategy for semi-join, a materialized table was created which
      contained a GEOMETRY column, a specialized BLOB column.  This caused
      an segmentation fault because such tables will have extra columns to record
      NULL information for the BLOB column, and the semi-join code was not 
      prepared for that.
      
      The solution is to disable MaterializeLookup strategy 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 MaterializeLookup.
      
      This fix also implies that it is detected earlier that subquery
      materialization can not be used either.  This means that code to
      revert the decision to use subquery materialization is no longer
      needed, and this has been removed.
     @ mysql-test/include/subquery_sj.inc
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_mat.result
        Extended query plans have been updated since it is now reflected that in->exist
        optimization will be performed for the involved queries.
     @ mysql-test/r/subquery_mat_all.result
        Extended query plan has been updated since it is now reflected that in->exist
        optimization will be performed for the involved queries.
     @ mysql-test/r/subquery_sj_all.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_all_jcl6.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_all_jcl7.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_dupsweed.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_dupsweed_jcl6.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_dupsweed_jcl7.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_firstmatch.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_firstmatch_jcl6.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_firstmatch_jcl7.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_loosescan.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_loosescan_jcl6.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_loosescan_jcl7.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_mat.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_mat_jcl6.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_mat_jcl7.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_mat_nosj.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_none.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_none_jcl6.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_none_jcl7.result
        Added new test case for Bug#48213
     @ sql/item.cc
        Added method Item::is_blob_field() that checks if this item will be
        represented by a Blob field.  Use to check if MaterializeLookup strategy
        can be used for semijoin. That is, if BLOB type is involved, 
        MaterializeLookup cannot be used.
     @ sql/item.h
        Added method Item::is_blob_field() that checks if this item will be
        represented by a Blob field.
     @ sql/item_subselect.cc
        Removed code to revert the decision to use subquery materialization.
        It will now be detected earlier that subquery materialization is not possible,
        so this code is no longer needed.
        Did some further renaming/reorganization to make this code a bit simpler.
     @ sql/sql_select.cc
        Added check for whether column types of subquery allow index to be created
        on materialized table.  That is, no BLOB/GEOMETRY columns should be involved.
        If not, subquery materialization and MaterializeLookup strategy for semijoin cannot be used.
     @ sql/table.h
        Added Semijoin_mat_optimize::lookup_allowed to record whether it is possible
        to use MaterializationLookup strategy for this semijoin materialization.
[7 Sep 2010 12:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117702

3233 oystein.grovlen@sun.com	2010-09-07
      Bug#48213 - Materialized subselect crashes if using GEOMETRY type
      
      Addendum: Move existing tests for subqueries involving BLOB columns from
      subquery_mat.inc to subquery_sj.inc.  This way, these tests will also
      be run for more cases involving semijoin materialization.
     @ mysql-test/include/subquery_mat.inc
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/include/subquery_sj.inc
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_mat.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_mat_all.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_mat_none.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_all.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_all_jcl6.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_all_jcl7.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_dupsweed.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_dupsweed_jcl6.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_dupsweed_jcl7.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_firstmatch.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_firstmatch_jcl6.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_firstmatch_jcl7.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_loosescan.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_loosescan_jcl6.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_loosescan_jcl7.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_mat.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_mat_jcl6.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_mat_jcl7.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_mat_nosj.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_none.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_none_jcl6.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_none_jcl7.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
[7 Sep 2010 19:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117727

3236 oystein.grovlen@sun.com	2010-09-07
      Bug#48213 Materialized subselect crashes if using GEOMETRY type
            
      The problem occurred because during execution of MaterializeLookup
      strategy for semi-join, a materialized table was created which
      contained a GEOMETRY column, a specialized BLOB column.  This caused
      an segmentation fault because such tables will have extra columns to record
      NULL information for the BLOB column, and the semi-join code was not 
      prepared for that.
      
      The solution is to disable MaterializeLookup strategy 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 MaterializeLookup.
      
      This fix also implies that it is detected earlier that subquery
      materialization can not be used either.  This means that code to
      revert the decision to use subquery materialization is no longer
      needed, and this has been removed.
     @ mysql-test/include/subquery_sj.inc
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_mat.result
        Extended query plans have been updated since it is now reflected that 
        in-to-exist transformation will be performed for the involved queries.
     @ mysql-test/r/subquery_mat_all.result
        Extended query plan has been updated since it is now reflected that in->exist
        optimization will be performed for the involved queries.
     @ mysql-test/r/subquery_sj_all.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_all_jcl6.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_all_jcl7.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_dupsweed.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_dupsweed_jcl6.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_dupsweed_jcl7.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_firstmatch.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_firstmatch_jcl6.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_firstmatch_jcl7.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_loosescan.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_loosescan_jcl6.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_loosescan_jcl7.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_mat.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_mat_jcl6.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_mat_jcl7.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_mat_nosj.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_none.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_none_jcl6.result
        Added new test case for Bug#48213
     @ mysql-test/r/subquery_sj_none_jcl7.result
        Added new test case for Bug#48213
     @ sql/item.cc
        Added method Item::is_blob_field() that checks if this item will be
        represented by a Blob field.  Use to check if MaterializeLookup strategy
        can be used for semijoin. That is, if BLOB type is involved, 
        MaterializeLookup cannot be used.
     @ sql/item.h
        Added method Item::is_blob_field() that checks if this item will be
        represented by a Blob field.
     @ sql/item_subselect.cc
        Removed code to revert the decision to use subquery materialization.
        It will now be detected earlier that subquery materialization is not possible,
        so this code is no longer needed.
        Did some further renaming/reorganization to make this code a bit simpler.
     @ sql/sql_select.cc
        Added check for whether column types of subquery allow index to be created
        on materialized table.  That is, no BLOB/GEOMETRY columns should be involved.
        If not, subquery materialization and MaterializeLookup strategy for semijoin cannot be used.
     @ sql/table.h
        Added Semijoin_mat_optimize::lookup_allowed to record whether it is possible
        to use MaterializeLookup strategy for this semijoin materialization.
[7 Sep 2010 19:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117732

3237 oystein.grovlen@sun.com	2010-09-07
      Bug#48213 - Materialized subselect crashes if using GEOMETRY type
      
      Addendum: Move existing tests for subqueries involving BLOB columns from
      subquery_mat.inc to subquery_sj.inc.  This way, these tests will also
      be run for more cases involving semijoin materialization.
     @ mysql-test/include/subquery_mat.inc
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/include/subquery_sj.inc
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_mat.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_mat_all.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_mat_none.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_all.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_all_jcl6.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_all_jcl7.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_dupsweed.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_dupsweed_jcl6.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_dupsweed_jcl7.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_firstmatch.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_firstmatch_jcl6.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_firstmatch_jcl7.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_loosescan.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_loosescan_jcl6.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_loosescan_jcl7.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_mat.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_mat_jcl6.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_mat_jcl7.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_mat_nosj.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_none.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_none_jcl6.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
     @ mysql-test/r/subquery_sj_none_jcl7.result
        Moved tests for subqueries involving BLOB columns from subquery_mat.inc to
        subquery_sj.inc to get better coverage for semijoin materialization.
[7 Sep 2010 19:53] Øystein Grøvlen
Pushed to mysql-next-mr with revision id 
oystein.grovlen@sun.com-20100907190718-63besk8ht8f93e9n.
[14 Sep 2010 9:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/118157

3242 Oystein Grovlen	2010-09-14
      Bug#48213 - Materialized subselect crashes if using GEOMETRY type
      (Addendum #2) 
      Removed part of comment for Item_in_subselect::setup_engine()
      since it is no longer true after fix for Bug#48213.
     @ sql/item_subselect.cc
        Removed comment that is invalid after Bug#48213 was fixed.
[2 Oct 2010 18:15] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)
[13 Nov 2010 16:11] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[23 Nov 2010 3:03] Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.