Crash in test_if_skip_sort_order with DISTINCT, view, InnoDB, on 64bit

[Elena Stepanova]

#3 <signal handler called>
#4 0x000000000079b7ea in test_if_skip_sort_order (tab=0x304b4c8,
    order=0x304bbc8, select_limit_arg=18446744073709551615, no_changes=false,
    map=0x2fe6e08) at sql_select.cc:17834
#5 0x0000000000772075 in JOIN::optimize (this=0x3038190) at sql_select.cc:1617
#6 0x00000000005d2f59 in st_select_lex::optimize_unflattened_subqueries (
    this=0x2e1d510) at sql_lex.cc:3127
#7 0x000000000085b969 in JOIN::optimize_unflattened_subqueries (
    this=0x3030070) at opt_subselect.cc:4534
#8 0x0000000000771c01 in JOIN::optimize (this=0x3030070) at sql_select.cc:1566
#9 0x0000000000776c07 in mysql_select (thd=0x2e1ab88,
    rref_pointer_array=0x2e1d760, tables=0x2faa3c8, wild_num=1, fields=...,
    conds=0x2fac078, og_num=0, order=0x0, group=0x0, having=0x0,
    proc_param=0x0, select_options=2147764736, result=0x2fe4a28,
    unit=0x2e1d028, select_lex=0x2e1d510) at sql_select.cc:2951
#10 0x000000000076d5dd in handle_select (thd=0x2e1ab88, lex=0x2e1cf88,
    result=0x2fe4a28, setup_tables_done_option=0) at sql_select.cc:283
#11 0x00000000006fc744 in execute_sqlcom_select (thd=0x2e1ab88,
    all_tables=0x2faa3c8) at sql_parse.cc:5112
#12 0x00000000006f381a in mysql_execute_command (thd=0x2e1ab88)
    at sql_parse.cc:2250
#13 0x00000000006ff124 in mysql_parse (thd=0x2e1ab88,
    rawbuf=0x2faa160 "SELECT * FROM t1, t2\nWHERE ( 2, 9 ) IN\n( SELECT DISTINCT a, pk FROM view_t1 ) \nOR a = b", length=87, found_semicolon=0x7fac5453dc98)
    at sql_parse.cc:6113
#14 0x00000000006f1032 in dispatch_command (command=COM_QUERY, thd=0x2e1ab88,
    packet=0x2fffa59 "SELECT * FROM t1, t2\nWHERE ( 2, 9 ) IN\n( SELECT DISTINCT a, pk FROM view_t1 ) \nOR a = b", packet_length=87) at sql_parse.cc:1221
#15 0x00000000006f0366 in do_command (thd=0x2e1ab88) at sql_parse.cc:916
#16 0x00000000006ed2bc in handle_one_connection (arg=0x2e1ab88)
    at sql_connect.cc:1191
#17 0x00007fac5381ca4f in start_thread () from /lib64/libpthread.so.0
#18 0x00007fac52c0682d in clone () from /lib64/libc.so.6

I could only reproduce it on one of two machines that I currently have access to:
openSUSE 11.3 (x86_64) -- reproducible
Fedora 12 32-bit -- not reproducible
Judging by the indicative number on the top of the stack trace, my guess is it's 64bit that makes the difference. However, if you cannot repeat it, please let me know and I will try to find more machines to experiment with.

bzr version-info
revision-id: <email address hidden>
date: 2011-12-08 04:22:38 +0400
build-date: 2011-12-09 23:25:32 +0200
revno: 3337
branch-nick: maria-5.3

Also reproducible on 5.3.2 release if materialization=on,in_to_exists=on.

Minimal optimizer_switch: none
(It looks like materialization=on,in_to_exists=on are required, but they are 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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

EXPLAIN also crashes.

Test case:

--source include/have_innodb.inc

# t1 must be InnoDB
CREATE TABLE t1 ( pk INT PRIMARY KEY, a INT, KEY(a) )
  ENGINE=InnoDB;
INSERT INTO t1 VALUES (0, 4),(8, 6);

CREATE TABLE t2 ( b INT, KEY(b) );
INSERT INTO t2 VALUES (7),(0);

# The view needs to be UNDEFINED or MERGE
CREATE VIEW view_t1 AS SELECT * FROM t1;

SELECT * FROM t1, t2
WHERE ( 2, 9 ) IN
    ( SELECT DISTINCT a, pk FROM view_t1 )
  OR a = b;

[Timour Katchaounov (timour)]

The crash is in test_if_skip_sort_order() in the line:

             if (used_key_parts > used_index_parts)
               used_pk_parts= used_key_parts-used_index_parts;
             rec_per_key= keyinfo->rec_per_key[used_key_parts-1];

where used_key_parts == 0, which results in an access to
keyinfo->rec_per_key[-1]

[Timour Katchaounov (timour)]

The bug is present only with materialization=on. A simpler test case:

CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, KEY(a) ) ENGINE=InnoDB;
INSERT INTO t1 VALUES (0, 4),(8, 6);

# The view needs to be UNDEFINED or MERGE
CREATE VIEW view_t1 AS SELECT * FROM t1;

set optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';

SELECT * FROM t1 WHERE (2, 9) IN (SELECT DISTINCT a, pk FROM view_t1) OR a = 7;

[Timour Katchaounov (timour)]

Analysis:

The reason for the crash is the following patch that improves several merged
MySQL fixes:

Revision Id: <email address hidden>
Changed the fixes for the following bugs:
Bug #39022: completed
Bug #39653: reverted as invalid
Bug #45640: ameliorated, simplified, optimized
Bug #48483: completed
Bug #49324: improved
Bug #51242/52336: reverted, applied a real fix.

The patch accepts this change in test_if_order_by_key():

          if (key_part == key_part_end && reverse == 0)
+ {
+ *used_key_parts= 0;
            DBUG_RETURN(1);
+ }

However it didn't take the corresponding lines in
test_if_skip_sort_order() to handle this new case:

***************
*** 13155,13161 ****
              select_limit= table_records;
            if (group)
            {
! rec_per_key= keyinfo->rec_per_key[used_key_parts-1];
              set_if_bigger(rec_per_key, 1);
              /*
                With a grouping query each group containing on average
--- 13158,13165 ----
              select_limit= table_records;
            if (group)
            {
! rec_per_key= used_key_parts ? keyinfo->rec_per_key[used_key_parts-1]
! : 1;
              set_if_bigger(rec_per_key, 1);
              /*
                With a grouping query each group containing on average

One of the two changes above is wrong/incomplete.

Reassigning to Igor, since he modified this code.

[Elena Stepanova (elenst)]

Fix released with 5.3.3-rc.