Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4620

Server crashes in test_if_skip_sort_order with nested IN subqueries, materialization+semijoin, InnoDB, 1-row tables

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.0.2
        • Fix Version/s: None
        • Component/s: None
        • Labels:
          None

          Description

          Note: I could only reproduce it on 10.0, because the test case requires use_stat_tables, otherwise it doesn't crash. I suppose the crash has nothing to do with the statistics, it just makes server to choose the faulty plan. I cannot however positively verify it, because EXPLAIN also crashes. In any case, please see if the underlying issue exists in 5.3/5.5, and if so, modify Fix version accordingly.

          #3  <signal handler called>
#4  0x0000000000733f3f in test_if_skip_sort_order (tab=0x7f371c08cb10, order=0x7f371c02b650, select_limit=1, no_changes=false, map=0x7f371c029558) at /home/elenst/bzr/10.0/sql/sql_select.cc:19334
#5  0x00000000006ecf15 in JOIN::optimize_inner (this=0x7f371c02c248) at /home/elenst/bzr/10.0/sql/sql_select.cc:1786
#6  0x00000000006e876a in JOIN::optimize (this=0x7f371c02c248) at /home/elenst/bzr/10.0/sql/sql_select.cc:1003
#7  0x0000000000a7d274 in Item_in_subselect::optimize (this=0x7f371c02b690, out_rows=0x7f374468f540, cost=0x7f374468f548) at /home/elenst/bzr/10.0/sql/item_subselect.cc:704
#8  0x00000000008be7ae in setup_jtbm_semi_joins (join=0x7f371c02b970, join_list=0x59690f0, join_where=0x7f371c02bd88) at /home/elenst/bzr/10.0/sql/opt_subselect.cc:5165
#9  0x00000000006e9319 in JOIN::optimize_inner (this=0x7f371c02b970) at /home/elenst/bzr/10.0/sql/sql_select.cc:1158
#10 0x00000000006e876a in JOIN::optimize (this=0x7f371c02b970) at /home/elenst/bzr/10.0/sql/sql_select.cc:1003
#11 0x00000000006f49d9 in mysql_select (thd=0x5965d80, rref_pointer_array=0x59691e0, tables=0x7f371c021a90, wild_num=1, fields=..., conds=0x7f371c02b690, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f371c02b950, unit=0x59688a8, select_lex=0x5968f80) at /home/elenst/bzr/10.0/sql/sql_select.cc:3215
#12 0x00000000006e5ba0 in handle_select (thd=0x5965d80, lex=0x59687f0, result=0x7f371c02b950, setup_tables_done_option=0) at /home/elenst/bzr/10.0/sql/sql_select.cc:371
#13 0x00000000006a14d3 in execute_sqlcom_select (thd=0x5965d80, all_tables=0x7f371c021a90) at /home/elenst/bzr/10.0/sql/sql_parse.cc:5021
#14 0x0000000000694909 in mysql_execute_command (thd=0x5965d80) at /home/elenst/bzr/10.0/sql/sql_parse.cc:2482
#15 0x00000000006a55ca in mysql_parse (thd=0x5965d80, rawbuf=0x7f371c0216f8 "SELECT * FROM t1 WHERE ( a, a ) IN ( \nSELECT b, MIN( c ) FROM t2, t3 WHERE ( b, c ) IN ( \nSELECT t1_inner2.a, t1_inner1.a FROM t1 AS t1_inner1, t1 AS t1_inner2 \nWHERE t1_inner2.a = t1_inner1.a AND t1_"..., length=234, parser_state=0x7f3744690510) at /home/elenst/bzr/10.0/sql/sql_parse.cc:6154
#16 0x000000000068fde5 in dispatch_command (command=COM_QUERY, thd=0x5965d80, packet=0x5969fd1 "SELECT * FROM t1 WHERE ( a, a ) IN ( \nSELECT b, MIN( c ) FROM t2, t3 WHERE ( b, c ) IN ( \nSELECT t1_inner2.a, t1_inner1.a FROM t1 AS t1_inner1, t1 AS t1_inner2 \nWHERE t1_inner2.a = t1_inner1.a AND t1_"..., packet_length=234) at /home/elenst/bzr/10.0/sql/sql_parse.cc:1274
#17 0x000000000068ea82 in do_command (thd=0x5965d80) at /home/elenst/bzr/10.0/sql/sql_parse.cc:983
#18 0x00000000008499a9 in do_handle_one_connection (thd_arg=0x5965d80) at /home/elenst/bzr/10.0/sql/sql_connect.cc:1267
#19 0x00000000008494c4 in handle_one_connection (arg=0x5965d80) at /home/elenst/bzr/10.0/sql/sql_connect.cc:1181
#20 0x0000000000c64604 in pfs_spawn_thread (arg=0x5948110) at /home/elenst/bzr/10.0/storage/perfschema/pfs.cc:1800
#21 0x00007f37521d3e9a in start_thread (arg=0x7f3744691700) at pthread_create.c:308
#22 0x00007f37512b1cbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

          bzr version-info

          revision-id: wlad@montyprogram.com-20130523125248-22m56m01r24t0dmb
