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

Assertion `rnd_records <= s->found_records' failed in best_access_path

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • N/A
    • 11.0.1
    • Optimizer
    • None

    Description

      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      		 
      INSERT INTO t1 VALUES (1),(2);
      		 
       
      		 
      CREATE TABLE t2 (b VARCHAR(1), c INT, d VARCHAR(1), e VARCHAR(1), KEY(b), KEY(d), KEY(e)) ENGINE=MyISAM;
      		 
      INSERT INTO t2 VALUES ('x',0,'-','-'),
      		 
      ('x',0,'-','-'),('x',5,'-','-'),('x',0,'-','-'),('x',0,'-','-'),
      		 
      ('x',0,'-','-'),('x',0,'w','-'),('x',0,'-','-'),('x',0,'-','-'),
      		 
      ('x',0,'-','-'),('x',0,'-','-'),('x',0,'-','u'),('x',0,'-','-'),
      		 
      ('x',0,'-','-'),('x',0,'-','t'),('x',0,'-','-'),('x',0,'-','-'),
      		 
      ('x',0,'-','-'),('x',0,'-','-'),('x',0,'-','-'),('x',0,'-','p'),
      		 
      ('x',0,'z','-'),('x',0,'-','-'),('x',0,'-','-'),('x',0,'-','v');
      		 
       
      		 
      SET JOIN_CACHE_LEVEL= 3;
      		 
      SELECT * FROM t1 JOIN t2 ON t1.a = t2.c WHERE t2.b IN ('o') OR t2.e >= 'f' OR t2.d > 'p';

      bb-11.0 23808cd2

      		 
      mariadbd: /data/src/bb-11.0/sql/sql_select.cc:8806: void best_access_path(JOIN*, JOIN_TAB*, table_map, const POSITION*, uint, bool, double, POSITION*, POSITION*): Assertion `rnd_records <= s->found_records' failed.
      		 
      230131 22:19:01 [ERROR] mysqld got signal 6 ;
      		 
       
      		 
      #7  0x00007ff3d9a99662 in __GI___assert_fail (assertion=assertion@entry=0x55683e7e5ac0 "rnd_records <= s->found_records", file=file@entry=0x55683e7ddce0 "/data/src/bb-11.0/sql/sql_select.cc", line=line@entry=8806, function=function@entry=0x55683e7e57a0 "void best_access_path(JOIN*, JOIN_TAB*, table_map, const POSITION*, uint, bool, double, POSITION*, POSITION*)") at assert.c:101
      		 
      #8  0x000055683bccb39a in best_access_path (join=0x62900026c238, s=s@entry=0x62900026d9c0, remaining_tables=remaining_tables@entry=2, join_positions=<optimized out>, idx=idx@entry=1, disable_jbuf=<optimized out>, record_count=<optimized out>, record_count@entry=2, pos=<optimized out>, loose_scan_pos=<optimized out>) at /data/src/bb-11.0/sql/sql_select.cc:8806
      		 
      #9  0x000055683bcd111d in get_costs_for_tables (join=join@entry=0x62900026c238, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=2, trace_one_table=trace_one_table@entry=0x7ff3d0926110, pos=<optimized out>, pos@entry=0x62900026dfb0, store_position=store_position@entry=0x7ff3d0926030, allowed_tables=<optimized out>, stop_on_eq_ref=<optimized out>) at /data/src/bb-11.0/sql/sql_select.cc:10726
      		 
      #10 0x000055683bcf4ead in best_extension_by_limited_search (join=join@entry=0x62900026c238, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=2, read_time=<optimized out>, search_depth=search_depth@entry=61, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x7ff3d09263e0) at /data/src/bb-11.0/sql/sql_select.cc:10983
      		 
      #11 0x000055683bcf8fff in best_extension_by_limited_search (join=join@entry=0x62900026c238, remaining_tables=remaining_tables@entry=3, idx=idx@entry=0, record_count=record_count@entry=1, read_time=read_time@entry=0, search_depth=search_depth@entry=62, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x7ff3d09266a0) at /data/src/bb-11.0/sql/sql_select.cc:11208
      		 
      #12 0x000055683bcfc8b4 in greedy_search (join=join@entry=0x62900026c238, remaining_tables=remaining_tables@entry=3, search_depth=search_depth@entry=62, use_cond_selectivity=use_cond_selectivity@entry=4) at /data/src/bb-11.0/sql/sql_select.cc:9954
      		 
      #13 0x000055683bcfd5a1 in choose_plan (join=join@entry=0x62900026c238, join_tables=<optimized out>, emb_sjm_nest=emb_sjm_nest@entry=0x0) at /data/src/bb-11.0/sql/sql_select.cc:9475
      		 
      #14 0x000055683bd9d4f8 in make_join_statistics (join=join@entry=0x62900026c238, tables_list=..., keyuse_array=keyuse_array@entry=0x62900026c590) at /data/src/bb-11.0/sql/sql_select.cc:6055
      		 
      #15 0x000055683bdb5243 in JOIN::optimize_inner (this=this@entry=0x62900026c238) at /data/src/bb-11.0/sql/sql_select.cc:2571
      		 
      #16 0x000055683bdb5aa5 in JOIN::optimize (this=this@entry=0x62900026c238) at /data/src/bb-11.0/sql/sql_select.cc:1899
      		 
      #17 0x000055683bdb7684 in mysql_select (thd=thd@entry=0x62b00007e218, tables=<optimized out>, fields=..., conds=<optimized out>, og_num=<optimized out>, order=<optimized out>, group=<optimized out>, having=<optimized out>, proc_param=<optimized out>, select_options=<optimized out>, result=<optimized out>, unit=<optimized out>, select_lex=<optimized out>) at /data/src/bb-11.0/sql/sql_select.cc:5114
      		 
      #18 0x000055683bdb8433 in handle_select (thd=thd@entry=0x62b00007e218, lex=lex@entry=0x62b000082588, result=result@entry=0x6290000e9c48, setup_tables_done_option=setup_tables_done_option@entry=0) at /data/src/bb-11.0/sql/sql_select.cc:609
      		 
      #19 0x000055683bb3ad33 in execute_sqlcom_select (thd=thd@entry=0x62b00007e218, all_tables=<optimized out>) at /data/src/bb-11.0/sql/sql_parse.cc:6263
      		 
      #20 0x000055683bb6d427 in mysql_execute_command (thd=thd@entry=0x62b00007e218, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /data/src/bb-11.0/sql/sql_parse.cc:3947
      		 
      #21 0x000055683bb8237b in mysql_parse (thd=thd@entry=0x62b00007e218, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7ff3d0928a60) at /data/src/bb-11.0/sql/sql_parse.cc:7998
      		 
      #22 0x000055683bb8a409 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x62b00007e218, packet=packet@entry=0x629000253219 "SELECT * FROM t1 JOIN t2 ON t1.a = t2.c WHERE t2.b IN ('o') OR t2.e >= 'f' OR t2.d > 'p'", packet_length=packet_length@entry=88, blocking=blocking@entry=true) at /data/src/bb-11.0/sql/sql_parse.cc:1894
      		 
      #23 0x000055683bb92724 in do_command (thd=0x62b00007e218, blocking=blocking@entry=true) at /data/src/bb-11.0/sql/sql_parse.cc:1407
      		 
      #24 0x000055683c157b87 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x6080000021b8, put_in_cache=put_in_cache@entry=true) at /data/src/bb-11.0/sql/sql_connect.cc:1415
      		 
      #25 0x000055683c15849c in handle_one_connection (arg=arg@entry=0x6080000021b8) at /data/src/bb-11.0/sql/sql_connect.cc:1317
      		 
      #26 0x000055683d3432ae in pfs_spawn_thread (arg=0x617000004698) at /data/src/bb-11.0/storage/perfschema/pfs.cc:2201
      		 
      #27 0x00007ff3d9f77ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
      		 
      #28 0x00007ff3d9b64aef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-26974 Improve selectivity and related costs in optimizer

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia added a comment - - edited

            We hit the assert in best_access_path

                DBUG_ASSERT(rnd_records <= s->found_records);

            Because:

            (gdb) p dbl_records
            		 
            $28 = 7.0000000000000009
            		 
            (gdb) p s->found_records
            		 
            $30 = 7

            the 7.0000... number comes from apply_selectivity_for_table()

            p table_records
            		 
            $31 = 25
            		 
            (gdb) p sel
            		 
            $32 = 0.28000000000000003


                dbl_records= table_records * sel;

            Looks like a rounding error: all numbers are double except for
            JOIN_TAB::found_records which is ha_rows.

            The value of cond_selectivity=0.280000...3 comes from here:

            calculate_cond_selectivity_for_table:

              original_selectivity= (table->opt_range_condition_rows /
            		 
                                     table_records);

            here

            (gdb) p table->opt_range_condition_rows  -- this is ha_rows.
            		 
            $37 = 7
            		 
            (gdb) p table_records  -- this is double
            		 
            $38 = 25
            		 
            (gdb) p original_selectivity
            		 
            $42 = 0.28000000000000003

            psergei Sergei Petrunia added a comment - - edited We hit the assert in best_access_path DBUG_ASSERT(rnd_records <= s->found_records); Because: (gdb) p dbl_records $28 = 7.0000000000000009 (gdb) p s->found_records $30 = 7 the 7.0000... number comes from apply_selectivity_for_table() p table_records $31 = 25 (gdb) p sel $32 = 0.28000000000000003 dbl_records= table_records * sel; Looks like a rounding error: all numbers are double except for JOIN_TAB::found_records which is ha_rows. The value of cond_selectivity=0.280000...3 comes from here: calculate_cond_selectivity_for_table : original_selectivity= (table->opt_range_condition_rows / table_records); here (gdb) p table->opt_range_condition_rows -- this is ha_rows. $37 = 7 (gdb) p table_records -- this is double $38 = 25 (gdb) p original_selectivity $42 = 0.28000000000000003
            psergei Sergei Petrunia added a comment - - edited

            ... just changing that to double is intrusive, because that value is taken from check_quick_select() which also returns ha_rows. check_quick_select() gets the value from multi_range_read_info_const() and so forth.

            psergei Sergei Petrunia added a comment - - edited ... just changing that to double is intrusive, because that value is taken from check_quick_select() which also returns ha_rows. check_quick_select() gets the value from multi_range_read_info_const() and so forth.
            psergei Sergei Petrunia added a comment -

            Fixed in bb-11.0 branch.

            psergei Sergei Petrunia added a comment - Fixed in bb-11.0 branch.

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.