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

DOUBLE_PREC_HB histogram has poor estimates for BIT columns

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Minor
    • Resolution: Unresolved
    • N/A
    • 10.11
    • Optimizer
    • None

    Description

      EDIT : The issue is actually not what is described in the report. See the comments!

      CREATE TABLE t (a varchar(1), b bit(64)) ENGINE=MyISAM;
       
      INSERT INTO t VALUES
        ('n',x'30'),('v',x'45'),('n',x'01'),('k',x'BE562B1A99001918'),
        ('t',x'30'),('n',x'30'),('n',x'30'),('f',x'21'),('y',x'10'),
        ('n',x'01'),('n',x'02'),('q',x'11'),('i',x'30'),('d',x'E2'),
        ('t',x'61'),('r',x'30'),('q',x'81'),('c',x'02'),('o',x'30'),
        ('v',x'30'),('n',x'02'),('c',x'01'),('g',x'13'),('g',x'22'),
        ('q',x'81'),('n',x'30'),('x',x'6C'),('e',x'15'),('n',x'23'),
        ('z',x'30'),('n',x'01'),('d',x'61'),('y',x'43'),('o',x'F9');
       
      set histogram_type= DOUBLE_PREC_HB;
      analyze table t persistent for all;
      ANALYZE FORMAT=JSON SELECT b FROM t WHERE 'w' NOT BETWEEN a AND 'y' AND b < b'1000000';
       
      set histogram_type= JSON_HB;
      analyze table t persistent for all;
      ANALYZE FORMAT=JSON SELECT b FROM t WHERE 'w' NOT BETWEEN a AND 'y' AND b < b'1000000';
       
      # Cleanup
      DROP TABLE t;
      

      DOUBLE_PREC_HB preview-10.8-MDEV-26519-json-histograms 9a86900b

              "table": {
                "table_name": "t",
                "access_type": "ALL",
                "r_loops": 1,
                "rows": 34,
                "r_rows": 34,
                "r_table_time_ms": 0.003460502,
                "r_other_time_ms": 0.006840761,
                "filtered": 5.555588245,
                "r_filtered": 5.882352941,
                "attached_condition": "'w' not between t.a and 'y' and t.b < 0x40"
              }
      

      JSON_HB

              "table": {
                "table_name": "t",
                "access_type": "ALL",
                "r_loops": 1,
                "rows": 34,
                "r_rows": 34,
                "r_table_time_ms": 0.003239832,
                "r_other_time_ms": 0.004062328,
                "filtered": 70.58823395,
                "r_filtered": 5.882352941,
                "attached_condition": "'w' not between t.a and 'y' and t.b < 0x40"
              }
      

      The total count is 34 rows and the result set is 2 rows.

      Also reproducible with InnoDB, Aria.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.