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

A new assertion added to check validity of calculated selectivity values fails

    XMLWordPrintable

Details

    Description

      After adding the assertion

       
      DBUG_ASSERT(0.0 < sel && sel <= 1.0);
      

      to check the validity of selectivity values returned by the function
      table_cond_selectivity() the following test case from selectivity.test fails:

      CREATE TABLE t1 (a varchar(16), b int, PRIMARY KEY(a), KEY(b));
      INSERT INTO t1 VALUES
        ('USAChinese',10), ('USAEnglish',20), ('USAFrench',30);
       
      CREATE TABLE t2 (i int);
      INSERT INTO t2 VALUES
        (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4);
      ANALYZE TABLE t1, t2;
       
      set use_stat_tables='preferably';
      set optimizer_use_condition_selectivity=3;
       
      EXPLAIN EXTENDED
      SELECT * FROM t1, t2
        WHERE  a <> 'USARussian' AND b IS NULL;
      

      The added assertion also fails for the test cases:

      set @@optimizer_use_condition_selectivity=2;
       
      set names utf8;
       
      CREATE DATABASE world;
       
      use world;
       
      --source include/world_schema.inc
       
      --disable_query_log
      --disable_result_log
      --disable_warnings
      --source include/world.inc
      --enable_warnings
      --enable_result_log
      --enable_query_log
       
      CREATE INDEX Name ON City(Name);
      CREATE INDEX CountryPopulation ON City(Country,Population);
      CREATE INDEX CountryName ON City(Country,Name);
       
      --disable_query_log
      --disable_result_log
      --disable_warnings
      ANALYZE TABLE City;
      --enable_warnings
      --enable_result_log
      --enable_query_log
       
      set @@optimizer_use_condition_selectivity=2;
       
      EXPLAIN
      SELECT * FROM City WHERE Country='FIN';
       
      DROP DATABASE world;
       
      CREATE TABLE t1 (
        a INT,
        b INT NOT NULL,
        c char(100),
        KEY (b, c),
        KEY (b, a, c)
      )
      DEFAULT CHARSET = utf8;
       
      INSERT INTO t1 VALUES 
      (1,  1, 1),
      (2,  2, 2),
      (3,  3, 3),
      (4,  4, 4),
      (5,  5, 5),
      (6,  6, 6),
      (7,  7, 7),
      (8,  8, 8),
      (9,  9, 9);
       
      INSERT INTO t1 SELECT a + 10,  b, c FROM t1;
      INSERT INTO t1 SELECT a + 20,  b, c FROM t1;
      INSERT INTO t1 SELECT a + 40,  b, c FROM t1;
      INSERT INTO t1 SELECT a + 80,  b, c FROM t1;
      INSERT INTO t1 SELECT a + 160, b, c FROM t1;
      INSERT INTO t1 SELECT a + 320, b, c FROM t1;
      INSERT INTO t1 SELECT a + 640, b, c FROM t1;
      INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80;
       
      EXPLAIN
      SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
      SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
       
      set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
       
      DROP TABLE t1;
      

      The latter two test cases are variants of the test cases taken from range_vs_index_merge.test and order_by.test correspondingly.
      They has to be run with the setting

      set @@optimizer_use_condition_selectivity=2;
      

      to make the added assertion fire.

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              igor Igor Babaev
              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.