Crash in JOIN::optimize in maria-5.3

[Philip Stoev]

Not repeatable in maria-5.3

Backtrace:

#4 <signal handler called>
#5 0x08306051 in JOIN::optimize (this=0xae6845a0) at sql_select.cc:1094
#6 0x0830b1bc in mysql_select (thd=0x9a2cbc8, rref_pointer_array=0x9a2e668, tables=0xae6086d0, wild_num=0, fields=..., conds=0xae6096d8, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae60a3e0, unit=0x9a2e2c4, select_lex=0x9a2e564)
    at sql_select.cc:2667
#7 0x083039d9 in handle_select (thd=0x9a2cbc8, lex=0x9a2e268, result=0xae60a3e0, setup_tables_done_option=0) at sql_select.cc:283
#8 0x082a1477 in execute_sqlcom_select (thd=0x9a2cbc8, all_tables=0xae6086d0) at sql_parse.cc:5070
#9 0x08298486 in mysql_execute_command (thd=0x9a2cbc8) at sql_parse.cc:2234
#10 0x082a3a12 in mysql_parse (thd=0x9a2cbc8,
    rawbuf=0xae608490 "SELECT f2 FROM t3\nWHERE ( f1 , f3 ) IN (\nSELECT MIN( f3 ) , f3\nFROM t2\n) AND f3 = '0'\nAND ( f3 , f3 ) IN (\nSELECT f3 , COUNT( f2 )\nFROM t1\n)", length=141, found_semicolon=0xb6d4d228) at sql_parse.cc:6077
#11 0x0829611b in dispatch_command (command=COM_QUERY, thd=0x9a2cbc8,
    packet=0x9a44d89 "SELECT f2 FROM t3\nWHERE ( f1 , f3 ) IN (\nSELECT MIN( f3 ) , f3\nFROM t2\n) AND f3 = '0'\nAND ( f3 , f3 ) IN (\nSELECT f3 , COUNT( f2 )\nFROM t1\n)", packet_length=141) at sql_parse.cc:1210
#12 0x082955c8 in do_command (thd=0x9a2cbc8) at sql_parse.cc:903
#13 0x082926a6 in handle_one_connection (arg=0x9a2cbc8) at sql_connect.cc:1154
#14 0x00821919 in start_thread () from /lib/libpthread.so.0
#15 0x0076acce in clone () from /lib/libc.so.6

bzr version-info:

revision-id: <email address hidden>
date: 2011-02-20 11:35:26 +0300
build-date: 2011-02-24 14:18:39 +0200
revno: 2922
branch-nick: maria-5.3-mwl90

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 Using where
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 13 const,<subquery2>.f3 1 Using where
3 SUBQUERY t1 ALL NULL NULL NULL NULL 5
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table

test case:

CREATE TABLE t1 ( f2 int(11), f3 int(11)) ;
INSERT INTO t1 VALUES ('1','1'),('0','9'),('9','5'),(NULL,'6'),('4','2');

CREATE TABLE t2 ( f3 int(11)) ;

CREATE TABLE t3 ( f1 int(11), f2 int(11), f3 int(11)) ;
INSERT INTO t3 VALUES ('964','4','0'),('982','6','8'),('983','5','7'),('984','7','7'),('985','6','0');

SELECT f2 FROM t3
WHERE ( f1 , f3 ) IN (
        SELECT MIN( f3 ) , f3
        FROM t2
) AND f3 = '0'
AND ( f3 , f3 ) IN (
        SELECT f3 , COUNT( f2 )
        FROM t1
);

[Philip Stoev (pstoev-askmonty)]

The same crash was observed with maria-5.3 and maria-5.3-mwl128, but with a vastly different data set and query.

Unfortunately, it was not possible to extract an MTR test case. So, in order to repeat, mount the attached datadir and execute;

SELECT n_nationkey FROM nation JOIN supplier JOIN partsupp ON s_suppkey = ps_suppkey JOIN lineitem ON ps_partkey = l_partkey AND ps_suppkey = l_suppkey JOIN orders JOIN customer ON o_custkey = c_custkey WHERE ps_partkey = 199;

If the datadir is dumped and loaded fresh, the query stops crashing and starts reporting "Impossible where", which is due to ps_partkey not having a value of 199.

[Sergey Petrunia (sergefp)]

Relevant columns from EXPLAIN:

explain SELECT n_nationkey FROM nation JOIN supplier JOIN partsupp ON s_suppkey = ps_suppkey JOIN lineitem ON ps_partkey = l_partkey AND ps_suppkey = l_suppkey JOIN orders JOIN customer ON o_custkey = c_custkey WHERE ps_partkey = 199;
-+----------+--------+--+---------------------+---------+-------------------------------+-
 | table | type | | key | key_len | ref |
