Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.3, 5.3.4
-
None
-
None
-
None
-
bzr version-info
revision-id: igor@askmonty.org-20120103040636-nc6o55vsxqadd1n0
date: 2012-01-02 20:06:36 -0800
build-date: 2012-01-05 15:17:09 +0200
revno: 3376
branch-nick: maria-5.3
Description
I'm leaving it 'Major' but not 'Critical' because the query in the test case is not very smart (due to the mix of aggregate and non-aggregate without a group by), although quite simple.
|
|
#2 0x00000000006e1cc0 in handle_segfault (sig=11) at mysqld.cc:2838
|
#3 <signal handler called>
|
#4 0x00000000008a7a20 in do_copy_not_null (copy=0x1cef128) at field_conv.cc:255
|
#5 0x00000000007ab08e in store_key_field::copy_inner (this=0x1cef100) at sql_select.h:1521
|
#6 0x000000000068cd2f in store_key::copy (this=0x1cef100) at sql_select.h:1462
|
#7 0x00000000007a083a in cp_buffer_from_ref (thd=0x1c2cbd8, table=0x1ce0608, ref=0x1cf1000)
|
at sql_select.cc:18791
|
#8 0x0000000000798ee8 in join_read_always_key (tab=0x1cf0df0) at sql_select.cc:15984
|
#9 0x000000000079747c in sub_select (join=0x1d37350, join_tab=0x1cf0df0, end_of_records=false)
|
at sql_select.cc:15301
|
#10 0x0000000000797c8e in evaluate_join_record (join=0x1d37350, join_tab=0x1cf0ad0, error=0)
|
at sql_select.cc:15501
|
#11 0x00000000007974d8 in sub_select (join=0x1d37350, join_tab=0x1cf0ad0, end_of_records=false)
|
at sql_select.cc:15304
|
#12 0x0000000000797c8e in evaluate_join_record (join=0x1d37350, join_tab=0x1cf07b0, error=0)
|
at sql_select.cc:15501
|
#13 0x00000000007974d8 in sub_select (join=0x1d37350, join_tab=0x1cf07b0, end_of_records=false)
|
at sql_select.cc:15304
|
#14 0x0000000000796ca6 in do_select (join=0x1d37350, fields=0x1d3f2b8, table=0x0, procedure=0x0)
|
at sql_select.cc:14965
|
#15 0x0000000000778872 in JOIN::exec (this=0x1d37350) at sql_select.cc:2704
|
#16 0x0000000000779109 in mysql_select (thd=0x1c2cbd8, rref_pointer_array=0x1c2f7a8,
|
tables=0x1cb1438, wild_num=0, fields=..., conds=0x1ced8b0, og_num=0, order=0x0, group=0x0,
|
having=0x0, proc_param=0x0, select_options=2147764736, result=0x1cedad8, unit=0x1c2f070,
|
select_lex=0x1c2f558) at sql_select.cc:2925
|
#17 0x000000000076fd8d in handle_select (thd=0x1c2cbd8, lex=0x1c2efd0, result=0x1cedad8,
|
setup_tables_done_option=0) at sql_select.cc:283
|
#18 0x00000000006feb20 in execute_sqlcom_select (thd=0x1c2cbd8, all_tables=0x1cb1438)
|
at sql_parse.cc:5148
|
#19 0x00000000006f5bdd in mysql_execute_command (thd=0x1c2cbd8) at sql_parse.cc:2281
|
#20 0x00000000007014fb in mysql_parse (thd=0x1c2cbd8,
|
rawbuf=0x1cb0ff0 "SELECT a, COUNT(*) FROM t1\nWHERE a IN ( \nSELECT b FROM t2, t3 \nWHERE c = b AND b = a\n)", length=86, found_semicolon=0x7f8de4905c98) at sql_parse.cc:6149
|
#21 0x00000000006f33b2 in dispatch_command (command=COM_QUERY, thd=0x1c2cbd8, packet=0x1ca7b89 "",
|
packet_length=86) at sql_parse.cc:1227
|
#22 0x00000000006f26e6 in do_command (thd=0x1c2cbd8) at sql_parse.cc:922
|
#23 0x00000000006ef60e in handle_one_connection (arg=0x1c2cbd8) at sql_connect.cc:1193
|
#24 0x00007f8dee0a3a4f in start_thread () from /lib64/libpthread.so.0
|
#25 0x00007f8ded48d82d in clone () from /lib64/libc.so.6
|
|
|
Minimal optimizer_switch: firstmatch=on,semijoin=on (current defaults)
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=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
EXPLAIN:
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
|
1 PRIMARY t2 ref b b 3 test.t1.a 2 100.00 Using index
|
1 PRIMARY t3 ref c c 3 test.t1.a 2 100.00 Using index; FirstMatch(t1)
|
Warnings:
|
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select `test`.`t1`.`a` AS `a`,count(0) AS `COUNT(*)` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t3`.`c` = `test`.`t1`.`a`))
|
SELECT a, COUNT(*) FROM t1
|
WHERE a IN (
|
SELECT b FROM t2, t3
|
WHERE c = b AND b = a
|
);
|
|
|
Reproducible on 5.3.3 release binaries.
Not reproducible on 5.2.10.
Could not reproduce on current 5.5, it chooses a different plan.
Test case:
SET optimizer_switch = 'firstmatch=on,semijoin=on';
|
|
|
CREATE TABLE t1 ( a VARCHAR(1) NOT NULL );
|
INSERT INTO t1 VALUES ('k'),('l');
|
|
|
CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) );
|
INSERT INTO t2 VALUES ('k'),('l');
|
|
|
CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) );
|
INSERT INTO t3 VALUES ('m'),('n');
|
|
|
SELECT a, COUNT(*) FROM t1
|
WHERE a IN (
|
SELECT b FROM t2, t3
|
WHERE c = b AND b = a
|
);
|
Attachments
Activity
Analysis:
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
1 PRIMARY t2 ref b b 3 test.t1.a 2 100.00 Using index
1 PRIMARY t3 ref c c 3 test.t1.a 2 100.00 Using index; FirstMatch(t1)
we crash when constructing the lookup key for doing a lookup on t3.c= t1.a. t1.a is defined as NOT NULL, and so has copy->from_null_ptr == NULL. Yet, do_copy_not_null() function is called for it which attempts to do copy from NULL-able field and tries to access from_null_ptr.
More details: create_ref_for_key( t3) creates a lookup reference for t3.c=t2.b. Later on, substitute_for_best_equal() changes t3.c (which is NULLable) to t1.a (which is NOT NULL).
When we fix ref access structures after equality substituion at:
#0 Copy_field::set (this=0x9f5d51c, to=0x9f5d570, from=0x9f45a60, save=false) at field_conv.cc:620
#1 0x0838e32d in store_key_field::change_source_field (this=0x9f5d508, fld_item=0x9f40270) at sql_select.h:1502
#2 0x08382bea in JOIN::optimize (this=0x9f4f298) at sql_select.cc:1275
We get:
(gdb) p from->maybe_null()
$130 = true
(gdb) p from->null_ptr
$131 = (uchar *) 0x0
db) p from->maybe_null()
$130 = true
(gdb) p from->table->alias->Ptr
$132 = 0x9f44f48 "t1"
(gdb) p from->table->maybe_null
$133 = 1
That is, table t1 is marked as NULLable, as if it was an inner table of an
outer join (which it is not)
table->maybe_null is set to TRUE by this code in JOIN::prepare():
if (mixed_implicit_grouping)
tbl->table->maybe_null= 1;
The problem happens only with semi-join subqueries. When one runs a
similar inner join, this code in JOIN::prepare():
while ((tbl= li++))
{ ... // comment if (mixed_implicit_grouping) tbl->table->maybe_null= 1; }is run for all three tables, t1, t2, t3.
When one runs a semi-join, maybe_null=1 is set only for table t1, because
tables t2 and t3 are still in subquery which has not yet been converted into
a semi-join.
This raises a question: should we set maybe_null=TRUE for tables that are
inside the semi-join, too?
The answer is NO:
tbl->maybe_null=TRUE is set, because references to "tbl.col" from HAVING or
select_list may evaluate to NULL when there are no matching records.
The HAVING clause and select_list may not contain references to tables that
are within a subquery:
- SQL syntax doesn't allow this
- convert_subq_to_sj() adds IN-equalities only into WHERE/ON clauses
- equality substitution is able to use injected IN-equality and replace a
parent_select_table.column with subquery_table.column, but equality
substitution is not performed for HAVING or select_list clauses.
=> there is no need to set tbl->maybe_null=TRUE for tables that are inner
within semi-joins.
Is this https://bugs.launchpad.net/maria/+bug/802965 ?