Crash in Item_field::Item_field from add_ref_to_table_cond() with semijoin+materialization

[Philip Stoev]

The following query:

SELECT t2.a, t1.c
FROM t1, t2
WHERE t2.d IN ( SELECT d FROM t3 )
AND t1.d = t2.d
GROUP BY 1 , 2;

causes the following crash:

#3 <signal handler called>
#4 0x000000000059e554 in Item_field::Item_field (this=0x52f59c0, f=0x0) at item.cc:2044
#5 0x00000000007445f1 in add_ref_to_table_cond (thd=0x51dd268, join_tab=0x52a0238) at sql_select.cc:19875
#6 0x000000000074e725 in JOIN::optimize (this=0x52e7f80) at sql_select.cc:1544
#7 0x00000000007505bb in mysql_select (thd=0x51dd268, rref_pointer_array=0x51dfe48, tables=0x5261f78, wild_num=0, fields=..., conds=0x529d5a0, og_num=2,
    order=0x0, group=0x529d740, having=0x0, proc_param=0x0, select_options=2147764736, result=0x529d8e0, unit=0x51df710, select_lex=0x51dfbf8)
    at sql_select.cc:2887
#8 0x0000000000756a7a in handle_select (thd=0x51dd268, lex=0x51df670, result=0x529d8e0, setup_tables_done_option=0) at sql_select.cc:283
#9 0x00000000006a33de in execute_sqlcom_select (thd=0x51dd268, all_tables=0x5261f78) at sql_parse.cc:5090
#10 0x00000000006a50bc in mysql_execute_command (thd=0x51dd268) at sql_parse.cc:2234
#11 0x00000000006ade55 in mysql_parse (thd=0x51dd268,
    rawbuf=0x5261bd0 "SELECT t2.a, t1.c\nFROM t1, t2\nWHERE t2.d IN ( SELECT d FROM t3 )\nAND t1.d = t2.d\nGROUP BY 1 , 2", length=95,
    found_semicolon=0x42329f08) at sql_parse.cc:6091
#12 0x00000000006aed25 in dispatch_command (command=COM_QUERY, thd=0x51dd268,
    packet=0x5258769 "SELECT t2.a, t1.c\nFROM t1, t2\nWHERE t2.d IN ( SELECT d FROM t3 )\nAND t1.d = t2.d\nGROUP BY 1 , 2", packet_length=95)
    at sql_parse.cc:1211
#13 0x00000000006b0333 in do_command (thd=0x51dd268) at sql_parse.cc:906
#14 0x000000000069ac67 in handle_one_connection (arg=0x51dd268) at sql_connect.cc:1186
#15 0x00000033b600673d in start_thread () from /lib64/libpthread.so.0
#16 0x00000033b58d40cd in clone () from /lib64/libc.so.6

Explain also crashes. Seems that t2 must have 1 row for the crash to happen.

minimal optimizer switch:

semijoin=ON,materialization=ON

full optimizer switch:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

bzr version-info:

revision-id: <email address hidden>
date: 2011-08-27 00:40:29 +0300
build-date: 2011-08-29 11:01:28 +0300
revno: 3167
branch-nick: maria-5.3

test case:

CREATE TABLE t1 (c int, d varchar(1), KEY(d)) ;
INSERT INTO t1 VALUES (2,'x'),(2,'x'),(2,'j'),(2,'c');

CREATE TABLE t2 (a int, d varchar(1)) ;
INSERT INTO t2 VALUES (1,'x');

CREATE TABLE t3 (d varchar(1)) ;
INSERT INTO t3 VALUES ('x'),('x'),('j'),('c');

SET SESSION optimizer_switch='semijoin=ON,materialization=ON';

SELECT t2.a, t1.c
FROM t1, t2
WHERE t2.d IN ( SELECT d FROM t3 )
AND t1.d = t2.d
GROUP BY 1 , 2;

[Sergey Petrunia (sergefp)]

It seems, the problem is that KEY_PART::fieldnr is not set for temporary tables. It is most apparent when materialization table's key has two columns:

MariaDB [bug836491]> create table t100 (a int, b int) ;
Query OK, 0 rows affected (0.12 sec)

MariaDB [bug836491]> insert into t100 select a,a from test.ten;
Query OK, 10 rows affected (0.05 sec)
Records: 10 Duplicates: 0 Warnings: 0

MariaDB [bug836491]> create table t101 as select * from t100;
Query OK, 10 rows affected (0.09 sec)
Records: 10 Duplicates: 0 Warnings: 0

MariaDB [bug836491]> explain select * from t100 where (a,b) in (select a,b from t101);
+----+-------------+-------------+--------+---------------+--------------+---------+-----------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------+--------------+---------+-----------+------+-------+
| 1 | PRIMARY | t100 | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 10 | func,func | 1 | |
| 2 | SUBQUERY | t101 | ALL | NULL | NULL | NULL | NULL | 10 | |
+----+-------------+-------------+--------+---------------+--------------+---------+-----------+------+-------+
3 rows in set (0.03 sec)

  Breakpoint 21, create_tmp_table (thd=0xba91280, param=0xbaf535c, fields=@0xbaf53d8, group=0x0, distinct=true, save_sum_fields=true, select_options=2147768832, rows_limit=18446744073709551615, table_alias=0x89ac970 "sj-materialize", do_not_open=false) at sql_select.cc:13130
(gdb) fini
  Run till exit from #0 create_tmp_table (thd=0xba91280, param=0xbaf535c, fields=@0xbaf53d8, group=0x0, distinct=true, save_sum_fields=true, select_options=2147768832, rows_limit=18446744073709551615, table_alias=0x89ac970 "sj-materialize", do_not_open=false) at sql_select.cc:13130
  0x08424f59 in setup_sj_materialization_part1 (sjm_tab=0xbb18b7c) at opt_subselect.cc:2959
  Value returned is $77 = (TABLE *) 0xbb1a1f0

(gdb) p sjm->table->key_info[0].key_part[0].fieldnr
  $80 = 0
(gdb) p sjm->table->key_info[0].key_part[1].fieldnr
  $81 = 0
(gdb) p sjm->table->key_info[0].key_parts
  $82 = 2

One can see that the key has two key parts, and key_part[1].fieldnr==key_part[0].fieldnr, which is apparently wrong (a column can't be present in the key two times)

[Sergey Petrunia (sergefp)]

If anybody is wondering why this has not been caught earlier:

In order to hit this bug,
1. parent subquery must use GROUP BY (so that add_ref_to_table_cond is invoked)
2. SJ-Materialization table must be accessed with lookups
3. and it the first non-const table

most of written-to-purpose materialization tests lack #1.