revno: 3752
branch-nick: 10.0

          Minimal optimizer_switch: materialization=on,semijoin=on
          Default optimizer_switch:

          index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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,extended_keys=off,exists_to_in=off

          EXPLAIN also crashes.

          Test case:

          
--source include/have_innodb.inc

SET optimizer_switch = 'materialization=on,semijoin=on';
SET use_stat_tables=PREFERABLY;

CREATE TABLE t1 (a VARCHAR(1) NOT NULL, INDEX (a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('x');

CREATE TABLE t2 (b VARCHAR(1), INDEX (b)) ENGINE=InnoDB;
INSERT INTO t2 VALUES ('z');

CREATE TABLE t3 (c VARCHAR(1), INDEX (c)) ENGINE=InnoDB;
INSERT INTO t3 VALUES ('y'),('y');

ANALYZE TABLE t1, t2, t3;

SELECT * FROM t1 WHERE ( a, a ) IN ( 
  SELECT b, MIN( c ) FROM t2, t3 WHERE ( b, c ) IN ( 
    SELECT t1_inner2.a, t1_inner1.a FROM t1 AS t1_inner1, t1 AS t1_inner2 
    WHERE t1_inner2.a = t1_inner1.a AND t1_inner2.a != 's'
  ) 
  GROUP BY b 
);

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                psergey Sergei Petrunia added a comment -

                Not repeatable with current 10.0,
                revno: 3771
                revision-id: sanja@askmonty.org-20130716124438-8dw9ix4rjnwaeib1

                Show
                psergey Sergei Petrunia added a comment - Not repeatable with current 10.0, revno: 3771 revision-id: sanja@askmonty.org-20130716124438-8dw9ix4rjnwaeib1
                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                I'll keep it open for 10.0.4 pre-release tests and will close if I don't encounter it anymore.

                Show
                elenst Elena Stepanova added a comment - I'll keep it open for 10.0.4 pre-release tests and will close if I don't encounter it anymore.
                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                The bug was fixed in 10.0.3 by this revision on the 10.0 tree:

                ------------------------------------------------------------
                revno: 3758 [merge]
                revision-id: sergii@pisem.net-20130606193229-7148u5wpdsdfnh89
                committer: Sergei Golubchik <sergii@pisem.net>
                branch nick: 10.0
                timestamp: Thu 2013-06-06 21:32:29 +0200
                message:
                10.0-base merge
                (without InnoDB - all InnoDB changes were ignored)

                and by this revision on 10.0-base tree:

                revno: 3645 [merge]
                revision-id: sergii@pisem.net-20130606155128-5mytep9v42626tfs
                committer: Sergei Golubchik <sergii@pisem.net>
                branch nick: 10.0-base
                timestamp: Thu 2013-06-06 17:51:28 +0200
                message:
                5.5 merge

                I couldn't track it down to 5.5 changes since the scenario involves functionality that is not in 5.5.

                Show
                elenst Elena Stepanova added a comment - The bug was fixed in 10.0.3 by this revision on the 10.0 tree: ------------------------------------------------------------ revno: 3758 [merge] revision-id: sergii@pisem.net-20130606193229-7148u5wpdsdfnh89 committer: Sergei Golubchik <sergii@pisem.net> branch nick: 10.0 timestamp: Thu 2013-06-06 21:32:29 +0200 message: 10.0-base merge (without InnoDB - all InnoDB changes were ignored) and by this revision on 10.0-base tree: revno: 3645 [merge] revision-id: sergii@pisem.net-20130606155128-5mytep9v42626tfs committer: Sergei Golubchik <sergii@pisem.net> branch nick: 10.0-base timestamp: Thu 2013-06-06 17:51:28 +0200 message: 5.5 merge I couldn't track it down to 5.5 changes since the scenario involves functionality that is not in 5.5.

                  People

                  • Assignee:
                    elenst Elena Stepanova
                    Reporter:
                    elenst Elena Stepanova
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: