Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Crash in Item_field::Item_field from add_ref_to_table_cond() with semijoin+materialization
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.
Launchpad bug id: 836491
Re: Crash in Item_field::Item_field from add_ref_to_table_cond() with semijoin+materialization
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);
---
-------------------------------------------------------------------------------+---
-------------------------------------------------------------------------------+---
-------------------------------------------------------------------------------+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)