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

Assertion `join->best_read < double(...) failed in bool greedy_search with optimizer_use_condition_selectivity>2, InnoDB

Details

    Description

      The problem appeared on 10.0 tree with the following revision:

      revno: 4465 [merge]
      revision-id: igor@askmonty.org-20141028233126-zt6nb214qnyqfuy8
      parent: jplindst@mariadb.org-20141027145816-cuwsbq6d5yber97u
      parent: igor@askmonty.org-20141028213331-qrdbjknh6i7nzsuf
      committer: Igor Babaev <igor@askmonty.org>
      branch nick: maria-10.0-trunk
      timestamp: Tue 2014-10-28 16:31:26 -0700
      message:
        Merge
          ------------------------------------------------------------
          revno: 4446.1.1
          revision-id: igor@askmonty.org-20141028213331-qrdbjknh6i7nzsuf
          parent: psergey@askmonty.org-20141016185808-n8cw47wxrn3a9yxm
          committer: Igor Babaev <igor@askmonty.org>
          branch nick: maria-10.0
          timestamp: Tue 2014-10-28 14:33:31 -0700
          message:
            Fixed bug mdev-6325.
            
            Field::selectivity should be set for all fields used in range conditions.

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (a VARCHAR(16), b INT, c INT, PRIMARY KEY(a), KEY(c)) ENGINE=InnoDB;
      INSERT INTO t1 VALUES ('USAChinese',1,10),('USAEnglish',2,20),('USAFrench',3,30);
       
      CREATE TABLE t2 (i int) ENGINE=InnoDB;
      INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4);
       
      SET use_stat_tables=PREFERABLY, optimizer_use_condition_selectivity=3;
      ANALYZE TABLE t1, t2;
       
      SELECT * FROM t1, t2 WHERE b IN ( SELECT 2 UNION SELECT 3 ) AND a <> 'USARussian' AND c IS NULL;

      10.0/sql/sql_select.cc:6950: bool greedy_search(JOIN*, table_map, uint, uint, uint): Assertion `join->best_read < double(1.79769313486231570815e+308L)' failed.
      141213 22:37:26 [ERROR] mysqld got signal 6 ;

      Stack trace from 10.0 revno 4543

      #6  0x00007fbaa3e056f1 in *__GI___assert_fail (assertion=0xf11688 "join->best_read < double(1.79769313486231570815e+308L)", file=<optimized out>, line=6950, function=0xf14680 "bool greedy_search(JOIN*, table_map, uint, uint, uint)") at assert.c:81
      #7  0x00000000006c17e5 in greedy_search (join=0x7fba91229bd0, remaining_tables=3, search_depth=62, prune_level=1, use_cond_selectivity=3) at 10.0/sql/sql_select.cc:6950
      #8  0x00000000006c0d6f in choose_plan (join=0x7fba91229bd0, join_tables=3) at 10.0/sql/sql_select.cc:6519
      #9  0x00000000006ba73d in make_join_statistics (join=0x7fba91229bd0, tables_list=..., conds=0x7fba912297f0, keyuse_array=0x7fba91229ed8) at 10.0/sql/sql_select.cc:4038
      #10 0x00000000006b13a3 in JOIN::optimize_inner (this=0x7fba91229bd0) at 10.0/sql/sql_select.cc:1339
      #11 0x00000000006b033a in JOIN::optimize (this=0x7fba91229bd0) at 10.0/sql/sql_select.cc:1024
      #12 0x00000000006b7ebf in mysql_select (thd=0x7fba9d9ee070, rref_pointer_array=0x7fba9d9f26e0, tables=0x7fba91341300, wild_num=1, fields=..., conds=0x7fba912297f0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fba91229bb0, unit=0x7fba9d9f1d80, select_lex=0x7fba9d9f2468) at 10.0/sql/sql_select.cc:3294
      #13 0x00000000006ae549 in handle_select (thd=0x7fba9d9ee070, lex=0x7fba9d9f1cb8, result=0x7fba91229bb0, setup_tables_done_option=0) at 10.0/sql/sql_select.cc:373
      #14 0x0000000000683244 in execute_sqlcom_select (thd=0x7fba9d9ee070, all_tables=0x7fba91341300) at 10.0/sql/sql_parse.cc:5261
      #15 0x000000000067b542 in mysql_execute_command (thd=0x7fba9d9ee070) at 10.0/sql/sql_parse.cc:2545
      #16 0x00000000006859bd in mysql_parse (thd=0x7fba9d9ee070, rawbuf=0x7fba91341088 "SELECT * FROM t1, t2 WHERE b IN ( SELECT 2 UNION SELECT 3 ) AND a <> 'USARussian' AND c IS NULL", length=95, parser_state=0x7fbaa5fc5610) at 10.0/sql/sql_parse.cc:6407
      #17 0x00000000006787b2 in dispatch_command (command=COM_QUERY, thd=0x7fba9d9ee070, packet=0x7fba986b9071 "SELECT * FROM t1, t2 WHERE b IN ( SELECT 2 UNION SELECT 3 ) AND a <> 'USARussian' AND c IS NULL", packet_length=95) at 10.0/sql/sql_parse.cc:1299
      #18 0x0000000000677b57 in do_command (thd=0x7fba9d9ee070) at 10.0/sql/sql_parse.cc:996
      #19 0x0000000000794877 in do_handle_one_connection (thd_arg=0x7fba9d9ee070) at 10.0/sql/sql_connect.cc:1375
      #20 0x00000000007945ca in handle_one_connection (arg=0x7fba9d9ee070) at 10.0/sql/sql_connect.cc:1289
      #21 0x0000000000ccda5e in pfs_spawn_thread (arg=0x7fba986b10f0) at 10.0/storage/perfschema/pfs.cc:1860
      #22 0x00007fbaa5bffb50 in start_thread (arg=<optimized out>) at pthread_create.c:304
      #23 0x00007fbaa3eb620d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

      Attachments

        Issue Links

          Activity

            The crash doesn't repeat if one re-runs the query. However, running the whole example reliably produces the crash.

            The crash is caused by the first call to table_cond_selectivity() returning inf.

            psergei Sergei Petrunia added a comment - The crash doesn't repeat if one re-runs the query. However, running the whole example reliably produces the crash. The crash is caused by the first call to table_cond_selectivity() returning inf.

            The first call for table_cond_selectivity(s=t1, idx=0).

            Selectivity becomes infinite here:

                          sel /= table->field[fldno]->cond_selectivity;

            The field in question is t1.c.

            psergei Sergei Petrunia added a comment - The first call for table_cond_selectivity(s=t1, idx=0). Selectivity becomes infinite here: sel /= table->field[fldno]->cond_selectivity; The field in question is t1.c.

            The WHERE clause has:

            c IS NULL

            This equality is used to construct ref access. Then, table_cond_selectivity attempts to discount the selectivity of "c IS NULL". This is where it divides by zero.

            psergei Sergei Petrunia added a comment - The WHERE clause has: c IS NULL This equality is used to construct ref access. Then, table_cond_selectivity attempts to discount the selectivity of "c IS NULL". This is where it divides by zero.

            The data in table t1:

            INSERT INTO t1 VALUES 
              ('USAChinese',1,10),
              ('USAEnglish',2,20),
              ('USAFrench', 3,30);

            There are 3 rows in total, 0 rows that have t1.c=NULL.

            get_column_range_cardinality(field="c") executes this code:

                if (nulls_incl)
                {
                  /* This is null single point range */
                  res= col_nulls;

            col_nulls=0, which is true. But then, we attempt to divide by zero to discount the selectivity.

            psergei Sergei Petrunia added a comment - The data in table t1: INSERT INTO t1 VALUES ('USAChinese',1,10), ('USAEnglish',2,20), ('USAFrench', 3,30); There are 3 rows in total, 0 rows that have t1.c=NULL. get_column_range_cardinality(field="c") executes this code: if (nulls_incl) { /* This is null single point range */ res= col_nulls; col_nulls=0, which is true. But then, we attempt to divide by zero to discount the selectivity.
            psergei Sergei Petrunia added a comment - A possible fix: http://lists.askmonty.org/pipermail/commits/2015-February/007456.html . igor , please review.

            A different fix for this bug has actually been pushed into the 10.0 bzr tree:

                revno: 4544.1.1
                revision-id: igor@askmonty.org-20150206040908-23e1eht6sj59qiz3
                parent: knielsen@knielsen-hq.org-20141218105908-7n3szfd3xb5bm8gs
                committer: Igor Babaev <igor@askmonty.org>
                branch nick: maria-10.0
                timestamp: Thu 2015-02-05 20:09:08 -0800
                message:
                  Fixed bug mdev-7316.
                  The function table_cond_selectivity() should take into account that condition selectivity
                  for some fields can be set to 0.

            this fix hasn't been merged into the git tree, yet.

            psergei Sergei Petrunia added a comment - A different fix for this bug has actually been pushed into the 10.0 bzr tree: revno: 4544.1.1 revision-id: igor@askmonty.org-20150206040908-23e1eht6sj59qiz3 parent: knielsen@knielsen-hq.org-20141218105908-7n3szfd3xb5bm8gs committer: Igor Babaev <igor@askmonty.org> branch nick: maria-10.0 timestamp: Thu 2015-02-05 20:09:08 -0800 message: Fixed bug mdev-7316. The function table_cond_selectivity() should take into account that condition selectivity for some fields can be set to 0. this fix hasn't been merged into the git tree, yet.

            Actually it has been merged already. After 10.0.16

            psergei Sergei Petrunia added a comment - Actually it has been merged already. After 10.0.16

            People

              igor Igor Babaev (Inactive)
              elenst Elena Stepanova
              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.