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

Assertion `cond_selectivity <= 1.000000001' upon range query (actual value 1.33)

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 11.0(EOL)
    • 11.0.2
    • Optimizer
    • None

    Description

      --source include/have_innodb.inc
       
      CREATE TABLE t (id int, a int, b char(3), PRIMARY KEY (id), KEY idx (a,b)) ENGINE=InnoDB;
       
      INSERT INTO t VALUES
      (1,8,'UT'),(2,0,'NU'),(3,1,'SD'),(4,0,'QU'),(5,0,'FL'),(6,0,'ZR'),
      (7,3,'LA'),(8,5,'NU'),(9,0,'NU'),(10,0,'SD'),(11,0,'NU'),(12,1,'SD'),
      (13,0,'BD'),(14,0,'PA'),(15,0,'VT'),(16,4,'WA'),(17,0,'ME'),(18,6,'OH'),
      (19,0,'ME'),(20,4,'NU'),(21,0,'SC'),(22,0,'GA'),(23,1,'CO'),(24,0,'IL'),
      (25,0,'GA'),(26,0,'HI'),(27,0,'BU'),(28,0,'NU'),(29,7,'LA'),(30,0,'NU'),
      (31,0,'JR'),(32,6,'BR'),(33,0,'NU'),(34,6,'CO'),(35,7,'NU'),(36,2,'LA'),
      (37,0,'PR'),(38,1,'UT'),(39,2,'BR'),(40,1,'HI'),(41,0,'SD'),(42,0,'RI'),
      (43,2,'LA'),(44,0,'TN'),(45,4,'HI'),(46,0,'VT'),(47,1,'NU'),(48,0,'SC'),
      (49,0,'TX'),(50,8,'DC'),(51,4,'NU'),(52,0,'AL'),(53,0,'CO'),(54,9,'PR'),
      (55,0,'AR'),(56,0,'SD'),(57,0,'RI'),(58,0,'XE'),(59,0,'NU'),(60,4,'EL'),
      (61,2,'LA'),(62,5,'UT'),(63,3,'NU'),(64,0,'RI'),(65,1,'NU'),(66,0,'BR'),
      (67,3,'WA'),(68,0,'TN'),(69,3,'HI'),(70,0,'OH'),(71,8,'GA'),(72,6,'AL'),
      (73,6,'NU'),(74,1,'HI'),(75,5,'JR'),(76,3,'RI'),(77,0,'DC'),(78,0,'SC'),
      (79,0,'CO'),(80,2,'BO'),(81,8,'XE'),(82,1,'NU'),(83,0,'SD'),(84,0,'PA'),
      (85,5,'PA'),(86,0,'QU'),(87,0,'PA'),(88,0,'NU'),(89,0,'ND'),(90,0,'UT'),
      (91,0,'NU'),(92,0,'NU'),(93,6,'ZR'),(94,0,'NU'),(95,2,'EL'),(96,0,'NU'),
      (97,0,'RI'),(98,5,'DC'),(99,7,'JR'),(100,5,'CO'),(101,0,'UT'),(102,0,'QU'),
      (103,0,'NU'),(104,0,'GA'),(105,7,'AK'),(106,0,'ZR'),(107,0,'YT'),(108,0,'MD'),
      (109,0,'NU'),(110,1,'EL'),(111,0,'ME'),(112,0,'VT'),(113,2,'NU'),(114,0,'CO'),
      (115,5,'TN'),(116,0,'OH'),(117,0,'GA'),(118,9,'GA'),(119,0,'CO'),(120,0,'AL'),
      (121,0,'NU'),(122,2,'NE'),(123,2,'TX'),(124,3,'CO'),(125,0,'TN'),(126,0,'WA'),
      (127,0,'NE'),(128,6,'TN'),(129,0,'BR'),(130,0,'ID'),(131,0,'NU'),(132,2,'EL'),
      (133,0,'PR'),(134,0,'NU'),(135,1,'AZ'),(136,7,'EL'),(137,0,'TN'),(138,0,'PA'),
      (139,5,'QU'),(140,0,'AR'),(141,0,'DC'),(142,2,'WA'),(143,7,'OH'),(144,2,'CO'),
      (145,6,'NU'),(146,9,'FL'),(147,0,'HI'),(148,0,'WA'),(149,1,'BR'),(150,3,'QU');
       
      ANALYZE TABLE t PERSISTENT FOR ALL; # Optional, fails either way
       
      SELECT id, MIN(id) FROM t
      WHERE (b > 'TX' OR b BETWEEN 'NE' AND 'SC') AND id IN (1,7,8) AND a = 5
      GROUP BY id;
      

      11.0 368dd22a

      mariadbd: /data/src/11.0/sql/sql_select.cc:31338: bool get_range_limit_read_cost(const POSITION*, const TABLE*, uint, ha_rows, ha_rows, double*, double*): Assertion `cond_selectivity <= 1.000000001' failed.
      230512 22:04:12 [ERROR] mysqld got signal 6 ;
       
      #9  0x00007fbe85453df2 in __GI___assert_fail (assertion=0x5558d62daa60 "cond_selectivity <= 1.000000001", file=0x5558d62c8ae0 "/data/src/11.0/sql/sql_select.cc", line=31338, function=0x5558d62daaa0 "bool get_range_limit_read_cost(const POSITION*, const TABLE*, uint, ha_rows, ha_rows, double*, double*)") at ./assert/assert.c:101
      #10 0x00005558d43ee056 in get_range_limit_read_cost (pos=0x629000349080, table=0x6190000f7398, keynr=0, rows_limit_arg=150, rows_to_scan=150, read_cost=0x7fbe710a9ba0, read_rows=0x7fbe710a9bc0) at /data/src/11.0/sql/sql_select.cc:31338
      #11 0x00005558d43eff8c in test_if_cheaper_ordering (tab=0x629000349fb0, order=0x6290000fc8a0, table=0x6190000f7398, usable_keys=..., ref_key=1, select_limit_arg=150, new_key=0x7fbe710a9ea0, new_key_direction=0x7fbe710a9ed0, new_select_limit=0x7fbe710a9f60, new_used_key_parts=0x7fbe710a9eb0, saved_best_key_parts=0x7fbe710a9ec0) at /data/src/11.0/sql/sql_select.cc:31698
      #12 0x00005558d43c7522 in test_if_skip_sort_order (tab=0x629000349fb0, order=0x6290000fc8a0, select_limit=18446744073709551615, no_changes=false, map=0x6190000f7428) at /data/src/11.0/sql/sql_select.cc:26252
      #13 0x00005558d431e286 in JOIN::optimize_stage2 (this=0x6290000fd2f0) at /data/src/11.0/sql/sql_select.cc:3287
      #14 0x00005558d43170f3 in JOIN::optimize_inner (this=0x6290000fd2f0) at /data/src/11.0/sql/sql_select.cc:2603
      #15 0x00005558d430fe47 in JOIN::optimize (this=0x6290000fd2f0) at /data/src/11.0/sql/sql_select.cc:1905
      #16 0x00005558d4331ab3 in mysql_select (thd=0x62b00017a218, tables=0x6290000faca8, fields=..., conds=0x6290000fc210, og_num=1, order=0x0, group=0x6290000fc8a0, having=0x0, proc_param=0x0, select_options=2164525824, result=0x6290000fd2c0, unit=0x62b00017e660, select_lex=0x6290000fa380) at /data/src/11.0/sql/sql_select.cc:5144
      #17 0x00005558d43016e8 in handle_select (thd=0x62b00017a218, lex=0x62b00017e588, result=0x6290000fd2c0, setup_tables_done_option=0) at /data/src/11.0/sql/sql_select.cc:616
      #18 0x00005558d4226ac4 in execute_sqlcom_select (thd=0x62b00017a218, all_tables=0x6290000faca8) at /data/src/11.0/sql/sql_parse.cc:6279
      #19 0x00005558d4214eeb in mysql_execute_command (thd=0x62b00017a218, is_called_from_prepared_stmt=false) at /data/src/11.0/sql/sql_parse.cc:3949
      #20 0x00005558d4231574 in mysql_parse (thd=0x62b00017a218, rawbuf=0x6290000fa238 "SELECT id, MIN(id) FROM t\nWHERE (b > 'TX' OR b BETWEEN 'NE' AND 'SC') AND id IN (1,7,8) AND a = 5\nGROUP BY id", length=109, parser_state=0x7fbe710aba20) at /data/src/11.0/sql/sql_parse.cc:8014
      #21 0x00005558d4207460 in dispatch_command (command=COM_QUERY, thd=0x62b00017a218, packet=0x629000285219 "SELECT id, MIN(id) FROM t\nWHERE (b > 'TX' OR b BETWEEN 'NE' AND 'SC') AND id IN (1,7,8) AND a = 5\nGROUP BY id", packet_length=109, blocking=true) at /data/src/11.0/sql/sql_parse.cc:1894
      #22 0x00005558d4204183 in do_command (thd=0x62b00017a218, blocking=true) at /data/src/11.0/sql/sql_parse.cc:1407
      #23 0x00005558d46cc00c in do_handle_one_connection (connect=0x608000003938, put_in_cache=true) at /data/src/11.0/sql/sql_connect.cc:1416
      #24 0x00005558d46cb9cd in handle_one_connection (arg=0x6080000038b8) at /data/src/11.0/sql/sql_connect.cc:1318
      #25 0x00005558d52c0324 in pfs_spawn_thread (arg=0x617000008218) at /data/src/11.0/storage/perfschema/pfs.cc:2201
      #26 0x00007fbe854a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
      #27 0x00007fbe855285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
      

      (gdb) f 10
      #10 0x0000556827d5a056 in get_range_limit_read_cost (pos=0x629000349080, table=0x6190000f7398, keynr=0, rows_limit_arg=150, 
          rows_to_scan=150, read_cost=0x7f6fffc6dba0, read_rows=0x7f6fffc6dbc0) at /data/src/11.0/sql/sql_select.cc:31338
      31338	      DBUG_ASSERT(cond_selectivity <= 1.000000001);
      (gdb) p cond_selectivity
      $1 = 1.3333333333333333
      

      Attachments

        Issue Links

          Activity

            This was caused of two minor issues:

            • get_quick_record_count() returned the number of rows for range with
              least cost, when it should have returned the minum number of rows
              for any range.
            • When changing REF to RANGE, we also changed records_out, which
              should not be done (number of records in the result will not
              change).
            monty Michael Widenius added a comment - This was caused of two minor issues: get_quick_record_count() returned the number of rows for range with least cost, when it should have returned the minum number of rows for any range. When changing REF to RANGE, we also changed records_out, which should not be done (number of records in the result will not change).

            Pushed to 11.0

            monty Michael Widenius added a comment - Pushed to 11.0

            People

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