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

LP:693329 - Assertion `!is_interleave_error' failed on low optimizer_search_depth

    XMLWordPrintable

Details

    Description

      As already reported in mysql bug http://bugs.mysql.com/bug.php?id=54429 , using low values for optimizer_search_depth causes an easy crash on multiple-table joins.

      The RQG sometimes generates joins involving many tables. In order to restrict the total optimization time per query, and thus run more queries per test run, optimizer_search_depth must be safe to use and not assert.

      assertion:

      mysqld: sql_select.cc:5498: bool greedy_search(JOIN*, table_map, uint, uint): Assertion `!is_interleave_error' failed.

      backtrace:

      #8  0x006b0d98 in __assert_fail () from /lib/libc.so.6
      #9  0x0831785c in greedy_search (join=0xae688eb0, remaining_tables=55, search_depth=4, prune_level=1) at sql_select.cc:5498
      #10 0x08316ebc in choose_plan (join=0xae688eb0, join_tables=55) at sql_select.cc:5120
      #11 0x0831205f in make_join_statistics (join=0xae688eb0, tables_arg=0xae631c80, conds=0x0, keyuse_array=0xae68dbec) at sql_select.cc:3216
      #12 0x08309e93 in JOIN::optimize (this=0xae688eb0) at sql_select.cc:956
      #13 0x0830fa81 in mysql_select (thd=0xb1fda70, rref_pointer_array=0xb1ff50c, tables=0xae631c80, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae64b8b0,
          unit=0xb1ff16c, select_lex=0xb1ff408) at sql_select.cc:2607
      #14 0x0830800d in handle_select (thd=0xb1fda70, lex=0xb1ff110, result=0xae64b8b0, setup_tables_done_option=0) at sql_select.cc:286
      #15 0x082a3400 in execute_sqlcom_select (thd=0xb1fda70, all_tables=0xae631c80) at sql_parse.cc:5070
      #16 0x08299e1b in mysql_execute_command (thd=0xb1fda70) at sql_parse.cc:2234
      #17 0x082a599e in mysql_parse (thd=0xb1fda70,
          rawbuf=0xae631940 "SELECT table2.f4  FROM t1  AS table1  LEFT  JOIN t1  AS table2  LEFT  JOIN t2  AS table3  LEFT  JOIN t3  AS table4  ON table3 .f1  = table4.f3  ON table2 .f1  LEFT  JOIN t4  AS table5  JOIN t5  ON tab"...,
          length=262, found_semicolon=0xae983228) at sql_parse.cc:6077
      #18 0x08297904 in dispatch_command (command=COM_QUERY, thd=0xb1fda70,
          packet=0xb217c31 "SELECT table2.f4  FROM t1  AS table1  LEFT  JOIN t1  AS table2  LEFT  JOIN t2  AS table3  LEFT  JOIN t3  AS table4  ON table3 .f1  = table4.f3  ON table2 .f1  LEFT  JOIN t4  AS table5  JOIN t5  ON tab"...,
          packet_length=265) at sql_parse.cc:1210
      #19 0x08296d6e in do_command (thd=0xb1fda70) at sql_parse.cc:903
      #20 0x08293e4a in handle_one_connection (arg=0xb1fda70) at sql_connect.cc:1154
      #21 0x00821919 in start_thread () from /lib/libpthread.so.0
      #22 0x0076acce in clone () from /lib/libc.so.6

      test case:

      SET SESSION optimizer_search_depth = 4;
       
      CREATE TABLE t1 (f1 int,f2 int,f3 int,f4 int) ;
      INSERT IGNORE INTO t1 VALUES (0,0,2,0),(NULL,0,2,0);
       
      CREATE TABLE t2 (f1 int) ;
      CREATE TABLE t3 (f3 int,PRIMARY KEY (f3)) ;
      CREATE TABLE t4 (f5 int) ;
      CREATE TABLE t5 (f2 int) ;
       
      SELECT alias2.f4  FROM t1  AS alias1
      LEFT    JOIN t1  AS alias2
              LEFT  JOIN t2  AS alias3
              LEFT  JOIN t3  AS alias4  ON alias3.f1 = alias4.f3
              ON alias2.f1
      LEFT    JOIN t4  AS alias5
              JOIN t5  ON alias5.f5
      ON alias2.f3  ON alias1.f2   ;

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              philipstoev Philip Stoev (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.