-+----------+--------+--+---------------------+---------+-------------------------------+-
 | lineitem | ref | | i_l_suppkey_partkey | 5 | const |
 | supplier | eq_ref | | PRIMARY | 4 | test.lineitem.l_suppkey |
 | nation | index | | PRIMARY | 4 | NULL |
 | partsupp | eq_ref | | PRIMARY | 8 | const,test.lineitem.l_suppkey |
 | orders | index | | i_o_custkey | 5 | NULL |
 | customer | eq_ref | | PRIMARY | 4 | test.orders.o_custkey |
-+----------+--------+--+---------------------+---------+-------------------------------+-

[Sergey Petrunia (sergefp)]

Analysis:

The crash happens when processing ref access on the `partsupp` table. The EXPLAIN shows:

 | partsupp | eq_ref | | PRIMARY | 8 | const,test.lineitem.l_suppkey |

i.e. partsupp uses ref acess over 2 key parts, with references being:

   const, test.lineitem.l_suppkey

Now, if we look inside create_ref_for_key() function, we see this code:

      for (i=0 ; i < keyparts ; keyuse++,i++)
      {
        ...
        if (!keyuse->used_tables &&
            !(join->select_options & SELECT_DESCRIBE))
        { // Compare against constant
          store_key_item tmp(thd,
                             keyinfo->key_part[i].field,
                             key_buff + maybe_null,
                             maybe_null ? key_buff : 0,
                             keyinfo->key_part[i].length,
                             keyuse->val,
                             FALSE);
          if (thd->is_fatal_error)
            DBUG_RETURN(TRUE);
          tmp.copy();
        }
        else
          *ref_key++= get_store_key(thd,
                                    keyuse,join->const_table_map,
                                    &keyinfo->key_part[i],
                                    key_buff, maybe_null);

ref_key here points to tab->ref.key_copy, and it's easy to see that
 - first part of the reference is "const", so we won't put anything into tab->ref.key_copy for it.
 - second part of the reference is test.lineitem.l_suppkey, and we will get one element into tab->ref.key_copy for it.

If we then proceed to equality substitution code in JOIN::optimize():

   /*
      Perform the optimization on fields evaliation mentioned above
      for all used ref items.
    */
1 for (JOIN_TAB *tab= join_tab + const_tables; tab < join_tab + tables; tab++)
    {
      for (uint i=0; i < tab->ref.key_parts; i++)
      {

        Item **ref_item_ptr= tab->ref.items+i;
        Item *ref_item= *ref_item_ptr;
        if (!ref_item->used_tables() && !(select_options & SELECT_DESCRIBE))
          continue;
        COND_EQUAL *equals= tab->first_inner ? tab->first_inner->cond_equal :
                                               cond_equal;
        ref_item= substitute_for_best_equal_field(ref_item, equals, map2table);
        ref_item->update_used_tables();
        if (*ref_item_ptr != ref_item)
        {
          *ref_item_ptr= ref_item;
          Item *item= ref_item->real_item();
          store_key *key_copy= tab->ref.key_copy[i];
=> if (key_copy->type() == store_key::FIELD_STORE_KEY)
          {
            store_key_field *field_copy= ((store_key_field *)key_copy);
            field_copy->change_source_field((Item_field *) item);
          }
        }
      }
    }

we'll see that the line marked with => assumes that tab->ref.key_copy[i] will hold an element for the i-th keypart. As was shown above, this is not true when we've had a reference to constant for some keypart N<i.

As a resul...

[Sergey Petrunia (sergefp)]

A simpler testcase:

create table t1 (a int);
insert into t1 values (1),(2);
insert into t1 select * from t1;

create table t2 (a int, b int, key(a,b));
insert into t2 values (1,1),(1,2),(1,3),(1,4),(2,5),(2,6),(2,7),(2,8),(2,9);
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;

create table t0 (a int, b int, key(a));
insert into t0 values (1,1),(2,2);

select * from t0 straight_join t1 straight_join t2 force index(a) where t2.a=1 and t2.b=t1.a and t1.a=t0.b and t0.a=1;

  Program received signal SIGSEGV, Segmentation fault.
  [Switching to Thread 0x903c2b90 (LWP 6653)]
  0x08357e57 in JOIN::optimize (this=0xb0df308) at sql_select.cc:1075
(gdb) list
  1070 if (*ref_item_ptr != ref_item)
  1071 {
  1072 *ref_item_ptr= ref_item;
  1073 Item *item= ref_item->real_item();
  1074 store_key *key_copy= tab->ref.key_copy[i];
  1075 if (key_copy->type() == store_key::FIELD_STORE_KEY)
  1076 {
  1077 store_key_field *field_copy= ((store_key_field *)key_copy);
  1078 field_copy->change_source_field((Item_field *) item);
  1079 }