[MDEV-67] LP:912510 - Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate function, IN subquery Created: 2012-01-05  Updated: 2012-01-31  Resolved: 2012-01-10

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.3, 5.3.4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None
Environment:

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
 
  );



 Comments   
Comment by Sergei Petrunia [ 2012-01-05 ]

Is this https://bugs.launchpad.net/maria/+bug/802965 ?

Comment by Elena Stepanova [ 2012-01-05 ]

This bug has been also filed in LP as https://bugs.launchpad.net/maria/+bug/912510

Comment by Sergei Petrunia [ 2012-01-06 ]

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).

Comment by Sergei Petrunia [ 2012-01-06 ]

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;

Comment by Sergei Petrunia [ 2012-01-08 ]

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.

Comment by Sergei Petrunia [ 2012-01-08 ]

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.

Comment by Sergei Petrunia [ 2012-01-08 ]

Committed a fix, waiting for review.

Generated at Thu Feb 08 06:26:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.