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

Assertion `((best.records_out) == 0.0 && (best.records) == 0.0) || (best.records_out)/(best.records) < 1.0000001' failed

Details

    Description

      CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a), KEY(b)) ENGINE=Aria;
      		 
      INSERT INTO t1 VALUES
      		 
      (1,13),(2,22),(3,8),(4,88),(5,6),(7,21),(9,64),(10,14),(11,15),(12,8),
      		 
      (6,20),(8,39),(13,0),(14,3),(15,54),(16,85),(17,1),(18,1),(19,0),(20,0);
      		 
       
      		 
      CREATE TABLE t2 (c INT) ENGINE=Aria;
      		 
      INSERT INTO t2 VALUES (1),(2),(3);
      		 
       
      		 
      SELECT a FROM t1 JOIN t2 WHERE a = b AND c <> 7 GROUP BY a HAVING a != 6 AND a <= 9;
      		 
       
      		 
      # Cleanup
      		 
      DROP TABLE t1, t2;

      bb-11.0-release 4a04f4f79378

      		 
      mariadbd: /data/src/bb-11.0-release/sql/sql_select.cc:9557: void best_access_path(JOIN*, JOIN_TAB*, table_map, const POSITION*, uint, bool, double, POSITION*, POSITION*): Assertion `((best.records_out) == 0.0 && (best.records) == 0.0) || (best.records_out)/(best.records) < 1.0000001' failed.
      		 
      230602 19:59:11 [ERROR] mysqld got signal 6 ;
      		 
       
      		 
      #9  0x00007febcea53df2 in __GI___assert_fail (assertion=0x5599b4e9bb00 "((best.records_out) == 0.0 && (best.records) == 0.0) || (best.records_out)/(best.records) < 1.0000001", file=0x5599b4e955c0 "/data/src/bb-11.0-release/sql/sql_select.cc", line=9557, function=0x5599b4e9b020 "void best_access_path(JOIN*, JOIN_TAB*, table_map, const POSITION*, uint, bool, double, POSITION*, POSITION*)") at ./assert/assert.c:101
      		 
      #10 0x00005599b2f1830b in best_access_path (join=0x629000285238, s=0x629000285da0, remaining_tables=1, join_positions=0x629000286800, idx=1, disable_jbuf=false, record_count=3, pos=0x629000287138, loose_scan_pos=0x629000287288) at /data/src/bb-11.0-release/sql/sql_select.cc:9557
      		 
      #11 0x00005599b2f208c1 in get_costs_for_tables (join=0x629000285238, remaining_tables=1, idx=1, record_count=3, trace_one_table=0x7febc73bc2c0, pos=0x6290002867c0, store_position=0x7febc73bc1e0, allowed_tables=0x7febc73bc220, stop_on_eq_ref=false) at /data/src/bb-11.0-release/sql/sql_select.cc:11074
      		 
      #12 0x00005599b2f21b80 in best_extension_by_limited_search (join=0x629000285238, remaining_tables=1, idx=1, record_count=3, read_time=0.011306746000000003, search_depth=61, use_cond_selectivity=4, processed_eq_ref_tables=0x7febc73bc5e0) at /data/src/bb-11.0-release/sql/sql_select.cc:11331
      		 
      #13 0x00005599b2f23611 in best_extension_by_limited_search (join=0x629000285238, remaining_tables=3, idx=0, record_count=1, read_time=0, search_depth=62, use_cond_selectivity=4, processed_eq_ref_tables=0x7febc73bc8c0) at /data/src/bb-11.0-release/sql/sql_select.cc:11557
      		 
      #14 0x00005599b2f1c5a2 in greedy_search (join=0x629000285238, remaining_tables=3, search_depth=62, use_cond_selectivity=4) at /data/src/bb-11.0-release/sql/sql_select.cc:10302
      		 
      #15 0x00005599b2f1a000 in choose_plan (join=0x629000285238, join_tables=3, emb_sjm_nest=0x0) at /data/src/bb-11.0-release/sql/sql_select.cc:9821
      		 
      #16 0x00005599b2f006db in make_join_statistics (join=0x629000285238, tables_list=..., keyuse_array=0x6290002855a0) at /data/src/bb-11.0-release/sql/sql_select.cc:6098
      		 
      #17 0x00005599b2edbfa4 in JOIN::optimize_inner (this=0x629000285238) at /data/src/bb-11.0-release/sql/sql_select.cc:2580
      		 
      #18 0x00005599b2ed5011 in JOIN::optimize (this=0x629000285238) at /data/src/bb-11.0-release/sql/sql_select.cc:1908
      		 
      #19 0x00005599b2ef6c83 in mysql_select (thd=0x62b00007e218, tables=0x6290000e69b8, fields=..., conds=0x6290000e8920, og_num=1, order=0x0, group=0x6290000e8b90, having=0x6290000e93c0, proc_param=0x0, select_options=2164525824, result=0x6290000e9e58, unit=0x62b000082660, select_lex=0x6290000e6350) at /data/src/bb-11.0-release/sql/sql_select.cc:5147
      		 
      #20 0x00005599b2ec68b2 in handle_select (thd=0x62b00007e218, lex=0x62b000082588, result=0x6290000e9e58, setup_tables_done_option=0) at /data/src/bb-11.0-release/sql/sql_select.cc:619
      		 
      #21 0x00005599b2deba60 in execute_sqlcom_select (thd=0x62b00007e218, all_tables=0x6290000e69b8) at /data/src/bb-11.0-release/sql/sql_parse.cc:6279
      		 
      #22 0x00005599b2dd9e87 in mysql_execute_command (thd=0x62b00007e218, is_called_from_prepared_stmt=false) at /data/src/bb-11.0-release/sql/sql_parse.cc:3949
      		 
      #23 0x00005599b2df6510 in mysql_parse (thd=0x62b00007e218, rawbuf=0x6290000e6238 "SELECT a FROM t1 JOIN t2 WHERE a = b AND c <> 7 GROUP BY a HAVING a != 6 AND a <= 9", length=83, parser_state=0x7febc73bea20) at /data/src/bb-11.0-release/sql/sql_parse.cc:8014
      		 
      #24 0x00005599b2dcc3fc in dispatch_command (command=COM_QUERY, thd=0x62b00007e218, packet=0x629000258219 "SELECT a FROM t1 JOIN t2 WHERE a = b AND c <> 7 GROUP BY a HAVING a != 6 AND a <= 9", packet_length=83, blocking=true) at /data/src/bb-11.0-release/sql/sql_parse.cc:1894
      		 
      #25 0x00005599b2dc911f in do_command (thd=0x62b00007e218, blocking=true) at /data/src/bb-11.0-release/sql/sql_parse.cc:1407
      		 
      #26 0x00005599b3291ba2 in do_handle_one_connection (connect=0x608000002cb8, put_in_cache=true) at /data/src/bb-11.0-release/sql/sql_connect.cc:1416
      		 
      #27 0x00005599b3291563 in handle_one_connection (arg=0x608000002c38) at /data/src/bb-11.0-release/sql/sql_connect.cc:1318
      		 
      #28 0x00005599b3e888be in pfs_spawn_thread (arg=0x617000005b98) at /data/src/bb-11.0-release/storage/perfschema/pfs.cc:2201
      		 
      #29 0x00007febceaa7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
      		 
      #30 0x00007febceb285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

      The failure started happening on 11.0 after this commit:

      commit 209fed8eeda06d16de9b563efa6d9ff61b41e534
      		 
      Author: Monty
      		 
      Date:   Sat May 27 12:18:49 2023 +0300
      		 
       
      		 
          MDEV-31258 Assertion `cond_selectivity <= 1.000000001' upon range query

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          monty Michael Widenius added a comment -

          I was not able to repeat this with latest 11.0
          Now checking the release branch: bb-11.0-release 4a04f4f79378

          monty Michael Widenius added a comment - I was not able to repeat this with latest 11.0 Now checking the release branch: bb-11.0-release 4a04f4f79378
          monty Michael Widenius added a comment -

          I was not able to repeat it with the release branch either.

          Just in the case someone is trying to find out what went wrong:
          In the release branch the query has the following plan:

          explain SELECT a FROM t1 JOIN t2 WHERE a = b AND c <> 7 GROUP BY a HAVING a != 6;
          id select_type table type possible_keys key key_len ref rows Extra
          1 SIMPLE t1 range PRIMARY,b b 5 NULL 20 Using where; Using temporary; Using filesort
          1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)

          monty Michael Widenius added a comment - I was not able to repeat it with the release branch either. Just in the case someone is trying to find out what went wrong: In the release branch the query has the following plan: explain SELECT a FROM t1 JOIN t2 WHERE a = b AND c <> 7 GROUP BY a HAVING a != 6; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,b b 5 NULL 20 Using where; Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
          elenst Elena Stepanova added a comment - - edited

          Still reproducible on bb-11.0-release 0005f2f06c8e1aea4915887decad67885108a929 (current top of the branch).
          Please note that judging by the EXPLAIN output above, you've missed the AND clause in HAVING, so your plan is different.

          explain SELECT a FROM t1 JOIN t2 WHERE a = b AND c <> 7 GROUP BY a HAVING a != 6 AND a <= 9;
          		 
          id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
          		 
          1	SIMPLE	t1	range	PRIMARY,b	PRIMARY	4	NULL	9	Using index condition; Using where; Using temporary; Using filesort
          		 
          1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)

          elenst Elena Stepanova added a comment - - edited Still reproducible on bb-11.0-release 0005f2f06c8e1aea4915887decad67885108a929 (current top of the branch). Please note that judging by the EXPLAIN output above, you've missed the AND clause in HAVING, so your plan is different. explain SELECT a FROM t1 JOIN t2 WHERE a = b AND c <> 7 GROUP BY a HAVING a != 6 AND a <= 9; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY ,b PRIMARY 4 NULL 9 Using index condition; Using where ; Using temporary ; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where ; Using join buffer (flat, BNL join )
          monty Michael Widenius added a comment -

          Fixed as part of MDEV-31356

          monty Michael Widenius added a comment - Fixed as part of MDEV-31356

          People

            monty Michael Widenius
            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.